Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-06-29
13
Medium Priority
?
423 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 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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 20

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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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.

782 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