Menu
I have Two tables FactLik (2 million rows) and UpdateStPrice ( 500k rows).
I need to update FactLik table using rules.
| 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 |
| TIMEKEY | PRODUCTKEY | PRODUCTGROUPKEY | WAREHOUSEKEY | PRICE |
-------------------------------------------------------------------
| 20121205 | 36975 | 89 | 6 | (null) |
ProductKey, WarehouseKey find in UpdateStPrice row where Priority is MAX and FactLik.TimeKey is between StartDate and EndDate.StartDate is MAX.EndDate is MIN. ExpectedResult from UpdateStPrice:
| PRODUCTKEY | WAREHOUSEKEY | STARTDATE | ENDDATE | PRIORITY | UNITPRICE |
---------------------------------------------------------------------------
| 36975 | 6 | 20121126 | 20121211 | 120 | 313 |
Result FactLik:
| TIMEKEY | PRODUCTKEY | PRODUCTGROUPKEY | WAREHOUSEKEY | PRICE |
-------------------------------------------------------------------
| 20121205 | 36975 | 89 | 6 | 313 |
SmallFiddle
BigFiddle
I have My QUERY which is very slow, actually she running to slow more than 12 hours. I have some Indexes on both tables (Execution plan suggested) but they don' help at all:)
So If you can help me to optimize this query I'll be very grateful.
345
SQL Query Optimization Techniques There are some useful practices to reduce the cost. But, the process of optimization is iterative. One needs to write the query, check query performance using io statistics or execution plan, and then optimize it. This cycle needs to be followed iteratively for query optimization.
You are correct in the assumption that for many queries the presence of useful indexes will result in a very noticeable speed improvement. Time required to create the index may result in blocking while the index is added to the table. The lock is very short lived, and most likely won't create a big problem.
I think you can do this by just ordering the 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
162
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
