The main reason why Microsoft introduced table variable in SQL Server 2000 is to reduce stored procedure recompilations (a recompilation occurs when the stored procedure execution plan is recreated). Table variables also have a well defined scope (the current procedure or function) and they induce less logging and locking (because their transaction last for a […]
Posts Tagged ‘indexes’
September 13, 2011 by Francois · 2 Comments
Filed under: Performance, TSQL
Social Tagging: Index • indexes • OPTION (RECOMPILE) • query plan • set statistics profile • statistics • sub-optimal query plan • table variables • temp tables
Filed under: Performance, TSQL
Social Tagging: Index • indexes • OPTION (RECOMPILE) • query plan • set statistics profile • statistics • sub-optimal query plan • table variables • temp tables
May 19, 2011 by Francois · Leave a Comment
Filed under: Performance, SSIS
Social Tagging: covering index • indexes • io statistics • Lookup • Non Cached Lookup • Partial Lookup • SSIS
Filed under: Performance, SSIS
Social Tagging: covering index • indexes • io statistics • Lookup • Non Cached Lookup • Partial Lookup • SSIS
The vast majority of the time, you will use SSIS Lookup component in Full Cache mode. This mode is the fastest because it queries the database only once (before the data flow starts) and apply hashing in order to do high-performance comparisons. Sometimes however you will have to use Non Cached lookups. For example if […]
RSS