Conversion of Queries - 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

Other SQL Server Topics (2005)

Conversion of Queries

Author

Topic

amitgogna
Starting Member

1 Post

Posted - 2010-12-13 : 03:55:45

Hi All,

It seems I am messed up this time. My last BOSS developed some queries in SQL 2000 that we need to convert to sql2005 so that the they do not run for 6 hours where they should end in 10 minutes. Here is a sample. Hope you can help:

DELETE FROM ORDER_LINE_REASONS WHERE
CONVERT(CHAR(10),ORDER_DATE,102)+CAST(ORDER_NUMBER AS CHAR(3))+CAST(LINE_NUMBER AS CHAR(3))+CAST(SEQUENCE AS CHAR(3))NOT IN
(SELECT CONVERT(CHAR(10),ORDER_DATE,102)+CAST(ORDER_NUMBER AS CHAR(3))+CAST(LINE_NUMBER AS CHAR(3))+CAST(SEQUENCE AS CHAR(3))FROM ORDER_LINES)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-13 : 04:26:09

A v2000 query has a good chance of running in v2005 without changes.
What makes you think the version of sql server will bring the time down from 6 hours to 10 mins?
Most of the time I would be concerned about 10 mins and look to redesign the system (depends on the system of course).

In the query here you are using a function on the table column so it will table scan. If the table is large it will take a long time. You can give a covering index or split the query up so that it scans an index but the better solution is not to use a function on the column - which looks like it might mean a redesign.
or maybe compare the columns seperately as well as the combination so it can use an index on the columns before using the functions.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-13 : 07:15:22

Column ORDER_DATE, does it have time information other than 00:00.00.000?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-13 : 07:17:10

If ORDER_DATE columns doesn't have time information (other than 00:00:00.000), try these substitutions instead.
They make you use index if present!

-- Take 1
DELETE	olr
FROM	ORDER_LINE_REASONS AS olr
WHERE	NOT EXISTS	(
				SELECT	*
				FROM	ORDER_LINES AS ol
				WHERE	ol.ORDER_DATE = olr.ORDER_DATE
					AND ol.ORDER_NUMBER = olr.ORDER_NUMBER
					AND ol.LINE_NUMBER = olr.LINE_NUMBER
					AND ol.SEQUENCE = olr.SEQUENCE
			)

-- Take 2
DELETE		olr
FROM		ORDER_LINE_REASONS AS olr
LEFT JOIN	ORDER_LINES AS ol ON ol.ORDER_DATE = olr.ORDER_DATE
			AND ol.ORDER_NUMBER = olr.ORDER_NUMBER
			AND ol.LINE_NUMBER = olr.LINE_NUMBER
			AND ol.SEQUENCE = olr.SEQUENCE
WHERE		ol.ORDER_DATE IS NULL

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources