?
Solved

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

Posted on 2009-06-29
13
Medium Priority
?
420 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 400 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 400 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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 2

Assisted Solution

by:kbac
kbac earned 400 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
 
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 400 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 400 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

719 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