High number of row estimates

What I can do to improve esitmates? Actual rows in about 10,000 and estimated is 500 billion (500,000,000,000) records.

avatar image By Kdixon 0 asked Dec 20, 2016 at 07:26 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Jan 08 at 05:24 PM

A WHERE clause with 15 AND/OR conditions quickly becomes difficult for any human, never mind the optimizer, to determine proper estimates. There are just too many possibilities to explore. Have you considered simplifying some of this for the optimizer by using #temp tables to store intermediate results, instead of trying to do everything in a single query with stacked CTEs?

10|10000 characters needed characters left

1 answer: sort voted first

The major issue here is that computing the set pta_recent is very inefficient, as the query is currently written. The rest of the query is largely irrelevant from a performance perspective.

If you are unable to change the current indexing, consider the following query in place of pta_recent:

 SELECT
     D.trc_number,
     -- pivot columns:
     first_pta = MAX(CASE WHEN CA.rn = 1 THEN CA.lph_id ELSE NULL END),
     second_pta = MAX(CASE WHEN CA.rn = 2 THEN CA.lph_id ELSE NULL END),
     third_pta = MAX(CASE WHEN CA.rn = 3 THEN CA.lph_id ELSE NULL END)
 FROM 
 (
     -- Find the distinct trc_number values using the index
     SELECT DISTINCT 
         LH.trc_number 
     FROM dbo.legpta_history AS LH 
     WHERE 
         LH.trc_number > ''
 ) AS D
 CROSS APPLY 
 (
     -- For each trc_number, find up to 3 highest lph_id values
     SELECT TOP (3)
         rn = ROW_NUMBER() OVER (ORDER BY LH2.lph_id DESC),
         LH2.lph_id 
     FROM dbo.legpta_history AS LH2 
     WHERE
         LH2.trc_number = D.trc_number 
     ORDER BY
         LH2.lph_id DESC
 ) AS CA
 GROUP BY
     D.trc_number;

This can use the existing indexing to obtain the set needed without any sorting:

New plan

This will be especially efficient if there are many lph_id per trc_number on average.

If you can change the indexing, consider replacing the existing index on trc_number with one on the same column, but sorted descending:

 CREATE INDEX i ON dbo.legpta_history (trc_number DESC)

Then use this query:

 SELECT
     N.trc_number,
     first_pta = MAX(CASE WHEN N.rn = 1 THEN N.lph_id ELSE NULL END),
     second_pta = MAX(CASE WHEN N.rn = 2 THEN N.lph_id ELSE NULL END),
     third_pta = MAX(CASE WHEN N.rn = 3 THEN N.lph_id ELSE NULL END)
 FROM 
 (
     SELECT
         LH.lph_id,
         LH.trc_number,
         rn = ROW_NUMBER() OVER (PARTITION BY LH.trc_number ORDER BY LH.lph_id DESC)
     FROM dbo.legpta_history AS LH
     WHERE LH.trc_number > ''
 ) AS N
 WHERE
     N.rn <= 3
 GROUP BY
     N.trc_number;

Expected plan:

Expected plan

There are other refinements possible, including a filtered index, and/or keyed on (trc_number ASC, lph_id DESC) (to potentially allow a parallel plan).

sp.png (48.2 kB)
sp.png (18.0 kB)
avatar image By SQLkiwi ♦ 6.6k answered Jan 09 at 05:49 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x580
x396
x2
x1

asked: Dec 20, 2016 at 07:26 PM

Seen: 53 times

Last Updated: Jan 09 at 05:49 PM