Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Table scan paranoia Expand / Collapse
Author
Message
Posted Friday, May 9, 2014 3:33 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:32 PM
Points: 42,458, Visits: 35,518
I can't see any useful covering indexes for that query, so it's probably just a case of the indexes being insufficiently selective and not covering. Cheaper to scan the table than do all the necessary key lookups.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1569465
Posted Friday, May 9, 2014 4:41 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:44 PM
Points: 384, Visits: 1,518
Thanks Gail. That's the much smarter way of saying what I was thinking.

Post #1569474
Posted Sunday, May 11, 2014 5:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 1,074, Visits: 6,359
sqldriver (5/9/2014)
I have a stored procedure hitting a 761969 row table and doing several table scans on it...


Properly written queries will cut down on those scans. Consider the query you posted. It can be rewritten like this:

;WITH updater AS (
SELECT TOP(@PRM_BufferSize)
TextExtractionStatus,
NEWTextExtractionStatus = (@PRM_Computer_id + 10000),
Computer_id,
NEWComputer_id = @PRM_Computer_id
FROM dbo.tbl_extr_items b
JOIN tbl_extensions d
ON b.extension_id = d.extension_id
JOIN item_types it
ON b.item_type_id = it.item_type_id
WHERE b.TextExtractionStatus IS NULL
AND collection_surr_id = @PRM_Collection_Surr_id
AND active = 1
AND process_status >= 200
ORDER BY item_idx
)
UPDATE updater SET
TextExtractionStatus = NEWTextExtractionStatus,
Computer_id = NEWComputer_id;

- because item_idx is unique. Here's a little test to show how it works:
DROP TABLE #test; DROP TABLE #Second;
CREATE TABLE #test (item_idx INT IDENTITY(1,1) PRIMARY KEY, TextExtractionStatus INT, Computer_id INT);
INSERT INTO #test (TextExtractionStatus, Computer_id)
VALUES (NULL, 10),(NULL, 10),(NULL, 10),(NULL, 10),(NULL, 10),(NULL, 10),(NULL, 10),(NULL, 10),(NULL, 10),(NULL, 10);

CREATE TABLE #Second (item_idx INT, Something INT)
INSERT INTO #Second (item_idx, Something)
VALUES (1,1),(2,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,1),(11,1);

SELECT * FROM #test;

DECLARE @PRM_BufferSize INT = 3, @PRM_Computer_id INT = 5;

WITH updater AS (
SELECT TOP(@PRM_BufferSize)
TextExtractionStatus,
NEWTextExtractionStatus = (@PRM_Computer_id + 10000),
Computer_id,
NEWComputer_id = @PRM_Computer_id
FROM #test t
INNER JOIN #Second s ON s.item_idx = t.item_idx
)
UPDATE updater SET
TextExtractionStatus = NEWTextExtractionStatus,
Computer_id = NEWComputer_id;

SELECT * FROM #test;




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1569607
Posted Sunday, May 11, 2014 6:44 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:44 PM
Points: 384, Visits: 1,518
Thanks Chris. I'll do some testing this week with that concept.
Post #1569657
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse