Solved

SQL query - a solution to avoid IN (...) statement

Posted on 2009-06-29
13
385 Views
Last Modified: 2012-05-07
Hi X-perts,

I have a list of items in the ListBox. Next, i have to select records from a table, where one of the fields corresponds to the names in the ListBox.

1) I build a string like (name1, name2, name3,... nameN)

2) SQL = "SELECT * from myTable WHERE myTable.[myField IN " & mystring

it works fine, but I am not sure if it is a good solution for large lists, i.e. 2,000 items. I am getting a huge string IN

are there any other ways how to structure this?

should I be afraid of too long IN statement?

Thanks
0
Comment
Question by:andy7789
13 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 100 total points
ID: 24742495
This is one of the way
If you want another way
you can add all this values in Temperory table
and table the join of that table in query
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 100 total points
ID: 24742590
well, I guess you should stick with IN. there is another way of doing so with Temp Table (Pratima already said about this above) but it may create more overhead on server as you are going to create temp table, insert those records (may be 2000+) in that temp table, join temp table to original table and after using it, drop temp table. if your application is being used by so many people at a time, your tempDB may running out of space if you don't have sufficient disk space.

in short, all depends on the situation but I would stick to IN statement.
0
 
LVL 5

Expert Comment

by:rizwanidrees
ID: 24742608
Totally agree with RiteshShah
0
 
LVL 2

Assisted Solution

by:kbac
kbac earned 100 total points
ID: 24743289
"In" seems to be the best option.

If you use temp table, you'll have an execution cost as everyone told.

Another option is to use a table (not temp) for this issue, then you'll need to use user based, time stamped identification fields for letting more than one user work on the same table and removing records after you're done, thus you'll have to fix the locking parameters of that table. It will be more complicated with higher cost. Unless you're going to do updates and calculations on these records, no need to do that.

Either way, you'll have to pass the string or values to the database, so no cost benefit on that part as well.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24743295
>>Another option is to use a table (not temp) for this issue,<<

it will again create write overhead on SQL Server, I have to stick to my statements in my above post that IN would be good in this situation (though kbac has also said the same)
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 24743900
What is your front end?  If it's msaccess then a local temp table would do the trick.  Creating SQL statements from data is risky since the SQL statement will fail if the data is not screened for ' or % etc.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24744563
If you are using SQL Server 2008 you can pass a table parameter, otherwise you can pass an Xml document to a Stored Procedure.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 100 total points
ID: 24744972
See attached.
comments.txt
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24745129
>>  a good solution for large lists, i.e. 2,000 items.

You have individuals going through and picking 2K items from a list box?

What if you super-grouped the lists. By department -- item types, something like that?

0
 

Author Comment

by:andy7789
ID: 24751795
Thank you guys! I have been testing a few different options and the IN option seems to be the worst.

If an average search of 2000 items takes about 3-4 sec, the same with 2000 IN items is over 2 minutes.
It works fine only for relatively short lists 20-50-100

It looks as the best option for me is to save the "WHERE...." filter string that was used for generating lists before and add it to another search.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24751807
>>It looks as the best option for me is to save the "WHERE...." filter string that was used for generating lists before and add it to another search.<<

can you please explain more?
0
 
LVL 19

Assisted Solution

by:NerdsOfTech
NerdsOfTech earned 100 total points
ID: 24752760
If you build a TEMP TABLE of ids -- you could LEFT JOIN it against the PRIMARY TABLE

SELECT b.* FROM temp a
LEFT JOIN
primary b
ON a.id = b.id

Simple yet effective.

=NerdsOfTech
0
 

Author Comment

by:andy7789
ID: 24752856
Thank you all - I am closing the question and sharing the points
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now