Solved

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

Posted on 2009-06-29
13
403 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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
 
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

828 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