ORDER BY clause slowing performance

[zooraw]

I have a table that I read. The table is indexed by the columns that I want to sort by (and in the same directions). I need to pick the Top 1 row from the table given certain criteria. If I do it without the ORDER BY clause, my code runs in less than a minute. If I include the ORDER BY, it takes over 25 minutes and doesn't pick the correct rows. Is this performance normal? Any way to improve performance?

 

[pkailas]

open up Query analyzer and execute your query in there. After you get your results click on Query > Display Estimated Execution Plan.

The results may give you some insight on where to make some improvments.

Perhaps a Clustered Index on your table?

Sorting your table will always take longer than not sorting.

 

[SQLSister]

What is your SQL Code?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[zooraw]

I tried a clustered index....didn't seem to help....




DECLARE @RecordID bigint
DECLARE @Hi varchar(2)
DECLARE @Bye varchar(2)

DROP TABLE Central.DBO.TempRecordCompInfo

CREATE TABLE Central.DBO.TempRecordCompInfo (
[RECORD_ID] [bigint] NOT NULL ,
[Hi] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bye] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

ALTER TABLE Central.DBO.TempRecordCompInfo WITH NOCHECK ADD
CONSTRAINT [PK_TempRecordCompInfo] PRIMARY KEY CLUSTERED
(
[RECORD_ID]
) ON [PRIMARY]

INSERT INTO Central.DBO.TempRecordCompInfo
SELECT RECORD_ID, null, null
FROM Central.DBO.RecordsToProcess

SET @RecordID = (SELECT Min(RECORD_ID) FROM Central.DBO.RecordsToProcess)

WHILE @RecordID IS NOT NULL
BEGIN

SELECT Top 1 @Hi = Hi,
@Bye = Bye
FROM Tables.DBO.Process a, Central.DBO.RecordsToProcess b
WHERE a.BUSINESS IN ('GOOD', 'ZZZZ')
AND a.LINE IN (b.LINE, 'ZZZ')
AND a.CUSTOMER IN (b.CUSTOMER, 'ZZ')
AND a.CUSTOMER_IND IN (b.CUSTOMER_IND, '99')
AND a.STATE IN (b.STATE, 'ZZZ')
AND a.CRIT_CD = b.CRIT_CD
AND a.DIS_CD IN (b.DIS_CD, '99')
AND a.NBR1 <= My1
AND a.NBR2 <= My2
AND a.NBR3 <= My3
AND a.NBR4 <= My4
AND a.BEGIN_DT <= '07/25/2005'
AND a.END_DT >= '07/25/2005'
ORDER BY
BUSINESS,
LINE,
CUSTOMER,
CUSTOMER_IND,
STATE,
CRIT_CD,
DIS_CD,
NBR1 DESC,
NBR2 DESC,
NBR3 DESC,
NBR4 DESC

UPDATE Central.DBO.TempRecordCompInfo
SET Hi = @Hi,
Bye = @Bye
WHERE RECORD_ID = @RecordID


SET @RecordID = (SELECT Min(RECORD_ID) FROM Central.DBO.RecordsToProcess WHERE RECORD_ID > @RecordID)

END -- WHILE

 

[donutman]

Congratulations, you've found a way to duplicate the performance of a cursor without using a cursor! If you are willing to take a step backward and consider an alternate solution, it's very likely that you can perform the update that you wish without a loop.
If so, tell us what the tables are about and what you are trying to do.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]