Query OPtimization Needed - 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 2000 Forums

Transact-SQL (2000)

Query OPtimization Needed

Author

Topic

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-08-30 : 03:22:24

EXEC(' delete o from P9240502_001 o' +
' where die_sequence <> (select MAX(die_sequence)' +
' from P9240502_001 i where i.wafer_sequence = o.wafer_sequence ' +
' and i.x = o.x and i.y = o.y) and o.sub_die IS NULL')

if there are more than one values on the same x, y position for the same wafer and sub_die is empty, I want to delete all those values except one with the maximum die_sequence.
In my view this query is fine, but if it can be more optimized, I'll be pleased.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-30 : 08:22:38

It can be greatly optimized.

Don't use dynamic SQL.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-30 : 15:49:52

ROFL Derrick!

As far as naming conventions go, this is one of the best ones I have ever seen

SELECT wafer_sequence FROM P9240502_001

Brilliant !!!
-----------------------------------------------------------------------------------

DELETE P9240502_001
FROM
	( 	SELECT wafer_sequence, x, y, MAX(die_sequence) AS max_die_sequence
		FROM P9240502_001
		GROUP BY wafer_sequence, x, y ) mxd
WHERE
	P9240502_001.wafer_sequence = mxd.wafer_sequence
	AND P9240502_001.x = mxd.x
	AND P9240502_001.y = mxd.y
	AND P9240502_001.die_sequence <> mxd.max_die_sequence
	AND P9240502_001.sub_die IS NULL


DELETE o
FROM
	P9240502_001 o
	JOIN
	( 	SELECT wafer_sequence, x, y, MAX(die_sequence) AS max_die_sequence
		FROM P9240502_001
		GROUP BY wafer_sequence, x, y ) mxd
	ON o.wafer_sequence = mxd.wafer_sequence
	AND o.x = mxd.x
	AND o.y = mxd.y
WHERE
	o.die_sequence <> mxd.max_die_sequence
	AND o.sub_die IS NULL

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources