Re: Filtering DB Call based on local Data
- From: "LVP" <lvp_agentman@xxxxxxxxxxx>
- Date: Wed, 19 Dec 2007 12:00:27 -0800
I agree with you, hence MVP vs. LVP
"Peter Bromberg [C# MVP]" <pbromberg@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:C51039C8-2346-4884-869A-420345039FA2@xxxxxxxxxxxxxxxx
I'd have to agree with Nick; using a TVF like "fn_Split" that accepts a
single delimited string of items and returns you a table that you can do a
join on is fast and easy. Moreover, with a delimited string you have a lot
less glop going over the wire.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
MetaFinder: http://www.blogmetafinder.com
"LVP" wrote:
depending on the speed you are looking for,
Send the Ids as XML, parse it in a SP into a temp table then Join and
return
your result set.
or depending on the number of Ids you can choose one method or another
method
How long does it take to put 10K into a temp table via many means? then
Join
LVP
"Nicholas Paldino [.NET/C# MVP]" <mvp@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote
in
message news:e5bosClQIHA.4684@xxxxxxxxxxxxxxxxxxxxxxx
Bill,
Not really. You should create some sort of dynamic query, using an
IN
statement, appending a new parameter for each of the items you want to
filter on. SQL Server will take care of the rest.
I think that SQL Server has a limitation on the number of parameters
that you can use on dynamic queries (around 2048 I think) so you might
exceed that limitation.
If you are using SQL Server 2008, you can use table-valued
parameters,
but I doubt you are doing that.
If you are using SQL Server 2005, you can create a table-valued
function in SQL Server which will take a comma-delimited string and
return
a table of ids, on which you can do a join (if the list of ids is
unique)
or perform an IN operation on. The one I wrote is a CLR function and
is
quite fast, even parsing 10,000 items (it beats out any other method
that
you can code with T-SQL).
Then, all you have to do is take your delimited string, pass it as a
single parameter to your query (it can be a stored procedure or dynamic
query at this point) and you should be done.
--
- Nicholas Paldino [.NET/C# MVP]
- mvp@xxxxxxxxxxxxxxxxxxxxxxxxxxx
"Bill" <wbpelen@xxxxxxxxx> wrote in message
news:03dfb4ec-cbbe-49b0-8a6b-49b45ecbed0f@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This may be a simple problem ( let's hope ). I am trying to extract
a
filtered set of records from a Sql Server Database. The set of rows,
however, is to be filtered based on data external to the database.
Specifically, this is an Excel Add-in.
The user selects a range which contains order numbers ( single
column ). Loading that data into a DataTable or Array is easy
enough.
Using those ( say 30 ) order_id values, I then want to get data
specific to the order from the order table on a Sql Server Database
( assume 1:1
relationship ).
I am new to ADO, I do not know how best to represent this data
( which
objects ) within the code.
If I use a DataSet where the short list of order ids is in one
DataTable with another table connected to the Sql Server order table,
will I end up loading all 300K of the orders before I get a chance to
filter the set.
Is there an easier way to select the data from the database using
different design options?
Ultimately it breaks down to a query like this:
Can I somehow relate data located only in memory/.Net Objects to a
table within the database?
The query could be written as below but the possibility is that the
list of orders could be as much as 10K.
SELECT a, b, c FROM orders WHERE order_id IN ( 100, 200, 300, ...,
999 )
Is there a way to do this without building that query?
.
- References:
- Filtering DB Call based on local Data
- From: Bill
- Re: Filtering DB Call based on local Data
- From: Nicholas Paldino [.NET/C# MVP]
- Re: Filtering DB Call based on local Data
- From: LVP
- Re: Filtering DB Call based on local Data
- From: Peter Bromberg [C# MVP]
- Filtering DB Call based on local Data
- Prev by Date: Re: Filtering DB Call based on local Data
- Next by Date: Re: Using COM EXE in C#
- Previous by thread: Re: Filtering DB Call based on local Data
- Next by thread: Re: Filtering DB Call based on local Data
- Index(es):
Relevant Pages
|
