List of parameters to look into a int column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

List of parameters to look into a int column

Author

Topic

1fred
Posting Yak Master

158 Posts

Posted - 2010-03-26 : 12:59:40

A function is passing as a parameter a list of items to look for. The items have the int data type. Something like this :

create table #table1 (
item int
)
insert into #table1
select 1
union select 2
union select 3
union select 4

declare @items varchar(30)
set @items = '(1,3,4)'

select * from #table1 where item in (1,3,4)
--I'd like something like this is it possible?
--select * from #table1 where item in (@items)


drop table #table1

the main table has around 100k lines so I'd like the solution to use the index. Item field is an integer with auto increment. It has a cluster index on it. But if I cast the type to varchar I lose the index performance. There are many parameters and based on the parameters, a dynamic SQL query is built.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 13:59:50

Does @items have to be a varchar(30), or could it be a table variable, with 1 column, and 1 row per value. You could then do an inner join onto the table variable, and the join would provide the filter you require.

You could write a script that creates a table variable, which is populated from your comma separated list (The table would have a single int column), and join that table variable onto your query.

OR, you could use dynamic SQL. Like this:


DECLARE @SQL VARCHAR(8000)
SET @SQL = 'select * from #table1 where item in (' + @items + ')'
EXEC (@SQL)

There are 10 types of people in the world, those that understand binary, and those that don't.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-03-26 : 14:16:35

Another solution is converting the list into temp table, if you don't want to use EXEC.

declare @table1 table (item int, test varchar(50))
insert into @table1 values(1, 'test1')
insert into @table1 values(2, 'test2')
insert into @table1 values(3, 'test3')
insert into @table1 values(4, 'test4')
insert into @table1 values(5, 'test5')

declare @items varchar(30)
set @items = '1,3,4'

declare @tempTable table(item int)
declare @temp varchar(10)
while len(@items) > 0
begin
set @temp = substring(@items, 0, charindex(',', @items))
set @items = substring(@items, charindex(',', @items) + len(','), len(@items))
if len(@temp) > 0
insert into @tempTable values(@temp)
if charindex(',', @items) = 0 and len(@items) > 0
begin
insert into @tempTable values(@items)
set @items = ''
end
end

select * from @table1 where item in (select item from @tempTable)

RESULT
item test
--- --------------------------------------------------
1 test1
3 test3
4 test4

For general, you should use split function written by someone in this forum to convert the list into temp table.

1fred
Posting Yak Master

158 Posts

Posted - 2010-03-26 : 14:43:57

Thanks, I'll try a few of these solutions. I really have performance issue with a query I did not write. It is using Dynamic sql after going through a list of different business rules to execute the final query.

what I find interesting is the difference, probably because of the execution plan issue with dynamic SQL.

When I run select * from table1 where item in @items
vs
select * from table1 where item in (1,2,3) explicitely
The dynamic sql is 4 times slower.... 58 secs VS 14 secs.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-27 : 02:00:43

wats about

select * from table1 where ','+ @items + ',' like '%,' + cast(item as varchar(10)) + ',%'

and pass @item as '1,2,3,4'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-27 : 08:01:17

quote:
Originally posted by visakh16

wats about

select * from table1 where ','+ @items + ',' like '%,' + cast(item as varchar(10)) + ',%'

and pass @item as '1,2,3,4'

The OP didn't want to use such a cast, as it would negate his index.

IME, SQL server is designed to work with matrices of data, so when you're feeding it several data elements which have been combined into a single element, such as this, then it's time to re-think your approach.

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-27 : 09:07:03

quote:
Originally posted by DBA in the making

quote:
Originally posted by visakh16

wats about

select * from table1 where ','+ @items + ',' like '%,' + cast(item as varchar(10)) + ',%'

and pass @item as '1,2,3,4'

The OP didn't want to use such a cast, as it would negate his index.

IME, SQL server is designed to work with matrices of data, so when you're feeding it several data elements which have been combined into a single element, such as this, then it's time to re-think your approach.

There are 10 types of people in the world, those that understand binary, and those that don't.

Thats fine as long as you're doing design. But there're lots of cases where you inherit an existing design and have to live with scenarios like this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-27 : 09:27:19

quote:
Originally posted by visakh16
Thats fine as long as you're doing design. But there're lots of cases where you inherit an existing design and have to live with scenarios like this

But there is something you can do about it. eg, in this case, convert the string to a temp table, and use that to filter your records. This will usually give better performance.

There are 10 types of people in the world, those that understand binary, and those that don't.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-29 : 02:42:55

Make sure to read this
http://www.sommarskog.se/arrays-in-sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources