February 21, 2007 at 2:49 pm
I have an application that run a moderately complex select, with 4 UNIONS, and a correlated subquery in the last union. (I can post specifics if anyone wants)
The problem is that I get a big difference in performance when I run the stored procedure (either in a SQL window or via the application) versus running the code outside of the SP.
Running the SP can take up to 3 minutes, while running the code separately takes 20-30 seconds. The application is timing out when it has a larger than usual result set, and causing grumpy users. What might explain the difference in execution time between the 2 methods ? I would think the SP would be faster than a query, not the other way around. TIA
February 21, 2007 at 3:35 pm
UNION is the slowest operation for combining data because it performs implict distinct to eliminate duplicates, subquery is the same as INNER JOIN the fastest. If you don't want to eliminate duplicates use UNION ALL. Run your code in QA click on show execution cost both times and also run both through profiler. Make sure your data type is UNION compatible so SQL Server is not doing type conversion and the query processor is not adding cost to the four UNION operations.
Kind regards,
Gift Peddie
February 22, 2007 at 8:23 am
Without seeing the code, I would bet that either 1) you are hard-coding a variable or two in your where clause in query analyzer which allows the query optimizer to pick the best plan without issue but the sproc to have to 'guess' at the best plan or 2) you are calling the sproc with input parameters that vary, but the initially cached plan is still used. The second can lead to extraordinary performance differences for some queries because a query that returns few rows will pick nested loop joins and then a subsequent call that would return half of all rows would still use nested loops which would be orders of magnitude less efficient than hash/merge joins.
One way around these issues is to use the RECOMPILE option on the sproc to cause the query optimizer to create a new query plan with each call.
I second the previous suggestion about UNION ALL as well. If you know there aren't duplicated information, use UNION ALL vice UNION.
Also, does running the SP take 3 mins after every exectution, or just the first?
You could also consider forcing the join order and join type, but you really have to know what you are doing to go down that road.
One last thing. Any time you ask for advice on a performance issue, you should always include the entire code as well as a list of number of rows in each table of the query.
SQLGuru
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 22, 2007 at 8:53 am
One quick test you can try in relation to the first section of Kevin's response is to DECLARE new variables within the body of the stored procedure matching the input parameters, assign the input parameters to the new variables, and use the new variables throughout the procedure in place of the input parameters. It seems strange but I have often seen improved performance from stored procedures using this hack. Try this first without the recompile hint as recompile adds just a bit of overhead prior to execution every time.
maddog
February 22, 2007 at 10:10 am
Thanks for the replies !! You guys were spot on, even though you didn't see the code ! The recompile did not seem to help, not did the UNION ALL. Hardcoding the parameters (date range) did help, but I can't do that in production. The date entered is almost always just today's date, but the SP times out if the result for a particular day is bigger than normal.
Here's the code in all it's "glory". The tables sizes are:
CREATE PROCEDURE [dbo].[tp_cs_get_cc_declines]
AS DATETIME
CONVERT(char(10),ARPCCT_M.ORD_CTG) AS 'CTRLGRPID', -- Control Group ID
CONVERT(char(10), ARPCCT_M.CTG_DTE, 101) AS 'CTRLGRPDATE', -- Control Group Date
CONVERT(char(15),PROORD_M.CTM_NBR) AS 'CUSTNBR', -- Customer Number
.ORD_NUM AS 'ORDERNBR', -- Order Number
RIGHT(RTRIM(LTRIM(ARPCCT_M.CARD_NBR)), 4) AS 'CARDNBR', -- Card Number
(ARPCCT_M.CARD_MM + '/20' + ARPCCT_M.CARD_YY) AS 'EXPIRE', -- Card Expiration
'PRO' AS 'ORDERTYPE',
('$' + CONVERT(VARCHAR(20), CONVERT(DECIMAL(18,2), ARPCCT_M.CRD_AMT)) + 'CR')
ELSE
('$' + CONVERT(VARCHAR(20), CONVERT(DECIMAL(18,2), ARPCCT_M.CRD_AMT)))
END AS 'CHARGEAMT' -- Charge Amount
INNER JOIN PROORD_M WITH (NOLOCK) ON PROORD_M.ORD_NUM = ARPCCT_M.ORD_NUM
WHERE
.LST_STA <> 'SUCCESS' AND
.LST_DTE >= @STARTDATE AND
.LST_DTE <= @ENDDATE
CONVERT(char(10), ARPCCT_M.LST_DTE, 101) AS 'TRANSDATE', -- Date of transaction
CONVERT(char(10),ARPCCT_M.ORD_CTG) AS 'CTRLGRPID', -- Control Group ID
CONVERT(char(10), ARPCCT_M.CTG_DTE, 101) AS 'CTRLGRPDATE', -- Control Group Date
CONVERT(char(15),CIRHDR_M.CTM_NBR) AS 'CUSTNBR', -- Customer Number
.ORD_NUM AS 'ORDERNBR', -- Order Number
RIGHT(RTRIM(LTRIM(ARPCCT_M.CARD_NBR)), 4) AS 'CARDNBR', -- Card Number
(ARPCCT_M.CARD_MM + '/20' + ARPCCT_M.CARD_YY) AS 'EXPIRE', -- Card Expiration
'CIR' AS 'ORDERTYPE',
('$' + CONVERT(VARCHAR(20), CONVERT(DECIMAL(18,2), ARPCCT_M.CRD_AMT)) + 'CR')
ELSE
('$' + CONVERT(VARCHAR(20), CONVERT(DECIMAL(18,2), ARPCCT_M.CRD_AMT)))
END AS 'CHARGEAMT' -- Charge Amount
ARPCCT_M WITH (NOLOCK)
INNER JOIN CIRHDR_M WITH (NOLOCK) ON CIRHDR_M.ORD_NBR = ARPCCT_M.ORD_NUM
.CCT_TYP = 'AUTH' AND
.LST_STA <> 'SUCCESS' AND
.LST_DTE >= @STARTDATE AND
.LST_DTE <= @ENDDATE
CONVERT(char(10), ARPCCT_M.LST_DTE, 101) AS 'TRANSDATE', -- Date of transaction
CONVERT(char(10),ARPCCT_M.ORD_CTG) AS 'CTRLGRPID', -- Control Group ID
CONVERT(char(10), ARPCCT_M.CTG_DTE, 101) AS 'CTRLGRPDATE', -- Control Group Date
CONVERT(char(15),ARPDOC_M.CTM_NBR) AS 'CUSTNBR', -- Customer Number
.ORD_NUM2 AS 'ORDERNBR', -- Order Number
RIGHT(RTRIM(LTRIM(ARPCCT_M.CARD_NBR)), 4) AS 'CARDNBR', -- Card Number
(ARPCCT_M.CARD_MM + '/20' + ARPCCT_M.CARD_YY) AS 'EXPIRE', -- Card Expiration
.SRC_REF
ELSE
.SRC_DTA3
END AS 'ORDERTYPE',
('$' + CONVERT(VARCHAR(20), CONVERT(DECIMAL(18,2), ARPCCT_M.CRD_AMT)) + 'CR')
ELSE
('$' + CONVERT(VARCHAR(20), CONVERT(DECIMAL(18,2), ARPCCT_M.CRD_AMT)))
END AS 'CHARGEAMT' -- Charge Amount
ARPCCT_M WITH (NOLOCK)
INNER JOIN ARPDOC_M WITH (NOLOCK) ON
.CTG_ORD = ARPCCT_M.ORD_NUM2
.LST_STA <> 'SUCCESS' AND
.LST_DTE >= @STARTDATE AND
.LST_DTE <= @ENDDATE
--R4
CONVERT(char(10), ARPCCT_M.LST_DTE, 101) AS 'TRANSDATE', -- Date of transaction
CONVERT(char(10),ARPCCT_M.ORD_CTG) AS 'CTRLGRPID', -- Control Group ID
CONVERT(char(10), ARPCCT_M.CTG_DTE, 101) AS 'CTRLGRPDATE', -- Control Group Date
CONVERT(char(15),ARPDOC_M.CTM_NBR) AS 'CUSTNBR', -- Customer Number
.ORD_NUM2 AS 'ORDERNBR', -- Order Number
RIGHT(RTRIM(LTRIM(ARPCCT_M.CARD_NBR)), 4) AS 'CARDNBR', -- Card Number
(ARPCCT_M.CARD_MM + '/20' + ARPCCT_M.CARD_YY) AS 'EXPIRE', -- Card Expiration
.SRC_REF
ELSE
.SRC_DTA3
END AS 'ORDERTYPE',
('$' + CONVERT(VARCHAR(20), CONVERT(DECIMAL(18,2), ARPCCT_M.CRD_AMT)) + 'CR')
ELSE
('$' + CONVERT(VARCHAR(20), CONVERT(DECIMAL(18,2), ARPCCT_M.CRD_AMT)))
END AS 'CHARGEAMT' -- Charge Amount
ARPCCT_M WITH (NOLOCK)
INNER JOIN ARPDOC_M WITH (NOLOCK) ON ARPDOC_M.CTG_ORD = ARPCCT_M.ORD_NUM
.LST_STA <> 'SUCCESS' AND
.LST_DTE >= @STARTDATE AND
.LST_DTE <= @ENDDATE AND
.ORD_NUM2
NOT IN (SELECT
.ORD_NUM2
FROM ARPCCT_M WITH (NOLOCK)
INNER JOIN ARPDOC_M WITH (NOLOCK) ON
.CTG_ORD = ARPCCT_M.ORD_NUM2
WHERE
.LST_STA <> 'SUCCESS' AND
.LST_DTE >= @STARTDATE AND
.LST_DTE <= @ENDDATE)
BY
.LST_DTE,
.ORD_CTG,
.CTG_DTE,
.ORD_NUM,
.ORD_NUM2,
.CTM_NBR,
.CARD_NBR,
.CARD_MM,
.CARD_YY,
.CCT_TRN,
.CRD_AMT,
.SRC_REF,
.SRC_DTA3
BY TRANSDATE ASC,CTRLGRPID ASC,CUSTNBR ASC,CARDNBR ASC, CHARGEAMT ASC
February 22, 2007 at 10:26 am
I didn't see that you tried the parameter sniffing workaround in your code to reassign the input parameters to declared variables but you may have listed you original code. I haven't inspected closely the code yet, but another tack you can try 'for fun' would be to create a #temp table with indexes, then instead of using the UNION statements insert each set of SELECT code into the #Temp table, and lastly spit out the contents of the #Temp table as the final result set.
BTW, does your 'homebrew' online ID signify that you are a brewer? Just finished off the last of a full-mash IPA I brewed up last November. Been brewing myself about 12 years and it makes satisfying pursuit outside of work.
maddog
February 22, 2007 at 10:50 am
Considering you started with the procedure run time being significantly longer than the same code without the procedure, you should really look at the execution plans.
If the performance is different, but the T-SQL is the same, you should be able to get the performance to be the same by adjusting the T-SQL until you end up with the same execution plan.
I would start by running both and comparing the execution plans. You are already using query hints so I assume you are familiar with them.
February 22, 2007 at 11:29 am
it is almost certainly query caching that is getting you in trouble here.
do all your tables have an index on LST_DTE? do you have indexes on the columns joined on?
what is the average number of rows for each day? If it is a small number relative to the total table count (<5% say), how about this. put in query hint OPTION (LOOP JOIN) to force a nested loop join for each table. This will get you into BIG trouble though if you run a date range that covers a month for example. If the option loop join works for a single day's execution, perhaps you could test out and find out where the efficiency point is crossed in letting the query optimizer to hash/merge joins. set up two complete queries in the sproc. the option loop join gets run for single date (or a few days) spread, and explicit hash/merge-match one gets called for large date spreads.
SQLGuru
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 22, 2007 at 1:35 pm
Maddogs .... Yes, I'm a homebrewer, although I haven't done much lately. We have a good homebrew club here in SW Connecticut. I switched to full mash & good yeast a while ago & never looked back ... Nothing like a nice hoppy home made IPA ... Mmmmmmmmm
Thanks for all the SQL help everyone. I got sidetracked by other issues and haven't got to any of the newer suggestions yet.
February 22, 2007 at 2:58 pm
>> I got sidetracked by other issues and haven't got to any of the newer suggestions yet.
WHAT?!?! You mean you don't get to work on one task to completion?!? OUTRAGEOUS!! :-)))
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply