2
\$\begingroup\$

I am having an issue with the following query returning results a bit too slow and I suspect I am missing something basic. My initial guess is the 'CASE' statement is taking too long to process its result on the underlying data. But it could be something in the derived tables as well.

The question is, how can I speed this up? Are there any glaring errors in the way I am pulling the data? Am I running into a sorting or looping issues somewhere? The query runs for about 40 seconds, which seems quite long. C# is my primary expertise, SQL is a work in progress. Any tips would be greatly appreciated!

  SELECT 
hdr.taker
, hdr.order_no
, hdr.po_no as display_po
, cust.customer_name
, hdr.customer_id
, 'INCORRECT-LARGE ORDER' + CASE 
                    WHEN (ext_price_calc >= 600.01 and ext_price_calc <= 800) and fee_price.unit_price <>  round(ext_price_calc * -.01,2)
                        THEN '-1%: $' + cast(cast(ext_price_calc * -.01 as decimal(18,2)) as varchar(255))
                    WHEN ext_price_calc >= 800.01 and ext_price_calc <= 1000 and fee_price.unit_price <>  round(ext_price_calc * -.02,2)
                        THEN '-2%: $' + cast(cast(ext_price_calc * -.02 as decimal(18,2)) as varchar(255))
                    WHEN ext_price_calc > 1000 and fee_price.unit_price <>  round(ext_price_calc * -.03,2)
                        THEN '-3%: $' + cast(cast(ext_price_calc * -.03 as decimal(18,2)) as varchar(255))
                    ELSE
                        'OK'
                  END AS Status
FROM
(myDb_view_oe_hdr hdr
LEFT OUTER JOIN myDb_view_customer cust 
ON hdr.customer_id = cust.customer_id)
LEFT OUTER JOIN wpd_view_sales_territory_by_customer territory
ON cust.customer_id = territory.customer_id
LEFT OUTER JOIN 
    (select
        order_no,
        SUM(ext_price_calc) as ext_price_calc
    from 
    (select
        hdr.order_no,
        line.item_id,
        (line.qty_ordered - isnull(qty_canceled,0)) * unit_price as ext_price_calc 
    from myDb_view_oe_hdr hdr
    left outer join myDb_view_oe_line line
    on hdr.order_no = line.order_no
    where 
        line.delete_flag = 'N'
        AND line.cancel_flag = 'N'
        AND hdr.projected_order = 'N'
        AND hdr.delete_flag = 'N'
        AND hdr.cancel_flag = 'N'
        AND line.item_id not in ('LARGE-ORDER-1%','LARGE-ORDER-2%', 'LARGE-ORDER-3%', 'FUEL','NET-FUEL', 'CONVENIENCE-FEE')) as line
    group by order_no)  as order_total
    on hdr.order_no = order_total.order_no
 LEFT OUTER JOIN 
    (select 
        order_no,
        count(order_no) as convenience_count
    from oe_line with (nolock)
    left outer join inv_mast inv with (nolock)
    on oe_line.inv_mast_uid = inv.inv_mast_uid
    where inv.item_id in ('LARGE-ORDER-1%','LARGE-ORDER-2%', 'LARGE-ORDER-3%')
        and oe_line.delete_flag <> 'Y'
    group by order_no) as fee_count
on hdr.order_no = fee_count.order_no
INNER JOIN 
    (select 
        order_no, 
        unit_price 
    from oe_line line with (nolock) 
    where line.inv_mast_uid in (select inv_mast_uid from inv_mast with (nolock) where item_id in ('LARGE-ORDER-1%','LARGE-ORDER-2%', 'LARGE-ORDER-3%'))) as fee_price
ON fee_count.order_no = fee_price.order_no
WHERE
    hdr.projected_order = 'N'
    AND hdr.cancel_flag = 'N'
    AND hdr.delete_flag = 'N'
    AND hdr.completed = 'N'
    AND territory.territory_id = ‘CUSTOMERTERRITORY’
    AND ext_price_calc > 600.00
    AND hdr.carrier_id <> '100004'
    AND fee_count.convenience_count is not null
    AND CASE 
            WHEN (ext_price_calc >= 600.01 and ext_price_calc <= 800) and fee_price.unit_price <>  round(ext_price_calc * -.01,2)
                THEN '-1%: $' + cast(cast(ext_price_calc * -.01 as decimal(18,2)) as varchar(255))
            WHEN ext_price_calc >= 800.01 and ext_price_calc <= 1000 and fee_price.unit_price <>  round(ext_price_calc * -.02,2)
                THEN '-2%: $' + cast(cast(ext_price_calc * -.02 as decimal(18,2)) as varchar(255))
            WHEN ext_price_calc > 1000 and fee_price.unit_price <>  round(ext_price_calc * -.03,2)
                THEN '-3%: $' + cast(cast(ext_price_calc * -.03 as decimal(18,2)) as varchar(255))
            ELSE
                'OK' END <> 'OK'
\$\endgroup\$
  • 2
    \$\begingroup\$ Yes the tables are all indexed properly. The database is part of our ERP system, not something I created. I'm sure its something in my query design... not the DB itself. \$\endgroup\$ – Evan L Nov 19 '13 at 21:11
  • 2
    \$\begingroup\$ 4 outer-joins, two of which contain sub-selects, joined regular to another subselect-with-its-own-subselect .... Then, many of these things are not tables, since they all have 'view' in the name. Wow..... \$\endgroup\$ – rolfl Nov 19 '13 at 22:16
  • 1
    \$\begingroup\$ stackoverflow.com/questions/7359702/… get a plan, figure out why it's slow from that. \$\endgroup\$ – rolfl Nov 19 '13 at 22:44
  • 1
    \$\begingroup\$ Thanks for all the comments, through some feedback and research on SO, I was able to determine that everyone was partially right. The LEFT OUTER JOIN's were unnecessary and could be replaced with INNER's. Also I was able to get rid of two of the sub-queries and replaced views with tables. 2 second execution now. \$\endgroup\$ – Evan L Nov 19 '13 at 22:55
  • 1
    \$\begingroup\$ Things to try: 1) Run the query with EXPLAIN SELECT …; 2) Distill the performance problem to a simple query that illustrates the bottleneck. (Try eliminating your CASE expressions, for example, and see what happens.) \$\endgroup\$ – 200_success Nov 20 '13 at 0:24
3
\$\begingroup\$

You need to pull some stuff out of your nested queries.

You have aggregate functions inside of a Nested Select Statement for Join Tables. That should be a temp table or a table variable, at the least.

select
    order_no,
    SUM(ext_price_calc) as ext_price_calc
from 
    (select
        hdr.order_no,
        line.item_id,
        (line.qty_ordered - isnull(qty_canceled,0)) * unit_price as ext_price_calc 
     from myDb_view_oe_hdr hdr left outer join myDb_view_oe_line line
            on hdr.order_no = line.order_no
     where 
        line.delete_flag = 'N'
        AND line.cancel_flag = 'N'
        AND hdr.projected_order = 'N'
        AND hdr.delete_flag = 'N'
        AND hdr.cancel_flag = 'N'
        AND line.item_id not in ('LARGE-ORDER-1%','LARGE-ORDER-2%', 'LARGE-ORDER-3%', 'FUEL','NET-FUEL', 'CONVENIENCE-FEE')) as line
group by order_no)  as order_total

This Query should be made into a Temp Table or a table Variable, so that you can call this table from the query without nesting 3-4 deep here. This is just plain messy.

This is just one of the things that you can do to clean this up.

When you clean it up, you should compare the execution plans to one another, a Select inside of a Select inside of a Select goes through all of those queries more times than is necessary.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.