T-SQL is not quite there yet
DWalker07
some frustrating issues that keep biting me in day-to-day use.
1) Calls to stored procedures can't be nested. That is, one stored
procedure can't do an Insert Into <Table> Exec <StoredProc1> if
StoredProc1 does the same thing to insert data from StoredProc2. Why
not?
Well, we can maybe get around that; chunks of code that return result
sets should be functions anyway. So, recasting these stored procedures
as table-valued functions leads to the recognition of just how bad the
restrictions are ...
2) Table-valued user-defined functions can't create temp tables. That
should be OK, because we can create table variables in user-defined
functions. But ...
3) It's hard to create indexes on table variables. It can be done by
creating a Unique constraint or a Unique Clustered constraint on one set
of columns, but sometimes this is not what we want. I often want non-
unique indexes, and I prefer to create one index per column.
And doing Cross Apply with unindexed tables can be really, really slow.
4) In spite of what I remember Microsoft saying way back when -- that
they would consider it a bug if table variables perform worse than
temporary tables -- I have found many situations where table variables do
perform slower.
For example, inserting data into a table variable from a Select statement
that selects records from a remote SQL server, is much slower than
inserting records from the same Select statement into a temp table.
Always, in my experience.
5) "Remote table-valued function calls are not allowed". Why not? Just
to frustrate us?
I realize that T-SQL is not yet a first class language, but nit-picky
things like "Can't nest Insert Into calls on stored procedures" and
"can't create temp tables in user-defined functions" and "Remote table-
valued function calls are not allowed" all conspire to make using SQL
that much harder.
David Walker
--CELKO--
T-SQL was not meant to be 3GL full language. That is ANSI/ISO
Standard SQL/PSM.
But you need to worry more the declarative features that are missing,
if you are writing proper SQL.
Tony Rogerson
"DWalker07" <no...@none.com> wrote in message
news:uzFdJaMZ...@TK2MSFTNGP05.phx.gbl...
> In SQL 2008, the T-SQL language is getting better, but there are still
> some frustrating issues that keep biting me in day-to-day use.
>
> 1) Calls to stored procedures can't be nested. That is, one stored
> procedure can't do an Insert Into <Table> Exec <StoredProc1> if
> StoredProc1 does the same thing to insert data from StoredProc2. Why
> not?
>
The first stored procedure can create the # table and any other nested one
can insert into that same # table thus giving you want you want.
I agree that is not quite structured in terms its not through the
parameters - this is where making table parameters updateable would be of
benefit.
> Well, we can maybe get around that; chunks of code that return result
> sets should be functions anyway. So, recasting these stored procedures
> as table-valued functions leads to the recognition of just how bad the
> restrictions are ...
You have elluded to the solution though, you should be looking at the table
expression "table valued function" - note - not an inline one! That will
then be expanded into the main query thus giving you the single statement.
But, that can be problematic - often its better to break down SQL statements
and use intermediate # tables, # tables because there are statistics kept on
# tables.
>
> 2) Table-valued user-defined functions can't create temp tables. That
> should be OK, because we can create table variables in user-defined
> functions. But ...
>
We had a discussion in the private groups about this one recently.
Personally I don't think this is a good idea, functions aren't suppose to be
stored procedures.
> 3) It's hard to create indexes on table variables. It can be done by
> creating a Unique constraint or a Unique Clustered constraint on one set
> of columns, but sometimes this is not what we want. I often want non-
> unique indexes, and I prefer to create one index per column.
table variables are only suppose to be for very small data sets - a few
hundred rows; often there is the incorrect perception that a table variable
is an in memory only structure - it isn't; its still a tempdb table at the
end of the day though there is less logging than a # table because table
variables aren't transactional.
>
> And doing Cross Apply with unindexed tables can be really, really slow.
>
CROSS APPLY is literally just running the query for every row so you run the
query 'x' number of times hence if the query isn't efficient in the first
place the problem will be magnified; that isn't a problem with the CROSS
APPLY its the same as it being a sub-query in that if that sub-query tables
aren't properly indexed etc.....
> 4) In spite of what I remember Microsoft saying way back when -- that
> they would consider it a bug if table variables perform worse than
> temporary tables -- I have found many situations where table variables do
> perform slower.
>
I don't honestly remember the exact statement.
Table variables fix the recompile problem we had which is probably what they
were referring to; on a recompile because of a # table it used to recompile
all the statements rather than the individual statement thus causing
everybody else calling the proc to block while the stored proc was being
recompiled.
> For example, inserting data into a table variable from a Select statement
> that selects records from a remote SQL server, is much slower than
> inserting records from the same Select statement into a temp table.
> Always, in my experience.
>
There should be no difference - can you give a repro - I'm really interested
in looking into that.
> 5) "Remote table-valued function calls are not allowed". Why not? Just
> to frustrate us?
>
That is probably because of expansion - when SQL Server looks at your
statement it expands table expressions it can into their real form so views,
table valued functions, cte's and derived tbales are all expanded into the
main query - doing that cross server would be problematic and would require
a recompile on every execution because you wouldn't know if the remote tvf
had been changed (schema).
> I realize that T-SQL is not yet a first class language, but nit-picky
> things like "Can't nest Insert Into calls on stored procedures" and
> "can't create temp tables in user-defined functions" and "Remote table-
> valued function calls are not allowed" all conspire to make using SQL
> that much harder.
>
There are a lot of things that we in business need to make our lives a dam
site easier; at the moment we code round them so its a matter of style of
coding.
What I do recommend though is that you not take what is my view above
because everyone has their own views and experiences but go to Microsoft
Connect and submit the above - I think some are already there in which case
vote on them.
> David Walker
Erland Sommarskog
> 1) Calls to stored procedures can't be nested. That is, one stored
> procedure can't do an Insert Into <Table> Exec <StoredProc1> if
> StoredProc1 does the same thing to insert data from StoredProc2. Why
> not?
My assumption is that they piggyback on the output buffer, and it is
a trick that can only be performed once.
In any case, see http://www.sommarskog.se/share_data.html for alternatives.
In this article I also discuss other problems with INSERT-EXEC and
shows that this is not a very good construct, with or without this
restriction.
> 3) It's hard to create indexes on table variables. It can be done by
> creating a Unique constraint or a Unique Clustered constraint on one set
> of columns, but sometimes this is not what we want. I often want non-
> unique indexes, and I prefer to create one index per column.
Keep in mind that table variables are declared entities and such
they are static. It makes perfect sense that you can add indexes on
them later. Possibly the syntax could permit you declare non-unique
indexes on it. Personally, I've never seen this much of an issue. Since
a non-clustered index includes the clustered index key this means that
adding the primary key to an extra index has no cost, as long as the PK
is clustered.
> 4) In spite of what I remember Microsoft saying way back when -- that
> they would consider it a bug if table variables perform worse than
> temporary tables -- I have found many situations where table variables do
> perform slower.
Certainly. Inserting into table variable precludes parallelism. And
since table variales do not have statistics, the optimizer has
less information than with a temp table which has statistics. Which means
that you get better chances for better query performance with temp tables.
(But with temp tables you can lose on recompiles instead.)
> 5) "Remote table-valued function calls are not allowed". Why not? Just
> to frustrate us?
Overall, there are a lot of restrictions with linked servers. For instance,
neither you cannot pass XML over linked servers.
I assume a lot of this has to do with that the linked server may not
support these features. Keep in mind that from SQL Server's perspective,
the linked server may be Oracle, MySQL or a home-grown data source.
In fact, I don't think there is any support in OLE DB at all for table-
valued functions, and OLE DB is what SQL Server uses the access the linked
server.
Now, since surely over 90% of all linked server in this world, is another
SQL Server instance, one could surely wish that SQL Server had special
support for this.
> I realize that T-SQL is not yet a first class language, but nit-picky
> things like "Can't nest Insert Into calls on stored procedures" and
> "can't create temp tables in user-defined functions" and "Remote table-
> valued function calls are not allowed" all conspire to make using SQL
> that much harder.
Again, I like to stress that the issue on remote table-valued function
is not a language issue. There is simply no infrastructure the language
can use.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
DWalker07
Erland Sommarskog <esq...@sommarskog.se> wrote in
news:Xns9CC3EF0F4...@127.0.0.1:
> DWalker07 (no...@none.com) writes:
>> 1) Calls to stored procedures can't be nested. That is, one stored
>> procedure can't do an Insert Into <Table> Exec <StoredProc1> if
>> StoredProc1 does the same thing to insert data from StoredProc2. Why
>> not?
>
> My assumption is that they piggyback on the output buffer, and it is
> a trick that can only be performed once.
So? That can be overcome.
>
> In any case, see http://www.sommarskog.se/share_data.html for
> alternatives. In this article I also discuss other problems with
> INSERT-EXEC and shows that this is not a very good construct, with or
> without this restriction.
Yes, that is a very good article, and I have read it, and you are a god.
Still, I can hope that one SP can call another SP that calls another SP
and get data returned all the way up.
In the programming world, entities that take parameters and return
results ought to be called functions, not "stored procedures". Stored
procedures that return data seem to be weird, in my mind. Stored
procedures "feel" like they ought to be limited to macros that do admin-
type stuff, rather than returning results. Of course, I know that's not
how SQL works.
>
>> 3) It's hard to create indexes on table variables. It can be done by
>> creating a Unique constraint or a Unique Clustered constraint on one
>> set of columns, but sometimes this is not what we want. I often want
>> non- unique indexes, and I prefer to create one index per column.
>
> Keep in mind that table variables are declared entities and such
> they are static. It makes perfect sense that you can add indexes on
> them later. Possibly the syntax could permit you declare non-unique
> indexes on it. Personally, I've never seen this much of an issue.
But I have seen huge speedups with temp tables when I have added indexes
to them. Sometimes, even, if logically the indexes can't be unique and
so I had to remove the index and then I cried because I couldn't use the
performance speedup.
> Since a non-clustered index includes the clustered index key this
> means that adding the primary key to an extra index has no cost, as
> long as the PK is clustered.
>
>> 4) In spite of what I remember Microsoft saying way back when -- that
>> they would consider it a bug if table variables perform worse than
>> temporary tables -- I have found many situations where table
>> variables do perform slower.
>
> Certainly. Inserting into table variable precludes parallelism.
Why does inserting into a table variable preclude parallelism if the same
is not true of inserting data into a temp table? Or is that also true of
inserting data in to a temporary table?
>And
> since table variales do not have statistics, the optimizer has
> less information than with a temp table which has statistics. Which
> means that you get better chances for better query performance with
> temp tables. (But with temp tables you can lose on recompiles
> instead.)
Many, many Internet blogs (which we all know are not the source of truth)
claim that table variables perform better than temp tables. I know that
blanket statements like that are almost useless, but still, I see blogs
recommending that people change their temp tables to table variables to
get performance improvements. I find it's rarely true. My reply to Tony
Rogerson will have more info.
And, table variables COULD have statistics if Microsoft wished for it to
be so! If a proc creates a temp table, populates it, uses it, and then
drops it, does it have time to get statistics?
>
>> 5) "Remote table-valued function calls are not allowed". Why not?
>> Just to frustrate us?
>
> Overall, there are a lot of restrictions with linked servers. For
> instance, neither you cannot pass XML over linked servers.
Which is another annoying restriction!
>
> I assume a lot of this has to do with that the linked server may not
> support these features.
But it MIGHT support these features! And I thought that there were ways
that the local server can ASK the remote server what features it
supports.
> Keep in mind that from SQL Server's
> perspective, the linked server may be Oracle, MySQL or a home-grown
> data source. In fact, I don't think there is any support in OLE DB at
> all for table- valued functions, and OLE DB is what SQL Server uses
> the access the linked server.
I know that, but I'm not using Oracle, etc.
>
> Now, since surely over 90% of all linked server in this world, is
> another SQL Server instance, one could surely wish that SQL Server had
> special support for this.
Yes!!
>
>> I realize that T-SQL is not yet a first class language, but nit-picky
>> things like "Can't nest Insert Into calls on stored procedures" and
>> "can't create temp tables in user-defined functions" and "Remote
>> table- valued function calls are not allowed" all conspire to make
>> using SQL that much harder.
>
> Again, I like to stress that the issue on remote table-valued function
> is not a language issue. There is simply no infrastructure the
> language can use.
There could be, though. (Anything is possible.) :-)
The catch-22 here is that I want to encapsulate pieces of logic into
functions (not stored procedures, because function A might need to return
data to function B which returns it to function C). And user-defined
functions are limited to table variables which can't be well-indexed.
And see my other post for an example of the slowness of remote calls
using table variables.
David Walker
DWalker07
you asked for.
"Tony Rogerson" <tonyro...@torver.net> wrote in
news:7423DAC9-45E3-4A75...@microsoft.com:
> "DWalker07" <no...@none.com> wrote in message
> news:uzFdJaMZ...@TK2MSFTNGP05.phx.gbl...
>> For example, inserting data into a table variable from a Select
>> statement that selects records from a remote SQL server, is much
>> slower than inserting records from the same Select statement into a
>> temp table. Always, in my experience.
>>
>
> There should be no difference - can you give a repro - I'm really
> interested in looking into that.
Here is a repro. This will be long, and you'll have to adjust it to
reproduce, because you need a remote linked server that you can test
with.
Declare @WorkTable Table
(AnonColumn1 UniqueIdentifier,
AnonColumn2 UniqueIdentifier,
AccountNumber Varchar(30),
RepNumber Varchar(10),
AnonColumn3 Varchar(100), AnonColumn4 Bit, AnonColumn5 Char(1),
AnonColumn6 Varchar(10), AnonColumn7 Bit, AnonColumn8 Bit)
Insert Into @Worktable (RepNumber) Values ('NUI')
Insert Into @Worktable (RepNumber) Values ('GCP')
Insert Into @Worktable (RepNumber) Values ('FLH')
Insert Into @Worktable (RepNumber) Values ('PDM')
Insert Into @Worktable (RepNumber) Values ('UMA')
Insert Into @Worktable (RepNumber) Values ('PWK')
Insert Into @Worktable (RepNumber) Values ('NRE')
Insert Into @Worktable (RepNumber) Values ('PAT')
Insert Into @Worktable (RepNumber) Values ('SEU')
Insert Into @Worktable (RepNumber) Values ('FIF')
Insert Into @Worktable (RepNumber) Values ('EZX')
Insert Into @Worktable (RepNumber) Values ('KGM')
Insert Into @Worktable (RepNumber) Values ('HUD')
Insert Into @Worktable (RepNumber) Values ('LSE')
Insert Into @Worktable (RepNumber) Values ('THN')
Insert Into @Worktable (RepNumber) Values ('XNB')
Insert Into @Worktable (RepNumber) Values ('JCZ')
Insert Into @Worktable (RepNumber) Values ('NLO')
Insert Into @Worktable (RepNumber) Values ('HRR')
Insert Into @Worktable (RepNumber) Values ('BJE')
Insert Into @Worktable (RepNumber) Values ('MZY')
Insert Into @Worktable (RepNumber) Values ('DYD')
Insert Into @Worktable (RepNumber) Values ('KHT')
Insert Into @Worktable (RepNumber) Values ('EQB')
Insert Into @Worktable (RepNumber) Values ('DZR')
Insert Into @Worktable (RepNumber) Values ('PID')
Insert Into @Worktable (RepNumber) Values ('QWL')
Insert Into @Worktable (RepNumber) Values ('BVD')
Insert Into @Worktable (RepNumber) Values ('NEE')
Insert Into @Worktable (RepNumber) Values ('STR')
Insert Into @Worktable (RepNumber) Values ('AOL')
Insert Into @Worktable (RepNumber) Values ('CIC')
Insert Into @Worktable (RepNumber) Values ('VBA')
Insert Into @Worktable (RepNumber) Values ('LIY')
Insert Into @Worktable (RepNumber) Values ('TGV')
Insert Into @Worktable (RepNumber) Values ('ZVC')
Insert Into @Worktable (RepNumber) Values ('KAN')
Insert Into @Worktable (RepNumber) Values ('XHN')
Insert Into @Worktable (RepNumber) Values ('RNJ')
Insert Into @Worktable (RepNumber) Values ('HME')
Insert Into @Worktable (RepNumber) Values ('LFA')
Insert Into @Worktable (RepNumber) Values ('WSV')
Insert Into @Worktable (RepNumber) Values ('CZV')
Insert Into @Worktable (RepNumber) Values ('UNO')
Insert Into @Worktable (RepNumber) Values ('MEZ')
Insert Into @Worktable (RepNumber) Values ('DQD')
Insert Into @Worktable (RepNumber) Values ('PZL')
Insert Into @Worktable (RepNumber) Values ('LHS')
Insert Into @Worktable (RepNumber) Values ('JJP')
Insert Into @Worktable (RepNumber) Values ('GXB')
Insert Into @Worktable (RepNumber) Values ('UGW')
Insert Into @Worktable (RepNumber) Values ('HZW')
Insert Into @Worktable (RepNumber) Values ('SNH')
Insert Into @Worktable (RepNumber) Values ('HLZ')
Insert Into @Worktable (RepNumber) Values ('QZL')
Insert Into @Worktable (RepNumber) Values ('UKX')
Insert Into @Worktable (RepNumber) Values ('LXB')
Insert Into @Worktable (RepNumber) Values ('DAW')
Insert Into @Worktable (RepNumber) Values ('XTN')
Insert Into @Worktable (RepNumber) Values ('QUD')
Insert Into @Worktable (RepNumber) Values ('AER')
Insert Into @Worktable (RepNumber) Values ('DBT')
Insert Into @Worktable (RepNumber) Values ('XQG')
Insert Into @Worktable (RepNumber) Values ('WWB')
Insert Into @Worktable (RepNumber) Values ('RQC')
Insert Into @Worktable (RepNumber) Values ('CDG')
Insert Into @Worktable (RepNumber) Values ('HGC')
Insert Into @Worktable (RepNumber) Values ('DWF')
Insert Into @Worktable (RepNumber) Values ('LHH')
Insert Into @Worktable (RepNumber) Values ('EJS')
Insert Into @Worktable (RepNumber) Values ('NOB')
Insert Into @Worktable (RepNumber) Values ('OJR')
Insert Into @Worktable (RepNumber) Values ('SYW')
Insert Into @Worktable (RepNumber) Values ('DSD')
Insert Into @Worktable (RepNumber) Values ('SPQ')
Insert Into @Worktable (RepNumber) Values ('MLO')
Insert Into @Worktable (RepNumber) Values ('XAA')
Insert Into @Worktable (RepNumber) Values ('AJP')
Insert Into @Worktable (RepNumber) Values ('DGV')
Insert Into @Worktable (RepNumber) Values ('KDT')
Insert Into @Worktable (RepNumber) Values ('OPN')
Insert Into @Worktable (RepNumber) Values ('NYF')
Insert Into @Worktable (RepNumber) Values ('QWD')
Insert Into @Worktable (RepNumber) Values ('BBF')
Insert Into @Worktable (RepNumber) Values ('WFC')
Insert Into @Worktable (RepNumber) Values ('DSX')
Insert Into @Worktable (RepNumber) Values ('OTI')
Insert Into @Worktable (RepNumber) Values ('LGY')
Insert Into @Worktable (RepNumber) Values ('DIH')
Insert Into @Worktable (RepNumber) Values ('RAU')
Insert Into @Worktable (RepNumber) Values ('NIO')
Insert Into @Worktable (RepNumber) Values ('HRC')
Insert Into @Worktable (RepNumber) Values ('UZK')
Insert Into @Worktable (RepNumber) Values ('BJG')
Insert Into @Worktable (RepNumber) Values ('MML')
Insert Into @Worktable (RepNumber) Values ('TQV')
Insert Into @Worktable (RepNumber) Values ('QUV')
Insert Into @Worktable (RepNumber) Values ('BTS')
Insert Into @Worktable (RepNumber) Values ('WZR')
Insert Into @Worktable (RepNumber) Values ('SME')
Insert Into @Worktable (RepNumber) Values ('TTV')
Insert Into @Worktable (RepNumber) Values ('VMW')
Insert Into @Worktable (RepNumber) Values ('IMV')
Insert Into @Worktable (RepNumber) Values ('ZHR')
Insert Into @Worktable (RepNumber) Values ('PLX')
Insert Into @Worktable (RepNumber) Values ('QKL')
Insert Into @Worktable (RepNumber) Values ('LGJ')
Insert Into @Worktable (RepNumber) Values ('JCE')
Insert Into @Worktable (RepNumber) Values ('IFE')
Insert Into @Worktable (RepNumber) Values ('DNH')
Insert Into @Worktable (RepNumber) Values ('OYT')
Insert Into @Worktable (RepNumber) Values ('HFN')
Insert Into @Worktable (RepNumber) Values ('ESG')
Insert Into @Worktable (RepNumber) Values ('DRA')
Insert Into @Worktable (RepNumber) Values ('FXE')
Insert Into @Worktable (RepNumber) Values ('AOZ')
Insert Into @Worktable (RepNumber) Values ('KVO')
Insert Into @Worktable (RepNumber) Values ('MVR')
Insert Into @Worktable (RepNumber) Values ('SPL')
Insert Into @Worktable (RepNumber) Values ('ENB')
Insert Into @Worktable (RepNumber) Values ('PFE')
Insert Into @Worktable (RepNumber) Values ('QXK')
Insert Into @Worktable (RepNumber) Values ('UGE')
Insert Into @Worktable (RepNumber) Values ('RTC')
Insert Into @Worktable (RepNumber) Values ('OIX')
Insert Into @Worktable (RepNumber) Values ('PYY')
Insert Into @Worktable (RepNumber) Values ('PRK')
Insert Into @Worktable (RepNumber) Values ('EZG')
Insert Into @Worktable (RepNumber) Values ('ZGI')
Insert Into @Worktable (RepNumber) Values ('ZME')
Insert Into @Worktable (RepNumber) Values ('KYY')
Insert Into @Worktable (RepNumber) Values ('CDZ')
Insert Into @Worktable (RepNumber) Values ('FJD')
Insert Into @Worktable (RepNumber) Values ('NHP')
Insert Into @Worktable (RepNumber) Values ('OPW')
Insert Into @Worktable (RepNumber) Values ('LAY')
Insert Into @Worktable (RepNumber) Values ('FZA')
Insert Into @Worktable (RepNumber) Values ('ZJF')
Insert Into @Worktable (RepNumber) Values ('NXX')
Insert Into @Worktable (RepNumber) Values ('HDP')
Insert Into @Worktable (RepNumber) Values ('IVU')
Insert Into @Worktable (RepNumber) Values ('PHC')
Insert Into @Worktable (RepNumber) Values ('FCN')
Insert Into @Worktable (RepNumber) Values ('BNW')
Insert Into @Worktable (RepNumber) Values ('XZU')
Insert Into @Worktable (RepNumber) Values ('NPH')
Insert Into @Worktable (RepNumber) Values ('AIY')
Insert Into @Worktable (RepNumber) Values ('ADP')
Insert Into @Worktable (RepNumber) Values ('XVE')
Insert Into @Worktable (RepNumber) Values ('PFZ')
Insert Into @Worktable (RepNumber) Values ('KUX')
Insert Into @Worktable (RepNumber) Values ('QZI')
Insert Into @Worktable (RepNumber) Values ('SIG')
Insert Into @Worktable (RepNumber) Values ('FUW')
Insert Into @Worktable (RepNumber) Values ('MXI')
Insert Into @Worktable (RepNumber) Values ('VDV')
Insert Into @Worktable (RepNumber) Values ('OIF')
Insert Into @Worktable (RepNumber) Values ('ORP')
Insert Into @Worktable (RepNumber) Values ('JKY')
Insert Into @Worktable (RepNumber) Values ('KPW')
Insert Into @Worktable (RepNumber) Values ('PMF')
Insert Into @Worktable (RepNumber) Values ('IVB')
Insert Into @Worktable (RepNumber) Values ('IFJ')
Insert Into @Worktable (RepNumber) Values ('KJI')
Insert Into @Worktable (RepNumber) Values ('TDZ')
Insert Into @Worktable (RepNumber) Values ('SUR')
Insert Into @Worktable (RepNumber) Values ('GZT')
Insert Into @Worktable (RepNumber) Values ('CZS')
Insert Into @Worktable (RepNumber) Values ('KFM')
Insert Into @Worktable (RepNumber) Values ('EYE')
Insert Into @Worktable (RepNumber) Values ('VIY')
Insert Into @Worktable (RepNumber) Values ('MDL')
Insert Into @Worktable (RepNumber) Values ('IDJ')
Insert Into @Worktable (RepNumber) Values ('GPA')
Insert Into @Worktable (RepNumber) Values ('OJC')
Insert Into @Worktable (RepNumber) Values ('YGQ')
Insert Into @Worktable (RepNumber) Values ('LDS')
Insert Into @Worktable (RepNumber) Values ('VEG')
Insert Into @Worktable (RepNumber) Values ('AOA')
Insert Into @Worktable (RepNumber) Values ('IDO')
Insert Into @Worktable (RepNumber) Values ('QSZ')
Insert Into @Worktable (RepNumber) Values ('CWD')
Insert Into @Worktable (RepNumber) Values ('FZN')
Insert Into @Worktable (RepNumber) Values ('SJC')
Insert Into @Worktable (RepNumber) Values ('XCK')
Insert Into @Worktable (RepNumber) Values ('ASJ')
Insert Into @Worktable (RepNumber) Values ('TEB')
Insert Into @Worktable (RepNumber) Values ('HWR')
Insert Into @Worktable (RepNumber) Values ('JFL')
Insert Into @Worktable (RepNumber) Values ('NAE')
Insert Into @Worktable (RepNumber) Values ('FUI')
Insert Into @Worktable (RepNumber) Values ('TCT')
Insert Into @Worktable (RepNumber) Values ('IPT')
Insert Into @Worktable (RepNumber) Values ('CTT')
Insert Into @Worktable (RepNumber) Values ('KIY')
Insert Into @Worktable (RepNumber) Values ('WKS')
Insert Into @Worktable (RepNumber) Values ('AEC')
Insert Into @Worktable (RepNumber) Values ('TYP')
Insert Into @Worktable (RepNumber) Values ('DBI')
Insert Into @Worktable (RepNumber) Values ('FRI')
Insert Into @Worktable (RepNumber) Values ('AWP')
Insert Into @Worktable (RepNumber) Values ('KVM')
Insert Into @Worktable (RepNumber) Values ('MRE')
Insert Into @Worktable (RepNumber) Values ('KXF')
Insert Into @Worktable (RepNumber) Values ('NXG')
Insert Into @Worktable (RepNumber) Values ('WUJ')
Insert Into @Worktable (RepNumber) Values ('YKH')
Insert Into @Worktable (RepNumber) Values ('PXM')
Insert Into @Worktable (RepNumber) Values ('VPT')
Insert Into @Worktable (RepNumber) Values ('CKL')
Insert Into @Worktable (RepNumber) Values ('COG')
Insert Into @Worktable (RepNumber) Values ('HQK')
Insert Into @Worktable (RepNumber) Values ('YXT')
Insert Into @Worktable (RepNumber) Values ('FQE')
Insert Into @Worktable (RepNumber) Values ('IRY')
Insert Into @Worktable (RepNumber) Values ('LUE')
Insert Into @Worktable (RepNumber) Values ('MUW')
Insert Into @Worktable (RepNumber) Values ('TUA')
Insert Into @Worktable (RepNumber) Values ('RWP')
Insert Into @Worktable (RepNumber) Values ('UFU')
Insert Into @Worktable (RepNumber) Values ('OGE')
Insert Into @Worktable (RepNumber) Values ('GYH')
Insert Into @Worktable (RepNumber) Values ('QRS')
Insert Into @Worktable (RepNumber) Values ('JBV')
Insert Into @Worktable (RepNumber) Values ('TVN')
Insert Into @Worktable (RepNumber) Values ('YAV')
Insert Into @Worktable (RepNumber) Values ('PIP')
Insert Into @Worktable (RepNumber) Values ('YKJ')
Insert Into @Worktable (RepNumber) Values ('DHK')
Insert Into @Worktable (RepNumber) Values ('BII')
Insert Into @Worktable (RepNumber) Values ('NGS')
Insert Into @Worktable (RepNumber) Values ('MPH')
Insert Into @Worktable (RepNumber) Values ('PML')
Insert Into @Worktable (RepNumber) Values ('ISO')
Insert Into @Worktable (RepNumber) Values ('LQN')
Insert Into @Worktable (RepNumber) Values ('HBR')
Insert Into @Worktable (RepNumber) Values ('SBF')
Insert Into @Worktable (RepNumber) Values ('YWK')
Insert Into @Worktable (RepNumber) Values ('THM')
Insert Into @Worktable (RepNumber) Values ('CIV')
Insert Into @Worktable (RepNumber) Values ('OVL')
Insert Into @Worktable (RepNumber) Values ('BKE')
Insert Into @Worktable (RepNumber) Values ('VCE')
Insert Into @Worktable (RepNumber) Values ('SJI')
Insert Into @Worktable (RepNumber) Values ('AXO')
Insert Into @Worktable (RepNumber) Values ('HWG')
Insert Into @Worktable (RepNumber) Values ('HDS')
Insert Into @Worktable (RepNumber) Values ('JHD')
Insert Into @Worktable (RepNumber) Values ('XEB')
Insert Into @Worktable (RepNumber) Values ('HRV')
Insert Into @Worktable (RepNumber) Values ('WNE')
Insert Into @Worktable (RepNumber) Values ('EVN')
Insert Into @Worktable (RepNumber) Values ('ATQ')
Insert Into @Worktable (RepNumber) Values ('EWB')
Insert Into @Worktable (RepNumber) Values ('BMQ')
Insert Into @Worktable (RepNumber) Values ('FDF')
Insert Into @Worktable (RepNumber) Values ('BUV')
Insert Into @Worktable (RepNumber) Values ('ONA')
Insert Into @Worktable (RepNumber) Values ('KZW')
Insert Into @Worktable (RepNumber) Values ('JES')
Insert Into @Worktable (RepNumber) Values ('BSQ')
Insert Into @Worktable (RepNumber) Values ('JKV')
Insert Into @Worktable (RepNumber) Values ('SRI')
Insert Into @Worktable (RepNumber) Values ('BCG')
Insert Into @Worktable (RepNumber) Values ('MXG')
Insert Into @Worktable (RepNumber) Values ('WBN')
Insert Into @Worktable (RepNumber) Values ('CEI')
Insert Into @Worktable (RepNumber) Values ('HAU')
Insert Into @Worktable (RepNumber) Values ('HRS')
Insert Into @Worktable (RepNumber) Values ('HNZ')
Insert Into @Worktable (RepNumber) Values ('YFQ')
Insert Into @Worktable (RepNumber) Values ('QGO')
Insert Into @Worktable (RepNumber) Values ('JUS')
Insert Into @Worktable (RepNumber) Values ('MEF')
Insert Into @Worktable (RepNumber) Values ('FMR')
Insert Into @Worktable (RepNumber) Values ('AQN')
Insert Into @Worktable (RepNumber) Values ('HIA')
Insert Into @Worktable (RepNumber) Values ('PLG')
Insert Into @Worktable (RepNumber) Values ('PLO')
Insert Into @Worktable (RepNumber) Values ('UEP')
Insert Into @Worktable (RepNumber) Values ('WRE')
Insert Into @Worktable (RepNumber) Values ('CKX')
Insert Into @Worktable (RepNumber) Values ('CAD')
Insert Into @Worktable (RepNumber) Values ('MOI')
Insert Into @Worktable (RepNumber) Values ('FQF')
Insert Into @Worktable (RepNumber) Values ('UDH')
Insert Into @Worktable (RepNumber) Values ('HJG')
Insert Into @Worktable (RepNumber) Values ('WLW')
Insert Into @Worktable (RepNumber) Values ('KQE')
Insert Into @Worktable (RepNumber) Values ('AOM')
Insert Into @Worktable (RepNumber) Values ('JET')
Insert Into @Worktable (RepNumber) Values ('DTM')
Insert Into @Worktable (RepNumber) Values ('ZDT')
Insert Into @Worktable (RepNumber) Values ('XUF')
Insert Into @Worktable (RepNumber) Values ('SWR')
Insert Into @Worktable (RepNumber) Values ('TAO')
Insert Into @Worktable (RepNumber) Values ('GXN')
Insert Into @Worktable (RepNumber) Values ('SWM')
Insert Into @Worktable (RepNumber) Values ('PMA')
Insert Into @Worktable (RepNumber) Values ('BXD')
Insert Into @Worktable (RepNumber) Values ('BDN')
Insert Into @Worktable (RepNumber) Values ('OML')
Insert Into @Worktable (RepNumber) Values ('XHP')
Insert Into @Worktable (RepNumber) Values ('XRE')
Insert Into @Worktable (RepNumber) Values ('HHA')
Insert Into @Worktable (RepNumber) Values ('LCC')
Insert Into @Worktable (RepNumber) Values ('VGU')
Insert Into @Worktable (RepNumber) Values ('RPW')
Insert Into @Worktable (RepNumber) Values ('EPL')
Insert Into @Worktable (RepNumber) Values ('RAJ')
Insert Into @Worktable (RepNumber) Values ('UGG')
Insert Into @Worktable (RepNumber) Values ('RRM')
Insert Into @Worktable (RepNumber) Values ('TNL')
Insert Into @Worktable (RepNumber) Values ('NKM')
Insert Into @Worktable (RepNumber) Values ('PTM')
Insert Into @Worktable (RepNumber) Values ('BOQ')
Insert Into @Worktable (RepNumber) Values ('MFL')
Insert Into @Worktable (RepNumber) Values ('BLI')
Insert Into @Worktable (RepNumber) Values ('LXD')
Insert Into @Worktable (RepNumber) Values ('SIY')
Insert Into @Worktable (RepNumber) Values ('YAN')
Insert Into @Worktable (RepNumber) Values ('NNI')
Insert Into @Worktable (RepNumber) Values ('CIF')
Insert Into @Worktable (RepNumber) Values ('ULD')
Insert Into @Worktable (RepNumber) Values ('FPJ')
Insert Into @Worktable (RepNumber) Values ('TTU')
Insert Into @Worktable (RepNumber) Values ('HBL')
Insert Into @Worktable (RepNumber) Values ('HSK')
Insert Into @Worktable (RepNumber) Values ('FND')
Insert Into @Worktable (RepNumber) Values ('UED')
Insert Into @Worktable (RepNumber) Values ('RGR')
Insert Into @Worktable (RepNumber) Values ('SJG')
Insert Into @Worktable (RepNumber) Values ('IFH')
Insert Into @Worktable (RepNumber) Values ('NLT')
Insert Into @Worktable (RepNumber) Values ('TVH')
Insert Into @Worktable (RepNumber) Values ('PHI')
Insert Into @Worktable (RepNumber) Values ('GKF')
Insert Into @Worktable (RepNumber) Values ('DSI')
Insert Into @Worktable (RepNumber) Values ('MTJ')
Insert Into @Worktable (RepNumber) Values ('VUZ')
Insert Into @Worktable (RepNumber) Values ('FZY')
Insert Into @Worktable (RepNumber) Values ('OBU')
Insert Into @Worktable (RepNumber) Values ('USY')
Insert Into @Worktable (RepNumber) Values ('FAR')
Insert Into @Worktable (RepNumber) Values ('CXQ')
Insert Into @Worktable (RepNumber) Values ('ERZ')
Insert Into @Worktable (RepNumber) Values ('CVA')
Insert Into @Worktable (RepNumber) Values ('HAS')
Insert Into @Worktable (RepNumber) Values ('TNG')
Insert Into @Worktable (RepNumber) Values ('AYN')
Insert Into @Worktable (RepNumber) Values ('ZDS')
Insert Into @Worktable (RepNumber) Values ('VRD')
Insert Into @Worktable (RepNumber) Values ('NWI')
Insert Into @Worktable (RepNumber) Values ('WTG')
Insert Into @Worktable (RepNumber) Values ('QNU')
Insert Into @Worktable (RepNumber) Values ('OVM')
Insert Into @Worktable (RepNumber) Values ('PDD')
Insert Into @Worktable (RepNumber) Values ('ONT')
Insert Into @Worktable (RepNumber) Values ('YXS')
Insert Into @Worktable (RepNumber) Values ('JBH')
Insert Into @Worktable (RepNumber) Values ('IQF')
Insert Into @Worktable (RepNumber) Values ('CRL')
Insert Into @Worktable (RepNumber) Values ('GIF')
Insert Into @Worktable (RepNumber) Values ('XPY')
Insert Into @Worktable (RepNumber) Values ('SEE')
Insert Into @Worktable (RepNumber) Values ('VLF')
Insert Into @Worktable (RepNumber) Values ('XDN')
Insert Into @Worktable (RepNumber) Values ('NIB')
Insert Into @Worktable (RepNumber) Values ('EZK')
Insert Into @Worktable (RepNumber) Values ('PKP')
Insert Into @Worktable (RepNumber) Values ('YKI')
Insert Into @Worktable (RepNumber) Values ('PYT')
Insert Into @Worktable (RepNumber) Values ('DPN')
Insert Into @Worktable (RepNumber) Values ('NTN')
Insert Into @Worktable (RepNumber) Values ('PYU')
Insert Into @Worktable (RepNumber) Values ('ZYD')
Insert Into @Worktable (RepNumber) Values ('VII')
Insert Into @Worktable (RepNumber) Values ('TBI')
Insert Into @Worktable (RepNumber) Values ('HJX')
Insert Into @Worktable (RepNumber) Values ('HTC')
Insert Into @Worktable (RepNumber) Values ('PPO')
Insert Into @Worktable (RepNumber) Values ('MIK')
Insert Into @Worktable (RepNumber) Values ('HQY')
Insert Into @Worktable (RepNumber) Values ('KTY')
Insert Into @Worktable (RepNumber) Values ('YEU')
Insert Into @Worktable (RepNumber) Values ('XVP')
Insert Into @Worktable (RepNumber) Values ('LBP')
Insert Into @Worktable (RepNumber) Values ('GFQ')
Insert Into @Worktable (RepNumber) Values ('DQN')
Insert Into @Worktable (RepNumber) Values ('QPQ')
Insert Into @Worktable (RepNumber) Values ('HDZ')
Insert Into @Worktable (RepNumber) Values ('VUY')
Insert Into @Worktable (RepNumber) Values ('HXF')
Insert Into @Worktable (RepNumber) Values ('NRS')
Insert Into @Worktable (RepNumber) Values ('XBW')
Insert Into @Worktable (RepNumber) Values ('ZQF')
Insert Into @Worktable (RepNumber) Values ('YLS')
Declare @RepInfoTable Table
(PrimaryRepCode varchar(20), AnonColumn1 Char(1),
AnonColumn2 Char(1), AnonColumn3 Varchar(10),
AnonColumn4 Varchar(10))
Declare @StartTime DateTime
Set @StartTime = GetDate()
-- HERE is the statement we are testing.
Insert Into @RepInfoTable (PrimaryRepCode, AnonColumn1,
AnonColumn2, AnonColumn3, AnonColumn4)
Select PrimaryRepCode, ExternalRepInfoTable.AnonColumn1,
ExternalRepInfoTable.AnonColumn2,
ExternalRepInfoTable.AnonColumn3,
ExternalRepInfoTable.AnonColumn4
-- Inner Join and Inner Remote Join: see note below.
From @WorkTable Inner Remote Join ExternalRepInfoTable
On [@WorkTable].RepNumber = ExternalRepInfoTable.PrimaryRepCode
And ExternalRepInfoTable.RepActive = 1
-- Alternate version, which takes the same amount of time:
--From ExternalRepInfoTable
--Where Exists (Select 1 From @WorkTable
-- Where RepNumber = ExternalRepInfoTable.PrimaryRepCode
-- And ExternalRepInfoTable.RepActive = 1)
Select @StartTime, 'Populate @RepInfoTable', Cast(86400.0*Cast(GetDate()-
@StartTime As Decimal(14, 8)) As Decimal(8,2))
*****
Notes:
I am checking elapsed time because 1) it is very repeatable for me, and
2) SQL's estimated execution plan and actual execution plan both suck
when there are remote tables involved, and 3) it's easier to do this than
to do a trace. And item number 1 makes it a good test. And elapsed time
is what's important to our users who are using the program and staring at
the user interface.
We use NVarchar and NChar instead of Varchar and Char in our local
tables, but the remote tables here are Varchar and Char, so I used those
in the temp table/table variable declarations.
Changing "And ExternalRepInfoTable.RepActive = 1" to "Where
ExternalRepInfoTable.RepActive = 1" in the statement above makes no
difference (I suspect the execution plan is the same).
Inner Join and Inner Remote Join take the same amount of time (30
seconds) when @WorkTable is a table variable.
Adding a unique index on @WorkTable does not make the run time any
faster.
Inner Join takes 0.36 seconds when @WorkTable is changed to a temp table
with no indexes defined. THIS is what baffles me!!! And it's
repeatable. 0.36 seconds is much better than 30 seconds.
When @WorkTable is changed to a temp table with no indexes defined, and
Inner Join is changed to Inner Remote Join, the statement takes 30
seconds again.
The thing called ExternalRepInfoTable is a synonym (so that it can be
updated without changing actual code) which points to a linked server
name and a view name on a remote SQL 2005 server (these servers are a few
feet apart, connected by gigabit Ethernet). It contains about 8000
records. All records in the work table are present in the
ExternalRepInfoTable.
It doesn't matter if the remote server is SQL 2000 or SQL 2005 or SQL
2008, and it doesn't matter if the local server where these are run is
SQL 2005 or SQL 2008. I get the same, consistent, repeatable results in
every combination.
Thanks for your interest. I have seen this effect (where table variables
are slow when they are involved in any kind of remote-ninja-magic, while
temp tables are not) for 3 years now. Maybe you can discover something
about what's happening. I predict that you can preproduce it: I hope
so.
David Walker
Linchi Shea
perspective. It puzzles me greatly that it has not been addressed for this
long.
Linchi
"DWalker07" wrote:
> .
>
Erland Sommarskog
> Thanks for the reply. Please see below.
Well, whereelse would I look? Above? :-)
>> My assumption is that they piggyback on the output buffer, and it is
>> a trick that can only be performed once.
>
> So? That can be overcome.
Maybe. But then it would call for a completely different architecture,
which reqiure a lot more work to do. Keep in mind if my assumption is
right, the choice is not really between INSERT-EXEC being possible in
multiple levels or only one; no, the choice is between INSERT-EXEC
being available in one level only, or not at all.
I like to stress that this is quite a lot of speculation on my part.
What is not speculation, however, is that INSERT-EXEC from a program-
mabiliy point of view is a fragile feature. Input-output table-valued
parameters would be the real solution.
> In the programming world, entities that take parameters and return
> results ought to be called functions, not "stored procedures". Stored
> procedures that return data seem to be weird, in my mind. Stored
> procedures "feel" like they ought to be limited to macros that do admin-
> type stuff, rather than returning results. Of course, I know that's not
> how SQL works.
Don't confuse T-SQL with client-side languages. SQL Server is a server-
side application, and needs to return data to the client to be useful.
Returning a result set from a stored procedure is a natural thing to do.
(Yes, I could think of differnt paradigms using TVPs, but we are not
quite there.)
> But I have seen huge speedups with temp tables when I have added indexes
> to them. Sometimes, even, if logically the indexes can't be unique and
> so I had to remove the index and then I cried because I couldn't use the
> performance speedup.
I don't dispute that you have seen performance benefits by indexing
temp tables. More than once I have been able to achieve huge performance
gains only by putting keys on temp tables that had none.
But here is a tip: also for temp tables it's better to add indexes up
front. SQL Server 2005 and later, cache temp table definitions, which
is good for performance in a high-load scenario. However, if you add
indexes with CREATE INDEX, this precludes caching.
> Why does inserting into a table variable preclude parallelism if the same
> is not true of inserting data into a temp table? Or is that also true of
> inserting data in to a temporary table?
That's some internal thing that I don't really remember. But it has to
do with that table variables lives in another part the engine than
regular tables.
> Many, many Internet blogs (which we all know are not the source of truth)
> claim that table variables perform better than temp tables.
Microsoft has touted this as well. But I have been able to make huge
performance gains by replacing a table variable with a temp table. And
vice versa.
> And, table variables COULD have statistics if Microsoft wished for it to
> be so! If a proc creates a temp table, populates it, uses it, and then
> drops it, does it have time to get statistics?
The cheif reason I've gotten performance benefits with table variables
over temp tables is precisely the lack of statistics. No statistics, no
recompiles.
>> Overall, there are a lot of restrictions with linked servers. For
>> instance, neither you cannot pass XML over linked servers.
>
> Which is another annoying restriction!
But as I said there is good reason for it. There simply is no infra-
structure available.
>> I assume a lot of this has to do with that the linked server may not
>> support these features.
>
> But it MIGHT support these features! And I thought that there were ways
> that the local server can ASK the remote server what features it
> supports.
There is indeed ways in OLE DB to inquire what the provider supports.
Still I suspect that it is not entirely unproblematic.
>> Keep in mind that from SQL Server's
>> perspective, the linked server may be Oracle, MySQL or a home-grown
>> data source. In fact, I don't think there is any support in OLE DB at
>> all for table- valued functions, and OLE DB is what SQL Server uses
>> the access the linked server.
>
> I know that, but I'm not using Oracle, etc.
I got bad news for you: SQL Server is not written for you only.
Erland Sommarskog
> I am checking elapsed time because 1) it is very repeatable for me, and
> 2) SQL's estimated execution plan and actual execution plan both suck
> when there are remote tables involved, and 3) it's easier to do this than
> to do a trace.
Measuring wallclock time is the right thing to do.
> Changing "And ExternalRepInfoTable.RepActive = 1" to "Where
> ExternalRepInfoTable.RepActive = 1" in the statement above makes no
> difference (I suspect the execution plan is the same).
They should be, as they are logically the same.
> Inner Join and Inner Remote Join take the same amount of time (30
> seconds) when @WorkTable is a table variable.
>...
> Adding a unique index on @WorkTable does not make the run time any
> faster.
>
> Inner Join takes 0.36 seconds when @WorkTable is changed to a temp table
> with no indexes defined. THIS is what baffles me!!! And it's
> repeatable. 0.36 seconds is much better than 30 seconds.
I was able to measure difference between table variable and a temp
table, but not that extreme. Then again, I don't have the data for the
remote table, so I had to make it up. (See below.)
The query plans are different, and no doubt this due to that table
variables do not have statistics and cannot trigger recompilation,
which a temp table can do.
And indeed, when I added OPTION (RECOMPILE) to the statement with the
table variable, I now get somewhat better execution time with a table
variable. (170 ms vs. 260 ms.) The reason the RECOMPILE hint helps is
that while table variables do not have statistics, they do have cardinality
information, and that is what SQL Server needs here to get it right.
Interesting enough, the plans are still different. They are about the
same, but the plan for the table variable has a spool operator. This
difference may explain why the table variable is faster.
Please try using OPTION (RECOMPILE) in your test case, and let me know
how it works out. If you still get poor execution times, I think it's
time to look at collations.
......................................................................
Here is my modified repro (I changed the INSERT statements into one, to
be table to look at the query plans.)
Notes:
1) The script includes a comment with a table definition to run on the
remote server. You need the script to load the remote table as it's
loaded frmo @Worktable.
2) You also need a Numbers table,
http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum
3) You need to change the name of the linked server.
SET NOCOUNT ON
Declare @Worktable Table
(AnonColumn1 UniqueIdentifier,
AnonColumn2 UniqueIdentifier,
AccountNumber Varchar(30),
RepNumber Varchar(10),
AnonColumn3 Varchar(100), AnonColumn4 Bit, AnonColumn5 Char(1),
AnonColumn6 Varchar(10), AnonColumn7 Bit, AnonColumn8 Bit)
Insert Into @Worktable (RepNumber)
SELECT 'NUI'
UNION ALL SELECT 'GCP'
UNION ALL SELECT 'FLH'
UNION ALL SELECT 'PDM'
UNION ALL SELECT 'UMA'
UNION ALL SELECT 'PWK'
UNION ALL SELECT 'NRE'
UNION ALL SELECT 'PAT'
UNION ALL SELECT 'SEU'
UNION ALL SELECT 'FIF'
UNION ALL SELECT 'EZX'
UNION ALL SELECT 'KGM'
UNION ALL SELECT 'HUD'
UNION ALL SELECT 'LSE'
UNION ALL SELECT 'THN'
UNION ALL SELECT 'XNB'
UNION ALL SELECT 'JCZ'
UNION ALL SELECT 'NLO'
UNION ALL SELECT 'HRR'
UNION ALL SELECT 'BJE'
UNION ALL SELECT 'MZY'
UNION ALL SELECT 'DYD'
UNION ALL SELECT 'KHT'
UNION ALL SELECT 'EQB'
UNION ALL SELECT 'DZR'
UNION ALL SELECT 'PID'
UNION ALL SELECT 'QWL'
UNION ALL SELECT 'BVD'
UNION ALL SELECT 'NEE'
UNION ALL SELECT 'STR'
UNION ALL SELECT 'AOL'
UNION ALL SELECT 'CIC'
UNION ALL SELECT 'VBA'
UNION ALL SELECT 'LIY'
UNION ALL SELECT 'TGV'
UNION ALL SELECT 'ZVC'
UNION ALL SELECT 'KAN'
UNION ALL SELECT 'XHN'
UNION ALL SELECT 'RNJ'
UNION ALL SELECT 'HME'
UNION ALL SELECT 'LFA'
UNION ALL SELECT 'WSV'
UNION ALL SELECT 'CZV'
UNION ALL SELECT 'UNO'
UNION ALL SELECT 'MEZ'
UNION ALL SELECT 'DQD'
UNION ALL SELECT 'PZL'
UNION ALL SELECT 'LHS'
UNION ALL SELECT 'JJP'
UNION ALL SELECT 'GXB'
UNION ALL SELECT 'UGW'
UNION ALL SELECT 'HZW'
UNION ALL SELECT 'SNH'
UNION ALL SELECT 'HLZ'
UNION ALL SELECT 'QZL'
UNION ALL SELECT 'UKX'
UNION ALL SELECT 'LXB'
UNION ALL SELECT 'DAW'
UNION ALL SELECT 'XTN'
UNION ALL SELECT 'QUD'
UNION ALL SELECT 'AER'
UNION ALL SELECT 'DBT'
UNION ALL SELECT 'XQG'
UNION ALL SELECT 'WWB'
UNION ALL SELECT 'RQC'
UNION ALL SELECT 'CDG'
UNION ALL SELECT 'HGC'
UNION ALL SELECT 'DWF'
UNION ALL SELECT 'LHH'
UNION ALL SELECT 'EJS'
UNION ALL SELECT 'NOB'
UNION ALL SELECT 'OJR'
UNION ALL SELECT 'SYW'
UNION ALL SELECT 'DSD'
UNION ALL SELECT 'SPQ'
UNION ALL SELECT 'MLO'
UNION ALL SELECT 'XAA'
UNION ALL SELECT 'AJP'
UNION ALL SELECT 'DGV'
UNION ALL SELECT 'KDT'
UNION ALL SELECT 'OPN'
UNION ALL SELECT 'NYF'
UNION ALL SELECT 'QWD'
UNION ALL SELECT 'BBF'
UNION ALL SELECT 'WFC'
UNION ALL SELECT 'DSX'
UNION ALL SELECT 'OTI'
UNION ALL SELECT 'LGY'
UNION ALL SELECT 'DIH'
UNION ALL SELECT 'RAU'
UNION ALL SELECT 'NIO'
UNION ALL SELECT 'HRC'
UNION ALL SELECT 'UZK'
UNION ALL SELECT 'BJG'
UNION ALL SELECT 'MML'
UNION ALL SELECT 'TQV'
UNION ALL SELECT 'QUV'
UNION ALL SELECT 'BTS'
UNION ALL SELECT 'WZR'
UNION ALL SELECT 'SME'
UNION ALL SELECT 'TTV'
UNION ALL SELECT 'VMW'
UNION ALL SELECT 'IMV'
UNION ALL SELECT 'ZHR'
UNION ALL SELECT 'PLX'
UNION ALL SELECT 'QKL'
UNION ALL SELECT 'LGJ'
UNION ALL SELECT 'JCE'
UNION ALL SELECT 'IFE'
UNION ALL SELECT 'DNH'
UNION ALL SELECT 'OYT'
UNION ALL SELECT 'HFN'
UNION ALL SELECT 'ESG'
UNION ALL SELECT 'DRA'
UNION ALL SELECT 'FXE'
UNION ALL SELECT 'AOZ'
UNION ALL SELECT 'KVO'
UNION ALL SELECT 'MVR'
UNION ALL SELECT 'SPL'
UNION ALL SELECT 'ENB'
UNION ALL SELECT 'PFE'
UNION ALL SELECT 'QXK'
UNION ALL SELECT 'UGE'
UNION ALL SELECT 'RTC'
UNION ALL SELECT 'OIX'
UNION ALL SELECT 'PYY'
UNION ALL SELECT 'PRK'
UNION ALL SELECT 'EZG'
UNION ALL SELECT 'ZGI'
UNION ALL SELECT 'ZME'
UNION ALL SELECT 'KYY'
UNION ALL SELECT 'CDZ'
UNION ALL SELECT 'FJD'
UNION ALL SELECT 'NHP'
UNION ALL SELECT 'OPW'
UNION ALL SELECT 'LAY'
UNION ALL SELECT 'FZA'
UNION ALL SELECT 'ZJF'
UNION ALL SELECT 'NXX'
UNION ALL SELECT 'HDP'
UNION ALL SELECT 'IVU'
UNION ALL SELECT 'PHC'
UNION ALL SELECT 'FCN'
UNION ALL SELECT 'BNW'
UNION ALL SELECT 'XZU'
UNION ALL SELECT 'NPH'
UNION ALL SELECT 'AIY'
UNION ALL SELECT 'ADP'
UNION ALL SELECT 'XVE'
UNION ALL SELECT 'PFZ'
UNION ALL SELECT 'KUX'
UNION ALL SELECT 'QZI'
UNION ALL SELECT 'SIG'
UNION ALL SELECT 'FUW'
UNION ALL SELECT 'MXI'
UNION ALL SELECT 'VDV'
UNION ALL SELECT 'OIF'
UNION ALL SELECT 'ORP'
UNION ALL SELECT 'JKY'
UNION ALL SELECT 'KPW'
UNION ALL SELECT 'PMF'
UNION ALL SELECT 'IVB'
UNION ALL SELECT 'IFJ'
UNION ALL SELECT 'KJI'
UNION ALL SELECT 'TDZ'
UNION ALL SELECT 'SUR'
UNION ALL SELECT 'GZT'
UNION ALL SELECT 'CZS'
UNION ALL SELECT 'KFM'
UNION ALL SELECT 'EYE'
UNION ALL SELECT 'VIY'
UNION ALL SELECT 'MDL'
UNION ALL SELECT 'IDJ'
UNION ALL SELECT 'GPA'
UNION ALL SELECT 'OJC'
UNION ALL SELECT 'YGQ'
UNION ALL SELECT 'LDS'
UNION ALL SELECT 'VEG'
UNION ALL SELECT 'AOA'
UNION ALL SELECT 'IDO'
UNION ALL SELECT 'QSZ'
UNION ALL SELECT 'CWD'
UNION ALL SELECT 'FZN'
UNION ALL SELECT 'SJC'
UNION ALL SELECT 'XCK'
UNION ALL SELECT 'ASJ'
UNION ALL SELECT 'TEB'
UNION ALL SELECT 'HWR'
UNION ALL SELECT 'JFL'
UNION ALL SELECT 'NAE'
UNION ALL SELECT 'FUI'
UNION ALL SELECT 'TCT'
UNION ALL SELECT 'IPT'
UNION ALL SELECT 'CTT'
UNION ALL SELECT 'KIY'
UNION ALL SELECT 'WKS'
UNION ALL SELECT 'AEC'
UNION ALL SELECT 'TYP'
UNION ALL SELECT 'DBI'
UNION ALL SELECT 'FRI'
UNION ALL SELECT 'AWP'
UNION ALL SELECT 'KVM'
UNION ALL SELECT 'MRE'
UNION ALL SELECT 'KXF'
UNION ALL SELECT 'NXG'
UNION ALL SELECT 'WUJ'
UNION ALL SELECT 'YKH'
UNION ALL SELECT 'PXM'
UNION ALL SELECT 'VPT'
UNION ALL SELECT 'CKL'
UNION ALL SELECT 'COG'
UNION ALL SELECT 'HQK'
UNION ALL SELECT 'YXT'
UNION ALL SELECT 'FQE'
UNION ALL SELECT 'IRY'
UNION ALL SELECT 'LUE'
UNION ALL SELECT 'MUW'
UNION ALL SELECT 'TUA'
UNION ALL SELECT 'RWP'
UNION ALL SELECT 'UFU'
UNION ALL SELECT 'OGE'
UNION ALL SELECT 'GYH'
UNION ALL SELECT 'QRS'
UNION ALL SELECT 'JBV'
UNION ALL SELECT 'TVN'
UNION ALL SELECT 'YAV'
UNION ALL SELECT 'PIP'
UNION ALL SELECT 'YKJ'
UNION ALL SELECT 'DHK'
UNION ALL SELECT 'BII'
UNION ALL SELECT 'NGS'
UNION ALL SELECT 'MPH'
UNION ALL SELECT 'PML'
UNION ALL SELECT 'ISO'
UNION ALL SELECT 'LQN'
UNION ALL SELECT 'HBR'
UNION ALL SELECT 'SBF'
UNION ALL SELECT 'YWK'
UNION ALL SELECT 'THM'
UNION ALL SELECT 'CIV'
UNION ALL SELECT 'OVL'
UNION ALL SELECT 'BKE'
UNION ALL SELECT 'VCE'
UNION ALL SELECT 'SJI'
UNION ALL SELECT 'AXO'
UNION ALL SELECT 'HWG'
UNION ALL SELECT 'HDS'
UNION ALL SELECT 'JHD'
UNION ALL SELECT 'XEB'
UNION ALL SELECT 'HRV'
UNION ALL SELECT 'WNE'
UNION ALL SELECT 'EVN'
UNION ALL SELECT 'ATQ'
UNION ALL SELECT 'EWB'
UNION ALL SELECT 'BMQ'
UNION ALL SELECT 'FDF'
UNION ALL SELECT 'BUV'
UNION ALL SELECT 'ONA'
UNION ALL SELECT 'KZW'
UNION ALL SELECT 'JES'
UNION ALL SELECT 'BSQ'
UNION ALL SELECT 'JKV'
UNION ALL SELECT 'SRI'
UNION ALL SELECT 'BCG'
UNION ALL SELECT 'MXG'
UNION ALL SELECT 'WBN'
UNION ALL SELECT 'CEI'
UNION ALL SELECT 'HAU'
UNION ALL SELECT 'HRS'
UNION ALL SELECT 'HNZ'
UNION ALL SELECT 'YFQ'
UNION ALL SELECT 'QGO'
UNION ALL SELECT 'JUS'
UNION ALL SELECT 'MEF'
UNION ALL SELECT 'FMR'
UNION ALL SELECT 'AQN'
UNION ALL SELECT 'HIA'
UNION ALL SELECT 'PLG'
UNION ALL SELECT 'PLO'
UNION ALL SELECT 'UEP'
UNION ALL SELECT 'WRE'
UNION ALL SELECT 'CKX'
UNION ALL SELECT 'CAD'
UNION ALL SELECT 'MOI'
UNION ALL SELECT 'FQF'
UNION ALL SELECT 'UDH'
UNION ALL SELECT 'HJG'
UNION ALL SELECT 'WLW'
UNION ALL SELECT 'KQE'
UNION ALL SELECT 'AOM'
UNION ALL SELECT 'JET'
UNION ALL SELECT 'DTM'
UNION ALL SELECT 'ZDT'
UNION ALL SELECT 'XUF'
UNION ALL SELECT 'SWR'
UNION ALL SELECT 'TAO'
UNION ALL SELECT 'GXN'
UNION ALL SELECT 'SWM'
UNION ALL SELECT 'PMA'
UNION ALL SELECT 'BXD'
UNION ALL SELECT 'BDN'
UNION ALL SELECT 'OML'
UNION ALL SELECT 'XHP'
UNION ALL SELECT 'XRE'
UNION ALL SELECT 'HHA'
UNION ALL SELECT 'LCC'
UNION ALL SELECT 'VGU'
UNION ALL SELECT 'RPW'
UNION ALL SELECT 'EPL'
UNION ALL SELECT 'RAJ'
UNION ALL SELECT 'UGG'
UNION ALL SELECT 'RRM'
UNION ALL SELECT 'TNL'
UNION ALL SELECT 'NKM'
UNION ALL SELECT 'PTM'
UNION ALL SELECT 'BOQ'
UNION ALL SELECT 'MFL'
UNION ALL SELECT 'BLI'
UNION ALL SELECT 'LXD'
UNION ALL SELECT 'SIY'
UNION ALL SELECT 'YAN'
UNION ALL SELECT 'NNI'
UNION ALL SELECT 'CIF'
UNION ALL SELECT 'ULD'
UNION ALL SELECT 'FPJ'
UNION ALL SELECT 'TTU'
UNION ALL SELECT 'HBL'
UNION ALL SELECT 'HSK'
UNION ALL SELECT 'FND'
UNION ALL SELECT 'UED'
UNION ALL SELECT 'RGR'
UNION ALL SELECT 'SJG'
UNION ALL SELECT 'IFH'
UNION ALL SELECT 'NLT'
UNION ALL SELECT 'TVH'
UNION ALL SELECT 'PHI'
UNION ALL SELECT 'GKF'
UNION ALL SELECT 'DSI'
UNION ALL SELECT 'MTJ'
UNION ALL SELECT 'VUZ'
UNION ALL SELECT 'FZY'
UNION ALL SELECT 'OBU'
UNION ALL SELECT 'USY'
UNION ALL SELECT 'FAR'
UNION ALL SELECT 'CXQ'
UNION ALL SELECT 'ERZ'
UNION ALL SELECT 'CVA'
UNION ALL SELECT 'HAS'
UNION ALL SELECT 'TNG'
UNION ALL SELECT 'AYN'
UNION ALL SELECT 'ZDS'
UNION ALL SELECT 'VRD'
UNION ALL SELECT 'NWI'
UNION ALL SELECT 'WTG'
UNION ALL SELECT 'QNU'
UNION ALL SELECT 'OVM'
UNION ALL SELECT 'PDD'
UNION ALL SELECT 'ONT'
UNION ALL SELECT 'YXS'
UNION ALL SELECT 'JBH'
UNION ALL SELECT 'IQF'
UNION ALL SELECT 'CRL'
UNION ALL SELECT 'GIF'
UNION ALL SELECT 'XPY'
UNION ALL SELECT 'SEE'
UNION ALL SELECT 'VLF'
UNION ALL SELECT 'XDN'
UNION ALL SELECT 'NIB'
UNION ALL SELECT 'EZK'
UNION ALL SELECT 'PKP'
UNION ALL SELECT 'YKI'
UNION ALL SELECT 'PYT'
UNION ALL SELECT 'DPN'
UNION ALL SELECT 'NTN'
UNION ALL SELECT 'PYU'
UNION ALL SELECT 'ZYD'
UNION ALL SELECT 'VII'
UNION ALL SELECT 'TBI'
UNION ALL SELECT 'HJX'
UNION ALL SELECT 'HTC'
UNION ALL SELECT 'PPO'
UNION ALL SELECT 'MIK'
UNION ALL SELECT 'HQY'
UNION ALL SELECT 'KTY'
UNION ALL SELECT 'YEU'
UNION ALL SELECT 'XVP'
UNION ALL SELECT 'LBP'
UNION ALL SELECT 'GFQ'
UNION ALL SELECT 'DQN'
UNION ALL SELECT 'QPQ'
UNION ALL SELECT 'HDZ'
UNION ALL SELECT 'VUY'
UNION ALL SELECT 'HXF'
UNION ALL SELECT 'NRS'
UNION ALL SELECT 'XBW'
UNION ALL SELECT 'ZQF'
UNION ALL SELECT 'YLS'
Declare @RepInfoTable Table
(PrimaryRepCode varchar(20), AnonColumn1 Char(1),
AnonColumn2 Char(1), AnonColumn3 Varchar(10),
AnonColumn4 Varchar(10))
/* Run this on the remote server only.
create table ExternalRepInfoTable
(PrimaryRepCode varchar(20) NOT NULL PRIMARY KEY, AnonColumn1 Char(1),
AnonColumn2 Char(1), AnonColumn3 Char(10),
AnonColumn4 Varchar(10), RepActive bit NOT NULL)
INSERT ExternalRepInfoTable (PrimaryRepCode, RepActive)
SELECT RepNumber, ascii(RepNumber) % 2
FROM @Worktable
INSERT ExternalRepInfoTable (PrimaryRepCode, RepActive)
SELECT checksum(newid()), 0
FROM Numbers
WHERE Number <= 10000
*/
select * into #worktable from @Worktable
Declare @StartTime DateTime
Set @StartTime = GetDate()
-- HERE is the statement we are testing.
Insert Into @RepInfoTable (PrimaryRepCode, AnonColumn1,
AnonColumn2, AnonColumn3, AnonColumn4)
Select PrimaryRepCode, E.AnonColumn1,
E.AnonColumn2,
E.AnonColumn3,
E.AnonColumn4
-- Inner Join and Inner Remote Join: see note below.
From @Worktable Inner Join REMOTESVR.tempdb.dbo.ExternalRepInfoTable E
On [@Worktable].RepNumber = E.PrimaryRepCode
And E.RepActive = 1
OPTION (RECOMPILE)
print 'Tvar took ' + ltrim(str(datediff(ms, @StartTime, getdate()))) + '
me.'
Set @StartTime = GetDate()
Insert Into @RepInfoTable (PrimaryRepCode, AnonColumn1,
AnonColumn2, AnonColumn3, AnonColumn4)
Select PrimaryRepCode, E.AnonColumn1,
E.AnonColumn2,
E.AnonColumn3,
E.AnonColumn4
-- Inner Join and Inner Remote Join: see note below.
From #worktable w Inner Join REMOTESVR.tempdb.dbo.ExternalRepInfoTable E
On w.RepNumber = E.PrimaryRepCode
And E.RepActive = 1
print 'Temp table took ' +
ltrim(str(datediff(ms, @StartTime, getdate()))) + ' me.'
go
drop table #worktable
DWalker07
up...
The collations are the same, we know that. I will try Option(Recompile)
and see what happens.
Why would the query plans be different between a table variable and a
temp table, IF neither the temp table nor the table variable has an
index, and you are running each version of code for the first time (which
means that a stored query plan is not a consideration)? Is that
expected?
My temp tables probably don't have statistics -- the temp tables are
always created, populated, used, and dropped within the same proc. (Does
SQL give them statistics in that time frame?)
These differences, on the order of 0.30 seconds versus 30 seconds, are
the norm for our environments. THAT's why I suspect that Microsoft's
original claim that table variables should perform as well as temp tables
was, maybe, flawed... or else there's a subtle bug somewhere. Of course,
it could be our environment, but I'm trying to see why.
Thanks.
David Walker
Erland Sommarskog <esq...@sommarskog.se> wrote in
news:Xns9CCACF78E...@127.0.0.1:
DWalker07
> And indeed, when I added OPTION (RECOMPILE) to the statement with the
> table variable, I now get somewhat better execution time with a table
> variable. (170 ms vs. 260 ms.) The reason the RECOMPILE hint helps is
> that while table variables do not have statistics, they do have
> cardinality information, and that is what SQL Server needs here to get
> it right.
>
> Interesting enough, the plans are still different. They are about the
> same, but the plan for the table variable has a spool operator. This
> difference may explain why the table variable is faster.
>
> Please try using OPTION (RECOMPILE) in your test case, and let me know
> how it works out. If you still get poor execution times, I think it's
> time to look at collations.
>
Reading about Option(Recompile) from Books On-Line led me to think that
for a query that is only executed once within a stored proc, the option
is useless:
"RECOMPILE
Instructs the SQL Server Database Engine to discard the plan generated
for the query after it executes, forcing the query optimizer to recompile
a query plan the next time the same query is executed."
So, if the same query is never executed "the next time", how can the
Option(Recompile) query hint have any effect? The queries involved here
are always being executed for the first time (that is, the first time
that the instance of the table variables or temp tables exist).
Or, are the query plans saved from one run of the proc to the next, even
when the queries involve table variables that can't persist, or temporary
tables that are created after the proc starts and deleted before the proc
ends?
David Walker
Erland Sommarskog
> Reading about Option(Recompile) from Books On-Line led me to think that
> for a query that is only executed once within a stored proc, the option
> is useless:
>
> "RECOMPILE
> Instructs the SQL Server Database Engine to discard the plan generated
> for the query after it executes, forcing the query optimizer to recompile
> a query plan the next time the same query is executed."
>
> So, if the same query is never executed "the next time", how can the
> Option(Recompile) query hint have any effect? The queries involved here
> are always being executed for the first time (that is, the first time
> that the instance of the table variables or temp tables exist).
>
> Or, are the query plans saved from one run of the proc to the next, even
> when the queries involve table variables that can't persist, or temporary
> tables that are created after the proc starts and deleted before the proc
> ends?
plan for the procedure. If there is not, the optimizer compiles the
procedure and builds plan for the entire procedure, although for
statements that refer to non-existing table, there is only a run-time
error at this point.
When the procedure is executed, invidual statements may be recompiled
for a number of reasons. Statistics changed, temp table created
etc. And OPTION (RECOMPILE). So when the procedure starts executing,
there is an original plan for the statement with OPTION (RECOMPILE) -
or maybe there is not; that does not matter. When the statement is
actually executued, the plan for that statement alone is created,
using the current values of the variables as parameters.
DWalker07
> DWalker07 (no...@none.com) writes:
>> Reading about Option(Recompile) from Books On-Line led me to think
>> that for a query that is only executed once within a stored proc, the
>> option is useless:
>>
>> "RECOMPILE
>> Instructs the SQL Server Database Engine to discard the plan
>> generated for the query after it executes, forcing the query
>> optimizer to recompile a query plan the next time the same query is
>> executed."
>>
>>
>> Or, are the query plans saved from one run of the proc to the next,
>> even when the queries involve table variables that can't persist, or
>> temporary tables that are created after the proc starts and deleted
>> before the proc ends?
>
> Yes. When a procedure is invoked, SQL Server check if there is any
> plan for the procedure. If there is not, the optimizer compiles the
> procedure and builds plan for the entire procedure, although for
> statements that refer to non-existing table, there is only a run-time
> error at this point.
>
> When the procedure is executed, invidual statements may be recompiled
> for a number of reasons. Statistics changed, temp table created
> etc. And OPTION (RECOMPILE). So when the procedure starts executing,
> there is an original plan for the statement with OPTION (RECOMPILE) -
> or maybe there is not; that does not matter. When the statement is
> actually executued, the plan for that statement alone is created,
> using the current values of the variables as parameters.
>
Very helpful, I *really* appreciate your input.
The doc says that Option(Recompile) makes SQL discard the plan *after*
the query executes. To me, clearly, this says that the recompile is not
applied to the query that the option appears IN. This sounds
suspiciously like wrong documentation to me. Can you please clarify
this? I'm sure that you know the truth. :-)
After reflecting on it further, I'm pretty sure that the option actually
makes SQL recompile a query plan for the *current* SQL statement that the
option appears in. Doing it the way that BOL describes it would be
strange -- having the option affect the next invocation of the same
query.
If I'm right, then the doc ought to say something like "causes SQL to
discard any previously compiled query plan for this statement, and create
a new one" rather than "causes SQL to discard the plan after the
statement executes".
This also matches your comment "When the statement is actually executed,
the plan for that statement alone is created, using the current values of
the variables as parameters." Do you agree that the doc is wrong (or
misleading)? If so, I'll submit a feedback.
Second, are you saying that a "temp table created" in a procedure
*always* causes a recompile, or just some of the time? In the typical
case where a proc or a function (this is a user-defined function)
declares a table variable or a temp table, puts some data into it, then
uses the table variable or temp table in a query, and then drops the temp
table (or exits the function which deletes the table variable)... is a
new query plan always, or just sometimes, created for the query that
contains the temp table or the table variable?
Thank you again for your time and expertise. It's invaluable.
David Walker
Erland Sommarskog
> The doc says that Option(Recompile) makes SQL discard the plan *after*
> the query executes. To me, clearly, this says that the recompile is not
> applied to the query that the option appears IN. This sounds
> suspiciously like wrong documentation to me. Can you please clarify
> this? I'm sure that you know the truth. :-)
The point is that once the plan has been executed it is thrown away.
Normally it would be put in cache, but since there is a request to
recompile it every time, it is useless to save it. Books Online is
correct, but maybe it focuses in the wrong thing.
> Second, are you saying that a "temp table created" in a procedure
> *always* causes a recompile, or just some of the time?
This is not entirely easy to explain, as there are several factors
involved. Say that you have:
CREATE PROCEDURE temptable_sp AS
CREATE TABLE #temp (a int NOT NULL)
INSERT #temp (a) SELECT object_id FROM sys.objects
SELECT MIN(a) FROM #temp
Then the procedure is invoked, an initial plan for the procedure is
created. This plan has three slots, of which the latter two are empty
slots, as #temp does not exist at this time.
When the INSERT and SELECT statements are reached, these statements are
recompiled and put into the cached plan. Next time the proedure is
executed, the INSERT statment is not recompiled, as now there is an
execution plan. SQL Server knows that this is the same temp as the last
time.
On the other hand, the SELECT statement is likely to be recompiled,
but for a different reason. Since more than six rows were inserted into
#temp this triggers autostats, and new statistics triggers recompilation.
However, when I tested, this did not happen, possible because the
statement is so trivial.
One way to see this is to run a Profiler trace, and include the
events SP:Recompile and SQL:StmtRecompile and also add the column
EventSubClass, which for these events gives the reason for recompilation.
> In the typical case where a proc or a function (this is a user-defined
> function) declares a table variable or a temp table,
Note that there is a big difference between temp tables and table variables
here. Since table variables are declared, SQL Server knows about them
already when it builds the initial plan, so the plan is complete from
the beginning. And since table variables does not have statistics, they
cannot cause recompilation of that reason either.
DWalker07
expertise.
David Walker
Erland Sommarskog <esq...@sommarskog.se> wrote in
news:Xns9CE6B03BE...@127.0.0.1:
DWalker07
Erland Sommarskog <esq...@sommarskog.se> wrote in
news:Xns9CE3F39D2...@127.0.0.1:
Can each statement in a stored proc have its own saved query plan, that
MIGHT be resued the next time the statement is encountered when the proc
is run? Even if the statement involves table variables, or temp tables
that are explicitly dropped when the proc ends?
The doc on compilation makes it sound like entire procs are compiled
rather than each statement within the proc compiled separately.
David Walker
Erland Sommarskog
> Can each statement in a stored proc have its own saved query plan, that
> MIGHT be resued the next time the statement is encountered when the proc
> is run? Even if the statement involves table variables, or temp tables
> that are explicitly dropped when the proc ends?
When a procedure is compiled the first time (or more exactly when there
is no plan for it in the cache), it is compiled in entirety. Recompilation
then happens on statement level, but the cache entry remains a single
one on procedure level-