Query Running Forever - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Running Forever

Author

Topic

josh.guffey
Starting Member

3 Posts

Posted - 2010-02-22 : 09:56:31

Hi Everyone,

We just migrated from 2000 to 2005 last week. I am having reservations regarding the configuration of the new database environment. I am trying to run the simple query below and it is running forever... The size of TABLE1 is: 7.969 MB with 149,713 records The size of TABLE2 is: 50.617 MB with 628,652 records. Last week, I let this query run for 3.5 hours before killing it and it had only brought back around 15K records.

SELECT A.FIELD1, A.FIELD2, A.FIELD3, A.FIELD4, B.FIELD5,
	CASE
	 WHEN B.FIELD5 IS NOT NULL THEN 'Y'
	 ELSE 'N'
	END AS [EXIST IN SOURCE2 FLAG]
FROM TEMP_PRD.dbo.TABLE1 A LEFT JOIN TEMP_PRD.dbo.TABLE2 B
ON RIGHT(A.FIELD1, 9) = RIGHT(B.FIELD5, 9)
ORDER BY A.FIELD1

I know the join on parsed data sets has to be chewing up a lot of resources, but 3.5 hours to run a query and only get partial results??? Something does not feel right with this.

Does anyone have any suggestions? They will definitely be appreciated.

Josh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 09:58:51

have you had a look at execution plan? what does that suggest?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

josh.guffey
Starting Member

3 Posts

Posted - 2010-02-22 : 10:07:41

When the plan gets down to the join, I can definitely tell that is the part wreaking havoc on the overall performance.

Here is the Estimated Operator Cost: 87383.237 (46%)
Here is the Estimated CPU Cost: 13971.5

Would a (IN/NOT IN) clause provide better performance than the Left Join?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 10:16:26

depends on number of records per group of RIGHT(A.FIELD1, 9) value. try exists also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-22 : 10:36:54

How can in(), not in() or exits() help on that when the select list needs columns from both tables?
That is really not clear to me...

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-02-22 : 10:51:38

Were the indexes rebuilt, and statistics updated, after migrating to SQL 2005?

Hints and Tips for migrating to SQL 2008 here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230

most apply to SQL 2005 also.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 10:56:01

quote:
Originally posted by webfred

How can in(), not in() or exits() help on that when the select list needs columns from both tables?
That is really not clear to me...

No, you're never too old to Yak'n'Roll if you're too young to die.

oops didnt notice that. In that case as you said they cant be used

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-02-22 : 11:49:36

You could try using temporary tables:


SELECT RIGHT(Field1, 9) AS JoinCol
	,Field1, Field2, Field3, Field4
INTO #Table1
FROM TEMP_PRD.dbo.Table1

SELECT RIGHT(Field5, 9) AS JoinCol
	,Field5
INTO #Table2
FROM TEMP_PRD.dbo.Table2

CREATE CLUSTERED INDEX IX_JoinTable1
ON #Table1 (JoinCol)

CREATE CLUSTERED INDEX IX_JoinTable2
ON #Table2 (JoinCol)

SELECT A.Field1, A.Field2, A.Field3, A.Field4, B.Field5,
	CASE
	 WHEN B.Field5 IS NOT NULL THEN 'Y'
	 ELSE 'N'
	END AS [EXIST IN SOURCE2 FLAG]
FROM #Table1 A
	LEFT JOIN #Table2 B
		ON A.JoinCol = b.JoinCol
ORDER BY A.FIELD1

ps Did you update the stats after migration?

DaleTurley
Yak Posting Veteran

76 Posts

Posted - 2010-02-22 : 12:16:59

Have you checked sysprocesses where blocked != 0. just to rule out a blocking issue while it's running.

josh.guffey
Starting Member

3 Posts

Posted - 2010-02-22 : 14:01:37

Thanks everyone for the information. I am not sure what was completed during migration, so I went back and updated the statistics and verfified the indexes. I checked to see is there were any blocked sysprocesses and there were none.

After that, I took the logic that Ifor provided (thank you) and was able to get the desired results. It only takes ~ 5-7 seconds to run.

I appreciate everyone's input. I was able to learn a great deal from this problem.

Thanks again,

Josh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources