September 4, 2009 at 11:00 am
Hi Everyone,
i have big problem with stored procedure since 3 weeks. Everything was perfect and now is working very slow. I don't know what is going on. I didn't change nothing on data base since 3 months and suddenly i have got a message from customer that his report doesn't work.
I use view like this which is working perfect, i have result in 1 sec.
this is view:
SELECT dbo.v_TargetGetDataForTotalAccessories.StoreId, dbo.v_TargetGetDataForTotalAccessories.StoreName,
dbo.v_TargetGetDataForTotalAccessories.Description, dbo.v_TargetGetDataForTotalAccessories.Contract,
SUM(dbo.v_TargetGetDataForTotalAccessories.LineTotal) AS Revenue, COUNT(dbo.v_TargetGetDataForTotalAccessories.Description) AS QtySold,
dbo.v_TargetGetDataForTotalAccessories.ProductType, dbo.v_TargetGetDataForTotalAccessories.PCCCode,
dbo.v_TargetGetDataForTotalAccessories.DateCreated, dbo.tblUser.UserName
FROM dbo.v_TargetGetDataForTotalAccessories INNER JOIN
dbo.tblUser ON dbo.v_TargetGetDataForTotalAccessories.PCCCode = dbo.tblUser.PCCCode
WHERE (dbo.v_TargetGetDataForTotalAccessories.ProductType = 3) OR
(dbo.v_TargetGetDataForTotalAccessories.ProductType = 8) OR
(dbo.v_TargetGetDataForTotalAccessories.ProductType = 9) OR
(dbo.v_TargetGetDataForTotalAccessories.ProductType = 10) OR
(dbo.v_TargetGetDataForTotalAccessories.ProductType = 11) OR
(dbo.v_TargetGetDataForTotalAccessories.ProductType = 12) OR
(dbo.v_TargetGetDataForTotalAccessories.ProductType = 14) OR
(dbo.v_TargetGetDataForTotalAccessories.ProductType = 15) OR
(dbo.v_TargetGetDataForTotalAccessories.ProductType = 16)
GROUP BY dbo.v_TargetGetDataForTotalAccessories.StoreId, dbo.v_TargetGetDataForTotalAccessories.StoreName,
dbo.v_TargetGetDataForTotalAccessories.Description, dbo.v_TargetGetDataForTotalAccessories.Contract,
dbo.v_TargetGetDataForTotalAccessories.ProductType, dbo.v_TargetGetDataForTotalAccessories.PCCCode,
dbo.v_TargetGetDataForTotalAccessories.DateCreated, dbo.tblUser.UserName
And after that i use stored procedure which takes ages to get result for example from 2009-07-01 to 2009-07-30 takes 12 min.
this is stored procedure:
USE [Rosbeg]
GO
/****** Object: StoredProcedure [CPOS].[RP_TotalAccessories] Script Date: 09/04/2009 16:20:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CPOS].[RP_TotalAccessories]
(
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@PIVOT BIT
)
AS
SET NOCOUNT ON
IF OBJECT_ID('TempDB..#PREPAYSALESNOEQUIP') IS NOT NULL
DROP TABLE #PREPAYSALESNOEQUIP
SELECT StoreName AS Location, SUM (Revenue) AS Contracts, UserName
INTO [#PREPAYSALESNOEQUIP]
FROM dbo.v_TotalAccessories
WHERE (DateCreated BETWEEN @STARTDATE AND @ENDDATE)
GROUP BY StoreName, UserName
IF @PIVOT = 1
EXEC sp_transform
@TableOrView_name = '[#PREPAYSALESNOEQUIP]',
@Aggregate_Column = 'Contracts',
@Select_Column = 'Location', -- row header
@pivot_column = 'Username' -- column header
ELSE
EXEC sp_transform
@TableOrView_name = '[#PREPAYSALESNOEQUIP]',
@Aggregate_Column = 'Contracts',
@Select_Column = 'Username', -- row header
@pivot_column = 'Location' -- column header
RETURN
Database has 237Mb. Could you help me where i have to search to fix that problem, because this is strange not every stored procedure are to slow like this above.
Regards
Thomas
September 4, 2009 at 11:04 am
I'd have to know what sp_transform does to even begin debug this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 11:18 am
GSquared (9/4/2009)
I'd have to know what sp_transform does to even begin debug this.
Hi GSquared,
sp_transform changes type of view report, but there is no problem with this, even i disabled this sp_transform i get report after 12 min, i dont know what is wrong :
for exemle this procedure is working perfect fast, i don't understand where is problem:
this a view:
SELECT dbo.tblTransaction.Status, dbo.tblTransaction.DateCreated, dbo.tblUser.UserName, dbo.tblProduct.ProductName, dbo.tblProduct.Contract,
dbo.tblOrder.EquipmentPack, dbo.tblOrder.ConnectionPack, dbo.tblStore.StoreName, dbo.tblTransaction.TransactionId, dbo.tblStore.StoreId
FROM dbo.tblUser INNER JOIN
dbo.tblOrder ON dbo.tblUser.PCCCode = dbo.tblOrder.PCCCode INNER JOIN
dbo.tblIMEI ON dbo.tblOrder.ConnectionPack = dbo.tblIMEI.PKSerNo INNER JOIN
dbo.tblTransaction ON dbo.tblOrder.TransactionId = dbo.tblTransaction.TransactionId INNER JOIN
dbo.tblStore ON dbo.tblTransaction.Location = dbo.tblStore.StoreId INNER JOIN
dbo.tblProduct ON dbo.tblIMEI.ProductCode = dbo.tblProduct.ProductCode
WHERE (dbo.tblTransaction.Status = 'Sale Complete')
this a procedure form this view:
USE [Rosbeg]
GO
/****** Object: StoredProcedure [CPOS].[RP_PrePaySalesNoEquip] Script Date: 09/04/2009 18:15:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CPOS].[RP_PrePaySalesNoEquip]
(
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@PIVOT BIT
)
AS
SET NOCOUNT ON
IF OBJECT_ID('TempDB..#PREPAYSALESNOEQUIP') IS NOT NULL
DROP TABLE #PREPAYSALESNOEQUIP
SELECT StoreName AS Location, COUNT(Contract) AS Contracts, UserName
INTO [#PREPAYSALESNOEQUIP]
FROM dbo.v_Get_Commission_Data
WHERE (Contract = 'F') AND (DateCreated BETWEEN @STARTDATE AND @ENDDATE) AND (EquipmentPack = '') OR
(Contract = 'P') AND (DateCreated BETWEEN @STARTDATE AND @ENDDATE) AND (EquipmentPack = '')
GROUP BY StoreName, UserName
IF @PIVOT = 1
EXEC sp_transform
@TableOrView_name = '[#PREPAYSALESNOEQUIP]',
@Aggregate_Column = 'Contracts',
@Select_Column = 'Location', -- row header
@pivot_column = 'Username' -- column header
ELSE
EXEC sp_transform
@TableOrView_name = '[#PREPAYSALESNOEQUIP]',
@Aggregate_Column = 'Contracts',
@Select_Column = 'Username', -- row header
@pivot_column = 'Location' -- column header
RETURN
September 4, 2009 at 12:43 pm
All the proc does is populate a temp table, then run that sp_transform proc.
This means that there are three things that could be slow:
1. The view
2. Creating the temp table
3. sp_transform
You stated that the view is fast.
Try running the temp table creation separately, see if that's slow or not.
If creating the temp table is slow, then please post the execution plan for that.
If creating the temp table is fast, then the problem is sp_transform.
So, have you tested how fast creating the temp table is?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 5, 2009 at 2:00 am
It would be interesting to know if the procedure is still slow when called with the WITH RECOMPILE option.
For example EXECUTE dbo.SuddenlySlowProc WITH RECOMPILE;
I suspect the SELECT INTO temp table part of the query - but I'm just guessing really. In addition to the possible causes already mentioned, the problem could be parameter snifffing, out of date statistics, a tempdb I/O bottleneck, blocking while waiting on a locked resource...a hundred things.
Any chance of running the procedure manually and uploading the Actual Execution plan from Management Studio?
Paul
September 5, 2009 at 4:54 am
GSquared (9/4/2009)
All the proc does is populate a temp table, then run that sp_transform proc.This means that there are three things that could be slow:
1. The view
2. Creating the temp table
3. sp_transform
You stated that the view is fast.
Try running the temp table creation separately, see if that's slow or not.
If creating the temp table is slow, then please post the execution plan for that.
If creating the temp table is fast, then the problem is sp_transform.
So, have you tested how fast creating the temp table is?
Hi GSquared thanks for reply,
I have tested all and view is fast , creating the tem as well and sp_transform too. I was working on that server all night and tested many different queries and sometimes were working fast and sometimes slow. I gave up. And from strange reason when i checked 15min ago my application .NET which is connected to this database is working perferct. Who can explain me what was is? I downloaded this database on my pc at home where i have exactly the same enviroment as on my customer's server and i have the same poblem like before. I'm confused.
anyway thanks very much
Regards
Thomas
September 5, 2009 at 5:08 am
Paul White (9/5/2009)
It would be interesting to know if the procedure is still slow when called with the WITH RECOMPILE option.For example EXECUTE dbo.SuddenlySlowProc WITH RECOMPILE;
I suspect the SELECT INTO temp table part of the query - but I'm just guessing really. In addition to the possible causes already mentioned, the problem could be parameter snifffing, out of date statistics, a tempdb I/O bottleneck, blocking while waiting on a locked resource...a hundred things.
Any chance of running the procedure manually and uploading the Actual Execution plan from Management Studio?
Paul
Hi Paul,
thanks for your suggestion , i have checked all yesterday and nothing still the same problem, but from strange reason today morning everything is working perfekt, i don't kwon what was it. only few stored procedures wasn't working yesterday but today everything is fine. I wrote the application which is connected to this databse i VB.NET and i create the reports for customers which wasn't working yesterday and now all is fine where i have done mistake. I didn't change nothing on the server ans in code behind as well and now is working :-D. May you know what could be?
thanks Regards
Thomas
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply