August 7, 2009 at 8:14 am
How can we make index on UDF/ Is there is away to replace table scan on UDF's
As per the article ,
UDF as Computed Column
By Dinesh Priyankara, 2003/06/18 - he mentioned like ..
But remember, you can make an index on UDF, if it is deterministic.
How can we index the UDF..
I am using 3 UDF in one store procedure , which trows table Scan and which is very expensive in terms of execution time ..
Is any way to aviod /replace Table scan with Seek .. so that the performance of the store procedures increases
Thanks a lot
August 7, 2009 at 8:27 am
It is possible to create a UDF with a unique index or primary key constraint, but it won't generate statistics and therefor won't be useful to you for querying. You'll end up with scans.
"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
August 7, 2009 at 9:46 am
CREATE FUNCTION dbo.F (@Input INTEGER)
RETURNS BIGINT
WITH SCHEMABINDING
AS
BEGIN
RETURN CONVERT(BIGINT, @Input) * CONVERT(BIGINT, 2);
END
GO
CREATE TABLE dbo.Test
(
Number INTEGER NOT NULL PRIMARY KEY,
Doubled AS dbo.F(Number)
)
GO
CREATE UNIQUE NONCLUSTERED INDEX nc1 ON dbo.Test (Doubled) INCLUDE (Number);
GO
INSERT dbo.Test (Number) VALUES (1);
INSERT dbo.Test (Number) VALUES (2);
INSERT dbo.Test (Number) VALUES (3);
INSERT dbo.Test (Number) VALUES (4);
INSERT dbo.Test (Number) VALUES (5);
SELECT Number FROM dbo.Test WHERE Doubled = 10;
GO
DROP TABLE dbo.Test;
DROP FUNCTION dbo.F;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 7, 2009 at 5:53 pm
Could we see the three UDF's and the query that uses them? Is the table scan occurring on a table which is being queried inside the UDF, or on a table being queried using multiple UDFs?
If you are using UDFs to determine the status of various things for filtering in your WHERE clause, then table scans are quite likely. The approach below is going to produce a table scan:
select cola,colb,colc
from dbo.ClientList
where dbo.ufActiveClient = 'Y'
and dbo.ufOverdueClient = 'Y'
and dbo.ufPreferredClient = 'N'
Don't laugh guys. Some of the developers where I work thought this was the smart way to code.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 7, 2009 at 7:00 pm
John Paul (8/7/2009)
How can we make index on UDF/ Is there is away to replace table scan on UDF'sAs per the article ,
UDF as Computed Column
By Dinesh Priyankara, 2003/06/18 - he mentioned like ..
But remember, you can make an index on UDF, if it is deterministic.
How can we index the UDF..
I am using 3 UDF in one store procedure , which trows table Scan and which is very expensive in terms of execution time ..
Is any way to aviod /replace Table scan with Seek .. so that the performance of the store procedures increases
Thanks a lot
If you haven't done so already, you really need to study the code that Paul posted.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 10, 2009 at 10:59 am
Hi PAUL ,
Thanks for code , But i can't use this .. because ..
According to your Code .. the dbo.test table has a chance of indexing(number) .. So that we can avoid TABLE SCAN
But my scenario is like
In Function i am using a @derived table , but i can't index any of the Columns, because , all the Columns has the NULL and duplicate values , So i can't using Indexing here,
Can you please tell me Is there any way to avoid Table Scan..
Excuse me that if this question doesn't make any sense ..
Function script looks like this ..
create function dbo.Function_name()
returns
@returnInfo TABLE
(
ID varchar(50),
area_Code varchar(50),
country_Code varchar(50)
)
as
begin
declare @lTable TABLE
(
ID numeric(10) identity,
ID varchar(50),
area_Code varchar(50),
country_Code varchar(50)
)
insert into @lTable (ID, area_Code, country_Code)
select distinct t1.ID, t1.area_Code, t1.country_Code
from Table1
JOIN table2 t2 on t1.ID = t2.ID
JOIN table3 t3 on t2.Code = t3.Code
order by t1.ID, t1.area_Code, t1.country_Code, t3.Name
UPDATE @lTable
set ID = ID -
(
select min(ID)
from @lTable
where ID = l.ID and area_Code = l.area_Code and country_Code = l.country_Code
)
from @lTable l
insert into @returnInfo
select
ID,
area_Code,
country_Code
from @lTable
group by
ID,
area_Code,
country_Code
return
end
August 10, 2009 at 11:13 am
That's a very different scenario than what Paul is talking about. You're looking at returning an indexed table variable, and he's talking about indexing a computed column (built from a function call).
I'm sorry to say - your scenario is likely not worth pursuing. The issue is with Table Variables, which suffer from two limitations:
- you can only create a primary key on them
- they don't accumulate statistics, so are treated as a single row in execution plans.
Which means they will pretty much always be a perf problem out of the box. Your best bet might be to dump it to a temp table, and index it instead (which should work just fine).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 10, 2009 at 11:50 am
I have a problem with your code. The following table variable has two columns with the same name.
declare @lTable TABLE
(
ID numeric(10) identity,
ID varchar(50),
area_Code varchar(50),
country_Code varchar(50)
)
Could you please clarify the your code so it is possible to know which ID field is being used in your calculations in the function?
August 10, 2009 at 12:03 pm
In addition, could you explain what this function is attempting to do?
August 10, 2009 at 12:12 pm
Hi ,
that 's my mistake .. here is the updated script
create function dbo.Function_name()
returns
@returnInfo TABLE
(
ID varchar(50),
area_Code varchar(50),
country_Code varchar(50)
)
as
begin
declare @lTable TABLE
(
ID numeric(10) identity,
empID varchar(50),
area_Code varchar(50),
country_Code varchar(50)
)
insert into @lTable (empID, area_Code, country_Code)
select distinct t1.empID, t1.area_Code, t1.country_Code
from Table1
JOIN table2 t2 on t1.empID = t2.empID
JOIN table3 t3 on t2.Code = t3.Code
order by t1.empID, t1.area_Code, t1.country_Code, t3.Name
UPDATE @lTable
set portnumber =
ID -
(
select min(ID)
from @lTable
where empID = l.empID and area_Code = l.area_Code and country_Code = l.country_Code
)
from @lTable l
insert into @returnInfo
select empID, area_Code, country_Code from @lTable
group by empID, area_Code, country_Code
return
end
August 10, 2009 at 12:17 pm
What is portnumber? This column isn't defined in the table variable and isn't used in a subsequent query of the table variable.
August 10, 2009 at 12:31 pm
Also, could still use an explaination of what the function is attempting to accomplish.
August 10, 2009 at 1:36 pm
Updated code : and According to my understanding
returns one row per Country Code with three porttypes import type columns
For each class the ports import types are ordered by name and the
first three are picked to be included..
Updated code is :
create function dbo.Function_name()
returns
@returnInfo TABLE
(
ID varchar(50),
area_Code varchar(50),
country_Code varchar(50),
PortType1 varchar(50),
PortType2 varchar(50),
PortType3 varchar(50)
)
as
begin
declare @lTable TABLE
(
ID numeric(10) identity,
empID varchar(50),
area_Code varchar(50),
country_Code varchar(50),
PortType varchar(50) NULL,
PortNum numeric(10) NULL
)
insert into @lTable (empID, area_Code, country_Code,PortType)
select distinct t1.empID, t1.area_Code, t1.country_Code
from Table1
JOIN table2 t2 on t1.empID = t2.empID
JOIN table3 t3 on t2.Code = t3.Code
order by t1.empID, t1.area_Code, t1.country_Code, t3.Name
UPDATE @lTable
set PortNum =
ID -
(
select min(ID)
from @lTable
where empID = l.empID and area_Code = l.area_Code and country_Code = l.country_Code
) + 1
from @lTable l
insert into @returnInfo
select empID, area_Code, country_Code , PortType1 , PortType2 , PortType3
from @lTable
group by empID, area_Code, country_Code
return
end
August 10, 2009 at 2:05 pm
John Paul (8/10/2009)
Updated code : and According to my understandingreturns one row per Country Code with three porttypes import type columns
For each class the ports import types are ordered by name and the
first three are picked to be included..
Updated code is :
create function dbo.Function_name()
returns
@returnInfo TABLE
(
ID varchar(50),
area_Code varchar(50),
country_Code varchar(50),
PortType1 varchar(50),
PortType2 varchar(50),
PortType3 varchar(50)
)
as
begin
declare @lTable TABLE
(
ID numeric(10) identity,
empID varchar(50),
area_Code varchar(50),
country_Code varchar(50),
PortType varchar(50) NULL,
PortNum numeric(10) NULL
)
insert into @lTable (empID, area_Code, country_Code,PortType)
select distinct t1.empID, t1.area_Code, t1.country_Code
from Table1
JOIN table2 t2 on t1.empID = t2.empID
JOIN table3 t3 on t2.Code = t3.Code
order by t1.empID, t1.area_Code, t1.country_Code, t3.Name
UPDATE @lTable
set PortNum =
ID -
(
select min(ID)
from @lTable
where empID = l.empID and area_Code = l.area_Code and country_Code = l.country_Code
) + 1
from @lTable l
insert into @returnInfo
select empID, area_Code, country_Code , PortType1 , PortType2 , PortType3
from @lTable
group by empID, area_Code, country_Code
return
end
Each time you post code that is different from the previous post and each time I keep seeing discrepancies in the code that require further explaination. Your last insert for instance, how are you getting PortType1, PortType2, PortType3 from table @lTable?
Something that would really help is if you would provide the DDL for the source tables, some sample data for those tables, and the expected resutls from this function based on the sample data.
August 10, 2009 at 2:23 pm
Sorry for that , i can't post Actual code , i changed / replaced using F3 , any way ..
PortType1, PortType2 , PortType3 from @ltabel
insert into @returnInfo
select empID, area_Code, country_Code ,
max(case when PortType= 1 then PortType1 end),
max(case when PortType = 2 then PortType2 end),
max(case when PortType = 3 then PortType3 end)
from @lTable
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply