Stored Proc with table variables running very slow?

  • Quite interesting Paul! I have seen numerous real-world systems where that was a killer. Things that make you go "HMMMMM"!!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (2/5/2010)


    Quite interesting Paul! I have seen numerous real-world systems where that was a killer. Things that make you go "HMMMMM"!!

    Well yes, absolutely. I'm not arguing the general point about statistics - you know I agree with you.

    I just had to respond to the specific example you raised.

    (And yes it is an interesting result - if I blogged, I might have blogged about it, but I don't LOL)

  • AHA! Do this:

    dbcc freeproccache

    go

    DECLARE @OneRow

    TABLE (

    -- row_id INTEGER IDENTITY PRIMARY KEY,

    value INTEGER NOT NULL

    );

    --INSERT @OneRow VALUES (123456);

    INSERT @OneRow VALUES (95); --can even put in 77 here and get zero rows, still get hash

    SELECT *

    FROM @OneRow R

    JOIN dbo.Data D

    ON D.value = R.value;

    Now you SHOULD get the nested loop but do not. Clearly the stats on the perm table lead the optimizer to the probability that your request is gonna hit the biggie so it does a hash even when it should NOT. So it simply kills you in a different manner. 🙂

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • DOH! More goof - you need an index on the value joined or it can never do anything but a table scan/hash. Back in a moment with better analysis...

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Clearly a bug in the optimizer - take it up with the SQL Team :laugh: :laugh: :laugh: :laugh: :laugh:

    No, seriously, I got my caveat post in first, so AHA! back 😀

    (note I haven't actually run your code yet, so I might be back in a minute*)

    * unlikely as it is now 5:19AM

  • TheSQLGuru (2/5/2010)


    DOH! More goof - you need an index on the value joined or it can never do anything but a table scan/hash. Back in a moment with better analysis...

    Bugger. I knew I should have read it first.

    P.S. You needn't think you can get away with adding indexes!!! Not the same problem as originally stated!

  • Try this:

    use tempdb

    go

    CREATE TABLE dbo.Data

    (

    row_id INTEGER IDENTITY PRIMARY KEY,

    value INTEGER NOT NULL,

    value2 varchar(10)

    );

    INSERT dbo.Data WITH (TABLOCK)

    (value, value2)

    SELECT TOP (750000)

    value = 123456,

    'asdf'

    FROM master.sys.columns C1,

    master.sys.columns C2,

    master.sys.columns C3;

    INSERT dbo.Data

    (value, value2)

    VALUES (95, 'zzz');

    create index idx on data (value)

    set statistics io on --show actual query plan too

    dbcc freeproccache

    go

    DECLARE @OneRow

    TABLE (

    -- row_id INTEGER IDENTITY PRIMARY KEY,

    value INTEGER NOT NULL

    );

    INSERT @OneRow VALUES (123456); --this one does get hash

    --INSERT @OneRow VALUES (95); --so does this one (bad)

    --insert @onerow values (77) --and this one (bad)

    --always hashes

    --cpu 187, duration 701, reads 2560

    SELECT d.value, d.value2

    FROM @OneRow R

    JOIN dbo.Data D

    ON D.value = R.value;

    --run this one for the 95 or 77 value only

    --cpu 0, duration 226, reads 13

    SELECT d.value, d.value2 --on my server this even gets parallelized due to massive estimated row count (75000)!

    FROM @OneRow R --but still MUCH fewer reads

    INNER LOOP JOIN dbo.Data D

    ON D.value = R.value

    --DROP TABLE dbo.Data;

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 7 posts - 31 through 36 (of 36 total)

You must be logged in to reply to this topic. Login to reply