Avoiding a table scan
-
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
-
Have you tried running the index tuning wizard against this ?!
**ASCII stupid question, get a stupid ANSI !!!** -
>> Have you tried running the index tuning wizard against this ?!
Yes. It came back with no recommendations.
-
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 !!!** -
>> 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?
-
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
-
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
-
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 allocSET ALLOC_QUANTITY = mps.PLAN_QUANTITY,
REQUESTED_QUANTITY = mps.PLAN_QUANTITY
FROM ALLOC_PROPOSED alloc
join MPS_PROPOSED mps on alloc.mps_id = mps.mps_idWHERE alloc.ALLOC_ID = @allocID
AND mps.MPS_ID = @mpsID
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply