-1

I have a script that build some # tables and the result is a SELECT * query.

This query where I plug in the actual variables used runs in a few seconds

SELECT *
FROM 
    #rec_subids 
    INNER JOIN #max_payment_dt
        ON 1 = 1
    INNER JOIN  vw_staging_lpmt
        ON lpmt_subid = 1008
        AND lpmt_asofdt >= '2017-09-20'

This query where the variables actually come from the # tables runs in 10+ minutes

select * from #max_payment_dt
2017-09-20 00:00:00.000
select * from #rec_subids
1008
SELECT *
FROM 
    #rec_subids 
    INNER JOIN #max_payment_dt
        ON 1 = 1
    INNER JOIN  vw_staging_lpmt
        ON lpmt_subid = r_subid
        AND lpmt_asofdt >= max_asofdt

Any thoughts of how I can speed up the second query without changing the vw_staging_lpmt?

These are 2 columns - 1 record, an ID and as Date. I understand the hardcoding would work better but can't hardcode here.

Here is the plan of the poorly performing query.

Here is the plan of the much better performing query.

closed as too broad by Erik Darling, RDFozz, Mr.Brownstone, hot2use, Colin 't Hart Nov 8 '17 at 12:58

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

Your first query has constants in it, and optimizer knows them when it makes a plan.

So it can use statistics on lpmt_subid and lpmt_asofdt, it estimated the predicate lpmt_asofdt >= '2017-09-20' as 74698 rows.

Your lease_payments has at least 7 mln of rows (if it was smaller server would not decide to make 74698 lookups but opted for full scan), but the statistics for '2017-09-20' is known, server decides that if it make index seek + lookups it will cost less then full clustered index scan.

When insted of constants you use temporary tables column values, server has no idea about what these values are, so it cannot use column statistics and does make full clustered index scan of lease_payments.

You use SQL Server 2012, it estimates cardinality of filtering by non-unique unknown value as power(TableCardinality, 3./4), it seems that your lease_paymentstable has about 3.406.611.597 rows (as estimation is 14.100.800 rows when matching with #rec_subids).

You can fix this by assigning r_subid, max_asofdtto local variables first (if you are sure that your temp tables are of 1 row each) and then adding recompile option to sniff these variables:

declare @max_asofdt date =
(select top 1 max_asofdt 
from #max_payment_dt);

declare @r_subid int =
(select top 1 r_subid 
from #rec_subids);


SELECT *
FROM 
    #rec_subids 
    INNER JOIN #max_payment_dt
        ON 1 = 1
    INNER JOIN  vw_staging_lpmt
        ON lpmt_subid = @r_subid
        AND lpmt_asofdt >= @max_asofdt
option(recompile);
  • Thank you sepupic Rewriting with variables works much better. Now need to rewrite the code because there will be more than one r_subid that needs to be passed in. – Max Nov 8 '17 at 16:41

Not the answer you're looking for? Browse other questions tagged or ask your own question.