December 12, 2018 at 7:32 pm
Hi there, I have want to modify a SP to select item's activities from a main table of 2 million records. Initially, I used cursor to return results and it was extremely slow when the table has grown big. Thus, I modified it to the following test code. All recommended indexes by tuning tools were applied but, the speed is still not as expected.
How can I improve it further?
Thanks in advance
Objective
1. Get each of the item's opening qty plus its activities movement from s_stkdetl
steps,
1. Get Open Qty => sum(qty in) - sum(qty out) from s_stkdetl
2. Get Item's activities movement from s_stkdetl
3. Union both queries
4. Order by item code and transaction code
DECLARE @PartFrom char(30), @PartTo char(30), @mDateFrom char(8), @mDateTo char(8),
@WhFrom char(10), @WhTo char(10), @LocFrom char(10), @LocTo char(10), @ShelfFrom char(10), @ShelfTo char(10),
@LotFrom char(20), @LotTo char(20), @GrpFrom char(10), @grpTo char(10), @catFrom char(10), @catTo char(10)
DECLARE @StockActivityTable TABLE
(Part_code char(30), Part_desc nchar(100), specification nchar(100), odr_ref char(12), dbcode char(10), crcode char(10),
Ref_no char(12), date datetime, source char(4), unit_cost numeric (18,6),
lot_no char(20), wh_code char(10), part_loc char(10), shelf_loc char(10), qty_open numeric(18,6), qty_in numeric(18,6), qty_out numeric(18,6), uom char(10),
Sel_price numeric(18,6), CustSupp_DO char(20), CustSupp_PO char(40), trn_date datetime , price numeric(18,6),
carton_no char(20), doe datetime, ro_level numeric(18,6), qty_odr numeric(18,6) )
Declare @PartType char(1), @PartDesc nchar(100), @PartSpec nchar(100), @StockHisOpnQty numeric(18,6),
@StockDetailOpnQty numeric(18,6), @mPartCode char(30), @PartUom char(10), @curPeriod char(6),
@FilterWhFrom char(10), @FilterWhTo char(10), @mUnit_cost numeric (18,6) , @price numeric(18,6),
@max_qty numeric(18,6), @qty_odr numeric(18,6) Set @PartFrom = '0'
set @PartTo = 'z'
--Set @PartFrom = 'R000'
--set @PartTo = 'R9999'
set @mDateFrom = '20181001'
set @mDateTo = '20181030'
Set @WhFrom = '0'
Set @WhTo ='z'
-- Set @LocFrom = '0'
-- Set @LocTo = 'z'
Set @LocFrom = 'ZONE B'
Set @LocTo = 'ZONE B'
Set @ShelfFrom =' '
Set @ShelfTo ='z'
Set @LotFrom ='-'
set @LotTo ='z'
set @GrpFrom ='RM'
set @GrpTo ='RM'
Set @catFrom ='0'
Set @catTo ='z'
SET @curPeriod = substring(@mDateFrom,1,6)
SET @FilterWhFrom = @WhFrom
SET @FilterWhTo = @WhTo
Set @StockHisOpnQty = 0
SELECT Part_code, Part_desc, Specification, 'B/FW Bal:' as odr_ref, '' as dbcode, '' as crcode,
'B/FW Bal:' as Ref_no, @mDateFrom as [date], 'BF' as [source],
isnull((Select top 1 unit_cost from s_locqty Where part_code=s.part_code),0) as unit_cost,
'' as lot_no, '' as wh_code, '' as part_loc, '' as shelf_loc,
--(isnull(@StockHisOpnQty,0) + isnull(@StockDetailOpnQty,0)) as qty_open,
ISNULL((Select ( (Select sum(qty) from s_stkdetl
where source LIKE 'R%' and convert(char(08), date, 112) >= '20000101' and convert(char(08), date, 112) < @mDateFrom
and part_code= s.part_code
and wh_code>=@FilterWhFrom and wh_code<=@FilterWhTo
and part_loc>=@LocFrom and part_loc<=@locTo
and shelf_loc >=@ShelfFrom and shelf_loc<=@shelfTo
and lot_no >=@LotFrom and lot_no<=@LotTo)
- (select isnull(sum(qty),0) from s_stkdetl
where (source LIKE 'I%' or (source='QI' and wh_code<>'*IQCWH*')) and convert(char(08), date, 112) >= '20000101' and convert(char(08), date, 112)< @mDateFrom
and part_code= s.part_code
and wh_code>=@FilterWhFrom and wh_code<=@FilterWhTo
and part_loc>=@LocFrom and part_loc<=@locTo
and shelf_loc >=@ShelfFrom and shelf_loc<=@shelfTo
and lot_no >=@LotFrom and lot_no<=@LotTo ) )),0) as qty_open,
0 as qty_in, 0 as qty_out, part_uom as uom, 0 as Sel_price, '' as CustSupp_DO, '' as CustSupp_PO, '' as trn_date,
CASE WHEN source='B' and l_r_price > 0
THEN l_r_price
ELSE dec_cost
END as price,
'' as carton_no, '' as doe, max_qty as ro_level, qty_odr as qty_odr
FROM s_stkmst s
Where Part_code>=@PartFrom AND Part_code<=@PartTo
and part_grp>=@GrpFrom and part_grp<=@GrpTo
and part_cat>=@CatFrom and part_cat<=@CatTo
--Order by part_code
UNION ALL
SELECT part_code,
(SELECT part_desc
FROM s_stkmst
WHERE Part_code = s_stkdetl.part_code) as part_desc,
(SELECT specification
FROM s_stkmst
WHERE Part_code = s_stkdetl.part_code) as specification,
odr_ref, dbcode, crcode,
ref_no, date, source, unit_cost, lot_no, wh_code, part_loc, shelf_loc, 0 as qty_open, qty as qty_in, 0 as qty_out,
(SELECT part_uom
FROM s_stkmst
WHERE Part_code = s_stkdetl.part_code) as uom,
sel_price,
CASE WHEN (source='RU' and isnull(dbcode,'')<>'' ) THEN po_no
WHEN (source='RM') THEN
(select remarks from s_stktrn where odr_ref=s_stkdetl.odr_ref)
WHEN (source='RW') THEN
(select top 1 prod_no from s_productoutput where job_no=s_stkdetl.ref_no)
WHEN (source='IM') THEN
(select remarks from s_stktrn where odr_ref=s_stkdetl.odr_ref)
WHEN (source='IW' or source='IP') THEN
(select job_no from s_mrshdr where mrs_no=s_stkdetl.ref_no)
ELSE (select supp_dono from s_porec where gidno=s_stkdetl.gid_no)
END AS CustSupp_DO,
CASE WHEN source LIKE 'R%' THEN
(Select top 1 po_no from s_porecdtl where gidno=s_stkdetl.ref_no and part_code=s_stkdetl.part_code)
ELSE po_no
END as CustSupp_PO,
trn_date,
(SELECT
CASE WHEN (source='B' and l_r_price>0) THEN l_r_price
ELSE dec_cost END as Price
FROM s_stkmst
Where Part_code = s_stkdetl.part_code) AS Price,
(select top 1 carton_no from s_porecdtl where gidno=s_stkdetl.gid_no and part_code=s_stkdetl.part_code)as carton_no,
doe,
(SELECT max_qty
FROM s_stkmst
WHERE Part_code = s_stkdetl.part_code) as ro_level,
(SELECT qty_odr
FROM s_stkmst
WHERE Part_code = s_stkdetl.part_code) as qty_odr
FROM s_stkdetl
WHERE convert(char(08), date, 112) >= @mDateFrom and convert(char(08), date, 112) <=@mDateTo
and Part_code>=@PartFrom AND Part_code<=@PartTo
and wh_code>=@FilterWhFrom and wh_code<=@FilterWhTo
and part_loc>=@LocFrom and part_loc<=@locTo
and shelf_loc >=@ShelfFrom and shelf_loc<=@shelfTo
and lot_no >=@LotFrom and lot_no<=@LotTo
Order by part_code, trn_date
December 13, 2018 at 12:05 am
First problem, your date parameters. They are char(80 and should be date, or datetime, or datetime2. You shouldn't be converting date values in your tables to character string for comparison purposes. Use the appropriate data types.
There is probably more but that was the first thing that jumped out at me during a quick scan of the code. It is late and I need to get some sleep as I have work in the morning.
Also, it would help if you could post the actual execution plan for the code as well the DDL for the tables involved and some sample data using tested DDL and DML scripts along with the expected results based on the sample data. Sample data, not production data.
December 13, 2018 at 2:04 am
Agree with everything Lynn said. Emphasizing, this:convert(
Is problematic and will lead to issues.
Also, since these are all local variables, they won't be sniffed (except in a recompile situation), so you'll be getting averages from the statistics. This may result in an optimal plan, but it may not. As paramaters, these values will be sniffed/sampled, using specific values to generate a plan. In some cases, that results in superior performa
Another point, the correlated sub-queries in the SELECT clause might be better done as JOINs, but you'll want to look at the execution plan and measure performance to test that out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 13, 2018 at 4:29 am
-- Step 1: replace all of the subqueries with their APPLY equivalent:
SELECT
d.part_code,
x1.part_desc,
x2.specification,
d.odr_ref, d.dbcode, crcode,
d.ref_no, d.date, d.source, d.unit_cost, d.lot_no, d.wh_code, d.part_loc, d.shelf_loc, 0 as qty_open, d.qty as qty_in, 0 as qty_out,
x3.uom,
d.sel_price,
CASE
WHEN (d.source='RU' and isnull(d.dbcode,'')<>'' ) THEN d.po_no
WHEN (d.source='RM') THEN x4.remarks
WHEN (d.source='RW') THEN x5.prod_no
WHEN (d.source='IM') THEN x6.remarks
WHEN (d.source='IW' or d.source='IP') THEN x7.job_no
ELSE x8.supp_dono
END AS CustSupp_DO,
CASE WHEN d.source LIKE 'R%' THEN x9.po_no ELSE d.po_no END as CustSupp_PO,
d.trn_date,
CASE WHEN (d.source='B' and d.l_r_price>0) THEN x10.l_r_price ELSE x10.dec_cost END as Price,
x11.carton_no,
d.doe,
x12.max_qty AS ro_level,
x13.qty_odrFROM s_stkdetl dFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 13, 2018 at 8:42 am
Another problem I just saw, your correlated subqueries in your select list have quite a few that use TOP 1 but there is no ORDER BY. What this means is you may not get the same results each time the query is run. Where you have multiple subqueries selecting different columns from the same table with the same criteria you may not get the data from the same row of data in the same run of the query.
Chris suggested using OUTER APPLY, but I would consolidate multiple calls to the same table with the same criteria to a single OUTER APPLY returning all the columns at once. I would also add an ORDER BY to ensure that the same data is returned on each run of the stored procedure if no data hase changed between runs.
December 13, 2018 at 7:07 pm
Hi Lynn, Grant and ChrisM,
Very much appreciated for your input. I will try all of your suggestions given and update you about the outcome.
December 13, 2018 at 8:08 pm
Hi Lynn, Grant and ChrisM,
Combine all of your above suggestions, I modified the queries into the following and the results are,
1. First execution time in local network production server is 2mins 50s (minimum workload),
2. Subsequent execution time varies from 13 to 30s.
3. Return 1345 rows from main table s_stkdetl contains about 2 millions records
4. Here attached the actual execution plan.
As for Lynn's comment on "Select Top 1" sub queries, the child tables' key is unique thus the results will always be the same.
Wish to have further speed enhancement if possible.
Thank you 🙂
SELECT Part_code, Part_desc, Specification, 'B/FW Bal:' as odr_ref, '' as dbcode, '' as crcode,
'B/FW Bal:' as Ref_no, @mDateFrom as [date], 'BF' as [source],
isnull((Select top 1 unit_cost from s_locqty Where part_code=s.part_code),0) as unit_cost,
'' as lot_no, '' as wh_code, '' as part_loc, '' as shelf_loc,
(SELECT
sum(qtyin) - sum(qtyout)
FROM (
SELECT
CASE WHEN ( source LIKE 'R%')
THEN qty END qtyin,
CASE WHEN ((source LIKE 'I%' or (source='QI' and wh_code<>'*IQCWH*')))
THEN qty END qtyout
FROM s_stkdetl
WHERE [date] >= '2000/01/01' and [date] < @mDateFrom
and part_code= s.part_code
and wh_code>=@FilterWhFrom and wh_code<=@FilterWhTo
and part_loc>=@LocFrom and part_loc<=@locTo
and shelf_loc >=@ShelfFrom and shelf_loc<=@shelfTo
and lot_no >=@LotFrom and lot_no<=@LotTo
) s_stkdetl) as opnqty,
0 as qty_in, 0 as qty_out, part_uom as uom, 0 as Sel_price, '' as CustSupp_DO, '' as CustSupp_PO, '' as trn_date,
CASE WHEN source='B' and l_r_price > 0
THEN l_r_price
ELSE dec_cost
END as price,
'' as carton_no, '' as doe, max_qty as ro_level, qty_odr as qty_odr
FROM s_stkmst s
Where Part_code>=@PartFrom AND Part_code<=@PartTo
and part_grp>=@GrpFrom and part_grp<=@GrpTo
and part_cat>=@CatFrom and part_cat<=@CatTo
UNION ALL
SELECT
d.part_code,
x1.part_desc,
x1.specification,
d.odr_ref, d.dbcode, crcode,
d.ref_no, d.date, d.source, d.unit_cost, d.lot_no, d.wh_code, d.part_loc, d.shelf_loc, 0 as qty_open, d.qty as qty_in, 0 as qty_out,
x1.part_uom,
d.sel_price,
CASE
WHEN (d.source='RU' and isnull(d.dbcode,'')<>'' ) THEN d.po_no
WHEN (d.source='RM') THEN x4.remarks
WHEN (d.source='RW') THEN x5.prod_no
WHEN (d.source='IM') THEN x4.remarks
WHEN (d.source='IW' or d.source='IP') THEN x7.job_no
ELSE x8.supp_dono
END AS CustSupp_DO,
CASE WHEN d.source LIKE 'R%' THEN x9.po_no ELSE d.po_no END as CustSupp_PO,
d.trn_date,
CASE WHEN (d.source='B' and x1.l_r_price>0) THEN x1.l_r_price ELSE x1.dec_cost END as Price,
x11.carton_no,
d.doe,
x1.max_qty AS ro_level,
x1.qty_odr
FROM s_stkdetl d
OUTER APPLY (SELECT part_desc, specification, part_uom, l_r_price, dec_cost, max_qty, qty_odr FROM s_stkmst WHERE Part_code = d.part_code) x1
OUTER APPLY (select remarks from s_stktrn where odr_ref = d.odr_ref) x4
OUTER APPLY (select top 1 prod_no from s_productoutput where job_no = d.ref_no) x5
OUTER APPLY (select job_no from s_mrshdr where mrs_no = d.ref_no) x7
OUTER APPLY (select supp_dono from s_porec where gidno = d.gid_no) x8
OUTER APPLY (Select top 1 po_no from s_porecdtl where gidno = d.ref_no and part_code = d.part_code) x9
OUTER APPLY (select top 1 carton_no from s_porecdtl where gidno = d.gid_no and part_code = d.part_code) x11
WHERE d.date >= @mDateFrom and d.date <= @mDateTo
and d.Part_code>=@PartFrom AND d.Part_code<=@PartTo
and d.wh_code>=@FilterWhFrom and d.wh_code<=@FilterWhTo
and d.part_loc>=@LocFrom and d.part_loc<=@locTo
and d.shelf_loc >=@ShelfFrom and d.shelf_loc<=@shelfTo
and d.lot_no >=@LotFrom and d.lot_no<=@LotTo
Order by part_code, trn_date
December 13, 2018 at 10:21 pm
In the plan 50+ percentage of cost going to key lookup for the table - [s_stkdetl]. You have to review the existing index and need to create a non clustered index with blow include clause.
[TotalERP_winwin].[dbo].[s_stkdetl].date, [TotalERP_winwin].[dbo].[s_stkdetl].dbcode, [TotalERP_winwin].[dbo].[s_stkdetl].crcode, [TotalERP_winwin].[dbo].[s_stkdetl].qty, [TotalERP_winwin].[dbo].[s_stkdetl].unit_cost, [TotalERP_winwin].[dbo].[s_stkdetl].sel_price, [TotalERP_winwin].[dbo].[s_stkdetl].po_no, [TotalERP_winwin].[dbo].[s_stkdetl].gid_no, [TotalERP_winwin].[dbo].[s_stkdetl].lot_no, [TotalERP_winwin].[dbo].[s_stkdetl].trn_date, [TotalERP_winwin].[dbo].[s_stkdetl].doe
Hope that you have clustered index for part_code. The actual and estimate numbers differs, check the stats update date and full or sample.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 14, 2018 at 4:59 am
Run the two UNIONed queries separately to confirm that it’s the lower one which is the pain point. Assuming it is, strip away the subqueries and work with the part of the query which the optimizer calculates to have 88% of the cost, which is retrieving rows from s_stkdetl according to all of the search criteria.
Partitioning is unexpected in a table so small, it’s only 2 million rows or so. SQL Server has calculated that the cheapest way to retrieve those rows is by partition elimination, but 16 partitions are checked to retrieve them, which seems to me to be a bit over the top. I’d expect 54663 rows to be retrieved from 2 million rows far more quickly if they were grabbed from an ordinary index which went some way to supporting the filters in the WHERE clause. You can model this theory as follows:
--isolate out the most expensive part of the query for closer examination
SELECT
d.odr_ref, d.source, d.item_no-- cluster keys
INTO#s_stkdetl
FROMs_stkdetl d
WHERE d.date >=@mDateFrom AND d.date <= @mDateTo
AND d.Part_code>= @PartFrom AND d.Part_code<= @PartTo
AND d.wh_code>= @FilterWhFrom AND d.wh_code<= @FilterWhTo
AND d.part_loc>= @LocFrom AND d.part_loc<= @locTo
AND d.shelf_loc>= @ShelfFrom AND d.shelf_loc<= @shelfTo
AND d.lot_no>= @LotFrom AND d.lot_no<= @LotTo
SELECT
d.part_code,
x1.part_desc,
x1.specification,
d.odr_ref, d.dbcode,crcode,
d.ref_no, d.date, d.source, d.unit_cost, d.lot_no, d.wh_code, d.part_loc, d.shelf_loc, 0 asqty_open, d.qty as qty_in, 0 as qty_out,
x1.part_uom,
d.sel_price,
CASE
WHEN (d.source='RU' and isnull(d.dbcode,'')<>'' ) THEN d.po_no
WHEN (d.source='RM') THEN x4.remarks
WHEN (d.source='RW') THEN x5.prod_no
WHEN (d.source='IM') THEN x4.remarks
WHEN (d.source='IW' or d.source='IP') THEN x7.job_no
ELSE x8.supp_dono
END ASCustSupp_DO,
CASE WHEN d.source LIKE 'R%' THEN x9.po_noELSE d.po_no END as CustSupp_PO,
d.trn_date,
CASE WHEN (d.source='B' and x1.l_r_price>0) THEN x1.l_r_priceELSE x1.dec_cost END as Price,
x11.carton_no,
d.doe,
x1.max_qty ASro_level,
x1.qty_odr
FROM#s_stkdetl t
INNER JOINs_stkdetl d
ON d.odr_ref= t.odr_ref
AND d.source = t.source
AND d.item_no= t.item_no
OUTER APPLY (SELECTpart_desc, specification, part_uom, l_r_price,dec_cost, max_qty, qty_odr FROMs_stkmst WHERE Part_code = d.part_code) x1
OUTER APPLY (selectremarks from s_stktrn where odr_ref = d.odr_ref) x4
OUTER APPLY (select top 1prod_no from s_productoutput where job_no = d.ref_no) x5
OUTER APPLY (selectjob_no from s_mrshdr where mrs_no = d.ref_no) x7
OUTER APPLY (selectsupp_dono from s_porec where gidno = d.gid_no) x8
OUTER APPLY (Select top 1po_no from s_porecdtl where gidno = d.ref_noand part_code = d.part_code) x9
OUTER APPLY (select top 1carton_no from s_porecdtl where gidno = d.gid_noand part_code = d.part_code) x11
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 16, 2018 at 8:03 pm
Thank you, ChrisM and Muthu for the advices.
It gonna take me sometimes to digest and restructure the query base on your points.
Thanks again to all of you.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply