Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Slow performing Query Expand / Collapse
Author
Message
Posted Thursday, March 12, 2009 4:28 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 5:07 PM
Points: 227, Visits: 192
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
Post #674819
Posted Thursday, March 12, 2009 5:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 11,314, Visits: 13,101
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

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
Post #674842
Posted Friday, March 13, 2009 9:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 6, 2012 10:04 AM
Points: 144, Visits: 455
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

Post #675349
Posted Friday, March 13, 2009 11:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 5:07 PM
Points: 227, Visits: 192
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


  Post Attachments 
showplan_test.txt (10 views, 1.79 KB)
Post #675476
Posted Friday, March 13, 2009 12:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 11,314, Visits: 13,101
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

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
Post #675496
Posted Friday, March 13, 2009 1:20 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 5:07 PM
Points: 227, Visits: 192
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
Post #675529
Posted Friday, March 13, 2009 1:22 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 5:07 PM
Points: 227, Visits: 192
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
Post #675531
Posted Friday, March 13, 2009 2:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 11,314, Visits: 13,101
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

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
Post #675606
Posted Friday, March 13, 2009 4:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 6, 2012 10:04 AM
Points: 144, Visits: 455
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
Post #675705
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse