Re: Parameterized Query
- From: "Cowboy \(Gregory A. Beamer\)" <NoSpamMgbworld@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 28 Jun 2006 07:21:14 -0500
The query Nate is asking for is something like this:
SELECT * FROM Table1
WHERE Col1 IN (1,3,4,5)
The only point for a parameter, client side, is the where clause and you
cannot parameterize multiple values. You can certainly do something that
pounds the items together and sends a single string, but you will not
benefit from execution cache plans on a server like SQL Server. Of course,
if this is Access, all bets are off. :-)
XML is a great option, if the sql in the sproc is fully qualified. It would
still be wise to profile and ensure you are hitting a cached execution plan
as there are many things in SQL Server sprocs that can invalidate the cache
option.
Yes, you could move to the client, but you still lose the benefit on the
server side, which is going to be the bottleneck in this type of situation.
Assembling the query, client or server side, will take very few cycles. It
is actually pulling the data that will cost the most.
Unfortunately, there are no real easy answers to a IN or NOT IN type of
query.
--
Gregory A. Beamer
*************************************************
Think Outside the Box!
*************************************************
"Chris Chilvers" <keeper@xxxxxxxxxxx> wrote in message
news:94f3a2l5dehmpcghgbl3pko0j9al0p3d6j@xxxxxxxxxx
On Tue, 27 Jun 2006 17:22:16 -0500, "Cowboy \(Gregory A. Beamer\)"
<NoSpamMgbworld@xxxxxxxxxxxxxxxxxx> wrote:
That cannot be done. You can put together a list as XML and run an OPEN
XML
(better in a sproc, as it is hard to debug dynamically. In a sproc, you
can
also pull apart a separated list. But, you cannot have an in. You will
have
to build the entire string yourself prior to sending it. And, yes, there
is
a perf penalty, as you will have to run the execution plan every time.
As opposed to this you could build the sql query on the client machine.
As you are working with numbers, as long as your code has ensured they
are numbers (i.e. if they were strings run something like Integer.parse
on them) you could just append them to the query.
Otherwise, if you were using strings you could look at using dynamic sql
with parameters (instead of directly in-lining the strings). This is all
assuming that your client machine was the one executing that sql
statement on the server, as opposed to that statement being part of a
stored procedure.
.
- References:
- Re: Parameterized Query
- From: Cowboy \(Gregory A. Beamer\)
- Re: Parameterized Query
- From: Chris Chilvers
- Re: Parameterized Query
- Prev by Date: Re: Parameterized Query
- Next by Date: Re: unique Key constraint
- Previous by thread: Re: Parameterized Query
- Next by thread: Re: Move Column in Datatable?
- Index(es):
Relevant Pages
|
