CODEWITHSUNDEEP
sqlsql-server-2008optimization
Evan L
Evan L

Reputation: 3855

Query taking too long - Optimization

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.

Note I am not asking "write my code" or "fix my code". Just for a pointer in the right direction, I can't seem to figure out where the slow down occurs. Each derived table runs very quickly (less than a second) by themselves, the joins seem correct and the result set is returning exactly what I need. It's just too slow and I'm sure there are better SQL scripter's out there ;) 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'

Upvotes: 0

Views: 5708

Answers (4)

FlyingSnowGhost
FlyingSnowGhost

Reputation: 187

I was having the same problem and I was able to solve it by indexing one of the tables and setting a primary key.

Upvotes: 1

Curt
Curt

Reputation: 5722

Just as a clue to the right direction for optimization:

  • When you do an OUTER JOIN to a query with calculated columns, you are guaranteeing not only a full table scan, but that those calculations must be performed against every row in the joined table. It appears that you can actually do your join to oe_line without the column calculations (i.e. by filtering ext_price_calc to a specific range).

  • You don't need to do most of the subqueries that are in your query--the master query can be recrafted to use regular table join syntax. Joins to subqueries containing subqueries presents a challenge to the SQL optimizer that it may not be able to meet. But by using regular joins, the optimizer has a much better chance at identifying more efficient query strategies.

  • You don't tag which SQL engine you're using. Every database has proprietary extensions that may allow for speedier or more efficient queries. It would be easier to provide useful feedback if you indicated whether you were using MySQL, SQL Server, Oracle, etc.

  • Regardless of the database you're using, reviewing the query plan is always a good place to start. This will tell you where most of the I/O and time in your query is being spent.

  • Just on general principle, make sure your statistics are up-to-date.

Upvotes: 2

FastAl
FastAl

Reputation: 6280

It's may not be solvable by any of us without the real stuff to test with.

IF that's the case and nobody else posts the answer, I can still help. Here is how to trouble shoot it.

(1) take joins and pieces out one by one.
(2) this will cause errors. Remove or fake the references to get rid of them.
(3) see how that works.
(4) Put items back before you try taking something else out
(5) keep track...
(6) also be aware where a removal of something might drastically reduce the result set.

You might find you're missing an index or some other smoking gun.

Upvotes: 1

Jack Simth
Jack Simth

Reputation: 151

I strongly suspect that the problem lies in the number of joins you're doing. A lot of databases do joins basically by systemically checking all possible combinations of the various tables as being valid - so if you're joinging table A and B on column C, and A looks like: Name:C Fred:1 Alice:2 Betty:3

While B looks like: C:Pet 1:Alligator 2:Lion 3:T-Rex

When you do the join, it checks all 9 possibilities: Fred:1:1:Alligator Fred:1:2:Lion Fred:1:3:T-Rex Alice:2:1:Alligator Alice:2:2:Lion Alice:2:3:T-Rex Betty:3:1:Alligator Betty:3:2:Lion Betty:3:3:T-Rex

And goes through and deletes the non-matching ones: Fred:1:1:Alligator Alice:2:2:Lion Betty:3:3:T-Rex

... which means with three entries in each table, it creates nine temporary records, sorts through them all, and deletes six of them ... all before it actually sorts through the results for what you're after (so if you are looking for Betty's Pet, you only want one row on that final result).

... and you're doing how many joins and sub-queries?

Upvotes: 0

Related Questions