September 1, 2005 at 3:07 pm
this is the code of the proc:
---------------------------------------
CREATE PROCEDURE [dbo].[BestSellingItemsWebTransFull]
@beginDate varchar(8),
@endDate varchar(8),
@sourceCode varchar(16),
@sourceRef varchar(16),
@cartid varchar(20),
@customerType char(2),
@division char(2),
@category varchar(4),
@vendorNo varchar(10),
@useCustType int
AS
DECLARE @sourceCodeCart varchar(16)
CREATE TABLE #ORDERNOS (
ORDERNO char(8)
)
CREATE TABLE #ITEMCOUNT (
EDP int,
AMT int
)
CREATE TABLE #BESTSELLERS (
EDP int,
AMT int
)
CREATE TABLE #ITEMAMTS (
EDP int,
ITEMNO varchar(20),
FULLITEMNO varchar(20),
AMT int,
DESCRIPTION varchar(80),
WAREHOUSELOCS_004 char(10)
)
--Get source code from cart
SELECT @sourceCodeCart = SOURCE FROM ECOMLIVE.dbo.WEBCART WITH (NOLOCK)
WHERE CARTID = @customerType + @division + @cartid
IF (@sourceCodeCart != @sourceCode) BEGIN
SET @sourceCode = @sourceCodeCart
END
--Get offerno for source session
declare @offerNo char(8)
SELECT @offerNo = OFFERNO FROM ECOMLIVE.dbo.SOURCES WITH (NOLOCK)
WHERE SOURCE = @sourceCode
IF (@useCustType = 0) BEGIN
SELECT @customerType = ''
END
--Replace with huge conditional
if @sourceRef != '' begin
--Get the source ref
--declare @sourceCode char(16)
SELECT @sourceCode = SOURCE FROM ECOMLIVE.dbo.SOURCEXREF WHERE REFSOURCE = @sourceRef
SELECT @offerNo = OFFERNO FROM ECOMLIVE.dbo.SOURCES WITH (NOLOCK)
WHERE SOURCE = @sourceCode
if @customerType != '' begin
INSERT INTO #ORDERNOS
SELECT SUBSTRING(FULLORDERNO, 1, 8) FROM ECOMLIVE.dbo.ORDERHEADER A WITH (NOLOCK), ECOMLIVE.dbo.CUSTOMERS B WITH (NOLOCK)
WHERE A.MAILDATE >= @beginDate and A.MAILDATE = @beginDate and A.MAILDATE = @beginDate and A.MAILDATE = @beginDate and A.MAILDATE <= @endDate
AND A.CUSTEDP = B.CUSTEDP
AND A.SOURCE = @sourceCode
end
end
--Build up the item count tables from the order tables
INSERT INTO #ITEMCOUNT
SELECT EDPNOS_001, SUM(ITEMQTYS_001) AS TOTAL FROM ECOMLIVE.dbo.ORDERSUBHEAD A WITH (NOLOCK), #ORDERNOS B WITH (NOLOCK) WHERE A.ORDERNO = B.ORDERNO GROUP BY EDPNOS_001 ORDER BY TOTAL DESC
INSERT INTO #ITEMCOUNT
SELECT EDPNOS_002, SUM(ITEMQTYS_002) AS TOTAL FROM ECOMLIVE.dbo.ORDERSUBHEAD A WITH (NOLOCK), #ORDERNOS B WITH (NOLOCK) WHERE A.ORDERNO = B.ORDERNO GROUP BY EDPNOS_002 ORDER BY TOTAL DESC
INSERT INTO #ITEMCOUNT
SELECT EDPNOS_003, SUM(ITEMQTYS_003) AS TOTAL FROM ECOMLIVE.dbo.ORDERSUBHEAD A WITH (NOLOCK), #ORDERNOS B WITH (NOLOCK) WHERE A.ORDERNO = B.ORDERNO GROUP BY EDPNOS_003 ORDER BY TOTAL DESC
--Get the EDP and sums
INSERT INTO #BESTSELLERS
SELECT EDP, SUM(AMT) AS TOTAL FROM #ITEMCOUNT A WITH (NOLOCK) GROUP BY EDP ORDER BY TOTAL DESC
--Replace with huge conditioanl
if @category != '' begin
if @vendorNo != '' begin
INSERT INTO #ITEMAMTS
SELECT A.EDPNO, SUBSTRING(ITEMNO, 1, CHARINDEX(' ',ITEMNO, 1)), ITEMNO, AMT, DESCRIPTION, SUBSTRING(WAREHOUSELOCS_004, 1, CHARINDEX(' ',WAREHOUSELOCS_004, 1))
FROM ECOMLIVE.dbo.ITEMMAST A WITH (NOLOCK), #BESTSELLERS B WITH (NOLOCK)
WHERE A.STATUS = 'R1'
AND A.EDPNO = B.EDP
AND A.VENDORNO != 'STA' AND A.VENDORNO != 'BRO' AND A.VENDORNO != 'ALP'
AND A.CATEGORY LIKE @category AND A.VENDORNO like @vendorNo
ORDER BY ITEMNO
end else begin
INSERT INTO #ITEMAMTS
SELECT A.EDPNO, SUBSTRING(ITEMNO, 1, CHARINDEX(' ',ITEMNO, 1)), ITEMNO, AMT, DESCRIPTION, SUBSTRING(WAREHOUSELOCS_004, 1, CHARINDEX(' ',WAREHOUSELOCS_004, 1))
FROM ECOMLIVE.dbo.ITEMMAST A WITH (NOLOCK), #BESTSELLERS B WITH (NOLOCK)
WHERE A.STATUS = 'R1'
AND A.EDPNO = B.EDP
AND A.VENDORNO != 'STA' AND A.VENDORNO != 'BRO' AND A.VENDORNO != 'ALP'
AND A.CATEGORY LIKE @category
ORDER BY ITEMNO
end
end else begin
if @vendorNo!= '' begin
INSERT INTO #ITEMAMTS
SELECT A.EDPNO, SUBSTRING(ITEMNO, 1, CHARINDEX(' ',ITEMNO, 1)), ITEMNO, AMT, DESCRIPTION, SUBSTRING(WAREHOUSELOCS_004, 1, CHARINDEX(' ',WAREHOUSELOCS_004, 1))
FROM ECOMLIVE.dbo.ITEMMAST A WITH (NOLOCK), #BESTSELLERS B WITH (NOLOCK)
WHERE A.STATUS = 'R1'
AND A.EDPNO = B.EDP
AND A.VENDORNO != 'STA' AND A.VENDORNO != 'BRO' AND A.VENDORNO != 'ALP'
AND A.VENDORNO like @vendorNo
ORDER BY ITEMNO
end else begin
INSERT INTO #ITEMAMTS
SELECT A.EDPNO, SUBSTRING(ITEMNO, 1, CHARINDEX(' ',ITEMNO, 1)), ITEMNO, AMT, DESCRIPTION, SUBSTRING(WAREHOUSELOCS_004, 1, CHARINDEX(' ',WAREHOUSELOCS_004, 1))
FROM ECOMLIVE.dbo.ITEMMAST A WITH (NOLOCK), #BESTSELLERS B WITH (NOLOCK)
WHERE A.STATUS = 'R1'
AND A.EDPNO = B.EDP
AND A.VENDORNO != 'STA' AND A.VENDORNO != 'BRO' AND A.VENDORNO != 'ALP'
ORDER BY ITEMNO
end
end
--Get the refined items with price
--get parent unique parent items
SELECT C.EDPNO, SUBSTRING(D.ITEMNO, 1, CHARINDEX(' ',D.ITEMNO, 1)) AS STYLEID, D.ITEMNO, B.DESCRIPTION, SUBSTRING(SUBSTRING(D.WAREHOUSELOCS_004, 1, CHARINDEX(' ',D.WAREHOUSELOCS_004, 1)), 1, 8) AS ITEMIMAGE, CAST((D.PRICE / 100) AS DECIMAL(8,2)) AS MASTPRICE, CAST((F.UNITPRICE / 100) AS DECIMAL(8,2)) AS OFFERPRICE, SUM(AMT) AS AMT
FROM #ITEMAMTS A WITH (NOLOCK), ECOMLIVE.dbo.STYLEHEADER B WITH (NOLOCK), ECOMLIVE.dbo.STYLEINDEX C WITH (NOLOCK), ECOMLIVE.dbo.ITEMMAST D WITH (NOLOCK), ECOMLIVE.dbo.OFFERITEMS F WITH (NOLOCK)
WHERE B.STYLE = A.ITEMNO
AND C.STYLEID = LEFT(A.ITEMNO + ' ', 12) + LEFT(@offerNo + ' ', 8)
AND C.EDPNO = (SELECT MIN(EDPNO) FROM ECOMLIVE.dbo.STYLEINDEX E WITH (NOLOCK) WHERE E.STYLEID = LEFT(A.ITEMNO + ' ', 12) + LEFT(@offerNo + ' ', 8))
AND D.EDPNO = C.EDPNO
AND F.OFFERITEM = @offerNo + RIGHT('00000000' + CONVERT(varchar(8), C.EDPNO), 8)
GROUP BY C.EDPNO, D.ITEMNO, B.DESCRIPTION, D.WAREHOUSELOCS_004, D.PRICE, F.UNITPRICE
ORDER BY AMT DESC
DROP TABLE #ORDERNOS
DROP TABLE #ITEMAMTS
DROP TABLE #BESTSELLERS
DROP TABLE #ITEMCOUNT
GO
September 2, 2005 at 11:51 am
This is where normalization would be your friend, but I guess that this is out of the question. I'm afraid I can't help you much with this.
September 2, 2005 at 1:19 pm
Thank you for your time. I agree that normalization would be key here but unfortunately is not an option. I won't name names but the database that this procedure attempts to work with belongs to our multichannel retailing system that is an established, major player in it's respective market. Seeing the datalayer of this system makes me ill. Knowing that large companies are selling software like this for outrageous costs makes me sick. If normalizing the database is not an option what would you do differently to speed up this stored procedure. I'm not asking for anything specific just best practices.
Thanks,
Paul
September 2, 2005 at 1:24 pm
Hard to say... the process just seems LOONNNNNNNGGGG. And it's not helped by the denormalisation. The best guess I can give you is to try to optimize your queries as much as possible, this is probabely the best thing you can do now.
Another thing that could help a little would be to use union all and do a single insert instead of 3 per set of columns :
Insert into table (col, list)
Select col, col2 from table
union all
Select col2, col22 from table
union all
Select col3, col32 from table
Nothing else comes to mind ATM.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply