1

Possible Duplicate:
SQL JOIN vs IN performance?

We're backed by SQL Server 2008. We have an application where a user can choose a list of items to search on, and they get all items based on those items. The two techniques we've used are:

SELECT * FROM mytable WHERE myval IN ('A', 'B', 'C')

and

INSERT INTO tempTable (tempvalue) ('A'), ('B'), ('C')
SELECT * FROM mytable m INNER JOIN tempTable t WHERE m.myval = t.tempvalue

The first option is easier, for sure, but performance is horrible once the user has chosen over 50 values. It's to the point where we're talking about scrapping the IN because performance degrades rapidly.

So, why does IN perform that badly? Is this typical or is it something specific with our setup?

Community
  • 1
  • 1
bryanjonker
  • 3,386
  • 3
  • 24
  • 37

1 Answers1

0

These are just ideas to improve SQL Queries in general.

  • I personally prefer the first method over saving the selected fields into a table, which might have more overheads than the first method.

  • Make sure you have created the relevant Indexes for your Main table. It is a good practice to create indexes based on your most frequent search fields, for example, if you search a employees tables for employee_name, you can create a index for that field and it will improve the query execution time. You can try this with the SQL Profiler.

Manoj De Mel
  • 927
  • 9
  • 16