Parsing one table to another, efficiency is killin

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Parsing one table to another, efficiency is killin

Author

Topic

bellboy
Starting Member

6 Posts

Posted - 2007-06-28 : 15:36:41

hi guys. I have one table which is basically like flat file, which I must parse into a relational style db. This table has about 130 columns in total, and each row essentially represents an order and look as follows:

SalesRep Customer_phone Order_type Prod_1_Rev ..... Prod_n_Rev Tot_Revenue Prod_1_units ...... Prod_n_units.

I have created 4 other tables to capture:
Sales_Rep (and info including mgr, etc)

Order Info (date of order order number, etc)

Order Line items (for each order all the products that were purchased, including units of each product, and pricing for each product, and a product code)

Products (list all the products)

after about a week or re-learning SQL (haven't used it in 7 years) I managed to get code that worked. I need to iterate line-by-line each order in the un-parsed file, and for each order itterate each column for order line items. I first did this with 2 nested cursosrs, which was a nightmare. Then I tried with Temporary tables, which was better but still slow (took about 3 mins to go through 1000 rows) and I just tried actual tables (which was slower that temporary tables)

Here is my code:

declare @OrderID int
declare @CurrentProductRev float(10)
declare @CurrentProductUnits float(10)
declare @Mth int
declare @Yr int

declare @SQLstr nvarchar(1000)
declare @params nvarchar(1000)

declare @OrderRow int
declare @ProductColRev int
declare @ProductColUnits int

declare @NumOrderRows int
declare @NumProductColRev int
declare @NumProductColUnits int

declare @ProdRev nvarchar(100)
declare @ProdUnits nvarchar(100)

SET @OrderRow = 1
SELECT @NumOrderRows = count(*) FROM Rep_V_Prod_Ont

--CREATE A TEMPORARY TABLES HOUSING ALL COLUMN NAMES FOR PRODUCT REVENUE

SELECT COLUMN_NAME
INTO CCC_ProductRevDetails
FROM CAMPAIGNS.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Rep_V_Prod_Ont'
AND RIGHT(COLUMN_NAME, 6) <> '_UNITS'
AND COLUMN_NAME <> 'PEIN'
AND COLUMN_NAME <> 'btn'
AND COLUMN_NAME <> 'compl_dt'
AND COLUMN_NAME <> 'issue_dt'
AND COLUMN_NAME <> 'manager'
AND COLUMN_NAME <> 'mth'
AND COLUMN_NAME <> 'order_no'
AND COLUMN_NAME <> 'order_tp'
AND COLUMN_NAME <> 'order_vol'
AND COLUMN_NAME <> 'representative'
AND COLUMN_NAME <> 'yr'
AND COLUMN_NAME <> 'TOTAL_REVENUE'
AND COLUMN_NAME <> 'RowID'
AND LEFT(COLUMN_NAME, 8) <> 'CONTRACT'
ORDER by COLUMN_NAME asc

--ADD Unique ID to all rows in Temp Product Revenue Table
ALTER TABLE CCC_ProductRevDetails
ADD ProdRevID INT IDENTITY(1,1) NOT NULL

SELECT @NumProductColRev = COUNT(*) FROM CCC_ProductRevDetails --GET NUMBER OF PRODUCT REV COLUMNS

--CREATE A TEMPORARY TABLES HOUSING ALL COLUMN NAMES FOR PRODUCT UNITS
--if exists (SELECT * from #ProductUnitsDetails)
-- DROP TABLE #ProductUnitsDetails

SELECT COLUMN_NAME
INTO CCC_ProductUnitsDetails
FROM CAMPAIGNS.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Rep_V_Prod_Ont'
AND RIGHT(COLUMN_NAME, 6) = '_UNITS'
ORDER by REPLACE(COLUMN_NAME, '_UNITS', '')

--ADD Unique ID to all rows in Temp Product Units Table
ALTER TABLE CCC_ProductUnitsDetails
ADD ProdUnitsID INT IDENTITY(1,1) NOT NULL

SELECT @NumProductColUnits = COUNT(*) FROM CCC_ProductUnitsDetails --GET NUMBER OF PRODUCT UNIT COLUMNS

--if exists (SELECT * from #OrderDetails)
--DROP TABLE #OrderDetails

WHILE @OrderRow <= @NumOrderRows
BEGIN
SET @ProductColUnits = 1
SET @ProductColRev = 1

--Insert Order detail into Order Table

INSERT CCC_Orders (RepID, Issue_dt, Compl_dt, Yr, Mth, btn, Order_no, order_vol, order_tp, TotRevenue)
SELECT b1.RepID, a1.Issue_dt, a1.Compl_dt, a1.Yr, a1.Mth, a1.btn, a1.Order_no, a1.order_vol, a1.order_tp, a1.TOTAL_REVENUE
FROM Rep_V_prod_Ont as a1 INNER JOIN CCC_Reps as b1
ON a1.PEIN = b1.PEIN
AND a1.representative = b1.representative
AND a1.manager = b1.manager
AND a1.Yr = b1.Yr
WHERE a1.RowID = @OrderRow

--get OrderID for this Order
SELECT @OrderID = IDENT_CURRENT('CCC_Orders')
--print @OrderID

WHILE @ProductColRev <= @NumProductColRev
BEGIN
SELECT @SQLStr = 'SELECT @ProdRev = COLUMN_NAME FROM CCC_ProductRevDetails WHERE ProdRevID = '+ CAST(@ProductColRev as nvarchar(100))
SET @params = N'@ProdRev nvarchar(100) OUTPUT, @ProductColRev int'
EXEC sp_executesql @SQLStr, @params, @ProdRev=@ProdRev OUTPUT, @ProductColRev=@ProductColRev
--print @prodRev

SELECT @SQLStr = 'SELECT @ProdUnits = COLUMN_NAME FROM CCC_ProductUnitsDetails WHERE ProdUnitsID = '+ CAST(@ProductColUnits as nvarchar(100))
SET @params = N'@ProdUnits nvarchar(100) OUTPUT, @ProductColUnits int'
EXEC sp_executesql @SQLStr, @params, @ProdUnits=@ProdUnits OUTPUT, @ProductColUnits=@ProductColUnits
--print @prodUnits

SELECT @SQLStr = 'SELECT @CurrentProductRev = ' + quotename(@ProdRev) +' FROM Rep_V_Prod_Ont WHERE RowID='+ CAST(@OrderRow as nvarchar(100))
SET @params = N'@CurrentProductRev float OUTPUT, @ProdRev varchar(100), @OrderRow int'

EXEC sp_executesql @SQLStr, @params, @CurrentProductRev=@CurrentProductRev OUTPUT, @ProdRev=@ProdRev, @OrderRow=@OrderRow
--print @CurrentProductRev

Set @SQLStr = 'SELECT @CurrentProductUnits = ' + quotename(@ProdUnits) +' FROM Rep_V_Prod_Ont WHERE RowID='+ CAST(@OrderRow as nvarchar(100))
SELECT @params = N'@CurrentProductUnits float OUTPUT, @ProdUnits varchar(100), @OrderRow int'

EXEC sp_executesql @SQLStr, @params, @CurrentProductUnits=@CurrentProductUnits OUTPUT, @Produnits=@ProdUnits, @OrderRow=@OrderRow
--print @CurrentProductUnits

IF(@CurrentProductRev <> 0 OR @CurrentProductUnits <> 0) --Then Insert
BEGIN

SELECT @Mth = Mth FROM Rep_V_Ontario WHERE RowId = @OrderRow
SELECT @Yr = Yr FROM Rep_V_Ontario WHERE RowId = @OrderRow

--print @ProdRev
--print @ProdUnits
INSERT INTO CCC_OrderLineItem (OrderID,ProductCode,Units,Revenue,Yr,Mth)
VALUES (@OrderID,@ProdRev,CAST(@CurrentProductUnits as float),CAST(@CurrentProductRev as float),CAST(@yr as int),CAST(@Mth as Int))
--print @CurrentProductUnits
--print @CurrentProductRev
END

Set @ProductColRev = @ProductColRev +1
Set @ProductColUnits = @ProductColUnits +1

END
Set @OrderRow = @OrderRow +1
end

Sorry for the length of this... However I am stumped, I get an update of this file weekly and it has upwards of 500,000 lines. I would like it to run in <30 mins. Is that too much to ask??

Thank you very much in Advance...

Mike

X002548
Not Just a Number

15586 Posts

Posted - 2007-06-28 : 15:57:24

You really need to do this in a set based manner, and lose the dynamic sql

Do me a favor, post the ddl of your "flat file" table (which is probably 1 column) and what's it's data looks like, then pos the ddl of one of your destination tables, and then telll us what the mapping is suppose to be

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

bellboy
Starting Member

6 Posts

Posted - 2007-06-29 : 09:50:32

I have to use dynamic or else my maintenance job is going to be hell. You see the so called "flat file", even though it's not a flat file, it's a SQL Table that has column names as follows:

2007_Orders
------------------------------------------------------------------------------
PEIN representative manager Issue_dt yr mth compl_dt btn order_no order_vol order_tp
------------------------------------------------------------------------------

2007_Orders (contd)
------------------------------------------------------------------------------
Prdct1_revenue ... Prdct65_revenue Tot_revenue Prdct1_Units... Prdct65_Units
------------------------------------------------------------------------------

... Now the reason I must use Dynamic is that the Products change every so often. I don't want to have to edit the variables or SELECT statements whenever a product changes. Also sometimes we add products, so adding to the SELECT (when you already have 130 columns) can turn into a nightmare. So I use the Information_schema on the table to create a table with the column names of the products. Then I iterate through those. I think I found a decent way to do this, which will avoid going through line-by-line of each order, but rather go through the set. The only drawback is it will still go through dynamic to select all products (on each order) that and <> 0.

I'll try that and let you know.

Do you have any thoughts?

Forgot to mention, to the above Flat File table I added a unique identifier column (believe it or not some order_no are not unique and some are blank), that's the basis for my new rationale mentioned above

pootle_flump

1064 Posts

Posted - 2007-06-29 : 10:42:10

Hi bellboy

Are you familiar with normalisation? This is a classic first normal form violation. If you are not familair please, please read this:
http://r937.com/relational.html

You will keep getting head aches like this and hard to debug code if you keep the table as it is.

bellboy
Starting Member

6 Posts

Posted - 2007-06-29 : 11:02:56

pootle_flump
I am familiar with normalization, but the data comes that way. I can't help it. The whole point of what I'm doing IS to normalize the data into a relational db style. I want to parse all the data that you see in the above post table format to 4 tables: SalesReps, Orders, OrderLineItems, and Products.

bellboy
Starting Member

6 Posts

Posted - 2007-06-29 : 11:38:01

GOT IT,,,, Query Time to parse and re-create tables was 3.5 minutes. Thanks for the set-based tip X002548.

Mike

pootle_flump

1064 Posts

Posted - 2007-06-29 : 11:41:42

quote:
Originally posted by bellboy

pootle_flump
I am familiar with normalization, but the data comes that way. I can't help it. The whole point of what I'm doing IS to normalize the data into a relational db style. I want to parse all the data that you see in the above post table format to 4 tables: SalesReps, Orders, OrderLineItems, and Products.

Beg your pardon. When you said it was in a table not a flat file I thought you meant as part of your db design rather than a staging table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources