Create Indexes not utilitized within the same SQL script

[BrooksMT]

I have a script which contains a "work" table that was being generated as it ran (via INTO).

Later queries which used the table ran too slow so I added CREATE INDEX statements on the work table.

This had no effect. So I made the work table permanent and added the indexes before the script runs, deleting all rows at the beginning of the script.

Now I see the performance improvement.

So this leads to the conclusion that the script is being compiled/pre-processed before it runs and that indexes created during the run are not used.

Can someone with specific knowledge confirm this?

The script is being run by an Execute SQL task in SSIS if that makes a difference.

Thx.

 

[gmmastros]

Are you creating this "work" table as a real table within your database, or is it a temp table (prefaced with the # character)?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BrooksMT]

Right now it is a real table.

Apparently I need to read about temp tables.

 

[gmmastros]

Temp tables are usually better. And it's super easy to change your code. Just put a # symbol in front of the table name where it appears in your code.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BrooksMT]

1. So what is the advantage of using temp tables (#) vs using normal tables and just DROPping them at the end of the script?

2. With temp tables won't I still have the issue of adding indexes to them not using utilized by queries later in the script?

 

[gmmastros]

1. If you have multiple users running the same code simultaneously, the same "real" table would be used by both processes. If you use a temp table, each process would get their own copy of the temp table to work with.

2. You won't have the same issue with the indexes because the query plan takes this in to account with temp tables. Now... that's not to say your query WILL use the index you created. In fact, it will only use the index if it decides it would be faster to use it. There are various ways to write a query such that an index won't be used.

My suggestion is this... change your code to use a temp table. Then run the code. If the performance is no better, then I would encourage you to post the code so we can take a look at it.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom