Best Index on a #Temp table

Question

Post reply

Best Index on a #Temp table

John Burchel

Hall of Fame

Points: 3611
More actions
August 29, 2006 at 11:50 am

#170222

Hi All,
Just wondering what the best index that would suit a temp table, I am querying a 99% non distinct field.
eg.
select * from #test where id_s = 453
Select count(*), count(distinct id_s) from #test
-------------------------
20567 1
Thanks
Kindest Regards,
John Burchel (Trainee Developer)

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

From what you show us. Only a clustered index on id_s could be usefull.

Is there any other queries run on the temp table? Can we see the whole table definition?

John Burchel Hall of Fame Points: 3611 More actions · Answer 2

DDL

S_id int

Event int

UNIQUE_PERSONURN char

sDate Datetime

alter table #temp add constraint idx_pku Primary Key Clustered( SDate , Event , UNIQUE_PERSONURN)

Then Bulk insert happens in this order

Create #temp
Bulk insert into it
Add constraint above
Perform an insert into another table (see below)
Drop constraint
Truncate table
Back to step 1

Most Performed Insert

Insert into Testing

Select *

from #temp

where Event & 3 <> 0 and s_id = 564882 and RTRIM(Convert(varChar(10), p.SeizeDate , 112 )) = '20060822'

Kindest Regards,

John Burchel (Trainee Developer)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Why do you need to do multiple inserts?

Why do you need to convert the datetime column in the table? The other way around would allow the server to use the clustered index.

Also if this is the only query running here.. You might be better served using this as the clustered index (even if it's not unique???) :

SeizeDate,s s_id, Event

John Burchel Hall of Fame Points: 3611 More actions · Answer 4

There is need to do multiple inserts as different files needs to be loaded onto the database, we dont have any other replication method.

The datatime column is being converted so as to make sure that the right date is being selected.

With those 3 columns you mentioned, they are already being used as a Clustered Primary key, but whilset checking the execution plan, I can see that its using a Clustered Index Scan, which isnt good. If I remove the promary key constraint, it uses a table scan, which isnt good either.

Where do i go now ?

Kindest Regards,

John Burchel (Trainee Developer)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Well a scan isn't always a bad thing. If you are really inserting every row of the table, then a scan IS required. However I don't know enough about this task to tell you wether it can be sped up (a lot) or not.