VB and VBA Users Source Code: Creating Indexes on Temporary Tables
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Creating Indexes on Temporary Tables
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Monday, October 14, 2002
Hits:
777
Category:
Database/SQL/ADO
Article:
The sensible use of indexes is the one of best post release optimisations available to a developer/DBA. However, a common misconception is that indexes created on temporary tables used in stored procedures will not be used by the optimiser. This is not the case. The index will be used providing the index is not applied until the temporary table is populated. Doing this will mean that meaningful statistics exist on the temporary table when the index is applied. Listed below are two methods of applying indexs. Method A. incorrectly creates the index before the table is populated. Not only will this index not be used, but it will also add an overhead to the population of the temporary table. Whereas Method B. creates the index after populating the table, this optimises the insert and will result in the index being used in subsequent where clauses and select statements. if object_id("TestProc") <> 0 drop proc TestProc go create proc TestProc as begin create table #TestTable ( Username char(30) NOT NULL, Age int NOT NULL, DepartmentID int NOT NULL ) --Limit the no. of rows populated into test table set rowcount 10000 --Method A. Incorrectly creates the temp. index before populating the table create index #user_idx on #TestTable(Username) --Insert into the temp. table insert #TestTable select * from MyTestUsers --Method B. Correctly creates the temp. index before populating the table create index #user_idx on #TestTable(Username) --Query the temp. table select * from #TestTable where Username like "Andrew%" drop table #TestTable end go --Test the stored procedures set showplan on go exec TestProc go set showplan off go
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
