Re: Single SELECT vs. multiple SELECTs on UNrelated tables ??
From: Les Gainous <lesgainous_at_earthlink.net>
Date: 1996/10/04
Message-ID: <325563CE.CFF_at_earthlink.net>#1/1
Date: 1996/10/04
Message-ID: <325563CE.CFF_at_earthlink.net>#1/1
Chris,
Generally the overhead is less when using SPs than with a direct statement. The direct statement has to be syntax checked, parsed, optimized against the table(s), etc. before execution.
An important point is that in a regular application where many folks are accessing the tables, submitting "in-line", direct SQL statements from the client could cause a great deal of blocking and "deadlock" situations. According to the documentation is SQL Server sees a deadlock situation, it'll kill the "youngest" connection (with a trappable error) in order to avoid deadlock. I don't think this will happen on multiple processes using direct SQL--it will just appear hung.
good.luck = true
--
Les Gainous, lesgainous_at_earthlink.net
Visit my web page at
http://home.earthlink.net/~lesgainous
Looking for a Client-Server job in California?
http://home.earthlink.net/~lesgainous/jobs.html
Chris Little wrote:
>
> Thanks for your response, Les. I expect the "combined" SELECT I provided
> initially is executed like a UNION.
>
> There are many advantages to procedures -- pre-parsing, etc. What is the
> overhead associated with them? i.e. is there a situation where one might
> want to replace a single SELECT * from TABLE_A with a procedure does same?
> I'm new to SQL Server, so am just curious.
>
> BTW, my name is Chris; Craig and I share USENet access, so our News profile
> can be misleading.
>
> Thanks.
>
> Chris
>
> ----------
> : From: Les Gainous <lesgainous_at_earthlink.net>
> : To: Craig <clittle_at_valueamerica.com>
> : Subject: Re: Single SELECT vs. multiple SELECTs on UNrelated tables ??
> : Date: Friday, October 04, 1996 1:53 AM
> :
> : Craig,
> :
> : Since you are concerned about "across-the-network calls" (we all are),
> : you could use two differenct methods:
> :
> : 1) UNION:
> :
> : SELECT *
> : FROM TABLE_A a
> : where a.col1=1
> : UNION SELECT *
> : FROM TABLEB b
> : where b.col1=99
> :
> : or,
> :
> : 2) Stored Procedure:
> :
> : create proc pMyProc as
> : SELECT *
> : FROM TABLE_A a
> : where a.col1=1
> :
> : SELECT *
> : FROM TABLEB b
> : where b.col1=99
> : go
> :
> : Using the stored procedure depicted in #2, you will get two result sets.
> : Some front-ends can't easily handle multiple result sets. If that's the
> : case then use a hybrid put the logic in #1 (union) in a stored procedure
> : (as in #2).
> :
> : Either way, you're only going "down the wire" only one round trip (one
> : way submits the command, the return leg gives you the results).
> :
> : good.luck = true
> :
> : --
> : Les Gainous, lesgainous_at_earthlink.net
> :
> : Visit my web page at
> : http://home.earthlink.net/~lesgainous
> :
> : Looking for a Client-Server job in California?
> : http://home.earthlink.net/~lesgainous/jobs.html
> :
> :
> : Craig wrote:
> : >
> : > SELECT * FROM TABLE_A a, TABLE_B b where a.col1=1 and b.col1=99
[this
> : > returns 1,2,3,98,99]
> : >
> : > versus
> : >
> : > SELECT * FROM TABLE_A a where a.col1=1 [this returns 1,2,3]
> : >
> : > SELECT * FROM TABLEB b where b.col1=99 [this returns 98,99]
> : >
> : > These are two absolutely unrelated tables and each return only one row
for
> : > the above specified conditions. So my question is:
> : >
> : > which is more efficient: the one select or two selects?
> : >
> : > To add more complexity, if this is a client/server application and you
want
> : > as few "across-the-network" calls to the database as possible, would
the
> : > single select have even greater merit. I would think so, but am
interested
> : > in input from the SQL world.
> : >
> : > Thanks.
> : >
> : > Chris
Received on Fri Oct 04 1996 - 00:00:00 CEST