Geek Answers Handbook

SQL UPDATE Query Optimization with Rules

I have two FactLik tables (2 million rows) and UpdateStPrice (500 thousand rows rows). I need to update the FactLik table using the rules.

UpdateStPrice

 | PRODUCTKEY | WAREHOUSEKEY | STARTDATE | ENDDATE | PRIORITY | UNITPRICE | --------------------------------------------------------------------------- | 36975 | 6 | 20120630 | 20121011 | 0 | 395 | | 36975 | 6 | 20121018 | 20291231 | 0 | 371 | | 36975 | 6 | 20121126 | 20121211 | 120 | 313 | | 36975 | 6 | 20121126 | 20121219 | 120 | 288 | | 36975 | 6 | 20121212 | 20291231 | 120 | 313 |

FactLik

 | TIMEKEY | PRODUCTKEY | PRODUCTGROUPKEY | WAREHOUSEKEY | PRICE | ------------------------------------------------------------------- | 20121205 | 36975 | 89 | 6 | (null) |

FactLik Row Update Rules

  • For the same ProductKey , WarehouseKey look for UpdateStPrice in the line, where Priority is MAX and FactLik.TimeKey is between StartDate and EndDate .
  • Now find the line where StartDate MAX.
  • Now find the line where EndDate is MIN.

Expected Result from UpdateStPrice :

 | PRODUCTKEY | WAREHOUSEKEY | STARTDATE | ENDDATE | PRIORITY | UNITPRICE | --------------------------------------------------------------------------- | 36975 | 6 | 20121126 | 20121211 | 120 | 313 |

Result of FactLik :

 | TIMEKEY | PRODUCTKEY | PRODUCTGROUPKEY | WAREHOUSEKEY | PRICE | ------------------------------------------------------------------- | 20121205 | 36975 | 89 | 6 | 313 |

Smallfiddle

Bigfiddle

I have my QUERY , which is very slow, in fact it runs slower for more than 12 hours. I have some indexes for both tables (supposedly an execution plan), but they don't help at all :)

So, if you can help me optimize this query, I will be very grateful.

+6
source share
3 answers

I think you can do this by simply ordering data from UpdateStPrice:

 UPDATE factlik SET price = (SELECT TOP 1 up.unitprice FROM updatestprice up WHERE up.productkey = factlik.productkey AND up.warehousekey = factlik.warehousekey AND factlik.timekey >= up.startdate AND factlik.timekey <= up.enddate ORDER BY priority desc, startdate desc, enddate )

sqlfiddle here

+3
source

This looks specifically for the window function in the subquery table:

 UPDATE factlik SET price = up.unitprice FROM factlik JOIN (SELECT factlik.timekey, factlik.productkey, factlik.warehousekey, up.unitprice, ROW_NUMBER() OVER(ORDER BY up.priority DESC, up.startdate DESC, up.enddate ASC) as rownum FROM updatestprice up JOIN factlik ON up.productkey = factlik.productkey AND up.warehousekey = factlik.warehousekey AND up.startdate <= factlik.timekey AND up.enddate > factlik.timekey) up ON up.timekey = factlik.timekey AND up.productkey = factlik.productkey AND up.warehousekey = factlik.warehousekey AND up.rownum = 1

( Performs the same as the previous example .)

0
source

this should work:

 UPDATE factlik SET price = (SELECT unitprice FROM updatestprice u INNER JOIN (SELECT ms.productkey, ms.warehousekey, max_priority, max_start, MIN(enddate) AS min_end FROM updatestprice u INNER JOIN factlik f ON u.productkey = f.productkey AND u.warehousekey = f.warehousekey AND timekey >= startdate AND timekey <= EndDate INNER JOIN (SELECT mp.productkey, mp.warehousekey, max_priority, MAX(startdate) AS max_start FROM updatestprice u INNER JOIN factlik f ON u.productkey = f.productkey AND u.warehousekey = f.warehousekey AND timekey >= startdate AND timekey <= EndDate INNER JOIN (SELECT u.productkey, u.warehousekey, MAX([priority]) AS max_priority FROM updatestprice u INNER JOIN factlik f ON u.productkey = f.productkey AND u.warehousekey = f.warehousekey AND timekey >= startdate AND timekey <= EndDate GROUP BY u.productkey, u.warehousekey ) mp ON u.productkey = mp.productkey AND u.warehousekey = mp.warehousekey AND u.priority = mp.max_priority GROUP BY mp.productkey, mp.warehousekey, max_priority ) ms ON u.productkey = ms.productkey AND u.warehousekey = ms.warehousekey AND u.priority = ms.max_priority AND u.startdate = ms.max_start GROUP BY ms.productkey, ms.warehousekey, max_priority, max_start ) me ON u.productkey = me.productkey AND u.warehousekey = me.warehousekey AND u.priority = me.max_priority AND u.startdate = me.max_start AND u.enddate = me.min_end)

returns:

 PRODUCTKEY WAREHOUSEKEY STARTDATE ENDDATE PRIORITY UNITPRICE 36975 0 20121018 20291231 0 371 36975 0 20120630 20121011 0 395 36975 0 20121126 20121211 100 313 36975 0 20121212 20291231 100 313 36975 0 20121126 20121219 100 288 36975 6 20120630 20121011 0 395 36975 6 20121018 20291231 0 371 36975 6 20121126 20121211 120 313 36975 6 20121126 20121219 120 288 36975 6 20121212 20291231 120 313
0
source

More articles:


All Articles