Slow query with many rows (simple query with no joins)

Question

I have a query that usually takes over five or six minutes to run. It has a WHERE and ORDER BY clause but no JOINs. But if I simplify the query to just this:

SELECT * FROM ReportIndex

It still takes over five or six minutes to run.

The table has over eleven million rows.

Since there's no join, I can't see how an index would make it faster. Can anyone suggest anything else I might try to speed up this query?

UPDATE:

This is the actual query I'm using. It returns 6,668,324 rows.

SELECT '1' AS vrsID,
    lprKey,
    lprRptName,
    lprTitle,
    lprDate,
    lprOwner,
    lprUserID,
    lprArchiveDate,
    lprTrackDate,
    lprActionView,
    lprActionEmail,
    lprActionExcel,
    lprActionForward,
    lprActionReassign,
    lprActionDownload,
    lprActionLocalPrint,
    lprActionServerPrint,
    lprPageCount,
    lprBytes,
    lprDataType,
    lprArchived,
    lprJobName,
    lprViewed,
    lprRptID
FROM ReportIndex
WHERE (lprOwner IN ('admin', 'APAdmin', 'APClerk', 'AP-Tab-700', 'AP-Tab-A-B', 'AP-Tab-A-K_EMP', 'AP-Tab-AP520', 'AP-Tab-CAN', 'AP-Tab-C-E', 'AP-Tab-COMM', 'AP-Tab-Confidential', 'AP-Tab-EE-Waiting', 'AP-Tab-F-O', 'AP-Tab-Historical', 'AP-Tab-LCD', 'AP-Tab-LCD_EMP', 'AP-Tab-LEA', 'AP-Tab-LPP', 'AP-Tab-LPS', 'AP-Tab-LPS_EMP', 'AP-Tab-LSI', 'AP-Tab-LTI', 'AP-Tab-L-Z_EMP', 'AP-Tab-P-R', 'AP-Tab-S-Z', 'AP-Tab-Unknown', 'Group-Category-VendorDocuments', 'Group-Quality-Control', 'Group-VendorDocs', 'HRAdmin', 'HR-Category-Payroll', 'HR-Category-Performance', 'HR-Category-Personnel', 'HR-Category-Upload', 'HR-Document-Delete', 'HR-Document-Index', 'HR-Group-DocumentMaintenance', 'HR-IndexQueue-Email', 'HRROLE01', 'HRROLE02', 'HRROLE03', 'HRROLE04', 'HRROLE05', 'HRROLE06', 'HRROLE09', 'HRROLE10', 'HRROLE11', 'HRROLE12', 'HRROLE13', 'HRROLE14', 'HRROLE15', 'HRROLE16', 'HRROLE17', 'HRROLE18', 'HRROLE19', 'HRROLE21', 'HRROLE23', 'HRROLE24', 'HRROLE25', 'HRROLE26', 'HRROLE28', 'HRROLE29', 'HRROLE30', 'HRROLE31', 'HRROLE34', 'HRROLE35', 'HRROLE36', 'HRROLE37', 'HRROLE39', 'HRROLE41', 'HRROLE42', 'HRROLE43', 'HRROLE44', 'HRROLE45', 'HRROLE46', 'HRROLE47', 'HRROLE48', 'HRROLE49', 'HRROLE50', 'HRROLE51', 'HRROLE52', 'HRROLE53', 'HRROLE54', 'HRROLE55', 'HRROLE56', 'HRROLE57', 'HRROLE58', 'HRROLE59', 'HRROLE60', 'HRROLE61', 'HRROLE62', 'HRROLE63', 'WFAdmin', 'AccountsPayable')
AND lprArchived = 0 AND lprPendingDelete = 0) AND lprDone=0
ORDER BY lprDate DESC

UPDATE 2:

Here's the execution plan: https://www.brentozar.com/pastetheplan/?id=Skjuu8sLX

Answer 1

This is too long for a comment.

Your query is essentially:

SELECT . . .
FROM ReportIndex
WHERE lprOwner IN (<big list>) AND
      lprArchived = 0 AND
      lprPendingDelete = 0 AND
      lprDone = 0
ORDER BY lprDate DESC

The standard recommendation is an index on something like ReportIndex(lprArchived, lprPendingDelete, lprDone, lprOwner, lprDate).

However, your query is selecting 6.6 million rows out of 12 million. An index won't help, because the SELECT is not sufficiently selective. If you were selecting 6.6 thousand rows, then an index might be a big help.

There is little you can do. If this is really important, you can create a clustered index on (lprDate). This will take a while to create -- all the data has to be sorted on the data pages.

The query should then proceed by reading the data in order and applying the WHERE conditions.

Answer 2

Efficiency wise try to avoid using the IN clause. If you have a large list of values inside the IN it can be very slow. To optimise its usually far bett er to added these values to a TVP and then join to it. You can also then add indexing to make the join more efficient. give the following a try:

DECLARE @tvp_lprOwner AS TABLE (owner NVARCHAR(255) NOT NULL);

INSERT INTO @tvp_lprOwner (owner)
VALUES ('admin') , ('APAdmin') , ('APClerk') , ('AP-Tab-700') , ('AP-Tab-A-B') , ('AP-Tab-A-K_EMP') , ('AP-Tab-AP520') , ('AP-Tab-CAN') , ('AP-Tab-C-E') , ('AP-Tab-COMM') , ('AP-Tab-Confidential') , 
('AP-Tab-EE-Waiting') , ('AP-Tab-F-O') , ('AP-Tab-Historical') , ('AP-Tab-LCD') , ('AP-Tab-LCD_EMP') , ('AP-Tab-LEA') , ('AP-Tab-LPP') , ('AP-Tab-LPS') , ('AP-Tab-LPS_EMP') , ('AP-Tab-LSI') , 
('AP-Tab-LTI') , ('AP-Tab-L-Z_EMP') , ('AP-Tab-P-R') , ('AP-Tab-S-Z') , ('AP-Tab-Unknown') , ('Group-Category-VendorDocuments') , ('Group-Quality-Control') , ('Group-VendorDocs') , ('HRAdmin') , 
('HR-Category-Payroll') , ('HR-Category-Performance') , ('HR-Category-Personnel') , ('HR-Category-Upload') , ('HR-Document-Delete') , ('HR-Document-Index') , ('HR-Group-DocumentMaintenance') , 
('HR-IndexQueue-Email') , ('HRROLE01') , ('HRROLE02') , ('HRROLE03') , ('HRROLE04') , ('HRROLE05') , ('HRROLE06') , ('HRROLE09') , ('HRROLE10') , ('HRROLE11') , ('HRROLE12') , ('HRROLE13') , 
('HRROLE14') , ('HRROLE15') , ('HRROLE16') , ('HRROLE17') , ('HRROLE18') , ('HRROLE19') , ('HRROLE21') , ('HRROLE23') , ('HRROLE24') , ('HRROLE25') , ('HRROLE26') , ('HRROLE28') , ('HRROLE29') , 
('HRROLE30') , ('HRROLE31') , ('HRROLE34') , ('HRROLE35') , ('HRROLE36') , ('HRROLE37') , ('HRROLE39') , ('HRROLE41') , ('HRROLE42') , ('HRROLE43') , ('HRROLE44') , ('HRROLE45') , ('HRROLE46') , 
('HRROLE47') , ('HRROLE48') , ('HRROLE49') , ('HRROLE50') , ('HRROLE51') , ('HRROLE52') , ('HRROLE53') , ('HRROLE54') , ('HRROLE55') , ('HRROLE56') , ('HRROLE57') , ('HRROLE58') , ('HRROLE59') , 
('HRROLE60') , ('HRROLE61') , ('HRROLE62') , ('HRROLE63') , ('WFAdmin') , ('AccountsPayable');

SELECT  '1' AS vrsID
,       RI.lprKey
,       RI.lprRptName
,       RI.lprTitle
,       RI.lprDate
,       RI.lprOwner
,       RI.lprUserID
,       RI.lprArchiveDate
,       RI.lprTrackDate
,       RI.lprActionView
,       RI.lprActionEmail
,       RI.lprActionExcel
,       RI.lprActionForward
,       RI.lprActionReassign
,       RI.lprActionDownload
,       RI.lprActionLocalPrint
,       RI.lprActionServerPrint
,       RI.lprPageCount
,       RI.lprBytes
,       RI.lprDataType
,       RI.lprArchived
,       RI.lprJobName
,       RI.lprViewed
,       RI.lprRptID
  FROM  ReportIndex RI
  JOIN @tvp_lprOwner O ON RI.lprOwner = O.owner
 WHERE  (lprArchived = 0 AND lprPendingDelete = 0)
   AND  lprDone = 0
 ORDER BY lprDate DESC;

Worth running it, then look at the execution plan, and index from there if need be.

Answer 3

Try creating a new index:

CREATE INDEX [IX_Composite] ON [ReportIndex] ([lprOwner], [lprArchived], [lprPendingDate], [lprDone], [lprDate]);

These 5 fields are included because 4 of them appear in your WHERE clause, and the last one is used in your ORDER BY.

Depending on your use case, it might be worth looking at paging the results. This can help where transferring the data in one big chunk is at the core of your issue.

Answer 4

From your responses on the comments section.

you mentioned there are no indexes on the table, and also, this might not be the only issue, hardware also would play a huge part on this as well.

So, what you need to do is first check the hardware specs, if is it outdated or can't handle that much of data processing, then stop there, and advise them to upgrade with a proper hardware. If they have a good hardware, then you can proceed to other SQL Server suggestions.

First, data order : I see in your query you're ordering the data by lprDate in descending order, this would be an expensive part of the query especially on large scale. Practical example is to use the same query without a where clause, just use ORDER BY lprDate DESC, and then compare it with the same query without it (use TOP 10000 to test). You'll see a performance difference between the two queries. If you always select the recent data from the table (which most people do), then you need to create a clustered index with a Descending order. This would resort all the data inside the table and also the new data will be always at the top page. The only issue that you might experience is resorting a 11M rows would take too much time to process, and sometimes the process fails. So, what I actually do (to avoid that) is recreating the same table with different name. Then create a new clustered index with the proper sort, along with the proper indexes that needed. Then when I'm done, I just reinsert the records by batches. You can do the same method, you can use lprDate or create an IDENTITY (or use the current if any) to make them clustered and adjust the sort to DESC, add then the proper indexes. After that, reinsert the data (in batches) into the new table, and make sure you specify the order by in your batches.

You can use this method of reinserting the data by batches :

DECLARE @Count  INT 
SET @Count = 1
WHILE @Count > 0
BEGIN
    INSERT INTO NewTable(Columns)
    SELECT 10000 Columns
    FROM 
        OriginalTable
    ORDER BY ID 

    SET @Count = @@ROWCOUNT
END

it'll insert 10 thousend rows in each run. you could increase or decrease the number of rows as you wish, whatever works best with you.

Now you could test the query in the new table, see how it would perform.

You might need to create an index for the actual query (in the post) with reording the WHERE clause as well.

So, your WHERE clause will be like this :

WHERE 
    lprArchived = 0 
AND     lprPendingDelete = 0
AND     lprDone=0
AND     lprOwner IN (.....)

AND your index will be :

CREATE INDEX [XReport] ON (lprArchived, lprPendingDelete, lprDone, lprOwner) INCLUDE (lprKey,  lprRptName,  lprTitle,  lprDate,  lprOwner,  lprUserID,  lprArchiveDate,  lprTrackDate,  lprActionView,  lprActionEmail,  lprActionExcel,  lprActionForward,  lprActionReassign,  lprActionDownload,  lprActionLocalPrint,  lprActionServerPrint,  lprPageCount,  lprBytes,  lprDataType,  lprArchived,  lprJobName,  lprViewed,  lprRptID)

AS for the lprOwner IN (.....) There are another approches that you could take :

First one (since you have large set of values), I would consider a comparsion between lprOwner values that is in the table, and the current used values. To see how much actually left in the table since you have about 94 values.

Basically :

SELECT DISTINCT lprOwner FROM ReportIndex WHERE lprOwner NOT IN(current used values)

If the total values returned are less than 94 rows, then using the reverse method will be much better. Which would be using something like this :

AND lprOwner NOT IN(the returned values)

You can also use Temp or TVP methods to join them with the query so it would be

SELECT ....
FROM ReportIndex ri 
LEFT JOIN (The values table) t ON ri.lprOwner = t.lprOwner
WHERE 
    lprArchived = 0 
AND     lprPendingDelete = 0
AND     lprDone=0

Another method is to use sub-query like this :

SELECT *
FROM (
SELECT .... , lprOwner 
FROM ReportIndex 
WHERE 
    lprArchived = 0 
AND     lprPendingDelete = 0
AND     lprDone=0
) D 
WHERE 
lprOwner ..... 

Using the sub-query method, will make the results first filtered by lprArchived, lprPendingDelete, and lprDone.. Then, The results will be filtered by IprOwner. So, you're filtering in two sets rather than one set. This could be useful in huge tables sometimes.

This is what I've in mind, surely, not every method or suggestion could be applied in all situations, but at least the idea itself could lead you to your salvation