Home Why does my query to hang/run for ever with variables instead of literals?
Reply: 1

Why does my query to hang/run for ever with variables instead of literals?

JC Ford
1#
JC Ford Published in 2017-12-05 20:28:17Z

I have a relatively simple query that runs in about 2.5 minutes when I run it with literal values in the where clause. But when I run it with local variables containing those same values, the query hangs or runs presumably forever. (I haven't tried letting it run more than 90 minutes.)

Here's the query with names obfuscated (because rules). I've tried replacing the EXISTS with INNER JOINs, but it didn't help. The variables below are local variables, not parameters. This can't be a parameter sniffing issue. But when the variables are replaced with their literal values (two DATETIMEs and three INTs) the query runs fine.

DECLARE @SubsetStart DATETIME = '2013-01-01 00:00:00'
DECLARE @SubsetEnd DATETIME = '2013-12-31 23:59:59'
DECLARE @SCD INT = 217
DECLARE @MFP INT = 8
DECLARE @EXP INT = 39298

SELECT
    MainTable.MFID ManufacturerID
    ,SUM(MainTable.AMT) AMT
FROM
    MainTable
WHERE
    EXISTS (
        SELECT TID
        FROM MTMTable 
        WHERE MTMTable.TID = MainTable.TID
            AND MTMTable.DEL = 0
            AND EXISTS (
                SELECT CID
                FROM RelatedTable
                WHERE RelatedTable.CID = MTMTable.CID
                    AND DEL = 0
                    AND RelatedTable.TD BETWEEN '2013-01-1' AND '2013-12-31 23:59:59'
            )
    )
    AND EXISTS (
        SELECT AID 
        FROM OtherTable 
        WHERE OtherTable.AID = MainTable.CAID
            AND OtherTable.AHTID = 8
            AND OtherTable.DEL = 0)
    AND MainTable.DAID <> 39298
    AND MainTable.SID = 217
GROUP BY
    MainTable.MFID

I am completely at a loss as to why this simple query should behave this way.

JM_
2#
JM_ Reply to 2017-12-06 14:03:59Z

The issue is that when you use local variables the optimizer ignores their value and uses general statistic assumptions. Basically at compile time it does not know the value of those variables. You can verify this by using the OPTION(RECOMPILE) hint on your query, this will recompile the query using the values in your variables.

You can read about why using local variables in stored procedures can hurt performance here:

https://www.brentozar.com/archive/2014/06/tuning-stored-procedures-local-variables-problems/

and here:

http://www.sqlbadpractices.com/using-local-variables-in-t-sql-queries/

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.301314 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO