help optimizing a query

[tirf]

I have written this query wich is taking lots of time to run
I have a table valued function which returns a list of id's
I want to get recordset from table1
if @val_value is null then all records from table1 will be returned otherwise, only for those id's from the table function

What will be the most optimized way to do it

declare @val_value varchar(100)

select a.*
from tbl1 tbl
where
(@val_value is null OR
tbl.id in (select id from fn_tableFun(@val_value)
)

Thanks

 

[markros]

Try

IF @Val_Value is NULL
   select a.* from tbl1 a
ELSE
   select a.* from tbl1 a
   where exists (select 1 from dbo.fn_tableFun(@val_Value) F
where F.ID = a.ID)

PluralSight Learning Library

 

[tirf]

Mark, I appreciate your responce

can you do it in one statement,

I tryed your approach, I don't see no difference in performace wise

Thanks

 

[gmmastros]

Can you show the code for the table valued function?

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

 

[tirf]

it would be very difficult for me to show the code for company reason

in general the what a function does it, based on the given input, it will return the id's

sorry for the vague explanation

Thanks

 

[gmmastros]

I understand very well the importance of protecting company proprietary information.

Let me ask you this...

You say this is a table valued function. Does it return a single row with multiple columns, a single column with multiple rows, or multiple columns and rows?

Does this function return a list of ID's that correspond to the primary key id in the table?

Can there be duplicates returned by the function?

I ask all this because there is a little known fact about table variables. When you have real tables that you are joining, having an index on the join column will significantly improve performance. But... you cannot put an index on a table variable (at least not in the classic sense). What you can do is create a primary key for the table variable. Primary keys are implemented through clustered unique indexes.

Since a table valued function basically returns a table variable, it's possible to declare a primary key for it which MIGHT improve your performance.

Ex:

Create Function dbo.EvenNumbers()
Returns @Blah Table(Id Int [!]Primary Key[/!])
AS
Begin
  Insert Into @Blah(Id)
  Select YourTable.id 
  From   YourTableWhere 
  Where  YourTable.Id % 2 = 0

  Return
End

Now, I'm not saying this will necessarily improve your performance, but it's easy to implement and test.

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

 

[markros]

In your table function is not an inline table function, then the performance may not be good.

You can read discussion and check links about functions performance here
Adam Haines SQL Server Quiz question

If the function is used to split comma-separated list of IDs, then check links referenced in the beginning of this blog post
Passing multiple ranges to stored procedure

PluralSight Learning Library

 

[tirf]

Does it return a single row with multiple columns, a single column with multiple rows, or multiple columns and rows?

- it returns single column with multiple rowa

Does this function return a list of ID's that correspond to the primary key id in the table?

yes, some id's may correspond, some may not

Can there be duplicates returned by the function?

no, the id's are unique


Thanks