I have grandkids twice as young as the last time I programmed with sql. I have written an sql script to update a monthly summary table from a transaction table with 1.5 mil rows. It will eventually be incorporated into a DTS that runs at night. It takes more than 7 hours to run as it is currently written. I know there is a way to optimize the script, probably using a combination of sum function, group by and nested cursors, but it will take me hours of trying and debuging to get it, Im sure. Here is the code I have. Can someone offer some direction to getting this to run faster? The summary records are created/updated based on the different combinations of the fields used in the where clause and exists function. If anyone needs more info, just let me know.
I hope this is too much to ask, but any help will be very appreciated. Also, I apologize in advance for the logic migraines my code may give to you sql-gurus!
Thanks!
Code:
DECLARE @Shortname_List varchar(115)
DECLARE @Invoice_Date smalldatetime,
@Transaction_Period int,
@Transaction_Month int,
@Transaction_Year int,
@Company int,
@Company_Name varchar(60),
@Company_Shortname varchar(11),
@Site int,
@Sitename varchar(30),
@Business_Activity int,
@Intercompany_Code int,
@Business_Type int,
@Transaction_Type int,
@Business_Unit int,
@Commodity_Group int,
@Commodity_Code int,
@Commodity_Description varchar(60),
@Freight_Cost_Type decimal(21,6),
@Transfer_Department decimal(17,2),
@Transfer_Type int,
@Summary_Dollars decimal(19,4),
@Summary_Pounds decimal(19,4),
@Summary_Shrinkage_Pounds decimal(19,4)
TRUNCATE TABLE dbo.monthly_recy_summary
DECLARE curs_stat CURSOR
FOR SELECT beleg_datum, abteilung, lager, stat_kkz, ekvk, type, gruppe, sonr, kostenfeld_3, kostenfeld_2, uland, pos_wert, lief_menge, shrink_mng
FROM dbo.r_stat
OPEN curs_stat
FETCH curs_stat INTO @Invoice_Date, @Site, @Business_Activity, @Intercompany_Code, @Business_Type, @Transaction_Type, @Commodity_Group, @Commodity_Code, @Freight_Cost_Type, @Transfer_Department, @Transfer_Type, @Summary_Dollars, @Summary_Pounds, @Summary_Shrinkage_Pounds
SELECT @Shortname_List = ' SRS SSX SSMC Morgan CityAuto Shred Resource Viking SoRec '
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @Transaction_Year = YEAR( @Invoice_Date )
SELECT @Transaction_Month = MONTH( @Invoice_Date )
SELECT @Transaction_Period = CAST( @Transaction_Year AS VARCHAR ) + RIGHT('00'+CAST( @Transaction_Month AS VARCHAR(2)),2)
SELECT @Company = sr_site.company, @Sitename = sr_site.sitename FROM dbo.sr_site WHERE sr_site.site = @Site
SELECT @Company_Name = fibupar.name_1 FROM dbo.fibupar WHERE fibupar.firmennr = @Company
SELECT @Company_Shortname = SUBSTRING(@Shortname_List, ((@Company * 11) - 10), 11)
SELECT @Commodity_Description = r_sorte.bemerkung, @Business_Unit = r_sorte.sparte FROM dbo.r_sorte WHERE r_sorte.nummer = @Commodity_Code
IF @Transaction_Type = 11
BEGIN
SELECT @Freight_Cost_Type = 0
END
ELSE
BEGIN
SELECT @Transfer_Department = 0, @Transfer_Type = 0
END
IF EXISTS( SELECT transaction_period FROM dbo.monthly_recy_summary
WHERE transaction_period = @Transaction_Period
AND company = @Company
AND site = @Site
AND business_activity = @Business_Activity
AND intercompany_code = @Intercompany_Code
AND business_type = @Business_Type
AND commodity_code = @Commodity_Code
AND freight_cost_type = @Freight_Cost_Type
AND transfer_department = @Transfer_Department
AND transfer_type = @Transfer_Type)
BEGIN
UPDATE dbo.monthly_recy_summary
SET summary_dollars = summary_dollars + @Summary_Dollars,
summary_pounds = summary_pounds + @Summary_Pounds,
summary_shrinkage_pounds = summary_shrinkage_pounds + @Summary_Shrinkage_Pounds
WHERE transaction_period = @Transaction_Period
AND company = @Company
AND site = @Site
AND business_activity = @Business_Activity
AND intercompany_code = @Intercompany_Code
AND business_type = @Business_Type
AND commodity_code = @Commodity_Code
AND freight_cost_type = @Freight_Cost_Type
AND transfer_department = @Transfer_Department
AND transfer_type = @Transfer_Type
END
ELSE
BEGIN
INSERT INTO dbo.monthly_recy_summary (
transaction_period,
transaction_month,
transaction_year,
company,
company_name,
company_shortname,
site,
sitename,
business_activity,
intercompany_code,
business_type,
transaction_type,
business_unit,
commodity_group,
commodity_code,
commodity_description,
freight_cost_type,
transfer_department,
transfer_type,
summary_dollars,
summary_pounds,
summary_shrinkage_pounds )
VALUES ( @Transaction_Period,
@Transaction_Month,
@Transaction_Year,
@Company,
@Company_Name,
@Company_Shortname,
@Site,
@Sitename,
@Business_Activity,
@Intercompany_Code,
@Business_Type,
@Transaction_Type,
@Business_Unit,
@Commodity_Group,
@Commodity_Code,
@Commodity_Description,
@Freight_Cost_Type,
@Transfer_Department,
@Transfer_Type,
@Summary_Dollars,
@Summary_Pounds,
@Summary_Shrinkage_Pounds )
END
FETCH curs_stat INTO @Invoice_Date, @Site, @Business_Activity, @Intercompany_Code, @Business_Type, @Transaction_Type, @Commodity_Group, @Commodity_Code, @Freight_Cost_Type, @Transfer_Department, @Transfer_Type, @Summary_Dollars, @Summary_Pounds, @Summary_Shrinkage_Pounds
END
CLOSE curs_stat
DEALLOCATE curs_stat
I hope this is too much to ask, but any help will be very appreciated. Also, I apologize in advance for the logic migraines my code may give to you sql-gurus!
Thanks!