Variable WHERE part of SELECT command

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I need to get data from a table from a database. The data will be queried
according ID column.

In a fixed time interval my application will receive the list of IDs. Than
all records with this IDs should be retrieved from database.

Tehre is not any select SQL command which would allow to put in query a list
of parameters, like SELECT ALL FROM XX WHERE ID is from MYList

What would be the most efficient way to get those required data from a db?

Thanks,

Lubomir
 
In a fixed time interval my application will receive the list of IDs. Than
all records with this IDs should be retrieved from database.
Click to expand...

First thing, you'll have to make this task in a thread because you
don't know if the task will be finished when the next group of id's
will come.
If you don't want to lose data that's the best way.
(of course, "fixed time" doesn't explicitly mean "short time", if your
fixed time is once every week, forget this idea :-) ).
Tehre is not any select SQL command which would allow to put in query a list
of parameters, like SELECT ALL FROM XX WHERE ID is from MYList
What would be the most efficient way to get those required data from a db?
Click to expand...

certainly a few ways. One I can tell cause I used it a few times, is
very simple, thread safe and easy to code : just use the database
possibilities...
I explain : in your database you create two tables : "IDGROUPS", with
only one column made of a server autoinc field, and "IDS" a table made
of two fields: IDGROUPS key, and ID.
tasks :
1) when you receive a group of ID you create an entry in IDGROUPS and
get the autoinc that is generated by the database.
2) you insert ID's in "IDS" table using preceding key and each ID you
received.
3) You create a thread with the "IDGROUPS" key as a parameter.
4) That's all for here.

In each thread :
1) you start with the key passed in parameter
2) You can easily use a SELECT, cause the WHERE is "WHERE xx.ID IN
(SELECT ID FROM "IDS" WHERE "GROUPKEY" = TheKeyInParameter)..."
3) you make the job.
4) You send a DELETE to "IDS" table with the key, then you DELETE also
the key in "IDGROUPS".
5) The thread is finished.


It is simple and safe. Of course there are certainly other ways. That's
one.
 
I prefer using XML personally, as it can be queried without altering the
input or placing anything in a temp table. It is a bit less efficient than a
standard query, but your other options are:

1. Populate a temp table
2. Use dynamic SQL

Both of these take a much larger perf hit, esp. on larger lists, than XML.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*********************************************
Think outside the box!
*********************************************
 
What is the benefit of using 2 tables IDGROUPS and IDS? Why not use IDS only
with autoinc column for a key?

Thanks,

Lubomir
 
What is the benefit of using 2 tables IDGROUPS and IDS? Why not use IDS only
with autoinc column for a key?
Click to expand...

because I used to be respectful with the relational nature of RDBMS.
One table is the "group of IDs", the master, the other one is the "list
of IDs for a group of IDs", the detail.
This is also allowing for a simple way to generate a "group of IDs"
key.
Of course, you can suppress "IDGROUPS" table and create a store proc
returning an autoinc value that you will use in the ID list table. But
this is more complex for the same result and less respectful with the
relational nature of the DB. Also semantics is preserved with 2 tables
(because you have 2 entities: groups and list of ids) so it is "better"
from this point of view.

Of course, you can adapt the solution to fit your needs. For myself
I'll use two tables, DB licences are generally not based upon table
number :-)
 
Sure there is. Use the IN expression. For example,
SELECT Col, Col1 FROM table
WHERE ID IN "15, 20, 35"

The problem is that the IN expression cannot take a parameter. However,
there are several techniques you can use to get around this limitation. For
example, you can create a Table-returning Function that converts a delimited
string to a table. Then you could code the IN expression to search for the
values in the table.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
 
O, I see.

Thanks

Lubomir



OD said:
because I used to be respectful with the relational nature of RDBMS.
One table is the "group of IDs", the master, the other one is the "list
of IDs for a group of IDs", the detail.
This is also allowing for a simple way to generate a "group of IDs"
key.
Of course, you can suppress "IDGROUPS" table and create a store proc
returning an autoinc value that you will use in the ID list table. But
this is more complex for the same result and less respectful with the
relational nature of the DB. Also semantics is preserved with 2 tables
(because you have 2 entities: groups and list of ids) so it is "better"
from this point of view.

Of course, you can adapt the solution to fit your needs. For myself
I'll use two tables, DB licences are generally not based upon table
number :-)

--


OD___
www.e-naxos.com
Click to expand...
 
O, I see.
Thanks
Click to expand...

I will add this comment : you need a Group ID (so 2 tables) because in
the solution I explained, you need to work within threads. So two
threads (or more) can be up at the same time. Thus, each thread must to
know on which "group of IDs" it must work on.
If you do not implement the thread part of the solution I presented,
you just need one table storing the list of IDS. There's then no
"group" entity since a "group" is processed by the main thread and
can't be mixed with another one.
 
What about just concatenating a string and using ExecuteSQL() *Greg ducks*

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*********************************************
Think outside the box!
*********************************************
 
That's ok, but can lead to SQL injection attacks.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
William (Bill) Vaughn said:
That's ok, but can lead to SQL injection attacks.
Click to expand...

Not if you are dealing with integers (most often scenario) and you are
careful. But yes, you are certainly correct and MS should really address
this - I mean adding support for arrays in parameters.
 
Thanks fro all answers. I implemented another table with "my list of IDs, and
with timestamp columns and used teh SQL command with "select, in, order by
and group" by parts and it works so far.

Yes, support for list off paramaters in SQL would be very convenient :-)

Regards,
Lubomir
 
Not if you are dealing with integers (most often scenario) and you are
careful. But yes, you are certainly correct and MS should really address
this - I mean adding support for arrays in parameters.
Click to expand...
That's ok, but can lead to SQL injection attacks.
Click to expand...

How to prevent SQl injection attacks at all? Some times we are forced
to use a string from our application. What then? Is there a simple way
to avoid the injections in .adonet or we should search for alternative
in SQL syntax?
 
Back
Top