March 25, 2011 at 7:25 am
In my database I have a UDF which takes in four parameters as input does a union between two tables and returns a table.
Example:
Function Definition is like:
Create function fnMyfunction (@a char (10), @b-2 varchar(32),@C int)
Returns table
As return
(select distinct ET.COLUMN1,ET.COLUMN2,ET.COLUMN3
From ExampleTable ET leftouter join (Select Distinct COLUMN1,COLUMN2,COLUMN3
From table1 where Column4=@a and (@b=2 and Column5=@b) or (@c=5 and Column5=@c))
UNION
(Select Distinct COLUMN1,COLUMN2,COLUMN3
From table1 where Column4=@a and (@b=2 and Column5=@b) or (@c=5 and Column5=@c))) JT ON ET.COLUMN1 =JT.COLUMN1
Where ET.COLUMN=@a
Groupby et.column1
Now My Problem is that.
When I Run a select statement in three different forms the Execution place show index seek and two other forms it shows index scan.
Examples
If I do a direct select
Select Column1,Column2,column3 from fnMyfunction(‘somevalue’,’SomeValue2’,’Somevalue3’) Where Column=’Somevalue4’
This query is doing an index scan on the two tables which participating in the UNION.
Now If I run the query Like this
Declare @a char (10), @b-2 varchar(32),@C int
Set @a =Somevalue1
Set @b-2=Somevalue2
Set @C=Somevalue3
Select Column1, Column2,column3 from fnMyfunction@a,@b,@c) Where Column=’Somevalue4’
The above query is faster and is doing a Index seek on the two tables in the UNION.
The third scenario is :
exec sp_executesql (Select Column1, Column2,column3 from fnMyfunction@a,@b,@c) Where Column=’Somevalue4’,N'@a char (10), @b-2 varchar(32),@C int',’ @a =’Somevalue1’,@b=’Somevalue2’, @C=’Somevalue3’)
This Dynamic query is Again doing a INDEX SCAN on the tables.
Just curious that what might be causing this Since all the three queries give out the same result but its on that one is doing a Index seek and the other two are doing Index Scans
I updated all the statistics indexes rebuilt .
March 25, 2011 at 7:35 am
Can you post table definitions, index definitions and execution plans? It's hard to picture what's going on from your description.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply