Index creation after Insert for Temporary Table inside a SP

Question

Index creation after Insert for Temporary Table inside a SP

ganeshc27

SSC Eights!

Points: 972
More actions
November 15, 2013 at 4:19 am

#280210

Hi,
I need a suggestion for one of the scenarios i'm facing...
I've a SP in which i'm creating a temporary table.
100 thousand records gets inserted into this temporary table.
Once the insert is complete, i'm creating a nonclustered index on the columns which will be mostly used in the WHERE clauses and JOIN conditions.
This SP can be run in multiple threads. So, at a given point of time, there will be multiple temp table getting created (with same name in different sessions) loaded even with half-a-million of records.
Once the need for this temporary table is over, i'm truncating the table. but there is still so code/logic happens post this Truncation in the SP.
My questions are =>
1) Is it ok to create these Non-clustered indexes after the insert happens?
2) Will these indexes be used in the subsequent references of this temporary table?
3) Is it good to truncate the temporary table in midst of the SP? (I'm truncating with the thought that it'll clear the huge number of records stored in the temp table and might clear the space in tempdb. Please correct me if i'm wrong)
Thanks.
Regards,
Ganesh
Regards,
Ganesh

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

First question, is that nonclustered index useful? Are the queries in the procedure using it or is it a waste of space?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ganeshc27 SSC Eights! Points: 972 More actions · Answer 2

The column on which the NCI is created, is being used in the WHERE conditions in the queries post this Index creation. How do I find whether this NCI is useful or not?

Regards,

Ganesh

Abu Dina SSChampion Points: 14155 More actions · Answer 3

ganeshc27 (11/15/2013)
The column on which the NCI is created, is being used in the WHERE conditions in the queries post this Index creation. How do I find whether this NCI is useful or not?

Examine the execution plan of the query?

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

ganeshc27 (11/15/2013)
The column on which the NCI is created, is being used in the WHERE conditions in the queries post this Index creation. How do I find whether this NCI is useful or not?

Time the procedure with the index being created, time the procedure without, run the time tests multiple times and average the results. Also, the execution plan but that won't give you the entire picture. It's possible that the index does get used but the overhead of creating it negates any advantage queries get from it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ganeshc27 SSC Eights! Points: 972 More actions · Answer 5

Thanks Gail. I'll try that out. Also, there's another query in my first thread.

3) Is it good to truncate the temporary table in midst of the SP? (I'm truncating with the thought that it'll clear the huge number of records stored in the temp table and might clear the space in tempdb. Please correct me if i'm wrong)

Regards,

Ganesh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

If you need to empty the table it's fine, faster than delete. If you don't have further use for the table, drop it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ganeshc27 SSC Eights! Points: 972 More actions · Answer 7

Does having DDL statements in midst of DML statements affect the performance of the SP? Will it improve performance if i move these Truncate / Drop statements at the end of the SP?

Regards,

Ganesh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Unless you're talking about many minutes of processing time and GB of TempDB space, just let the temp tables go out of scope and they'll be automatically dropped.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass