May 25, 2009 at 1:22 am
Dear Experts,
I have problem with the high CPU utilized queries and long / recurrent running queries in my production server. This below function is creating a lot of heck where it is declaring the TEMP table as below which is in code.
I have tuned and kept more research and did modifications. But still its using highest CPU utilization when i ran the Profiler and identifying the costliest queries. This function runs max number of times in a day. as
total_logical_reads last_logical_reads min_logical_reads max_logical_reads
9806704894 495323 22 495569
total_elapsed_timelast_elapsed_time min_elapsed_time max_elapsed_time
17352725587 737305 0 4754883
Can anyone please look into this and help me to make anyother modificationsor any one fine tune this one.
---------Actual Function Starts here.
DECLARE @TempOEDetails table
--------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[fn_RS_WHLBConsignorCommaSepList]
(
@LBFormId int
)
RETURNS varchar(4000)
AS
BEGIN -- The below two temporary table variables is used to put all the business units in a table. --
DECLARE @LBFormId BIGINT --
SET @LBFormId = 8678
DECLARE @t2 TABLE (ID INT IDENTITY, Column1 NVARCHAR(100))
DECLARE @t3 TABLE (Column1 NVARCHAR(100))
INSERT INTO @t2
SELECT BusinessUnitId
FROM (
SELECT FormId LBFormId, BusinessUnitId
FROM AgIndCWHLoadBuilding
WHERE BusinessUnitId is not null AND FormId = @LBFormId
UNION
SELECT LBFormId, BPName BusinessUnitId FROM AgCWHLBNAShipmentDetails
WHERE
BPName is not null AND LBFormId = @LBFormId
UNION
SELECT FormId LBFormId, BusinessUnitId FROM AgIndCWHLoadBuilding
WHERE BusinessUnitId in (SELECT storerkey FROM agcwhexceedstorer WHERE type=1)
AND BusinessUnitId is not null AND FormId = @LBFormId
UNION
SELECT FormId LBFormId, BusinessUnitId FROM AgIndCWHLoadBuilding
WHERE BusinessUnitId in (SELECT clcode FROM AgCWHNonAjiraClientsMaster) AND BusinessUnitId is not null AND FormId = @LBFormId ) SUB --
SELECT * FROM @t2
UPDATE @t2 SET Column1 = replace(Column1,' ','')+',' --
SELECT * FROM @t2
DECLARE @Values NVARCHAR(100), @ID INT
SELECT @ID = MIN(ID) FROM @t2
WHILE @ID is NOT NULL
BEGIN
SELECT @Values = SUBSTRING(Column1, 1,PATINDEX('%,%',Column1))
FROM @t2
WHERE ID = @ID
WHILE ISNULL(@Values,'') <> ''
BEGIN
INSERT INTO @t3
SELECT @Values
UPDATE @t2 SET Column1 = STUFF(Column1, 1, LEN(@Values), '')
WHERE ID = @ID
SELECT @Values = SUBSTRING(Column1, 1,PATINDEX('%,%',Column1)) FROM @t2 WHERE ID = @ID
END
SELECT @ID = MIN(ID)
FROM @t2 WHERE ID > @ID
END UPDATE @t3 SET Column1 = replace(Column1,',','') --
SELECT DISTINCT * FROM @t3
-------- Actual Function Starts here.
DECLARE @TempOEDetails table
( ColumnDetail varchar(4000) )
DECLARE @CommaSepList varchar(4000)
DECLARE @TempCommaSepList varchar(4000)
SET @CommaSepList = ''
BEGIN
INSERT INTO @TempOEDetails
SELECT MCDL01
FROM
(
SELECT LTRIM(RTRIM(MCMCU)) MCMCU , LTRIM(RTRIM(MCDL01)) MCDL01
FROM AgIndCWHJDEBranchPlant WHERE MCMCU IN (select DISTINCT Column1 from @t3)
UNION
SELECT storerkey AS MCMCU, company AS MCDL01 FROM agcwhexceedstorer
WHERE storerkey IN (select DISTINCT Column1 from @t3)
UNION
SELECT BPName AS MCMCU, ClientName AS MCDL01 FROM AgCWHLBNAShipmentDetails
WHERE BPName IN (select DISTINCT Column1 from @t3) ) SUB
where MCDL01 IS NOT NULL
END
IF @@ROWCOUNT > 0
UPDATE @TempOEDetails
SET @CommaSepList = ( @CommaSepList + ColumnDetail + ', ' )
IF(len(@CommaSepList)>0 )
BEGIN
Set @TempCommaSepList= substring( @CommaSepList, 1, ( len( @CommaSepList ) - 1 )
) END
ELSE
BEGIN
SET @TempCommaSepList = ''
END
RETURN @TempCommaSepList
END
May 25, 2009 at 1:42 am
Assume you've dealt with indexing and such
A few obvious comments
Could you change union to union all?
in (select DISTINCT Column1 from @t3)
Could you ensure unique values in @t3 rather than using distinct?
INTO @TempOEDetails
SELECT MCDL01
FROM
(
SELECT LTRIM(RTRIM(MCMCU)) MCMCU , LTRIM(RTRIM(MCDL01)) MCDL01
.....
Why have MCMCU in the derived table?
Doean't need to be a derived table (probably won't affect performance apart from the reduced resultset)
INSERT INTO @TempOEDetails
SELECT LTRIM(RTRIM(MCDL01)) MCDL01
FROM AgIndCWHJDEBranchPlant WHERE MCMCU IN (select DISTINCT Column1 from @t3)
UNION
SELECT company AS MCDL01 FROM agcwhexceedstorer
WHERE storerkey IN (select DISTINCT Column1 from @t3)
UNION
SELECT ClientName AS MCDL01 FROM AgCWHLBNAShipmentDetails
WHERE BPName IN (select DISTINCT Column1 from @t3) ) SUB
where MCDL01 IS NOT NULL
The bit at the end could be
select @CommaSepList = coalesce(@CommaSepList+ ', ', '') + ColumnDetail
from @TempOEDetails
To save the following statement to get rid of the trailing ,
Will have a look at the loop later - that's probably one of the main issues.
Cursors never.
DTS - only when needed and never to control.
May 25, 2009 at 1:45 am
I don't think that we can see from your code where your problem is. We don't know anything about the views and tables that you are using. Without knowing the number of records in your objects, the structure of the tables, the indexes that were created, it is very hard to tell where your problem is. You can use the profiler to find out about the problematic statement in the function itself. Use the sp:StmntCompleted event to get the logical reads, writes and CPU per statement in your function (don't forget to use the proper filter so you'll have only the relevant function in your trace).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 25, 2009 at 2:01 am
Looks like the loop is just splitting up comma delimitted lists from @t2
This would mean you don't need the identity.
insert @t3
select distinct SUBSTRING(Column1, 1,PATINDEX('%,%',Column1))
from @t2
while @@ROWCOUNT 0
begin
update @t2
set Column1 = STUFF(Column1, 1, PATINDEX('%,%',Column1), '')
delete @t2 where PATINDEX('%,%',Column1) = 0
insert @t3
select distinct SUBSTRING(Column1, 1,PATINDEX('%,%',Column1))
from @t2
end
Cursors never.
DTS - only when needed and never to control.
May 25, 2009 at 2:03 am
Those should help but really need to know where the issues are.
Can you materialise the datasets you are using and maybe update them from triggers.
It looks like the table structure doesn't really suit the system you are using so I would look at normlising that - if you did that you could probably get rid of the function altogether.
Cursors never.
DTS - only when needed and never to control.
May 25, 2009 at 2:31 am
Table has indexing properly and when ran Profiler the part which is using high CPU is at TempTable :
----------------------------------------------------------------------------------------------
INSERT INTO @TempOEDetails
SELECT MCDL01
FROM
(
SELECT LTRIM(RTRIM(MCMCU)) MCMCU , LTRIM(RTRIM(MCDL01)) MCDL01
FROM AgIndCWHJDEBranchPlant WHERE MCMCU IN (select DISTINCT Column1 from @t3)
UNION
SELECT storerkey AS MCMCU, company AS MCDL01 FROM agcwhexceedstorer
WHERE storerkey IN (select DISTINCT Column1 from @t3)
UNION
SELECT BPName AS MCMCU, ClientName AS MCDL01 FROM AgCWHLBNAShipmentDetails
WHERE BPName IN (select DISTINCT Column1 from @t3) ) SUB
where MCDL01 IS NOT NULL
END
IF @@ROWCOUNT > 0
UPDATE @TempOEDetails
SET @CommaSepList = ( @CommaSepList + ColumnDetail + ', ' )
IF(len(@CommaSepList)>0 )
BEGIN
Set @TempCommaSepList= substring( @CommaSepList, 1, ( len( @CommaSepList ) - 1 )
) END
ELSE
BEGIN
------------------------------------------------------------------------------------------------
This part of the fucntion is running number of times and creating much time to execute and many times its executing. When this is executing in production i can see there is a raise in CPU utilization suddenly from 36+ to 99 %.
can anyone reframe the function to a good extnet in a single shot... As i did making lot of changes in th function, but not yet resolved.
Thanks in advance...
May 25, 2009 at 2:47 am
Have a look at my previous post about this bit.
Cursors never.
DTS - only when needed and never to control.
May 25, 2009 at 4:07 am
insert @t3
select distinct SUBSTRING(Column1, 1,PATINDEX('%,%',Column1))
from @t2
while @@ROWCOUNT 0
begin
update @t2
set Column1 = STUFF(Column1, 1, PATINDEX('%,%',Column1), '')
delete @t2 where PATINDEX('%,%',Column1) = 0
insert @t3
select distinct SUBSTRING(Column1, 1,PATINDEX('%,%',Column1))
from @t2
end
---------------------------------------------------------
adding this to the functionit thrown error like :
insert @t3 and @t2 .......
Can anyone has idea how to reframe this to a fine tuned function..... Please help me to fix this....
Fine tuning is appreciated....
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply