Grandma trying to write sql 1

[danisem]

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.

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!

 

[vongrunt]

General idea:

- truncate table
- big ugly INSERT INTO.... SELECT... GROUP BY
- few simple additional queries to fill in dependent denormalized columns (tran year/month, company name etc).

To make that as simple as possible, more information about table monthly_recy_summary is necessary. Does it have any primary key or unique constraint? Which columns in that table are required (declared as NOT NULL)?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[danisem]

Thanks for the reply. The monthly_recy_summary table has no unique key. The combination of the fields in the where clause seen in the code is what makes each row unique. It is indexed though on the transaction period with a secondary index on transaction period and company. We have started a data warehouse to work with Business Objects and our first reports were against the transaction table r_stat and are too slow, hence the reason for a summary table. The combination of fields the data is queried on are in the where clause and come from selections in master data tables when transactions are entered so these fields can change if say a new freight cost type or commodity code are added to the master data files in the accounting software. Also, I can't create unique keys with concat of the fields because in the report queries, not all fields are used all the time. One data provider or variable may want company and business unit and another may just want commodity code and transaction year, etc. Does this info help you help me?
Thanks again!

 

[vongrunt]

Partially. I still need to know about NOT NULLable columns in table monthly_recy_summary. Check table definition with EM or use INFORMATION_SCHEMA.COLUMNS view to find such columns, if any.

Also: source table (r_stat) has 1.5M rows. How many rows typically has target table (monthly_recy_summary)?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[danisem]

Sorry, I forgot to answer the nulls. All the columns, except summary_dollars, summary_pounds, and summary_shrinkage_pounds are set to no nulls. There will always be a value for these, even if it is zero.

The r_stat table has accumulated transactions since 01/2004 and when I ran the summary script above it put almost 50,000 rows in summary table. (As we continue to develop the data warehouse, it will only process the last 3 months instead of the whole r_stat table.)

Thanks again.

 

[vongrunt]

OK, all nulls except summary* columns. Will try to post code without a cursor tomorrow.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

Some things I noticed in the meantime:

- transaction_period is declared as int, while calculations work as it is varchar(6) (???)
- company depends on site so it can be excluded from aggregation (SUM) and filled in later
- columns transaction_type and commodity_group are not used within aggregation (SUM) and don't depend on any GROUPed BY column.

Here we go... first truncate table and fill it with aggregated data (according to EXISTS() check):

TRUNCATE TABLE dbo.monthly_recy_summary

INSERT INTO dbo.monthly_recy_summary
(	transaction_year, transaction_month, site, business_activity, intercompany_code, business_type,
	commodity_code, freight_cost_type, transfer_department, transfer_type,
	summary_dollars, summary_pounds, summary_shrinkage_pounds  
)
SELECT transaction_year, transaction_month, site, business_activity, intercompany_code, business_type,
	commodity_code, freight_cost_type, transfer_department, transfer_type,
	SUM(summary_dollars), SUM(summary_pounds), SUM(summary_shrinkage_pounds)
FROM
(	SELECT YEAR(beleg_datum) as Transaction_year, MONTH(beleg_datum) as Transaction_month, 
		abteilung AS Site, 
		lager AS Business_Activity, stat_kkz AS Intercompany_Code, 
		ekvk AS Business_Type, type AS Transaction_Type, gruppe AS Commodity_Group, sonr AS Commodity_Code, 
		CASE WHEN type= 11 THEN 0 ELSE kostenfeld_3 END AS Freight_Cost_Type, 
		CASE WHEN type<>11 THEN 0 ELSE kostenfeld_2 END AS Transfer_Department, 
		CASE WHEN type<>11 THEN 0 ELSE uland END AS Transfer_Type, 
		pos_wert AS Summary_Dollars, lief_menge AS Summary_Pounds, shrink_mng AS Summary_Shrinkage_Pounds
		FROM dbo.r_stat
) source -- this name is necessary but irrelevant
GROUP BY transaction_year, transaction_month, site, business_activity, intercompany_code, business_type,
	commodity_code, freight_cost_type, transfer_department, transfer_type

Now first we fill directly dependent columns:

UPDATE mrs
SET transaction_period = transaction_year * 100 + transaction_month,
	company = sr_site.company,
	sitename = sr_site.sitename,
	business_unit =  r_sorte.sparte,
	commodity_description = r_sorte.bemerkung
FROM monthly_recy_summary	
LEFT OUTER JOIN dbo.sr_site ON sr_site.site = mrs.site
LEFT OUTER JOIN dbo.r_sorte ON r_sorte.nummer = mrs.commodity_code

... and then company* columns (2nd dependency level: site -> company -> company*)

UPDATE mrs
SET 	company_name = fibupar.name_1,
	company_shortname = short_names.short_name
FROM monthly_recy_summary	
LEFT OUTER JOIN fibupar ON fibupar.firmennr = mrs.company
LEFT OUTER JOIN
(	SELECT  1 as company_id, '' AS short_name UNION
	SELECT  2, 'SRS' UNION
	SELECT  3, 'SSX' UNION
	SELECT  4, 'SSMC' UNION
	SELECT  5, 'Morgan City' UNION
	SELECT  6, 'Auto Shred' UNION
	SELECT  7, 'Resource' UNION
	SELECT  8, 'Viking' UNION
	SELECT  9, '' UNION
	SELECT 10, 'SoRec' UNION
	SELECT 11, ''
) short_names ON short_names.company_id = mrs.company

After that two columns still remain NULL: transaction_type and commodity_group. I'm not sure what to do with them. Two choices:

a) if they can be aggregated, then simply put them into INSERT INTO... SELECT... (derived table) ... GROUP BY statement (4 times).
b) otherwise they represent first value in a group (similar to FIRST() function in Access).

Problem with b) is that in your original code cursor over table r_stat has no ORDER BY clause. Because order is not determined, first value in a group becomes kind of random. Perhaps r_stat has clustered index?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[danisem]

Thanks so much for the help! In response to the items you pointed out:

The period is originally int in the software it comes from so I just carried that over to the data warehouse.

It was an oversight, (bug) on my part for the transaction type and commodity group not to be included as summarized fields. They should. Good catch!

The missing ORDER BY clause is just an example of 'forgetting more than I remember'. r_stat doesn't have clustered indexing, just quite a few composites.

The code you wrote is great. I added transaction_type and commodity_group and now I am struggling with a 'syntax error near the keyword GROUP'. I hope I can at least figure that out, but I've included the modified code. Basically the same except for adding transaction_type and commodity_group.

This is the first time I have worked with SQL Server. (My past SQL exp is with Oracle.) But the Query Analyzer is a great tool.

Thanks, again!

TRUNCATE TABLE dbo.monthly_recy_summary

INSERT INTO dbo.monthly_recy_summary
(    transaction_year, transaction_month, site, business_activity, intercompany_code, business_type, transaction_type,
    commodity_code, commodity_group, freight_cost_type, transfer_department, transfer_type,
    summary_dollars, summary_pounds, summary_shrinkage_pounds  
)
SELECT transaction_year, transaction_month, site, business_activity, intercompany_code, business_type, transaction_type,
    commodity_code, commodity_group, freight_cost_type, transfer_department, transfer_type,
    SUM(summary_dollars), SUM(summary_pounds), SUM(summary_shrinkage_pounds)
FROM
(    SELECT YEAR(beleg_datum) as Transaction_year, MONTH(beleg_datum) as Transaction_month, 
        abteilung AS Site, 
        lager AS Business_Activity, stat_kkz AS Intercompany_Code, 
        ekvk AS Business_Type, type AS Transaction_Type, sonr AS Commodity_Code, gruppe AS Commodity_Group, 
        CASE WHEN type= 11 THEN 0 ELSE kostenfeld_3 END AS Freight_Cost_Type, 
        CASE WHEN type<>11 THEN 0 ELSE kostenfeld_2 END AS Transfer_Department, 
        CASE WHEN type<>11 THEN 0 ELSE uland END AS Transfer_Type, 
        pos_wert AS Summary_Dollars, lief_menge AS Summary_Pounds, shrink_mng AS Summary_Shrinkage_Pounds
        FROM dbo.r_stat
)
GROUP BY transaction_year, transaction_month, site, business_activity, intercompany_code, business_type, transaction_type,
    commodity_code, commodity_group, freight_cost_type, transfer_department, transfer_type
      
UPDATE dbo.monthly_recy_summary
SET transaction_period = transaction_year * 100 + transaction_month,
    company = sr_site.company,
    sitename = sr_site.sitename,
    business_unit =  r_sorte.sparte,
    commodity_description = r_sorte.bemerkung
FROM dbo.monthly_recy_summary    
LEFT OUTER JOIN dbo.sr_site ON sr_site.site = monthly_recy_summary.site
LEFT OUTER JOIN dbo.r_sorte ON r_sorte.nummer = monthly_recy_summary.commodity_code

UPDATE dbo.monthly_recy_summary
SET     company_name = fibupar.name_1,
    company_shortname = short_names.short_name
FROM dbo.monthly_recy_summary    
LEFT OUTER JOIN fibupar ON fibupar.firmennr = monthly_recy_summary.company
LEFT OUTER JOIN
(    SELECT  1 as company_id, '' AS short_name UNION
    SELECT  2, 'SRS' UNION
    SELECT  3, 'SSX' UNION
    SELECT  4, 'SSMC' UNION
    SELECT  5, 'Morgan City' UNION
    SELECT  6, 'Auto Shred' UNION
    SELECT  7, 'Resource' UNION
    SELECT  8, 'Viking' UNION
    SELECT  9, '' UNION
    SELECT 10, 'SoRec' UNION
    SELECT 11, ''
) short_names ON short_names.company_id = monthly_recy_summary.company

 

[vongrunt]

Em... derived table name (source) is missing. Like I said, it is necessary in order to pass syntax check but name itself is irrelevant in this query (nowhere used or referenced). Use whatever name you want but don't erase it.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[danisem]

I realized I did not give feedback! Your script was 'plug & play' perfect. I ran in 1 min 19 secs. Thanks so much for the help!