July 8, 2009 at 2:22 pm
I have a legacy chunk of code that has about 90 columns returning, with an inline scaler function that does a lookup to another table on about 70 of them.
Is it faster to remove the inline function and replace it with a direct join to the table the function queries?
I know its poorly designed, but I am trying to make the best of it and tune it as much as possible.
July 8, 2009 at 3:16 pm
The , perhaps over simple, response is yes , removing scalar functions and joining will improve perfomance.
July 8, 2009 at 3:23 pm
I should have mentioned that all 70 of these columns look up a value from the *same table*.
I tried replacing the inline calls with self-joins (yep for all 70 of them) and that actually made things worse.
July 8, 2009 at 3:25 pm
Some of that will also depend on what else the function is doing. Is it just a simple lookup or is it performing calculations? I would recommend looking at the logical reads before changing the query and after changing the query.
Can you post your code?
July 8, 2009 at 3:25 pm
Some of that will also depend on what else the function is doing. Is it just a simple lookup or is it performing calculations? I would recommend looking at the logical reads before changing the query and after changing the query.
Can you post your code?
July 8, 2009 at 3:30 pm
The function is of the form:
create FUNCTION [dbo].[fn_GetDescription]
(
@pID INT
)
RETURNS nVarchar(100)
AS
BEGIN
-- Declare the return variable here
DECLARE @Descrip nVarchar(1000)
-- Add the T-SQL statements to compute the return value here
SELECT @Descrip = Descrip from tblDescriptions where ID = @pID;
-- Return the result of the function
RETURN ISNULL(@Descrip,NULL)
END
The report code was
SELECT ID, [dbo].[fn_GetDescription](OrderType), [dbo].[fn_GetDescription](OrderStatus), [dbo].[fn_GetDescription](OrderProductStatus)....etc etc.
The descriptions table holds lookup descriptions for a lot of order and product status type columns.
After testing stuff out, the function call seems to perform better than a lot of joins straight to the Descriptions table.
July 8, 2009 at 3:32 pm
Is it looking up a value in the same row??
July 8, 2009 at 3:38 pm
It looks to me like they've created a universal 'Descriptions' table that gets linked to every other table in the system. Would this be an accurate statement?
July 8, 2009 at 3:40 pm
John Rowan (7/8/2009)
It looks to me like they've created a universal 'Descriptions' table that gets linked to every other table in the system. Would this be an accurate statement?
Yup exactly like as specified http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/
July 8, 2009 at 6:43 pm
Yep, that's what we got, along with a bunch of big 'ol wide flat file tables.
Its a mess, trying to make it do what it can until we can gut it and redo it.
Turns out for some reason, calling the function is faster than self-joining to the 'descriptions' table 70 times.
Not what I expected...but i'll go with it for now.
July 9, 2009 at 1:22 am
Are your statistics up to date ?. Just as a thought I would guess what is happening is that an incorrect query plan is being generated for the version with joins.
July 9, 2009 at 3:23 am
I have a similar issue. 'Reference Values' table comprising (amongst its 27 fields)
RFVAL_REFNO (ID),
RFVDM_CODE (Reference Type),
Description,
Main)Code, Start_DTTM, End_DTTM, etc.
8012 rows, 830 Reference Types.
The first thing that I did when I found this (apart from creating a Primary Key!!!) was to create specific views, and use these views in my joins:
CREATE VIEW [dbo].[vw_ref_ethnic_group]
AS
SELECT RFVAL_REFNO
, DESCRIPTION
, MAIN_CODE
, RFTYP_CODE
, START_DTTM
, END_DTTM
FROM dbo.REFERENCE_VALUES
WHERE (RFVDM_CODE = 'ETHGR')
I'm trusting SQL Server to optimize, despite using a view and hope that the code looks more intelligible
July 9, 2009 at 4:05 am
Hi Ian,
I would of taken in the 'other way' and had a view to union smaller tables( for compatability only until time permitted to do all the joins correctly). Obviously a unique 'compatibility' id will probably be needed on the Colours and Gender tables
Take a look at what happens to the query plans if you specify a type.
Create Table Colours
(
ColourId integer primary key,
ColourCode varchar(20),
)
go
Create Table Gender
(
GenderId integer primary key,
GenderCode varchar(20),
)
go
insert into Colours values(1,'Red')
insert into Colours values(2,'Blue')
insert into Gender values(1,'Male')
insert into Gender values(2,'Female')
go
Create View MasterView
as
Select colourId as Id,
ColourCode as Code,
'Colour' As type
from colours
union
Select GenderId as Id,
GenderCode as Code,
'Gender' as type
from Gender
select * from MasterView where type = 'Colour'
select * from MasterView where type = 'Gender' and Id = 1
select * from MasterView where Id = 1
July 9, 2009 at 9:26 am
I would suggest a similar approach to Dave's last post. If your Descriptions table has some sort of identifier column that allows you to group Descriptions into types, I would create a Description table for each type and move the descriptions into these normalized tables. Use these to join to inside your query and create a view called Descriptions that UNIONs each of the smaller Descriptions tables together so you are not breaking other parts of the application that happen to be using the universal Descriptions table. It sounds to me like you've got other normalization problems to deal with as well so using interim views may become your friend as you phase your schema changes into your database.
Let us know how it goes. With 70 joins, I'm curious to see if breaking the universal table into smaller, normalized tables will help.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply