We help IT Professionals succeed at work.

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

Medium Priority
436 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
Comment
Watch Question

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Totally agree with RiteshShah
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
>>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)
Bill RossProgrammer

Commented:
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.
CERTIFIED EXPERT
Top Expert 2012

Commented:
If you are using SQL Server 2008 you can pass a table parameter, otherwise you can pass an Xml document to a Stored Procedure.
awking00Information Technology Specialist
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Most Valuable Expert 2014

Commented:
>>  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?

Author

Commented:
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.
>>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?
NerdsOfTechTechnology Scientist
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you all - I am closing the question and sharing the points
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.