Slow performing Query

Question

Slow performing Query

ramoha

SSCommitted

Points: 1909
More actions
March 12, 2009 at 4:28 pm

#384121

Hi All,
I have a query which is joining two tables:
Tables A is 100000 and Table B is 275Million but I'm filtering the number of rows to 500K with a where condition but still my query is running extremely slow.
Original Query:
-----------------------------------------------------------------------------------------------------
Insert into
select distinct kw_id
from StageDB.dbo.tt_wf_49b955a9_gtsang_2BC -- 10000o rows
where KW_ID not in ( select KW_ID from DW_KWDM_KW_GRP_MAP where kw_ID = 6675) -- filter used to get only 500K rows
-----------------------------------------------------------------------------------------------------
Hardware: 16CPU,64bit SQL Server Enterprise, 27GB RAM
But when I modified the query to use a #temp table it executes in seconds:
-----------------------------------------------------------------------------------------------------
select KW_ID into #temp_6675 from DW_KWDM_KW_GRP_MAP where kw_ID = 6675
Insert into
select distinct kw_id
from StageDB.dbo.tt_wf_49b955a9_gtsang_2BC
where KW_ID not in ( select KW_ID from #temp_6675)
-----------------------------------------------------------------------------------------------------
Can someone give some insight into why SQL Server 2005 is scanning the entire table when the WHERE condition is provided and the OPTIMIZER also picked up the correct index seek?
Thanks
Razi, M.
http://questivity.com/it-training.html

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Which table in the example is tableA and which is tableB? Which table is showing the clustered index seek? Can you post the execution plan? When was the last time you updated the statistics?

Wouldn't this be the same query?

select distinct

kw_id

from

StageDB.dbo.tt_wf_49b955a9_gtsang_2BC

Where

kw_id <> 6675

I mean, since the your sub-query is filtering on kw_id = 6675 and returning kw_id all the rows returned by it will return the same kw_id, 6675.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Joseph-465703 SSCrazy Points: 2122 More actions · Answer 2

Have you ever tried using INNER JOIN..if possible can you try it whether it works.

l

for example :-

Insert into

select distinct kw_id

from StageDB.dbo.tt_wf_49b955a9_gtsang_2BC a -- 10000o rows

INNER JOIN DW_KWDM_KW_GRP_MAP b

ON a.KW_ID <> b.KW_ID

AND b.kw_ID = 6675

ramoha SSCommitted Points: 1909 More actions · Answer 3

The execution plan is pasted below. I tried formatting it to be more presentable but the editor did not allow. So I have attached the showplan_text file.

The table with 100K rows is tt_wf_49b955a9_gtsang_2BC; 500k is DW_KWDM_KW_GRP_MAP with where condition; else total count is 275M

Thanks,

Razi, M.
http://questivity.com/it-training.html

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Thanks. You know you can save the graphical execution plan as a .sqlplan and then attach it to the post. It has more information.

Did you consider the query I presented in my last post? Is there some reason why that query would have different results than your original query?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ramoha SSCommitted Points: 1909 More actions · Answer 5

I used your suggested way but it still took 20 minutes on a server with minimal activity. It completed in 10 seconds when I do it with #temptable

Razi, M.
http://questivity.com/it-training.html

ramoha SSCommitted Points: 1909 More actions · Answer 6

I used your suggested way but it still took 20 minutes on a server with minimal activity. It completed in 10 seconds when I used #temptable which was created based on where condition (select kw_id into #temp from triton_ss..DW_KWDM_KW_GRP_MAP where KW_GRP_ID = 6197)

select distinct a.kw_id

from StageDB.dbo.tt_wf_49b955a9_gtsang_2BC a inner join triton_ss..DW_KWDM_KW_GRP_MAP b on

a.kw_id <> b.kw_id and b.KW_GRP_ID = 6197

Razi, M.
http://questivity.com/it-training.html

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

Yeah, my method is not going to be any faster since you can't eliminate DW_KWDM_KW_GRP_MAP from the query. In your original post you did not show that the criteria was on KW_GROUP_ID you just has kw_id.

Having the correct query helps make the exection plan make more sense now as well. It looks like, and I am much better at reading a graphical plan, the query is hitting the larger table twice to do the seeks, once on the inner side of a nested loop join which is eliminating the non-matching rows, then you have another seek against it for a hash join to eliminate the ones that are not part of the group.

By using a temp table, you are only hitting the large table once which helps account for the better performance.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Joseph-465703 SSCrazy Points: 2122 More actions · Answer 8

can you create a nonclustered index on temp table

like

CREATE NONCLUSTERED INDEX NCIDX_KW_ID

ON #temp_6675(KW_ID)

drop the index at the end of the statement. and try whether it works...

also refer BOL like : ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/39123830-dc95-4b15-a582-3b43edb400bc.htm