performance problem 1

[tirf]

I have this query and taking long time to execute

declare @id varchar(max)

set @id = '1, 2, 3, 4, 5'

select *
from table1
where @id is null or
exists (select 1 from split_fun(@id) where id = table1.id)

could you suggest better way to write it

 

[gmmastros]

Try this. If this executes faster and you would like me to explain, let me know.

declare @id varchar(max)

set @id = '1, 2, 3, 4, 5'

select *
from table1
where @id is null 

Union All 

select *
from   table1
       Inner Join split_fun(@id) As SplitValue
         On SplitValue.Id = table1.id

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tirf]

Thanks, George it indeed reduced the time.

I face a tricky situation, what if the variable is 2 or more?

declare @id varchar(max), @name varchar(max)

set @id = '1, 2, 3, 4, 5'
set @name = 'Washington, Jefferson, Lincoln'

select *
from table1
where (@id is null or
exists (select 1 from split_fun(@id) where id = table1.id)
)
AND
(
@name is null OR exists (select 1 from split_fun(@name) where name = table1.name
)

Thanks

 

[gmmastros]

You could try a union query that only returns the ID, and then join back to the main table to get the rest of the data.

Ex:

declare @id varchar(max), @name varchar(max)

set @id = '1, 2, 3, 4, 5'
set @name = 'Washington, Jefferson, Lincoln'

; With SelectedIds As
(
select PrimaryKeyColumn
from table1
where @id is null 

Union

select PrimaryKeyColumn
from   table1
       Inner Join split_fun(@id) As SplitValue
         On SplitValue.Id = table1.id 

Union

Select PrimaryKeyColumn
From   table1
where  @name is null

Union

Select PrimaryKeyColumn
From   table1
       Inner Join split_fun(@Name) As Names
         On Names.Name = table1.Name 
)
Select Table1.*
From   Table1
       Inner Join SelectedIds
         On Table1.Id = SelectedIds.Id

Using UNION instead of UNION ALL will only return a distinct list of ids. So, you basically get a distinct list of id's that you want to return and then join back to the original table to get the rest of the data.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Can you show us the code for split_fun? I suspect there may be things you can do within the function to also improve the performance.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom