July 26, 2006 at 8:56 am
Hi, Im pritty new to t_sql...Came up with this script well actually 3 of them to help report on sales... prob is it has to process 11000 rows a day and it takes all night to run, is there a cheap way to speed things up...any help would be very helpfull!
[Code]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PerdictiveTable_Period3] AS
SET NOCOUNT ON
DELETE FROM dbo.PerdictivePeriod1
DECLARE @Bit Int
DECLARE @ProdID Varchar(30)
DECLARE @CustID varchar(30)
DECLARE @DailyUse Int
DECLARE @PerdictDate datetime
DECLARE @Quantity Int
DECLARE @DateBought Datetime
DECLARE @dateLastBought datetime
DECLARE @ProdCode varchar(30)
DECLARE @ID varchar(30)
DECLARE @MajNum varchar(30)
DECLARE @MinorGroup varchar(30)
DECLARE @PhoneNumber Varchar(30)
DECLARE @Var1 Int
DECLARE @Var2 Int
DECLARE @Period datetime
DECLARE @Period1 datetime
DECLARE @CustName varchar(30)
DECLARE @Addr1 varchar(30)
DECLARE @Addr2 varchar(30)
DECLARE @Addr3 varchar(30)
DECLARE @Postcode varchar(30)
DECLARE @Region varchar(50)
DECLARE @SalesMan varchar(30)
DECLARE @ProdName varchar(30)
DECLARE @Quantity_Bought varchar(30)
DECLARE @SalesManName varchar(200)
DECLARE @StockRoom varchar(100)
DECLARE @LastWeek datetime
DECLARE @Today datetime
DECLARE @SizeTable Int
DECLARE @Month datetime
DECLARE @StockRoomCode varchar(30)
DECLARE @Var3 Datetime
DECLARE @Var4 varchar(30)
SET @Today= getdate()
SET @Period1 = getDate() - 90
Print @Period1
SET @LastWeek = getdate() + 7
DECLARE @Count INT
SET @Count = 0
SELECT CustID, DateLastBought, DateBought, Prod_Code, Quantity_Bought, StockRoomCode, MinorNumber
INTO #PredictiveDateRange
FROM dbo.PerdictiveSalesTable
WHERE DateBought between @Period1 and @Today
CREATE TABLE #TempPredictivePeriod1([CustID] varchar(10),
[DailyUsage] int,
[NextDelivery] datetime,
[ProdID] varchar(200),
[Prod_Code] varchar(200),
[MajorNumber] varchar(200),
[MinorNumber] varchar(200),
[CustName] varchar(200),
[Addr1] varchar(200),
[Addr2] varchar(200),
[Addr3] varchar(200),
[PostCode] varchar(200),
[PhoneNumber] varchar(200),
[DateLastBought] datetime,
[DateBought] datetime,
[Call] int,
[SalesMan_Code] varchar(200),
[SalesMan_Name] varchar(200),
[Quantity_Bought] varchar(200),
[Region] varchar(10),
[Prod_Name] varchar(200),
[StockRoom] varchar(200),
StockRoomCode int)
INSERT INTO dbo.PerdictivePeriod3
SELECT DISTINCT CustID, null As 'DailyUsage' ,null As 'NextDelivery' ,ProdID, Prod_Code, Rtrim(MajorNumber),
Rtrim(MinorNumber), CustName, addr1, addr2, addr3, PostCode, PhoneNo As 'PhoneNumber', DateLastBought, null as 'DateBought',null As 'Call',
SalesMan As 'SalesMan_Code',SalesManName As 'SalesMan_Name',null as 'Quantity_Bought',Region, Prod_Name, StockRoom, StockRoomCode
FROM dbo.PerdictiveSalesTable
WHERE DateLastBought between @Period1 and @Today
DECLARE INP95 Cursor
FOR SELECT DISTINCT CustID, Prod_Code
FROM #PredictiveDateRange
OPEN INP95
FETCH NEXT FROM INP95 Into @CustID, @ProdID
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'Start'
print DATEPART(second, GETDATE())
CREATE TABLE #Counter(DateBought datetime,
DateLastBought datetime,
Quantity_Bought Int,
[ID] [int] IDENTITY (1, 1))
INSERT INTO #Counter
SELECT dateBought, datelastbought, Quantity_Bought
FROM #PredictiveDateRange
WHERE CustID = @CustID and Prod_Code = @ProdID
ORDER BY dateBought ASC
SET @SizeTable = (SELECT count(*) From #Counter)
IF @SizeTable > 1
BEGIN
SET @DateBought = (SELECT dateBought
FROM #Counter
WHERE [ID] = @SizeTable - 1)
SET @DateLastBought = (SELECT datelastBought
FROM #Counter
WHERE [ID] = @SizeTable)
SET @Quantity_Bought = (SELECT Quantity_Bought
FROM #Counter
WHERE [ID] = @SizeTable)
END
ELSE
BEGIN
SET @DateLastBought = (SELECT datelastBought
FROM #Counter
WHERE [ID] = @SizeTable)
SET @DateBought = @DateLastBought
SET @Quantity_Bought = (SELECT Quantity_Bought
FROM #Counter
WHERE [ID] = @SizeTable)
END
SET @Var1 = (SELECT sum(Quantity_Bought)
FROM dbo.PerdictiveSalesTable
Where datebought > @Period1 and CustID = @CustID and @ProdID = Prod_Code)
IF @Var1 = '' or @Var1 = 0
BEGIN
SET @Var1 = 1
END
ELSE IF @Var1 < 0
BEGIN
SET @Var1 = @Var1 * (-1)
END
ELSE
BEGIN
SET @Var1 = @Var1
END
SET @DailyUse = ((@Var1 / 90))
IF @DailyUse = 0
BEGIN
SET @DailyUse = 1
END
SET @Var2 = (SELECT max(Quantity_Bought) FROM #PredictiveDateRange where DateLastBought = @DateLastBought and CustID = @CustID and @ProdID = Prod_Code)
If @Var2 < 0
Begin
SET @Var2 = @Var2 * (-1)
End
SET @Var2 = @Var2 / @DailyUse
SET @PerdictDate = DATEADD(day,@Var2, @DateLastBought)
IF @PerdictDate between @Today and @LastWeek
BEGIN
SET @Bit = DateDiff(day, @Today, @PerdictDate)
END
ELSE
BEGIN
SET @Bit = 0
END
UPDATE dbo.PerdictivePeriod3
SET DailyUsage = @DailyUse,
NextDelivery = @PerdictDate,
DateBought = @DateBought,
Quantity_Bought = @Quantity_Bought,
DateLastBought = @DateLastBought,
Call = @Bit
WHERE CustID = @CustID And Prod_Code = @ProdID
DROP TABLE #Counter
Print 'End'
Print DATEPART(second, GETDATE())
FETCH NEXT FROM INP95 Into @CustID, @ProdID
END
CLOSE INP95
DEALLOCATE INP95
DECLARE @WeekInTheFuture datetime
SET @WeekInTheFuture = getdate() + 7
DELETE FROM dbo.PerdictivePeriod3 WHERE NextDelivery < @Today or NextDelivery > @WeekInTheFuture or NextDelivery is null or NextDelivery =''
[/Code]
Ps..what are the code tags here? Like dont seem to format it very well...
Thanks
July 26, 2006 at 11:37 am
Oh my. I'm not sure why you are creating, (dropping and then creating) the table #Counter. You are using @Parameters and you already have all this information in #PredictiveDateRange.
Also, I am not sure why @Var1, @Var2, and @DailyUse are integers as the division you use will string them out and could be causing conversion difficulties for SQL.
You may be able to handle many of these updates without a cursor using CASE statements as well a specific WHERE constraints...
I wasn't born stupid - I had to study.
July 26, 2006 at 11:59 am
I eliminated the creation of temp table from inside the cursor BUT you ll receive beter input if you post the Tables that you have and what you want to do
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PerdictiveTable_Period3] AS
SET NOCOUNT ON
DELETE FROM dbo.PerdictivePeriod1
DECLARE @Bit Int
DECLARE @ProdID Varchar(30)
DECLARE @CustID varchar(30)
DECLARE @DailyUse Int
DECLARE @PerdictDate datetime
DECLARE @Quantity Int
DECLARE @DateBought Datetime
DECLARE @dateLastBought datetime
DECLARE @ProdCode varchar(30)
DECLARE @ID varchar(30)
DECLARE @MajNum varchar(30)
DECLARE @MinorGroup varchar(30)
DECLARE @PhoneNumber Varchar(30)
DECLARE @Var1 Int
DECLARE @Var2 Int
DECLARE @Period datetime
DECLARE @Period1 datetime
DECLARE @CustName varchar(30)
DECLARE @Addr1 varchar(30)
DECLARE @Addr2 varchar(30)
DECLARE @Addr3 varchar(30)
DECLARE @Postcode varchar(30)
DECLARE @Region varchar(50)
DECLARE @SalesMan varchar(30)
DECLARE @ProdName varchar(30)
DECLARE @Quantity_Bought varchar(30)
DECLARE @SalesManName varchar(200)
DECLARE @StockRoom varchar(100)
DECLARE @LastWeek datetime
DECLARE @Today datetime
DECLARE @SizeTable Int
DECLARE @Month datetime
DECLARE @StockRoomCode varchar(30)
DECLARE @Var3 Datetime
DECLARE @Var4 varchar(30)
SET @Today= getdate()
SET @Period1 = getDate() - 90
Print @Period1
SET @LastWeek = getdate() + 7
DECLARE @Count INT
SET @Count = 0
SELECT CustID, DateLastBought, DateBought, Prod_Code, Quantity_Bought, StockRoomCode, MinorNumber
INTO #PredictiveDateRange
FROM dbo.PerdictiveSalesTable
WHERE DateBought between @Period1 and @Today
CREATE TABLE #TempPredictivePeriod1([CustID] varchar(10),
[DailyUsage] int,
[NextDelivery] datetime,
[ProdID] varchar(200),
[Prod_Code] varchar(200),
[MajorNumber] varchar(200),
[MinorNumber] varchar(200),
[CustName] varchar(200),
[Addr1] varchar(200),
[Addr2] varchar(200),
[Addr3] varchar(200),
[PostCode] varchar(200),
[PhoneNumber] varchar(200),
[DateLastBought] datetime,
[DateBought] datetime,
[Call] int,
[SalesMan_Code] varchar(200),
[SalesMan_Name] varchar(200),
[Quantity_Bought] varchar(200),
[Region] varchar(10),
[Prod_Name] varchar(200),
[StockRoom] varchar(200),
StockRoomCode int)
INSERT INTO dbo.PerdictivePeriod3
SELECT DISTINCT CustID, null As 'DailyUsage' ,null As 'NextDelivery' ,ProdID, Prod_Code, Rtrim(MajorNumber),
Rtrim(MinorNumber), CustName, addr1, addr2, addr3, PostCode, PhoneNo As 'PhoneNumber', DateLastBought, null as 'DateBought',null As 'Call',
SalesMan As 'SalesMan_Code',SalesManName As 'SalesMan_Name',null as 'Quantity_Bought',Region, Prod_Name, StockRoom, StockRoomCode
FROM dbo.PerdictiveSalesTable
WHERE DateLastBought between @Period1 and @Today
DECLARE INP95 Cursor
FOR SELECT DISTINCT CustID, Prod_Code
FROM #PredictiveDateRange
OPEN INP95
FETCH NEXT FROM INP95 Into @CustID, @ProdID
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'Start'
print DATEPART(second, GETDATE())
SET @DateBought=NULL
SET @DateLastBought=NULL
SET @Quantity_Bought=NULL
SELECT TOP 2
@DateBought=dateBought,
@DateLastBought=CASE WHEN @DateLastBought IS NULL THEN DateLastBought ELSE @DateLastBought END,
@Quantity_Bought=CASE WHEN @Quantity_Bought IS NULL THEN Quantity_Bought ELSE @Quantity_Bought END
FROM #PredictiveDateRange
WHERE CustID = @CustID and Prod_Code = @ProdID
ORDER BY dateBought DESC
SET @Var1 = NULL
SELECT @Var1=sum(Quantity_Bought)
FROM dbo.PerdictiveSalesTable
WHERE datebought > @Period1 AND CustID = @CustID AND @ProdID = Prod_Code
SET @Var1=ABS(ISNULL(@Var1,0))
IF @Var1 = 0 SET @Var1 = 1
SET @DailyUse = ((@Var1 / 90))
IF @DailyUse = 0 SET @DailyUse = 1
SET @Var2=NULL
SELECT @Var2=ABS(MAX(Quantity_Bought))
FROM #PredictiveDateRange
WHERE DateLastBought = @DateLastBought and CustID = @CustID and @ProdID = Prod_Code
SET @Var2=ISNULL(@Var2,0)
SET @Var2 = @Var2 / @DailyUse
SET @PerdictDate = DATEADD(day,@Var2, @DateLastBought)
IF @PerdictDate between @Today and @LastWeek
BEGIN
SET @Bit = DateDiff(day, @Today, @PerdictDate)
END
ELSE
BEGIN
SET @Bit = 0
END
UPDATE dbo.PerdictivePeriod3
SET DailyUsage = @DailyUse,
NextDelivery = @PerdictDate,
DateBought = @DateBought,
Quantity_Bought = @Quantity_Bought,
DateLastBought = @DateLastBought,
Call = @Bit
WHERE CustID = @CustID And Prod_Code = @ProdID
Print 'End'
Print DATEPART(second, GETDATE())
FETCH NEXT FROM INP95 Into @CustID, @ProdID
END
CLOSE INP95
DEALLOCATE INP95
DECLARE @WeekInTheFuture datetime
SET @WeekInTheFuture = getdate() + 7
DELETE FROM dbo.PerdictivePeriod3 WHERE NextDelivery < @Today or NextDelivery > @WeekInTheFuture or NextDelivery is null or NextDelivery =''
Vasc
July 27, 2006 at 1:07 am
Hi, and Thanks!
The reason for the #Temp table inside the cursor was to make sure no duplicates got through, because there are row exactly the same in the input table, so Distinct wouldn't work.
Vasc, the table commin in (dbo.PerdictiveSalesTable) is a table with 10 cols and 100,000 rows, it's meant to take each customer work out their next date of needing an order...it also works out their daily use based on past 3 months ordering....The output as you can see is a smallish table with all that info init that a bunch of if statments are run on for an Asp page.
Thanks again!
July 27, 2006 at 1:41 am
Post the DDL of the tables referenced in this exercise....plus some input data (in the form of INSERT statements)....plus expected results. Don't over-simplify the data...otherwise somebody might suggest a solution which won't work for your boundary cases.
This should help others to visualise what you are trying to solve.
Posting an EXECUTION plan from the SP would also help. Your main problem is the CURSOR, DROPPING and CREATING tables and then maybe LACK of INDICES.
Finally....having 'pure duplicates' suggests a data-model that needs improving....and maybe that is the end to start working on fixing 1st.....otherwise everything else is a sticking-plaster on a gaping wound.
July 27, 2006 at 2:12 am
I will post those things...but just quick about the datamodel...we have to take our data from system21(DB2)..a 1980's linked lib's DB with no validation or relations....So not much I can do there
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply