Avoiding a table scan

Question

Post reply

Avoiding a table scan

Bob Clarke

Valued Member

Points: 65
More actions
May 21, 2005 at 9:34 pm

#362609

I have a stored procedure that typically runs in 5-6 minutes about once per month. Yesterday, it slowed to a crawl, so I opened SQL Profiler and saw that the following statement was taking about 2.5 seconds to execute each time it ran (it is in a cursor loop), and the READS column of SQL Profiler showed a value of over 650,000 which is about the size of the table:
UPDATE ALLOC_PROPOSED
SET ALLOC_QUANTITY = mps.PLAN_QUANTITY,
REQUESTED_QUANTITY = mps.PLAN_QUANTITY
FROM ALLOC_PROPOSED alloc, MPS_PROPOSED mps
WHERE alloc.ALLOC_ID = @allocID
AND mps.MPS_ID = @mpsID
ALLOC_ID is the primary key (nonclustered) of table ALLOC_PROPOSED, and MPS_ID is the primary key (nonclustered) of table MPS_PROPOSED.
I assumed by the READS value and the time it took for the update statement to execute that a table scan was occurring. If so, how can I avoid it, and do you have any thoughts on why SQL Server now is doing a scan, when previous runs did not?
Environment: SQL Server 2000, SP 3A.
Bob

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Have you tried running the index tuning wizard against this ?!

**ASCII stupid question, get a stupid ANSI !!!**

Bob Clarke Valued Member Points: 65 More actions · Answer 2

>> Have you tried running the index tuning wizard against this ?!

Yes. It came back with no recommendations.

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

Do your ALLOC_PROPOSED & MPS_PROPOSED have any clustered indexes at all ? I was wondering why you didn't make your primary keys clustered ?

Also, if the # of rows in your table increases substantially every month and you have a cursor looping through these - you might just want to post the entire procedure to see if someone could come up with a solution that does not involve cursors ?

**ASCII stupid question, get a stupid ANSI !!!**

Bob Clarke Valued Member Points: 65 More actions · Answer 4

>> I was wondering why you didn't make your primary keys clustered ?

Perhaps they should be, but this database was designed and put in before I worked here, and I haven't had time yet to fully review it.

A co-worker suggested I change the query slightly by adding one more criteria to the WHERE clause, so the query is now:

UPDATE ALLOC_PROPOSED

SET ALLOC_QUANTITY = mps.PLAN_QUANTITY,

REQUESTED_QUANTITY = mps.PLAN_QUANTITY

FROM ALLOC_PROPOSED alloc, MPS_PROPOSED mps

WHERE alloc.ALLOC_ID = @allocID

AND mps.MPS_ID = @mpsID

AND alloc.MPS_ID = mps.MPS_ID

It worked, but I am not sure why. My understanding of SQL operations is faulty, I suppose. Without the new WHERE clause addition, I thought (theoretically) that SQL Server would do a cross join of ALLOC_PROPOSED and MPS_PROPOSED, and apply the WHERE clause to pare down the result set. Since ALLOC_ID and MPS_ID are identity columns, that seems the intermediate result set would give me just two rows. But, the profiler showed a read of over 650,000 records, which implied that either it was doing a table scan, or the 650,000 value indicated the cross join that was done.

Any thoughts?

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

Are the index statistics up to date?

Databases are living breathing entities. It's not that uncommon that once in a while, behaviour for the 'same' query may change. There are many reasons for this. Data changes, index density changes, index statistics may become outdated if not maintained, the overall volume of a table may rise above a 'critical level' etc etc...

There are no hard rules, only that indexes (and queries) need ongoing monitoring and maintenance. What worked yesterday may change tomorrow, and need tuning or rearranging. It's very much the nature of living things.

/Kenneth

Ian Yates SSCoach Points: 19738 More actions · Answer 6

Firstly, someone on this forum could have possibly made the suggestion if you had the table definitions including in your post - makes things easier to understand sometimes

Having said that, the query as you have it is a cross join - SQL will definitely create that entire cross-joined table (so scanning each table completely) and then apply your where clause. Including the extra condition in your where clause helped it make use of limiting rows selected from each table - avoiding the scan.

If you were running it in a cursor, you could also have (yukky procedural logic, but in the cursor it probably wouldn't hurt) done something like

declare @plan_qty int

select @plan_qty = PLAN_QUANTITY

from MPS_PROPOSED

where MPS_ID = @mpsID

Then do your update of a single table avoiding the need to join at all....

Or, you could do something more like

UPDATE ALLOC_PROPOSED

SET ALLOC_QUANTITY = mps.PLAN_QUANTITY,

REQUESTED_QUANTITY = mps.PLAN_QUANTITY

FROM ALLOC_PROPOSED alloc,

(select PLAN_QUANTITY from MPS_PROPOSED where mps.MPS_ID = @mpsID) mps

WHERE alloc.ALLOC_ID = @allocID

but that might still perform like a dog - haven't tried.

There's at least two ways to skin cats

Glad to hear you solved your issue in any case!

Cheers,

Ian

okie_greg SSC Eights! Points: 911 More actions · Answer 7

This is faster because you have now joined the tables (in the older syntax) so that SQL can work on a smaller set. A cleaner way and in my experience slightly faster, is to use the SQL-92 syntax:

UPDATE alloc

SET ALLOC_QUANTITY = mps.PLAN_QUANTITY,

REQUESTED_QUANTITY = mps.PLAN_QUANTITY

FROM ALLOC_PROPOSED alloc

join MPS_PROPOSED mps on alloc.mps_id = mps.mps_id

WHERE alloc.ALLOC_ID = @allocID

AND mps.MPS_ID = @mpsID