October 21, 2011 at 9:12 am
Here is the parameter plan.
Yes, the parameter is coming in as comma delimited.
October 21, 2011 at 9:13 am
GilaMonster (10/21/2011)
Table variables and large amounts of data? Hell yes that's a problem unless all you're doing is a direct select from the table variable, no joins, no filters.
My guess is
select whatever where col in (SELECT from @table)
Absolutely murders semi-complexe plans (usually).
October 21, 2011 at 9:15 am
Loner (10/21/2011)
Here is the parameter plan.Yes, the parameter is coming in as comma delimited.
We need the ACTUAL plan, this is estimated.
We need it for both good & bad executions.
October 21, 2011 at 9:16 am
I used
select *
from @table
INNER JOIN table1
INNER JOIN table2
WHERE...
October 21, 2011 at 9:17 am
Loner (10/21/2011)
I usedselect *
from @table
INNER JOIN table1
INNER JOIN table2
WHERE...
Yes that's one of the bad ways to use it.
#temp is definitely the way to go here unless you are debuging and need that info unaffected by a rollback.
October 21, 2011 at 9:26 am
Loner (10/21/2011)
I usedselect *
from @table
INNER JOIN table1
INNER JOIN table2
WHERE...
That's just about guaranteed to produce a sub-optimal execution plan. One of the senior devs on the Query Optimiser team explicitly states to never use a table variable anywhere where query costing is important (ie joins, filters, aggregation)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2011 at 9:30 am
GilaMonster (10/21/2011)
Loner (10/21/2011)
I usedselect *
from @table
INNER JOIN table1
INNER JOIN table2
WHERE...
That's just about guaranteed to produce a sub-optimal execution plan. One of the senior devs on the Query Optimiser team explicitly states to never use a table variable anywhere where query costing is important (ie joins, filters, aggregation)
I personally go way more severe (simple) than that.
Everything defaults to temp table untill I need to do something I can't do with temp table.
Actual use of @tbl since then? once to debug a corruption bug.
Now I don't waste time on hunting 'em all down in prod now unless I have an issue with perf on that SSRS report.
October 21, 2011 at 5:35 pm
Such JOIN condition is usually very bad for performance, because the optimizer cannot use an index seek on it:
"ON dbo.TrimLeadingZeros(t.driverid) = dbo.TrimLeadingZeros(r.driverId)"
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply