SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Slow performing Query


Slow performing Query

Author
Message
ramoha
ramoha
SSC Eights!
SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)

Group: General Forum Members
Points: 849 Visits: 193
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
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78830 Visits: 14970
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Joseph-465703
Joseph-465703
SSC Eights!
SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)

Group: General Forum Members
Points: 980 Visits: 465
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
ramoha
SSC Eights!
SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)

Group: General Forum Members
Points: 849 Visits: 193
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
Attachments
showplan_test.txt (32 views, 1.00 KB)
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78830 Visits: 14970
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
ramoha
ramoha
SSC Eights!
SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)

Group: General Forum Members
Points: 849 Visits: 193
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
ramoha
SSC Eights!
SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)

Group: General Forum Members
Points: 849 Visits: 193
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
  Jack Corbett
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78830 Visits: 14970
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Joseph-465703
Joseph-465703
SSC Eights!
SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)

Group: General Forum Members
Points: 980 Visits: 465
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search