Can someone tell me why these execution plans are so much different?

Joe.Starnes 2014-01-23 17:34:57

I have 1 query that I pass 2 different variable values but for some reason one takes 25 seconds to run and the other takes 1.

link text

D. Ames 2014-01-24 13:25:28
One thing to check: Is the settings on the database connection the same for both queries?
Joe.Starnes 2014-01-24 14:08:08
Yep these queries are ran against the same Database. I run the identical queries and just pass in 2 different values. for some reason 1 takes long and 1 is really fast.
SQLkiwi 2014-01-27 10:26:29
Isn't it just that the different parameter values cause different branches of the logic to be executed? There are lots of statements there (too many to check in detail) but the plans look very different to me.
Kevin 2014-02-14 18:14:59
Could you take a moment to fully describe ANY differences between the slow and fast run, just to make sure I understand the scenario?

When I look at the plans, there are zero differences between the two sets of runs where the SQL Statements are the same. However, I see that in FAST there are calls to temporary tables (#TempData, for example), while in SLOW there are calls to table variables (@TMPPLTDATA). Table variables have some performance issues compared to temp tables, specifically that the optimizer always assumes that they will return only one row.

However, the bigger thing I learned, is that these batches aren't doing anywhere near the same thing. There's quite a bit of difference between the two.

So it's simply a matter of different batches doing different work.