June 30, 2016 at 8:09 am
We have performance issue
and Java guys asked me if it;s possible to
make this procedure very fast....
So far , I can only make it 2000 milliseconds...
They want it 400...
3 parameters accept pipe delimited list:
@ORDER_NUM varchar(1000) = null,
@BOR_ORDER_NUM varchar(1000) = null,
@BOR_ORDER_GROUP_ID varchar(1000) = null
To parse delimited list I use the function fnSplitString (below)
/****** Object: StoredProcedure [WOE].[getOrderDetailsForBatch] Script Date: 6/27/2016 2:47:39 PM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
ALTER PROCEDURE [WOE].[getOrderDetailsForBatch]
@RECORD_TYPE_CD varchar(50),
@ORDER_NUM varchar(1000) = null,
@BOR_ORDER_NUM varchar(1000) = null,
@BOR_ORDER_GROUP_ID varchar(1000) = null
WITH EXEC AS CALLER
AS
---####################################################################################################################################################
--- Procedure: getOrderDetailsForBatch
--- Created: 2016-06-12
/*
usage:
exec WOE.getOrderDetailsForBatch
@RECORD_TYPE_CD = 'SO',
@ORDER_NUM = 'NZ-000020|CI-000033',
@BOR_ORDER_NUM = '0001592|0002470',
@BOR_ORDER_GROUP_ID = '160222-BLA9883|160222-BLA9885'
*/
---#####################################################################################################################################################
SET NOCOUNT ON;
BEGIN
declare @RECORD_TYPE_CD_L varchar(50)
declare @ORDER_NUM_L varchar(1000)
declare @BOR_ORDER_NUM_L varchar(1000)
declare @BOR_ORDER_GROUP_ID_L varchar(1000)
set @RECORD_TYPE_CD_L = @RECORD_TYPE_CD
set @ORDER_NUM_L = @ORDER_NUM
set @BOR_ORDER_NUM_L = @BOR_ORDER_NUM
set @BOR_ORDER_GROUP_ID_L = @BOR_ORDER_GROUP_ID
if datalength(@ORDER_NUM_L) = 0 and datalength(@BOR_ORDER_NUM_L) = 0 and datalength(@BOR_ORDER_GROUP_ID_L) = 0
begin
DECLARE @msg VARCHAR(255)
SET @msg = 'One of @ORDER_NUM, @BOR_ORDER_NUM, or @BOR_ORDER_GROUP_ID is required input'
RAISERROR ( @msg, 1,16)
--RETURN -1
print @msg
return
end
--------------------------------------------------------------------------------------------------------------------
declare @lists table
(
value varchar(50),
paramname varchar(20)
)
insert into @lists(value,paramname) select *,'@ORDER_NUM' from WOE.fnSplitString(@ORDER_NUM_L,'|')
insert into @lists(value,paramname) select *,'@BOR_ORDER_NUM' from WOE.fnSplitString(@BOR_ORDER_NUM_L,'|')
insert into @lists(value,paramname) select *,'@BOR_ORDER_GROUP_ID' from WOE.fnSplitString(@BOR_ORDER_GROUP_ID_L,'|')
--------------------------------------------------------------------------------------------------------------------
SELECT
--
--OI.ORDER_NUM,
--OI.BOR_ORDER_NUM,
--OG.BOR_ORDER_GROUP_ID,
--
OG.ORDER_UID AS OG_ORDER_UID
,OG.RECORD_TYPE_CD AS OG_RECORD_TYPE_CD
,OG.EFFECTIVE_TS AS OG_EFFECTIVE_TS
,OG.ORDER_STAGE_CD AS OG_ORDER_STAGE_CD
,OG.ORDER_STRTGY_CD AS OG_ORDER_STRTGY_CD
,OG.ORDER_PRICE_PLAN_CD AS OG_ORDER_PRICE_PLAN_CD
,OG.OPTION_STRATEGY_CD AS OG_OPTION_STRATEGY_CD
,OG.ORDER_DT AS OG_ORDER_DT
,OG.BOR_USER_ID AS OG_BOR_USER_ID
,OG.BOR_CD AS OG_BOR_CD
,OG.BOR_ORDER_GROUP_ID AS OG_BOR_ORDER_GROUP_ID
,OG.WDS_PARTY_ID AS OG_WDS_PARTY_ID
,OG.VERSION_NUM AS OG_VERSION_NUM
,OI.ORDER_ITEM_UID AS OI_ORDER_ITEM_UID
,OI.RECORD_TYPE_CD AS OI_RECORD_TYPE_CD
,OI.EFFECTIVE_TS AS OI_EFFECTIVE_TS
,OI.ORDER_UID AS OI_ORDER_UID
,OI.ORDER_NUM AS OI_ORDER_NUM
,OI.USER_ID AS OI_USER_ID
,OI.ACCT_NUM AS OI_ACCT_NUM
,OI.ACCT_BOB_CD AS OI_ACCT_BOB_CD
,OI.MARKET_COUNTRY_CD AS OI_MARKET_COUNTRY_CD
,OI.SECRTY_SYMBOL AS OI_SECRTY_SYMBOL
,OI.SECRTY_TYPE_CD AS OI_SECRTY_TYPE_CD
,OI.USER_REALM_CD AS OI_USER_REALM_CD
,OI.ORDER_TYPE_CD AS OI_ORDER_TYPE_CD
,OI.ACTION_TYPE_CD AS OI_ACTION_TYPE_CD
,OI.DURATN_TYPE_CD AS OI_DURATN_TYPE_CD
,OI.ORDER_QUANTY_AMT AS OI_ORDER_QUANTY_AMT
,OI.GOOD_TILL_DT AS OI_GOOD_TILL_DT
,OI.PRICE_AMT AS OI_PRICE_AMT
,OI.STOP_LIMIT_PRICE_AMT AS OI_STOP_LIMIT_PRICE_AMT
,OI.TRAIL_STOP_TRIGER_PRICE_AMT AS OI_TRAIL_STOP_TRIGER_PRICE_AMT
,OI.DELTA_AMT AS OI_DELTA_AMT
,OI.DELTA_UNIT_TYPE_CD AS OI_DELTA_UNIT_TYPE_CD
,OI.DELTA_LIMIT_AMT AS OI_DELTA_LIMIT_AMT
,OI.REFRNC_RSRVTN AS OI_REFRNC_RSRVTN
,OI.NEXT_DAY_ORDER_IND AS OI_NEXT_DAY_ORDER_IND
,OI.SOLICITED_TRADE_IND AS OI_SOLICITED_TRADE_IND
,OI.BEHALF_CD AS OI_BEHALF_CD
,OI.TRADER_BOB_CD AS OI_TRADER_BOB_CD
,OI.TO_CAD_EXCHNG_RATE AS OI_TO_CAD_EXCHNG_RATE
,OI.FLOOR_COMENT_TXT AS OI_FLOOR_COMENT_TXT
,OI.DISCRETIONARY_PRICE_AMT AS OI_DISCRETIONARY_PRICE_AMT
,OI.FLAT_COMISN_AMT AS OI_FLAT_COMISN_AMT
,OI.PER_SHARE_COMISN_AMT AS OI_PER_SHARE_COMISN_AMT
,OI.SCHED_PCT AS OI_SCHED_PCT
,OI.CALCD_COMISN_EST_AMT AS OI_CALCD_COMISN_EST_AMT
,OI.REAL_TIME_PRICE_AMT AS OI_REAL_TIME_PRICE_AMT
,OI.API_CONSMR_ID AS OI_API_CONSMR_ID
,OI.ORDER_ITEM_STAT_CD AS OI_ORDER_ITEM_STAT_CD
,OI.MF_TRADE_AMT AS OI_MF_TRADE_AMT
,OI.SWITCH_TO_SECRTY_SYMBOL AS OI_SWITCH_TO_SECRTY_SYMBOL
,OI.MF_DIVID_OPTION_CD AS OI_MF_DIVID_OPTION_CD
,OI.SCHED_TYPE_CD AS OI_SCHED_TYPE_CD
,OI.SCHED_NM AS OI_SCHED_NM
,OI.CALCD_PRIN_EST_AMT AS OI_CALCD_PRIN_EST_AMT
,OI.EST_CURNCY_CD AS OI_EST_CURNCY_CD
,OI.LINKED_ORDER_NUM AS OI_LINKED_ORDER_NUM
,OI.BUYING_POWER_AFFECT_AMT AS OI_BUYING_POWER_AFFECT_AMT
,OI.INSIDER_IND AS OI_INSIDER_IND
,OI.SIGNIFICANT_SHRHLD_IND AS OI_SIGNIFICANT_SHRHLD_IND
,OI.PRO_TRADER_IND AS OI_PRO_TRADER_IND
,OI.ROLL_OVER_INSTRC_CD AS OI_ROLL_OVER_INSTRC_CD
,OI.TERM_DAY_NUM AS OI_TERM_DAY_NUM
,OI.TERM_YEAR_NUM AS OI_TERM_YEAR_NUM
,OI.GIC_RATE_PCT AS OI_GIC_RATE_PCT
,OI.INT_PAYMENT_FREQ_CD AS OI_INT_PAYMENT_FREQ_CD
,OI.COMPND_FREQ_CD AS OI_COMPND_FREQ_CD
,OI.DEBT_INSTRM_ISSUER_CD AS OI_DEBT_INSTRM_ISSUER_CD
,OI.DEBT_INSTRM_ISSUER_NM AS OI_DEBT_INSTRM_ISSUER_NM
,OI.DEBT_INSTRM_MATURE_DT AS OI_DEBT_INSTRM_MATURE_DT
,OI.YIELD_PCT AS OI_YIELD_PCT
,OI.PRINCIPAL_AMT AS OI_PRINCIPAL_AMT
,OI.ORDER_ITEM_CREATION_TS AS OI_ORDER_ITEM_CREATION_TS
,OI.ORDER_LIFE_CYCLE_STAGE_CD AS OI_ORDER_LIFE_CYCLE_STAGE_CD
,OI.INCLUDE_COMISN_IND AS OI_INCLUDE_COMISN_IND
,OI.BOR_ORDER_NUM AS OI_BOR_ORDER_NUM
,OI.AON_IND AS OI_AON_IND
,OI.EXERCISE_OPTION_IND AS OI_EXERCISE_OPTION_IND
,OI.ESTIMATED_TOTAL_AMT AS OI_ESTIMATED_TOTAL_AMT
,OI.BOR_ORDER_MARKET_CD AS OI_BOR_ORDER_MARKET_CD
,OI.ORDER_MARKET_CURNCY_CD AS OI_ORDER_MARKET_CURNCY_CD
,OI.NEW_ISSUE_IND AS OI_NEW_ISSUE_IND
,OI.ORDER_LEG_ID AS OI_ORDER_LEG_ID
,OI.SHORT_IND AS OI_SHORT_IND
,OI.TRIGGER_IND AS OI_TRIGGER_IND
,OI.OPTION_CD AS OI_OPTION_CD
,OI.CERTIF_REQ_IND AS OI_CERTIF_REQ_IND
,OI.INT_CALC_CD AS OI_INT_CALC_CD
,OI.NET_DEBT_CREDIT_AMT AS OI_NET_DEBT_CREDIT_AMT
,OI.OPTION_MULTIPLIER AS OI_OPTION_MULTIPLIER
,OI.PROSP_IND AS OI_PROSP_IND
,OI.MF_FAMILY_CD AS OI_MF_FAMILY_CD
,OI.MF_DEALER_NUM AS OI_MF_DEALER_NUM
,OI.BOR_ORDER_SUB_SEQ_NUM AS OI_BOR_ORDER_SUB_SEQ_NUM
,OI.ORDER_REJECT_CD AS OI_ORDER_REJECT_CD
,OI.TRANSFER_PRICE_AMT AS OI_TRANSFER_PRICE_AMT
,OI.ALT_BHLF_CD AS OI_ALT_BHLF_CD
,OI.MF_ALT_ORDER_NUM AS OI_MF_ALT_ORDER_NUM
,OI.AUTH_REJECT_ACTION_CD AS OI_AUTH_REJECT_ACTION_CD
,OI.AUTH_REJECT_DT AS OI_AUTH_REJECT_DT
,OI.MF_ACCT_NUM AS OI_MF_ACCT_NUM
,OI.BOR_ORDER_RECEIVE_TS AS OI_BOR_ORDER_RECEIVE_TS
,OI.OTHER_MF_ACCT_NUM AS OI_OTHER_MF_ACCT_NUM
,OI.POSTED_IND AS OI_POSTED_IND
,OI.NET_AMT_IND AS OI_NET_AMT_IND
,OI.MF_LOAD_CD AS OI_MF_LOAD_CD
,OI.MF_COMISN_PCT AS OI_MF_COMISN_PCT
,OI.RESID_GEO_CD AS OI_RESID_GEO_CD
,OI.BOR_LAST_UPDATE_TS AS OI_BOR_LAST_UPDATE_TS
,OI.NUM_OF_ERROR AS OI_NUM_OF_ERROR
,OI.DESTINATION_CD AS OI_DESTINATION_CD
,OI.SHORT_NM AS OI_SHORT_NM
,OI.ACCT_LOB_CD AS OI_ACCT_LOB_CD
,OI.CUSIP_NUM AS OI_CUSIP_NUM
,OI.AFFIRMATION_DT AS OI_AFFIRMATION_DT
,OI.COMPLIANCE_CD AS OI_COMPLIANCE_CD
,OI.BOR_COMISN_AMT AS OI_BOR_COMISN_AMT
,OI.ACCRURED_INT_AMT AS OI_ACCRURED_INT_AMT
,OI.VERSION_NUM AS OI_VERSION_NUM
,OI.EVENT_LOG_ID AS OI_EVENT_LOG_ID
,COND.ORDER_ITEM_UID AS COND_ORDER_ITEM_UID
,COND.RECORD_TYPE_CD AS COND_RECORD_TYPE_CD
,COND.ORDER_COND_CD AS COND_ORDER_COND_CD
,COND.EFFECTIVE_TS AS COND_EFFECTIVE_TS
,COND.GROUP_COND_IND AS COND_GROUP_COND_IND
,COND.ORDER_COND_CTGY_CD AS COND_ORDER_COND_CTGY_CD
,COND.ORDER_COND_ACNLEG_IND AS COND_ORDER_COND_ACNLEG_IND
,COND.REF_ATTRIBUTE AS COND_REF_ATTRIBUTE
,COND.XREF_ATTRIBUTE AS COND_XREF_ATTRIBUTE
,COND.AUTO_ACNLEG_IND AS COND_AUTO_ACNLEG_IND
,COND.SOURCE_CD AS COND_SOURCE_CD
,COND.SOURCE_SEVERITY_CODE AS COND_SOURCE_SEVERITY_CODE
,TRLR.ORDER_ITEM_UID AS TRLR_ORDER_ITEM_UID
,TRLR.RECORD_TYPE_CD AS TRLR_RECORD_TYPE_CD
,TRLR.TRAILER_TYPE_CD AS TRLR_TRAILER_TYPE_CD
,TRLR.EFFECTIVE_TS AS TRLR_EFFECTIVE_TS
,TRLR.FTNOTE_TRAIL_1_TXT AS TRLR_FTNOTE_TRAIL_1_TXT
,TRLR.FTNOTE_TRAIL_2_TXT AS TRLR_FTNOTE_TRAIL_2_TXT
,TRLR.CODED_TRAILER_CD AS TRLR_CODED_TRAILER_CD
,TSTP.ORDER_ITEM_UID AS TSTP_ORDER_ITEM_UID
,TSTP.RECORD_TYPE_CD AS TSTP_RECORD_TYPE_CD
,TSTP.TRAIL_STOP_REF_PRICE_AMT AS TSTP_TRAIL_STOP_REF_PRICE_AMT
,TSTP.TRAIL_STOP_REF_PRICE_TS AS TSTP_TRAIL_STOP_REF_PRICE_TS
,TSTP.REMAIN_FILL_QTY AS TSTP_REMAIN_FILL_QTY
,TSTP.FILLED_QTY AS TSTP_FILLED_QTY
,TSTP.AVG_FILL_PRICE_AMT AS TSTP_AVG_FILL_PRICE_AMT
,TSTP.VERSION_NUM AS TSTP_VERSION_NUM
,FILL.ORDER_ITEM_UID AS FILL_ORDER_ITEM_UID
,FILL.RECORD_TYPE_CD AS FILL_RECORD_TYPE_CD
,FILL.ORDER_FILL_SUB_SEQ_NUM AS FILL_ORDER_FILL_SUB_SEQ_NUM
,FILL.EXCHANGE_MARKET_CD AS FILL_EXCHANGE_MARKET_CD
,FILL.ORDER_FILL_QUANTY_AMT AS FILL_ORDER_FILL_QUANTY_AMT
,FILL.ORDER_FILL_PRICE_AMT AS FILL_ORDER_FILL_PRICE_AMT
,FILL.ORDER_FILL_TS AS FILL_ORDER_FILL_TS
,FILL.BOR_ORDER_FILL_NUM AS FILL_BOR_ORDER_FILL_NUM
,FILL.OMS_ORDER_NUM AS FILL_OMS_ORDER_NUM
,FILL.BOR_ORDER_FILL_TS AS FILL_BOR_ORDER_FILL_TS
,FILL.TRADE_DT AS FILL_TRADE_DT
,FILL.TRADE_NUM AS FILL_TRADE_NUM
,FILL.TRADE_PROCESS_DT AS FILL_TRADE_PROCESS_DT
,FILL.SETTLE_DT AS FILL_SETTLE_DT
,FILL.BOR_ORDER_FILL_SEQ_NUM AS FILL_BOR_ORDER_FILL_SEQ_NUM
FROM
WOE.ORDER_ITEM OI
INNER JOIN
WOE.ORDER_GROUP OG
ON OI.ORDER_UID = OG.ORDER_UID
AND OI.RECORD_TYPE_CD = OG.RECORD_TYPE_CD
LEFT OUTER JOIN
WOE.ORDER_COND COND
ON OI.ORDER_ITEM_UID = COND.ORDER_ITEM_UID
AND OI.RECORD_TYPE_CD = COND.RECORD_TYPE_CD
LEFT OUTER JOIN
WOE.ORDER_TRAILER TRLR
ON OI.ORDER_ITEM_UID = TRLR.ORDER_ITEM_UID
AND OI.RECORD_TYPE_CD = TRLR.RECORD_TYPE_CD
LEFT OUTER JOIN
WOE.ORDER_TRAIL_STOP_REF_FILL_REMAIN TSTP
ON OI.ORDER_ITEM_UID = TSTP.ORDER_ITEM_UID
AND OI.RECORD_TYPE_CD = TSTP.RECORD_TYPE_CD
LEFT OUTER JOIN
WOE.ORDER_FILL FILL
ON OI.ORDER_ITEM_UID = FILL.ORDER_ITEM_UID
AND OI.RECORD_TYPE_CD = FILL.RECORD_TYPE_CD
WHERE
(OI.RECORD_TYPE_CD = @RECORD_TYPE_CD)
AND
(
((datalength(@ORDER_NUM_L) > 0 and OI.ORDER_NUM collate Latin1_General_BIN IN (select value from @lists where paramname = '@ORDER_NUM')))
or
((datalength(@BOR_ORDER_NUM_L) > 0 and OI.BOR_ORDER_NUM collate Latin1_General_BIN IN (select value from @lists where paramname = '@BOR_ORDER_NUM')))
or
((datalength(@BOR_ORDER_GROUP_ID_L) > 0 and OG.BOR_ORDER_GROUP_ID collate Latin1_General_BIN IN (select value from @lists where paramname = '@BOR_ORDER_GROUP_ID')))
)
IF @@ROWCOUNT > 0
RETURN 0
--print 'RETURN 0'
ELSE
RETURN -1
--print 'RETURN -1'
END
GO
Function fnSplitString.....
CREATE FUNCTION [WOE].[fnSplitString]
--select * from WOE.fnSplitString('Querying|SQL|Server','|')
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
--GRANT SELECT
GO
GRANT SELECT ON WOE.fnSplitString TO ROLE_WOE_READ_WRITE
GO
TableNameRecordCount
-------------------------
ORDER_COND8593
ORDER_FILL 1861
ORDER_GROUP38646
ORDER_ITEM43186
ORDER_TRAILER57401
ORDER_TRAIL_STOP_REF_FILL_REMAIN41528
Indexes details attached
Execution Plan is attached
June 30, 2016 at 9:31 am
The most obvious improvement you can make is to replace your string splitter with one which does not contain loops and has been highly optimised.
Have a read here[/url] for such a thing.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 30, 2016 at 9:41 am
Creating an index on (paramname, value) in @lists is worth a test – should remove those table scans.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 30, 2016 at 9:42 am
Yes, and consider using a temp table instead of a table variable. The former has statistics on it and so you're likely to get a better query plan, especially if there are a large number of elements in your delimited lists.
John
June 30, 2016 at 9:54 am
You could also check this to learn about catch-all-queries. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
I would create a different temp table for each parameter.
June 30, 2016 at 10:48 am
Phil Parkin !!
Your recommendation was the most valuable !!
Thanks a lot.
I added clustered unique index on table variable @lists.
declare @lists table
(
value varchar(50),
paramname varchar(20),
UNIQUE CLUSTERED (value, paramname)
)
Unfortunately with regular temp table it was slower.
Reg temp table - 2000 milliseconds
Table variable - 1460 milliseconds
And with temp table I still get Table Scan in ex. plan.
With table variable I get Index Seek..
Latest Ex. plan is attached.
I have some improvement but still far away form target... 400 milliseconds.
Am I measuring performance correctly?
This is what I do:
Set statistics time on
go
<exec proc>
go
Set statistics time off
Go
This is my output:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 641 ms, elapsed time = 745 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 641 ms, elapsed time = 746 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Then I throw it into:
http://www.statisticsparser.com/
and it sums up everything..
In this case it is 1.491 milliseconds
June 30, 2016 at 10:58 am
Just to let you know guys..
Calls to function that splits strings costs nothing (0%) in Execution Plan..
So I didn't bother to pay attention to it...
June 30, 2016 at 10:59 am
I suggest that you focus on the string splitter now.
As far as I remember, query plans do not return accurate results for UDFs.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 30, 2016 at 11:05 am
Instead of the complex OR construct in your Where clause, you might try splitting it up into three queries, one for each parameter, and then using Union All to put them together.
Or, if the results are supposed to be exclusive, use Intersect instead of Union All. I.e.: If Record Type and Order Num are both provided, is it supposed to pull the records that fit both criteria, or the records that fit either criterion? Right now, it does "either".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 30, 2016 at 11:41 am
1) What are all table create scripts. You are getting convert_implicits and I wonder if data types are all correct.
2) Stop doing the datalength > 0 thing. You need to build out 6 separate queries, one covering each parameter state combination (3 factorial = 3*2*1 = 6 permutations). Each of the resulting queries should have OPTION (RECOMPILE) on it. You could also put each of these 6 in a separate sproc and hit those with a master sproc. This is best probably. This is the single most important thing to do once any meaningful convert_implicits are dealt with.
3) You have way-off estimates, leading to a tempdb spill on the SORT. Above changes should help with this.
4) Separate temp object per input. This too will help with estimates. Filtering IN clauses is bad when it can be avoided.
5) You have way-off estimates leading to full table scans and merge joins that are inefficient given actual row counts. Hopefully above things will fix that. If not you will need to put KEY fields from filtering in a temp table and then make a second pass to get actual output columns from all tables.
6) Fixing string splitting will not be big savings given this particular plan but should still be done. Search Delimited8KSplit.
7) I would not bother with index on temp object(s). This is almost always a complete waste of time for no/insufficient benefit.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 30, 2016 at 12:11 pm
Kevin said a lot of what I was going to say especially about fixing that sort with the tempdb spill - that's going to kill you. An index on [WOEC].[WOE].[ORDER_ITEM].ORDER_ITEM_UID, provided that the optimizer chooses it will help you a lot. I would say, too, that OPTION (RECOMPILE) should be mandatory for this proc.
Edit: The rest of my response removed - I misread the query code.
-- Itzik Ben-Gan 2001
June 30, 2016 at 12:23 pm
Alan.B (6/30/2016)
Kevin said a lot of what I was going to say especially about fixing that sort with the tempdb spill - that's going to kill you. An index on [WOEC].[WOE].[ORDER_ITEM].ORDER_ITEM_UID, provided that the optimizer chooses it will help you a lot. I would say, too, that OPTION (RECOMPILE) should be mandatory for this proc.RVO (6/30/2016)
Just to let you know guys..Calls to function that splits strings costs nothing (0%) in Execution Plan..
So I didn't bother to pay attention to it...
First, some good general advice would be to take those estimations with a grain of salt.
That splitter is a huge problem. What I can tell you for 100% sure is: that splitter is preventing a parallel plan. It's a multivalued Table Valued Function (mTVF) which means that you're going to get a serial execution plan. mTVFs kill parallelism.
DelimitedSplit8K and DelimitedSplit8K_LEAD (referenced in my signature) are inline table valued functions and don't kill parallelism. This query would probably run much faster with all four of your CPUs on working on it. The fastest way to speed this query up dramatically:
1. ADD Option (Recompile)
2. Replace your splitter with DelimitedSplit8K_LEAD
3. Run the proc and check the actual execution
If you are not getting a parallel execution plan:
4. Download make_parallel and add the following code to the end of your SELECT query:
CROSS APPLY make_parallel()
Maybe I am missing it, but I don't see the OP using the string splitter in the main query. It is only used to populate the temp object, which is about zero amount of the work being done here. All your talk about voiding parallelism (which is often VERY important) is pointless here right?
Also note these are small tables. The entire cost of this query, even with all those table scans, is just over 10. Perhaps the OP has smartly reset their cost threshold for parallelism to something over that value, or there is something else preventing parallelism (or deciding it still isn't worth it).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 30, 2016 at 12:34 pm
TheSQLGuru (6/30/2016)
Alan.B (6/30/2016)
Kevin said a lot of what I was going to say especially about fixing that sort with the tempdb spill - that's going to kill you. An index on [WOEC].[WOE].[ORDER_ITEM].ORDER_ITEM_UID, provided that the optimizer chooses it will help you a lot. I would say, too, that OPTION (RECOMPILE) should be mandatory for this proc.RVO (6/30/2016)
Just to let you know guys..Calls to function that splits strings costs nothing (0%) in Execution Plan..
So I didn't bother to pay attention to it...
First, some good general advice would be to take those estimations with a grain of salt.
That splitter is a huge problem. What I can tell you for 100% sure is: that splitter is preventing a parallel plan. It's a multivalued Table Valued Function (mTVF) which means that you're going to get a serial execution plan. mTVFs kill parallelism.
DelimitedSplit8K and DelimitedSplit8K_LEAD (referenced in my signature) are inline table valued functions and don't kill parallelism. This query would probably run much faster with all four of your CPUs on working on it. The fastest way to speed this query up dramatically:
1. ADD Option (Recompile)
2. Replace your splitter with DelimitedSplit8K_LEAD
3. Run the proc and check the actual execution
If you are not getting a parallel execution plan:
4. Download make_parallel and add the following code to the end of your SELECT query:
CROSS APPLY make_parallel()
Maybe I am missing it, but I don't see the OP using the string splitter in the main query. It is only used to populate the temp object, which is about zero amount of the work being done here. All your talk about voiding parallelism (which is often VERY important) is pointless here right?
Also note these are small tables. The entire cost of this query, even with all those table scans, is just over 10. Perhaps the OP has smartly reset their cost threshold for parallelism to something over that value, or there is something else preventing parallelism (or deciding it still isn't worth it).
OMG! <Smacking head...> Yeah - I misread the code, I thought he was splitting the strings inside his WHERE clause.
-- Itzik Ben-Gan 2001
June 30, 2016 at 1:42 pm
Alan.B (6/30/2016)
TheSQLGuru (6/30/2016)
Alan.B (6/30/2016)
Kevin said a lot of what I was going to say especially about fixing that sort with the tempdb spill - that's going to kill you. An index on [WOEC].[WOE].[ORDER_ITEM].ORDER_ITEM_UID, provided that the optimizer chooses it will help you a lot. I would say, too, that OPTION (RECOMPILE) should be mandatory for this proc.RVO (6/30/2016)
Just to let you know guys..Calls to function that splits strings costs nothing (0%) in Execution Plan..
So I didn't bother to pay attention to it...
First, some good general advice would be to take those estimations with a grain of salt.
That splitter is a huge problem. What I can tell you for 100% sure is: that splitter is preventing a parallel plan. It's a multivalued Table Valued Function (mTVF) which means that you're going to get a serial execution plan. mTVFs kill parallelism.
DelimitedSplit8K and DelimitedSplit8K_LEAD (referenced in my signature) are inline table valued functions and don't kill parallelism. This query would probably run much faster with all four of your CPUs on working on it. The fastest way to speed this query up dramatically:
1. ADD Option (Recompile)
2. Replace your splitter with DelimitedSplit8K_LEAD
3. Run the proc and check the actual execution
If you are not getting a parallel execution plan:
4. Download make_parallel and add the following code to the end of your SELECT query:
CROSS APPLY make_parallel()
Maybe I am missing it, but I don't see the OP using the string splitter in the main query. It is only used to populate the temp object, which is about zero amount of the work being done here. All your talk about voiding parallelism (which is often VERY important) is pointless here right?
Also note these are small tables. The entire cost of this query, even with all those table scans, is just over 10. Perhaps the OP has smartly reset their cost threshold for parallelism to something over that value, or there is something else preventing parallelism (or deciding it still isn't worth it).
OMG! <Smacking head...> Yeah - I misread the code, I thought he was splitting the strings inside his WHERE clause.
Happens to the best of us - and seems to happen to me far too often now that I am on the wrong side of 50! :w00t:
You did make some very good points though! Hopefully the search engines will pick that up and make it useful for others.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 30, 2016 at 2:53 pm
In PAT (pre-PROD) environment.
performance is terrible.
77 rows returned in 2 min and 56 sec..
Very slow...
Execution Plan is attached...
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply