Slow performing Query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Slow performing Query

Slow performing Query

Rate Topic

Display Mode

Topic Options

Author

Message

Razi, M

Posted Thursday, March 12, 2009 4:28 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:37 PM
Points: 214, Visits: 166

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

Jack Corbett

Posted Thursday, March 12, 2009 5:15 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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

Joseph-465703

Posted Friday, March 13, 2009 9:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 06, 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

Razi, M

Posted Friday, March 13, 2009 11:56 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:37 PM
Points: 214, Visits: 166

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

Jack Corbett

Posted Friday, March 13, 2009 12:19 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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?

Post #675496

Razi, M

Posted Friday, March 13, 2009 1:20 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:37 PM
Points: 214, Visits: 166

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

Razi, M

Posted Friday, March 13, 2009 1:22 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:37 PM
Points: 214, Visits: 166

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

Jack Corbett

Posted Friday, March 13, 2009 2:56 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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.

Post #675606

Joseph-465703

Posted Friday, March 13, 2009 4:43 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 06, 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 »

Permissions