Re: T-SQL is not quite there yet
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Thu, 19 Nov 2009 23:05:33 +0000 (UTC)
DWalker07 (none@xxxxxxxx) writes:
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, SQL Server MVP, esquel@xxxxxxxxxxxxx
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
.
- References:
- T-SQL is not quite there yet
- From: DWalker07
- Re: T-SQL is not quite there yet
- From: Erland Sommarskog
- Re: T-SQL is not quite there yet
- From: DWalker07
- T-SQL is not quite there yet
- Prev by Date: Re: ISO SQL Date Format / ISO Date Formats - question to --CELKO-- on your continued misuse of the inconsistent date formats in your advice
- Next by Date: Re: select into question
- Previous by thread: Re: T-SQL is not quite there yet
- Next by thread: RE: T-SQL is not quite there yet
- Index(es):
Relevant Pages
|
