Solved

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

Posted on 2009-06-29
13
418 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 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

632 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