January 24, 2008 at 8:15 am
Hello ,
When i run the query with the commented out where clausule it runs a few minutes and returns the results when using the where clausule like it's stated it keeps on running and running ,...
is there another option/workaround for this ?
The table has about 4.6million records
regards ,
stanja
select * from
(select p.product_code product_code
, c.colour_code colour_code
, sz.size_code size_code
, sh.shop_number_ads as shop_number_ads
, md_managed as md_managed
, max(sh.id_shop) id_shop
, sum(qty_full_stock) as qty
, max(sku.id_product) as id_product
, max(sku.id_colour) as id_colour
, max(sku.id_size) as id_size
, max(vat_percentage) vat_percentage
, sum(dbo.fn_gen_product_costprice(sku.id_product,sku.id_colour,sku.id_size,getdate())*qty_full_stock) as cost
, sum(dbo.fn_gen_product_actualprice(tsku.id_shop,sku.id_product,sku.id_colour,sku.id_size,getdate())*qty_full_stock) as retail
from
t_shop_sku tsku
join t_shop sh on sh.id_shop=tsku.id_shop
join t_sku sku on sku.id_sku=tsku.id_sku
join t_product p on p.id_product=sku.id_product
join t_colour c on c.id_colour=sku.id_colour
join t_size sz on sz.id_size=sku.id_size
left join t_product_country_vat pcv on pcv.id_product=p.id_product and pcv.id_country=sh.id_country and pcv.active=1
left join t_vat v on v.id_vat=pcv.id_vat
group by
p.product_code
, c.colour_code
, sz.size_code
, sh.shop_number_ads
, md_managed) sub1
left join
(select itemid, warehouseid, sum(actualquantity) as absqty, sum(deltacost) as avgcostprice, sum(deltasales) as salesprice, sum(deltasalesex) as salespriceex from ads.UK_ADS.dbo.inventoryhist9 a
group by itemid, warehouseid) old
on old.itemid = 'UK'+right('0000000000' + sub1.product_code,10)+right('000' + sub1.colour_code,3)+sub1.size_code
and old.warehouseid = sub1.shop_number_ads
where
--isnull(old.absqty,0) <> sub1.qty
isnull(old.avgcostprice,0) <> sub1.cost
and sub1.md_managed = 0
January 24, 2008 at 9:21 am
Make sure it's indexed.
Try to remove the functions from the WHERE clause somehow, maybe add a computed column. Avoid the , that causes a complete table scan. Think about another logical way to handle this.
January 24, 2008 at 9:23 am
You should try enabling the code and getting an estimated execution plan. That will tell you where the problems are.
Most likely you'll see that you're getting a table scan.
"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
January 24, 2008 at 9:58 am
Ok someone who is better at how indexes work tell me if this is correct.
If you change the code
isnull(old.avgcostprice,0) <> sub1.cost
to
NOT (old.avgcostprice = sub1.cost)
1) Are these equivalent? (after almost 20 years null's still get me every now and again)
2) Will the 2nd version allow for an index?
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
January 24, 2008 at 10:07 am
You might care to dump the two derived tables into indexed temp tables and run your join against that. With that many records, you've got to be making the server suffer.
The user-defined functions (which I suspect are doing single value table lookups) are forcing table scans. That's costprice and actualprice. They're going to act like glorified correlated subqueries, meaning - for 4.6M records, they're going to force execution of 2 x 4.6M subqueries to pull in the scalar values). That alone is going to kill perf. Re-write that without the UDF's.
Also - is there any chance that sub.qty1 is never 0 (looks to me like it would likely be NULL or >0)? if so - your ISNULL statements are useless.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply