April 23, 2012 at 1:05 pm
Here is my tables and indexes structure :
CREATE TABLE [dbo].[Clients](
[ClientId] [int] IDENTITY(1,1) NOT NULL,
[LastName] [varchar](15) NOT NULL,
[FirstName] [varchar](12) NOT NULL,
[Sex] [varchar](1) NOT NULL,
[DOB] [date] NULL,
[SSN] [varchar](9) NULL,
[RegionId] [smallint] NULL,
CONSTRAINT [PK_Clients_ID] PRIMARY KEY CLUSTERED
(
[ClientId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[ClientTrx](
[TrxId] [int] IDENTITY(1,1) NOT NULL,
[ClientId] [int] NOT NULL,
[TrxType] [varchar](1) NULL,
[TrxDate] [date] NULL,
[TrxAccount] [varchar](5) NULL,
[TrxTitle] [varchar](5) NULL,
[TrxAmount] [decimal](12, 2) NULL,
[TrxMonth] [date] NULL,
CONSTRAINT [PK_ClientTrx] PRIMARY KEY CLUSTERED
(
[TrxId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_ClientTrx_ClientId] ON [dbo].[ClientTrx]
(
[ClientId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_ClientTrx_Trxdate] ON [dbo].[ClientTrx]
(
[TrxDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ClientTrxClientIdTrxMonth] ON [dbo].[ClientTrx]
(
[ClientId] ASC,
[TrxMonth] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Here is my function with returns transaction total for given parameters.
CREATE FUNCTION [dbo].[fnTransTotal]
(
@ClientId int,
@AcType varchar(5),
@OnDate Date
)
RETURNS Decimal(10,2)
AS
BEGIN
DECLARE @BalAmt Decimal(10,2)
Declare @CrBal money, @DbBal money
SELECT @CrBal = IsNull(SUM(CASE WHEN TrxType = 'C' THEN TrxAmount ELSE 0 END),0),
@DbBal = IsNUll(SUM(CASE WHEN TrxType = 'D' THEN TrxAmount ELSE 0 END),0)
FROM ClientTrx
INNER JOIN Accounts ON Accounts.AcType = ClientTrx.TrxAccount
WHERE (@ClientId is null OR ClientTrx.ClientId = @ClientId)
AND (@AcType = '' OR Accounts.AcType = @AcType)
AND TrxMonth <= @OnDate
Set @BalAmt = @CrBal - @DbBal
RETURN (@BalAmt)
END
I use this function in T-SQL something like this -
SELECT ClientId, LastName, FirstName, dbo.fnTransTotal (ClientId, 'ACT1',GetDate()) AS Act1Tot,
dbo.fnTransTotal (ClientId, 'ACT2',GetDate()) AS Act2Tot FROM Clients
It takes about 30 mins to return final results.
If I change WHERE condition to
WHERE (ClientTrx.ClientId = @ClientId)
AND (Accounts.AcType = @AcType)
AND TrxMonth <= @OnDate
after changing this it runs in 30 secs - huge difference. I created the function for general purpose so I can use it with different combination as requirements come. Is it a bad idea to use variable in where condition? Or any better idea to improve this query?
Appreciate your help.
Thanks.
April 23, 2012 at 1:15 pm
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
To improve that further, take the UDFs out and do all the work in a single query. Data-accessing scalar UDFs are about the worst thing that you can do in terms of performance.
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
April 23, 2012 at 1:34 pm
If you would still like to use a function, give this a try. It isn't coded as a catch all query.
CREATE FUNCTION [dbo].[itvf_TransTotal]
(
@ClientId int,
@AcType varchar(5),
@OnDate Date
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN(
SELECT
IsNull(SUM(CASE WHEN TrxType = 'C' THEN TrxAmount ELSE 0 END),0) -
IsNUll(SUM(CASE WHEN TrxType = 'D' THEN TrxAmount ELSE 0 END),0) AS BalanceAmt
FROM
ClientTrx
INNER JOIN Accounts
ON Accounts.AcType = ClientTrx.TrxAccount
WHERE
(ClientTrx.ClientId = @ClientId)
AND (Accounts.AcType = @AcType)
AND TrxMonth <= @OnDate)
SELECT
ClientId,
LastName,
FirstName,
tt1.BalanceAmt AS Act1Tot,
tt2.BalanceAmt AS Act2Tot
FROM
Clients
CROSS APPLY dbo.itvf_TransTotal (ClientId, 'ACT1',GetDate()) tt1
CROSS APPLY dbo.itvf_TransTotal (ClientId, 'ACT2',GetDate()) tt2;
Missing one table, or I would have tested the above function. Give it a try and see how it does.
I also do agree with Gail that putting it in one query makes a lot of sense as well. Sometimes, however, using a function makes sense when the same code is used in multiple queries as it makes it easier to localize changes.
April 23, 2012 at 1:36 pm
Lynn Pettis (4/23/2012)
Sometimes, however, using a function makes sense when the same code is used in multiple queries as it makes it easier to localize changes.
Providing it's an inline table-valued function, absolutely. Scalar functions that access data impact performance way too much.
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
April 23, 2012 at 1:45 pm
GilaMonster (4/23/2012)
Lynn Pettis (4/23/2012)
Sometimes, however, using a function makes sense when the same code is used in multiple queries as it makes it easier to localize changes.Providing it's an inline table-valued function, absolutely. Scalar functions that access data impact performance way too much.
Much agree. I found that out myself and did a blog about it (now about 3 years old). It was quite interesting and illuminating.
April 23, 2012 at 3:27 pm
Thank Gail and Lynn,
I will have to think more about my function and will rewrite to optimize it.
Gali - I read the article provedied in your link and make more sense to create dynamic query but I think dynamic query is not allowed in UDF so I will have to think something different.
Thanks again for all your quick responses.
April 23, 2012 at 3:39 pm
No, it's not.
If you're on SQL 2008 SP2 or above, you can use OPTION(RECOMPILE) (assuming that's allowed in a function), but in a scalar function that could also have nasty effects on performance.
Unless you're going inline table-valued UDFs (as Lynn showed), you really are better off avoiding functions.
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
April 23, 2012 at 4:12 pm
How do I use table-valued UDFs instead of scalar-valued UDF? Can you please show me in my query.
Thanks.
April 23, 2012 at 4:26 pm
mak101 (4/23/2012)
How do I use table-valued UDFs instead of scalar-valued UDF? Can you please show me in my query.
Lynn already did.
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
April 24, 2012 at 9:27 am
Oops, missed it. I will try and let you guys know the performance.
Thanks.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply