Slow performance of stored procedure.
-
The company I work for uses a backend management system that handles all of the accounting, shipping/fulfillment, CRM, and Web Channel functions. I develop the websites that utilize the API provided by this software. The software uses a SQL Server database.
I had a need to write my own procedures to get certain data from the database that the API lacked. I have a couple of questions regarding how I am connecting to the database and how this management software connects to the database in the hopes that someone may be able to shed light on the approach that the management software is taking versus the approach I am taking to retrieve data from the db.
This management software runs on it's own application server and uses "Listeners" which are executables that continuously run listening for requests from other parts of the system. When the software executes it's sprocs its very fast and it does it through these listeners. The software executes sprocs contained within the same database as the data it retrieves. When I execute my sprocs I am connecting to the db with the sprocs (which is on the same sql server instance but on a different DB to keep the production DB clean), executing the sproc and disconnecting.
1. Would it be wise to assume that the listeners are keeping an open connection to the database and therefore is the reason why its queries run so fast?
2. Are my sprocs running so slow because they reside on a different db than the one it's retrieving data from?
2. If #2 isn't what is impacting the performance then if I wanted to compete with these listeners, should I create a listener? If so, does anyone have any experience with the internal workings of a listener and perhaps what it actually accomplishes and how?
I just found sqlservercentral.com and wanted to see if I could pick yer brains on some of the nagging questions I've been having.
Thanks,
Paul
-
1 - It's possible but that shouldn't cause any noticable performance degradation (unless you have 1000s of open connections).
2 - Maybe, but we'd have to see the execution plan to make sure that the code is as optimized as possible
3 - I really think it's more 2 than 1.
Can you run that and paste back the results and the code of the proc?
SET SHOWPLAN_TEXT ON
GO
--exec code of the proc here
GO
SET SHOWPLAN_TEXT OFF
GO
-
I did the above and got an error message: Invalid object name '#ORDERNOS'.
I am using temp tables in this proc.
--Paul
-
How do you usually call the proc?? Make sure you have all the code needed to make it run.
-
This is what I tried to run:
------------------------------------
SET SHOWPLAN_TEXT ON
GO
DECLARE @rc int
DECLARE @beginDate varchar(8)
DECLARE @endDate varchar(8)
DECLARE @sourceCode varchar(16)
DECLARE @sourceRef varchar(16)
DECLARE @cartid varchar(20)
DECLARE @customerType char(2)
DECLARE @division char(2)
DECLARE @category varchar(4)
DECLARE @vendorNo varchar(10)
DECLARE @useCustType int
-- Set parameter values
SET @beginDate = '20040824'
SET @endDate = '20050824'
SET @sourceCode = 'IT3503WWWEBORDER'
SET @sourceRef = ''
SET @cartid = '2608082414120946'
SET @customerType = '04'
SET @division = '00'
SET @category = ''
SET @vendorNo = ''
SET @useCustType = 0
EXEC @rc = [WWSPORT].[dbo].[BestSellingItemsWebTransFull] @beginDate, @endDate, @sourceCode, @sourceRef, @cartid, @customerType, @division, @category, @vendorNo, @useCustType
GO
SET SHOWPLAN_TEXT OFF
GO
-
If you run ONLY the proc without the set... on/off, does it work?
-
It does work if I run the proc without the set on/off.
-
Can you copy the code of the proc in QA and execute it directly...
If you get the same error I'm afraid you'll have to use a table variable instead of a temp table.
-
I tried that as well and I get the same error with the set on/off in there. I've never used table variables so I'll need to check into that.
Thanks,
Paul
-
That's where you're getting the problem >>
SET SHOWPLAN_TEXT ON
GO
CREATE TABLE #test (a int not null)
select * from #test
drop table #test
GO
SET SHOWPLAN_TEXT OFF
this option seems to only fetch the plan without actually executing any DDL. Maybe you could run the code that creates/drop the temp table outside the set options.
-
ok, that worked! Here are the results once I ran it:
---------------------------
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @beginDate varchar(8)
DECLARE @endDate varchar(8)
DECLARE @sourceCode varchar(16)
DECLARE @sourceRef varchar(16)
DECLARE @cartid varchar(20)
DECLARE @customerType char(2)
DECLARE @division char(2)
DECLARE @category varchar(4)
DECLARE @vend
SET @endDate = '20050824'
SET @sourceCode = 'IT3503WWWEBORDER'
SET @sourceRef = ''
SET @cartid = '2608082414120946'
SET @customerType = '04'
SET @division = '00'
SET @category = ''
SET @vendorNo = ''
SET @useCustType = 0
----------
DECLARE @sourceCodeCart varchar(16)
--Get source code from cart
SELECT @sourceCodeCart = SOURCE FROM ECOMLIVE.dbo.WEBCART WITH (NOLOCK)
WHERE CARTID = @customerType + @division + @cartid
(11 row(s) affected)
StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE
[Expr1002]=Convert([WEBCART].[SOURCE])))|--Clustered Index Seek(OBJECT
[ECOMLIVE].[dbo].[WEBCART].[ISAM_CARTID090001]), SEEK[WEBCART].[CARTID]=[@customerType]+[@division]+[@cartid]) ORDERED FORWARD)(2 row(s) affected)
StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
(3 row(s) affected)
StmtText
-----------------------------------------------------------------------------------------------------------------------------------
|--Bookmark Lookup(BOOKMARK
[Bmk1000]), OBJECT[ECOMLIVE].[dbo].[SOURCES]))|--Index Seek(OBJECT
[ECOMLIVE].[dbo].[SOURCES].[SOURCE_020056]), SEEK[SOURCES].[SOURCE]=[@sourceCode]) ORDERED FORWARD)(2 row(s) affected)
StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------
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
(4 row(s) affected)
StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE
[Expr1002]=Convert([SOURCEXREF].[SOURCE])))|--Bookmark Lookup(BOOKMARK
[Bmk1000]), OBJECT[ECOMLIVE].[dbo].[SOURCEXREF]))|--Index Seek(OBJECT
[ECOMLIVE].[dbo].[SOURCEXREF].[REFSOURCE_020054]), SEEK[SOURCEXREF].[REFSOURCE]=[@sourceRef]) ORDERED FORWARD)(3 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------
SELECT @offerNo = OFFERNO FROM ECOMLIVE.dbo.SOURCES WITH (NOLOCK)
WHERE SOURCE = @sourceCode
(1 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------
|--Bookmark Lookup(BOOKMARK
[Bmk1000]), OBJECT[ECOMLIVE].[dbo].[SOURCES]))|--Index Seek(OBJECT
[ECOMLIVE].[dbo].[SOURCES].[SOURCE_020056]), SEEK[SOURCES].[SOURCE]=[@sourceCode]) ORDERED FORWARD)(2 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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 <= @endDate
and B.CUSTTYPE = @customerTy
(2 row(s) affected)
StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Table Insert(OBJECT
[tempdb].[dbo].[#ORDERNOS___________________________________________________________________________________________________________00000000331B]), SET[#ORDERNOS].[ORDERNO]=[Expr1002]))|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE
[Expr1002]=Convert(substring([A].[FULLORDERNO], 1, 8))))|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, OUTER REFERENCES
[A].[CUSTEDP]) WITH PREFETCH)|--Clustered Index Scan(OBJECT
[ECOMLIVE].[dbo].[ORDERHEADER].[PK_ORDERHEADER] AS [A]), WHERE([A].[SOURCE]=[@sourceCode] AND [A].[MAILDATE]>=[@beginDate]) AND [A].[MAILDATE]<=[@endDate]))|--Clustered Index Seek(OBJECT
[ECOMLIVE].[dbo].[CUSTOMERS].[ISAM_CUSTEDP010016] AS ), SEEK.[CUSTEDP]=[A].[CUSTEDP]), WHERE.[CUSTTYPE]=[@customerType]) ORDERED FORWARD)(7 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
end else 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 <= @endDate
AND A.CUSTEDP = B.C
(1 row(s) affected)
StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Table Insert(OBJECT
[tempdb].[dbo].[#ORDERNOS___________________________________________________________________________________________________________00000000331B]), SET[#ORDERNOS].[ORDERNO]=[Expr1002]))|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE
[Expr1002]=Convert(substring([A].[FULLORDERNO], 1, 8))))|--Parallelism(Gather Streams)
|--Clustered Index Scan(OBJECT
[ECOMLIVE].[dbo].[ORDERHEADER].[PK_ORDERHEADER] AS [A]), WHERE([A].[SOURCE]=[@sourceCode] AND [A].[MAILDATE]>=[@beginDate]) AND [A].[MAILDATE]= @beginDate and A.MAILDATE <= @endDateand B.CUSTTYPE = @customerType
AND A.CUSTEDP = B.CUST
(2 row(s) affected)
StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Table Insert(OBJECT
[tempdb].[dbo].[#ORDERNOS___________________________________________________________________________________________________________00000000331B]), SET[#ORDERNOS].[ORDERNO]=[Expr1002]))|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE
[Expr1002]=Convert(substring([A].[FULLORDERNO], 1, 8))))|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, OUTER REFERENCES
[A].[CUSTEDP]) WITH PREFETCH)|--Clustered Index Scan(OBJECT
[ECOMLIVE].[dbo].[ORDERHEADER].[PK_ORDERHEADER] AS [A]), WHERE([A].[SOURCE]=[@sourceCode] AND [A].[MAILDATE]>=[@beginDate]) AND [A].[MAILDATE]<=[@endDate]))|--Clustered Index Seek(OBJECT
[ECOMLIVE].[dbo].[CUSTOMERS].[ISAM_CUSTEDP010016] AS ), SEEK.[CUSTEDP]=[A].[CUSTEDP]), WHERE.[CUSTTYPE]=[@customerType]) ORDERED FORWARD)(7 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
end else begin
INSERT INTO #ORDERNOS
SELECT SUBSTRING(FULLORDERNO, 1, 8) FROM ECOMLIVE.dbo.ORDERHEADER A, ECOMLIVE.dbo.CUSTOMERS B
WHERE A.MAILDATE >= @beginDate and A.MAILDATE <= @endDate
AND A.CUSTEDP = B.CUSTEDP
AND A.SOURCE = @
(1 row(s) affected)
StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Table Insert(OBJECT
[tempdb].[dbo].[#ORDERNOS___________________________________________________________________________________________________________00000000331B]), SET[#ORDERNOS].[ORDERNO]=[Expr1002]))|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE
[Expr1002]=Convert(substring([A].[FULLORDERNO], 1, 8))))|--Parallelism(Gather Streams)
|--Clustered Index Scan(OBJECT
[ECOMLIVE].[dbo].[ORDERHEADER].[PK_ORDERHEADER] AS [A]), WHERE([A].[SOURCE]=[@sourceCode] AND [A].[MAILDATE]>=[@beginDate]) AND [A].[MAILDATE]<=[@endDate]))(5 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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 EDPNO
(1 row(s) affected)
StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Table Insert(OBJECT
[tempdb].[dbo].[#ITEMCOUNT__________________________________________________________________________________________________________00000000331B]), SET[#ITEMCOUNT].[AMT]=[Expr1004], [#ITEMCOUNT].[EDP]=[Expr1003]))|--Top(ROWCOUNT est 0)
|--Sort(ORDER BY
[Expr1004] DESC))|--Compute Scalar(DEFINE
[Expr1003]=Convert([A].[EDPNOS_001]), [Expr1004]=Convert([Expr1002])))|--Stream Aggregate(GROUP BY
[A].[EDPNOS_001]) DEFINE[Expr1002]=SUM([A].[ITEMQTYS_001])))|--Sort(ORDER BY
[A].[EDPNOS_001] ASC))|--Bookmark Lookup(BOOKMARK
[Bmk1000]), OBJECT[ECOMLIVE].[dbo].[ORDERSUBHEAD] AS [A]))|--Nested Loops(Inner Join, OUTER REFERENCES
.[ORDERNO]))|--Table Scan(OBJECT
[tempdb].[dbo].[#ORDERNOS___________________________________________________________________________________________________________00000000331B] AS ))|--Index Seek(OBJECT
[ECOMLIVE].[dbo].[ORDERSUBHEAD].[IX_ORDERSUBHEAD_1] AS [A]), SEEK[A].[ORDERNO]=.[ORDERNO]) ORDERED FORWARD)(10 row(s) affected)
StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
(1 row(s) affected)
StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Table Insert(OBJECT
[tempdb].[dbo].[#ITEMCOUNT__________________________________________________________________________________________________________00000000331B]), SET[#ITEMCOUNT].[AMT]=[Expr1004], [#ITEMCOUNT].[EDP]=[Expr1003]))|--Top(ROWCOUNT est 0)
|--Sort(ORDER BY
[Expr1004] DESC))|--Compute Scalar(DEFINE
[Expr1003]=Convert([A].[EDPNOS_002]), [Expr1004]=Convert([Expr1002])))|--Stream Aggregate(GROUP BY
[A].[EDPNOS_002]) DEFINE[Expr1002]=SUM([A].[ITEMQTYS_002])))|--Sort(ORDER BY
[A].[EDPNOS_002] ASC))|--Bookmark Lookup(BOOKMARK
[Bmk1000]), OBJECT[ECOMLIVE].[dbo].[ORDERSUBHEAD] AS [A]))|--Nested Loops(Inner Join, OUTER REFERENCES
.[ORDERNO]))|--Table Scan(OBJECT
[tempdb].[dbo].[#ORDERNOS___________________________________________________________________________________________________________00000000331B] AS ))|--Index Seek(OBJECT
[ECOMLIVE].[dbo].[ORDERSUBHEAD].[IX_ORDERSUBHEAD_1] AS [A]), SEEK[A].[ORDERNO]=.[ORDERNO]) ORDERED FORWARD)(10 row(s) affected)
StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
(1 row(s) affected)
StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Table Insert(OBJECT
[tempdb].[dbo].[#ITEMCOUNT__________________________________________________________________________________________________________00000000331B]), SET[#ITEMCOUNT].[AMT]=[Expr1004], [#ITEMCOUNT].[EDP]=[Expr1003]))|--Top(ROWCOUNT est 0)
|--Sort(ORDER BY
[Expr1004] DESC))|--Compute Scalar(DEFINE
[Expr1003]=Convert([A].[EDPNOS_003]), [Expr1004]=Convert([Expr1002])))|--Stream Aggregate(GROUP BY
[A].[EDPNOS_003]) DEFINE[Expr1002]=SUM([A].[ITEMQTYS_003])))|--Sort(ORDER BY
[A].[EDPNOS_003] ASC))|--Bookmark Lookup(BOOKMARK
[Bmk1000]), OBJECT[ECOMLIVE].[dbo].[ORDERSUBHEAD] AS [A]))|--Nested Loops(Inner Join, OUTER REFERENCES
.[ORDERNO]))|--Table Scan(OBJECT
[tempdb].[dbo].[#ORDERNOS___________________________________________________________________________________________________________00000000331B] AS ))|--Index Seek(OBJECT
[ECOMLIVE].[dbo].[ORDERSUBHEAD].[IX_ORDERSUBHEAD_1] AS [A]), SEEK[A].[ORDERNO]=.[ORDERNO]) ORDERED FORWARD)(10 row(s) affected)
StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------------------
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
(1 row(s) affected)
StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Table Insert(OBJECT
[tempdb].[dbo].[#BESTSELLERS________________________________________________________________________________________________________00000000331B]), SET[#BESTSELLERS].[AMT]=[Expr1001], [#BESTSELLERS].[EDP]=[A].[EDP]))|--Top(ROWCOUNT est 0)
|--Sort(ORDER BY
[Expr1001] DESC))|--Compute Scalar(DEFINE
[Expr1001]=If ([Expr1010]=0) then NULL else [Expr1011]))|--Stream Aggregate(GROUP BY
[A].[EDP]) DEFINE[Expr1010]=COUNT_BIG([A].[AMT]), [Expr1011]=SUM([A].[AMT])))|--Sort(ORDER BY
[A].[EDP] ASC))|--Table Scan(OBJECT
[tempdb].[dbo].[#ITEMCOUNT__________________________________________________________________________________________________________00000000331B] AS [A]))(7 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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), #BESTSELLE
(3 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Table Insert(OBJECT
[tempdb].[dbo].[#ITEMAMTS___________________________________________________________________________________________________________00000000331B]), SET[#ITEMAMTS].[WAREHOUSELOCS_004]=[Expr1006], [#ITEMAMTS].[AMT]=.[|--Top(ROWCOUNT est 0)
|--Sort(ORDER BY
[Expr1004] ASC))|--Compute Scalar(DEFINE
[Expr1002]=Convert([A].[EDPNO]), [Expr1003]=substring([A].[ITEMNO], 1, charindex(' ', [A].[ITEMNO], 1)), [Expr1004]=Convert([A].[ITEMNO]), [Expr1005]=Convert([A].[DESCRIPTION]), [Expr1006]=Convert(substr|--Nested Loops(Inner Join, OUTER REFERENCES
.[EDP]))|--Table Scan(OBJECT
[tempdb].[dbo].[#BESTSELLERS________________________________________________________________________________________________________00000000331B] AS ))|--Clustered Index Seek(OBJECT
[ECOMLIVE].[dbo].[ITEMMAST].[ISAM_EDPNO020028] AS [A]), SEEK[A].[EDPNO]=Convert(.[EDP])), WHERE(((([A].[STATUS]='R1' AND [A].[VENDORNO]'STA') AND [A].[VENDORNO]'BRO') AND [A].(7 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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), #
(1 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Table Insert(OBJECT
[tempdb].[dbo].[#ITEMAMTS___________________________________________________________________________________________________________00000000331B]), SET[#ITEMAMTS].[WAREHOUSELOCS_004]=[Expr1006], [#ITEMAMTS].[AMT]=.[|--Top(ROWCOUNT est 0)
|--Sort(ORDER BY
[Expr1004] ASC))|--Compute Scalar(DEFINE
[Expr1002]=Convert([A].[EDPNO]), [Expr1003]=substring([A].[ITEMNO], 1, charindex(' ', [A].[ITEMNO], 1)), [Expr1004]=Convert([A].[ITEMNO]), [Expr1005]=Convert([A].[DESCRIPTION]), [Expr1006]=Convert(substr|--Nested Loops(Inner Join, OUTER REFERENCES
.[EDP]))|--Table Scan(OBJECT
[tempdb].[dbo].[#BESTSELLERS________________________________________________________________________________________________________00000000331B] AS ))|--Clustered Index Seek(OBJECT
[ECOMLIVE].[dbo].[ITEMMAST].[ISAM_EDPNO020028] AS [A]), SEEK[A].[EDPNO]=Convert(.[EDP])), WHERE((([A].[STATUS]='R1' AND [A].[VENDORNO]'STA') AND [A].[VENDORNO]'BRO') AND [A].[(7 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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), #BESTSELLE
(2 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Table Insert(OBJECT
[tempdb].[dbo].[#ITEMAMTS___________________________________________________________________________________________________________00000000331B]), SET[#ITEMAMTS].[WAREHOUSELOCS_004]=[Expr1006], [#ITEMAMTS].[AMT]=.[|--Top(ROWCOUNT est 0)
|--Sort(ORDER BY
[Expr1004] ASC))|--Compute Scalar(DEFINE
[Expr1002]=Convert([A].[EDPNO]), [Expr1003]=substring([A].[ITEMNO], 1, charindex(' ', [A].[ITEMNO], 1)), [Expr1004]=Convert([A].[ITEMNO]), [Expr1005]=Convert([A].[DESCRIPTION]), [Expr1006]=Convert(substr|--Nested Loops(Inner Join, OUTER REFERENCES
.[EDP]))|--Table Scan(OBJECT
[tempdb].[dbo].[#BESTSELLERS________________________________________________________________________________________________________00000000331B] AS ))|--Clustered Index Seek(OBJECT
[ECOMLIVE].[dbo].[ITEMMAST].[ISAM_EDPNO020028] AS [A]), SEEK[A].[EDPNO]=Convert(.[EDP])), WHERE((([A].[STATUS]='R1' AND [A].[VENDORNO]'STA') AND [A].[VENDORNO]'BRO') AND [A].[(7 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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), #
(1 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Table Insert(OBJECT
[tempdb].[dbo].[#ITEMAMTS___________________________________________________________________________________________________________00000000331B]), SET[#ITEMAMTS].[WAREHOUSELOCS_004]=[Expr1006], [#ITEMAMTS].[AMT]=.[|--Top(ROWCOUNT est 0)
|--Sort(ORDER BY
[Expr1004] ASC))|--Compute Scalar(DEFINE
[Expr1002]=Convert([A].[EDPNO]), [Expr1003]=substring([A].[ITEMNO], 1, charindex(' ', [A].[ITEMNO], 1)), [Expr1004]=Convert([A].[ITEMNO]), [Expr1005]=Convert([A].[DESCRIPTION]), [Expr1006]=Convert(substr|--Nested Loops(Inner Join, OUTER REFERENCES
.[EDP]))|--Table Scan(OBJECT
[tempdb].[dbo].[#BESTSELLERS________________________________________________________________________________________________________00000000331B] AS ))|--Clustered Index Seek(OBJECT
[ECOMLIVE].[dbo].[ITEMMAST].[ISAM_EDPNO020028] AS [A]), SEEK[A].[EDPNO]=Convert(.[EDP])), WHERE(([A].[STATUS]='R1' AND [A].[VENDORNO]'STA') AND [A].[VENDORNO]'BRO') AND [A].[V(7 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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.WAREHOU
(1 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE
[Expr1009]=substring([D].[ITEMNO], 1, charindex(' ', [D].[ITEMNO], 1)), [Expr1010]=substring(substring([D].[WAREHOUSELOCS_004], 1, charindex(' ', [D].[WAREHOUSELOCS_004], 1)), 1, 8), [Expr1011]=Convert([D].[PRICE]/100), [Expr101|--Sort(ORDER BY
[Expr1008] DESC))|--Compute Scalar(DEFINE
[Expr1008]=If ([Expr1025]=0) then NULL else [Expr1026]))|--Stream Aggregate(GROUP BY
[C].[EDPNO], .[DESCRIPTION], [F].[UNITPRICE]) DEFINE[Expr1025]=COUNT_BIG([A].[AMT]), [Expr1026]=SUM([A].[AMT]), [D].[ITEMNO]=ANY([D].[ITEMNO]), [D].[WAREHOUSELOCS_004]=ANY([D].[WAREHOUSELOCS_004]), [D].[P|--Sort(ORDER BY
[C].[EDPNO] ASC, .[DESCRIPTION] ASC, [F].[UNITPRICE] ASC))|--Bookmark Lookup(BOOKMARK
[Bmk1001]), OBJECT[ECOMLIVE].[dbo].[STYLEHEADER] AS ))|--Nested Loops(Inner Join, OUTER REFERENCES
[A].[ITEMNO]))|--Nested Loops(Inner Join, OUTER REFERENCES
[C].[EDPNO]))| |--Filter(WHERE
[C].[EDPNO]=[Expr1006]))| | |--Bookmark Lookup(BOOKMARK
[Bmk1002]), OBJECT[ECOMLIVE].[dbo].[STYLEINDEX] AS [C]))| | |--Nested Loops(Inner Join, OUTER REFERENCES
[A].[ITEMNO]))| | |--Nested Loops(Inner Join, OUTER REFERENCES
[Expr1006]))| | | |--Stream Aggregate(GROUP BY
[E].[STYLEID], [Rank1018]) DEFINE[Expr1006]=MIN([E].[EDPNO]), [A].[AMT]=ANY([A].[AMT]), [A].[ITEMNO]=ANY([A].[ITEMNO])))| | | | |--Sort(ORDER BY
[E].[STYLEID] ASC, [Rank1018] ASC))| | | | |--Bookmark Lookup(BOOKMARK
[Bmk1005]), OBJECT[ECOMLIVE].[dbo].[STYLEINDEX] AS [E]))| | | | |--Nested Loops(Inner Join, OUTER REFERENCES
[A].[ITEMNO]))| | | | |--Rank
| | | | | |--Table Scan(OBJECT
[tempdb].[dbo].[#ITEMAMTS___________________________________________________________________________________________________________00000000331B] A| | | | |--Index Seek(OBJECT
[ECOMLIVE].[dbo].[STYLEINDEX].[STYLEID_020083] AS [E]), SEEK[E].[STYLEID]=substring([A].[ITEMNO]+' ', 1, 12)+substring([@offerNo]+'| | | |--Clustered Index Seek(OBJECT
[ECOMLIVE].[dbo].[ITEMMAST].[ISAM_EDPNO020028] AS [D]), SEEK[D].[EDPNO]=[Expr1006]) ORDERED FORWARD)| | |--Index Seek(OBJECT
[ECOMLIVE].[dbo].[STYLEINDEX].[STYLEID_020083] AS [C]), SEEK[C].[STYLEID]=substring([A].[ITEMNO]+' ', 1, 12)+substring([@offerNo]+' ', 1, 8)) ORDERED FORWARD| |--Clustered Index Seek(OBJECT
[ECOMLIVE].[dbo].[OFFERITEMS].[ISAM_OFFERITEM020034] AS [F]), SEEK[F].[OFFERITEM]=[@offerNo]+right('00000000'+Convert([C].[EDPNO]), 8)) ORDERED FORWARD)|--Index Seek(OBJECT
[ECOMLIVE].[dbo].[STYLEHEADER].[ISAM_STYLE_020092] AS ), SEEK.[STYLE]=[A].[ITEMNO]) ORDERED FORWARD)(23 row(s) affected)
-
I don't have time to scan all this ATM. Fast pointer >> everytime you see a scan (table, clustered index or index scan). That can be the reason why this is runing slower than it could. NOw I don't have all the specs of the server and the tables but I would assume that this proc cannot run in less than 300-400 MS (forget nanoseconds here). This is why it runs slower than your other version of the connection.
-
Thanks for your help. Your pointers are much appreciated and your help has been great in getting me started on fixing this proc. Take it easy.
Thanks,
Paul
-
Just out of curiosity, would it be more efficient to break a huge proc into smaller stored procedures that do their respective things. This procedure does some ugly things in my opinion in order to get the data it needs and it does those things because the database design of the management system seems highly inefficient. So would it be better to have one proc that runs a bunch of smaller procs?
Thanks,
Paul
-
A bunch of smaller procs is almost never a bad idea. Can you send the code of the proc only?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply