September 4, 2009 at 9:31 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 1:41 pm
More information would be useful here. For instance, do you know if the slowdown is caused by sp_transform or by the select in CPOS.RP_TotalAccessories?
September 4, 2009 at 1:49 pm
This is a duplicate thread. Discussion is already underway in the other forum it was posted in.
t_marciniszyn, please don't double-post.
- 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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply