July 14, 2009 at 5:44 am
Hi
I have a table consisting of millions of records.
and I am looking for only few records like:
select * from table1 where column1 in('AAA','BBB','CCC','DDD')
What is the best way to retrive few records from millions?
Is there any alternative to IN?
What query should be for retriving minimum records and maximum records?
July 14, 2009 at 6:42 am
It really depends on the configuration of your tables & indexes, but if the IN clause is causing poor performance, you could try creating a temp table, load the variables into there, and then do an INNER JOIN to retrieve the data. Something like:
CREATE TABLE #MyVals
(SomeValue nvarchar(50));
INSERT INTO #MyVals
SELECT 'A'
UNION
SELECT 'B'
UNION
SELECT 'C'
SELECT *
FROM BigTable b
JOIN #MyVals m
ON b.SomeValue = m.SomeValue
For very small sets of data, it varies, but start with fewer than 50 rows, a table variable could work better than a temp table. But it really depends. You'll need to experiment.
Another option is to use the BETWEEN operator instead of the IN clause
SELECT *
FROM BigTable b
WHERE b.SomeValue BETWEEN 'A' and 'C'
BETWEEN usually resolves to >= and <=, so you could use those as well.
You'll need to test all these options within your system to determine what will work best.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply