Function and performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Function and performance

Author

Topic

sardinka
Posting Yak Master

142 Posts

Posted - 2006-10-17 : 13:58:08

I have the following function which is working perfectly fine.
The only problem I have with this function is a performance.
Is it some how I could modify the function in order to get beetter performance?
CREATE FUNCTION dbo.viewAnswers
(@TranID decimal,@groupID varchar(10),@SortOrder1 int,@SortOrder2 int )
RETURNS varchar(8000)
AS
BEGIN
Declare @Notes varchar(8000)
Set @Notes=''
Select @Notes=@Notes + Case Answer when 'Yes' then 'Y' else 'N' end + ' '
From answers wa JOIN groupRules gr
ON wa.GroupID=gr.groupID AND gr.RuleID=wa.RuleID
Where wa.GroupID=@groupID and tranid=@TRANID
and ( sortOrder between @SortOrder1 and @SortOrder2 and gr.groupID=@groupID)
order by sortorder
Return @Notes
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-17 : 13:59:32

Could you post what indexes you have on answers and groupRules tables?

Tara Kizer

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-17 : 14:12:04

Why not do this as a sproc?

And are you sure it will only return one value?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sardinka
Posting Yak Master

142 Posts

Posted - 2006-10-17 : 14:25:38

It does work and return the right value. Why I should use sp instead of function?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-17 : 14:28:21

Regardless if you use a UDF or a stored procedure, the performance will be the same. So let's try to fix the performance before deciding on switching methods.

So the answer to my indexes question is...?

Tara Kizer

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-17 : 14:30:15

quote:
Originally posted by tkizer

Regardless if you use a UDF or a stored procedure, the performance will be the same.

You sure about that?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-17 : 14:35:06

Before testing the performance between UDFs and stored procedures, we need to get the query inside of it to perform better. The performance difference between the two should be negligible. Let's take this one step at a time before saying you should use a stored procedure for this.

Tara Kizer

sardinka
Posting Yak Master

142 Posts

Posted - 2006-10-17 : 15:11:21

It will return some value based on a passed arguments.
the return values looks like this "Y Y N Y...."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-17 : 15:15:12

I'm going to repost this since you haven't provided the answer yet:

Could you post what indexes you have on answers and groupRules tables?

Tara Kizer

sardinka
Posting Yak Master

142 Posts

Posted - 2006-10-17 : 15:27:12

GroupID and RuleID

sardinka
Posting Yak Master

142 Posts

Posted - 2006-10-17 : 15:29:04

answers:GroupID,RuleID,TranID
groupRules: GroupID, RuleID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-17 : 15:29:37

On both tables?

Could you post the DDL for both tables using Enterprise Manager's Generate SQL Script? Make sure to select the option to script the constraints and indexes in the last tab.

Tara Kizer

sardinka
Posting Yak Master

142 Posts

Posted - 2006-10-17 : 15:37:05

ALTER TABLE [dbo].[GroupRules] ADD
CONSTRAINT [PK_GroupRules] PRIMARY KEY CLUSTERED
(
[GroupID],
[RuleID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Answers] ADD
(
[TranID],
[GroupID],
[RuleID]
) ON [PRIMARY]
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-17 : 15:41:10

Huh? Could you post the entire script and not just portions of it?

Tara Kizer

sardinka
Posting Yak Master

142 Posts

Posted - 2006-10-17 : 15:51:27

My primary key is a clustered index

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-17 : 15:51:53

Could you read the hint link in my sig and post what it asks for?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-17 : 15:53:21

quote:
Originally posted by sardinka

My primary key is a clustered index

This isn't what was asked for.

Tara Kizer

sardinka
Posting Yak Master

142 Posts

Posted - 2006-10-17 : 16:06:29

CREATE TABLE [dbo].[GroupRules] (
[GroupID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RuleID] [int] NOT NULL ,
[RuleText] [varchar] (400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Answers] (
[TranID] [numeric](18, 0) NOT NULL ,
[GroupID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RuleID] [int] NOT NULL ,
[Answer] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateModified] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[GroupRules] ADD
CONSTRAINT [PK_GroupRules] PRIMARY KEY CLUSTERED
(
[GroupID],
[RuleID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Answers] ADD
CONSTRAINT [PK_Answers] PRIMARY KEY CLUSTERED
(
[TranID],
[GroupID],
[RuleID]
) ON [PRIMARY]
GO

INSERT INTO GroupRules(GroupID, RuleID, RuleText)
SELECT '111111',1, 'Answer1' UNION ALL
SELECT '111111',2, 'Answer1' UNION ALL
SELECT '111111',3, 'Answer1' UNION ALL
SELECT '111111',1, 'Answer1' UNION ALL
SELECT '777777',2, 'Answer1' UNION ALL
SELECT '111111',4, 'Answer1' UNION ALL
SELECT '777777',1, 'Answer1' UNION ALL
SELECT '888888',1, 'Answer1' UNION ALL

INSERT INTO Answers(TranID, GroupID, RuleID,Answer,DateModified)
SELECT 123,'111111', 2,'Yes',GetDate() UNION ALL
SELECT 123,'111111', 1,'No',GetDate() UNION ALL
SELECT 123,'111111', 4,'Yes',GetDate() UNION ALL
SELECT 123,'111111', 3,'Yes',GetDate() UNION ALL
SELECT 456,'111111', 2,'Yes',GetDate() UNION ALL
SELECT 456,'111111', 2,'Yes',GetDate() UNION ALL
SELECT 456,'111111', 1,'Yes',GetDate() UNION ALL
SELECT 456,'111111', 4,'Yes',GetDate() UNION ALL
SELECT 456,'111111', 3,'Yes',GetDate() UNION ALL
SELECT 789,'888888', 1,'Yes',GetDate() UNION ALL
SELECT 777,'888888', 4,'Yes',GetDate() UNION ALL
SELECT 987,'777777', 1,'Yes',GetDate() UNION ALL
SELECT 987,'777777', 2,'No',GetDate()

RESULT:
123,'111111','N Y Y Y'
456,'111111','Y Y Y Y'
777,'888888','Y '
789,'888888','Y '
987,'777777','Y N'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-17 : 16:09:58

I believe it won't be able to use an index on the answers table for this part of the query:

ON wa.GroupID=gr.groupID AND gr.RuleID=wa.RuleID

Since TranID is the first column in the index and isn't included here. If you ran the following query:

Select answer
From answers wa JOIN groupRules gr
ON wa.GroupID=gr.groupID AND gr.RuleID=wa.RuleID
Where wa.GroupID=@groupID and tranid=@TRANID
and ( sortOrder between @SortOrder1 and @SortOrder2 and gr.groupID=@groupID)
order by sortorder

What does the execution plan show?

Tara Kizer

sardinka
Posting Yak Master

142 Posts

Posted - 2006-10-17 : 16:27:02

Select Cost:0%
Sort cost:0%
Parallelism: cost:0%
Nested Loops:cost:6%
Answers: Cost 94%

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-17 : 18:02:51

Well we need more information than that from the execution plan. Run SET SHOWPLAN_TEXT ON and post the results here.

Tara Kizer

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources