Query too slow - Optimization

Question

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'

• Evan L: 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.
• rolfl: 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.....
• rolfl: stackoverflow.com/questions/7359702/… get a plan, figure out why it's slow from that.
• Evan L: 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.
• 200_success: 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.)

Answer 1

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.