• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

QUERY PRICE

Hi All,

I have query below:

SELECT
A.BarangCode
, A.FullName
, Satuan
, 1 AS Ratio1
, (
SELECT
TOP 1
SalesPrice
FROM
TMPRICELIST AS B
WHERE A.BarangCode = B.BarangCode
AND A.Satuan = B.SatuanCode
AND CONVERT(VARCHAR, PriceDate, 112) <= CONVERT(VARCHAR, GETDATE(), 112)
ORDER BY PriceDate DESC
) AS SalesPriceSatuan
, Satuan1
, Ratio1
, (
SELECT
TOP 1 SalesPrice
FROM TMPRICELIST AS B
WHERE A.BarangCode = B.BarangCode
AND A.Satuan1 = B.SatuanCode
AND CONVERT(VARCHAR, PriceDate, 112) <= CONVERT(VARCHAR, GETDATE(), 112)
ORDER BY PriceDate DESC
) AS SalesPriceSatuan1
, Satuan2
, Ratio2
, (
SELECT
TOP 1 SalesPrice
FROM TMPRICELIST AS B
WHERE A.BarangCode = B.BarangCode
AND A.Satuan2 = B.SatuanCode
AND CONVERT(VARCHAR, PriceDate, 112) <= CONVERT(VARCHAR, GETDATE(), 112)
ORDER BY PriceDate DESC
) AS SalesPriceSatuan2

FROM TMBARANG AS A

Is there anything I should do to make it perfect ?

Thank you.
0
emi_sastra
Asked:
emi_sastra
  • 9
  • 6
  • 4
1 Solution
 
chaauCommented:
First that hurts the eyes is this:
CONVERT(VARCHAR, PriceDate, 112) <= CONVERT(VARCHAR, GETDATE(), 112)

Open in new window

Use just the dates:
PriceDate <= GETDATE()

Open in new window

.

You can try to convert the SalesPriceSatuan, SalesPriceSatuan1, SalesPriceSatuan2 calculations to cte, but you will not gain any performance improvements, IMHO.
0
 
PortletPaulCommented:
"hurts the eyes" :) and agreed...

"to make it perfect" -- I don't claim perfection, but some more points:

1. you are using 3 "correlated subqueries" within the select clause.
From a performance perspective such subqueries in the select clause and not good
> inside the where clause is better

each of those subqueries is also using 'TOP 1' and an order by
- ordering information is a relatively costly operation that should be minimized.
PLUS it appears all 3 subqueries are actually the same thing (? is that right)


2. Indentation/layout
It is always (no exception) best to use consistent indentation
try this as an example

3. Signposting (comments)
also very useful


4. I'm going to suggest you use row_number() instead of those subqueries
(I'll post this a bit later)
0
 
chaauCommented:
@PortletPaul:
PLUS it appears all 3 subqueries are actually the same thing (? is that right)
No, they are different in this part: AND A.Satuan2 = B.SatuanCode
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
PortletPaulCommented:
here is an alternative, and I now know those 3 subqueries are subtly different
THAT is an example of something to note in your code
;WITH
ctePrices AS (
                /* This CTE is used to calculate row_number() once only
                   that row_number() calculation provides "the most recent price"
                   */
                SELECT
                  B.SalesPrice
                , B.BarangCode
                , B.SatuanCode
                , row_number() over (partition by B.BarangCode, B.SatuanCode order by B.PriceDate DESC) as row_ref
                /* NB: row_ref = 1 is "the most recent price" as it is sorted in DESCending order*/
                FROM TMPRICELIST AS B
                WHERE B.PriceDate <- getdate()
             )
SELECT
      A.BarangCode
    , A.FullName
    , Satuan
    , 1             AS Ratio1
    , P1.SalesPrice AS SalesPriceSatuan
    , Satuan1
    , Ratio1
    , P2.SalesPrice AS SalesPriceSatuan1
    , Satuan2
    , Ratio2
    , P3.SalesPrice AS SalesPriceSatuan2
FROM TMBARANG AS A
LEFT JOIN ctePrices as P1 ON A.BarangCode = P1.BarangCode
                         AND A.Satuan = P1.SatuanCode       /* NB link on Satuan */
                         AND P1.row_ref = 1
LEFT JOIN ctePrices as P2 ON A.BarangCode = P2.BarangCode
                         AND A.Satuan1 = P2.SatuanCode       /* NB link on Satuan1 */
                         AND P1.row_ref = 1
LEFT JOIN ctePrices as P3 ON A.BarangCode = P3.BarangCode
                         AND A.Satuan2 = P3.SatuanCode       /* NB link on Satuan2 */
                         AND P1.row_ref = 1

Open in new window

NB: LEFT JOINs have been used as these have the same behaviour as a subquery in the select clause. IF possible use INNER JOINS - but only you can make that choice.
0
 
PortletPaulCommented:
>>No, they are different in this part: AND A.Satuan2 = B.SatuanCode
as I discovered and before I saw that note above
THAT is an example of something to note in your code
0
 
PortletPaulCommented:
@chaau - "your" does not mean you in the above
 - but thanks for pointing out what I missed initially

@ emi_sastra
please note that it is not necessary to convert datetime fields to varchar for comparisons
in fact that is not a good thing to do for these reasons:
1. it is slower just because you are using functions when you don't need to
2. varchar comparisons are slower than datetime-to-datetime comparisons
3. applying a function to a field removes the opportunity to the optimizer of using an index on the field you have applied the function to
0
 
chaauCommented:
@PortletPaul: In your LEFT JOINs you always refer to P1.row-ref. You probably wanted to refer to P2 and P3.
Anyway, from my experience, CTEs are usually slower than subqueries. It is up to the OP to measure the performance.
0
 
PortletPaulCommented:
oops (the deliberate error?)

                WHERE B.PriceDate <- getdate()

should of course be:

                WHERE B.PriceDate <= getdate()

see line 13 of ID: 39255271
0
 
PortletPaulCommented:
ooops2: good pick-ups chaau - thanks
I don't claim perfection
below are the compound corrections
;WITH
ctePrices AS (
                /* This CTE is used to calculate row_number() once only
                   that row_number() calculation provides "the most recent price"
                   */
                SELECT
                  B.SalesPrice
                , B.BarangCode
                , B.SatuanCode
                , row_number() over (partition by B.BarangCode, B.SatuanCode order by B.PriceDate DESC) as row_ref
                /* NB: row_ref = 1 is "the most recent price" as it is sorted in DESCending order*/
                FROM TMPRICELIST AS B
                WHERE B.PriceDate <= getdate() -- correction
             )
SELECT
      A.BarangCode
    , A.FullName
    , Satuan
    , 1             AS Ratio1
    , P1.SalesPrice AS SalesPriceSatuan
    , Satuan1
    , Ratio1
    , P2.SalesPrice AS SalesPriceSatuan1
    , Satuan2
    , Ratio2
    , P3.SalesPrice AS SalesPriceSatuan2
FROM TMBARANG AS A
LEFT JOIN ctePrices as P1 ON A.BarangCode = P1.BarangCode
                         AND A.Satuan = P1.SatuanCode       /* NB link on Satuan */
                         AND P1.row_ref = 1 
LEFT JOIN ctePrices as P2 ON A.BarangCode = P2.BarangCode
                         AND A.Satuan1 = P2.SatuanCode       /* NB link on Satuan1 */
                         AND P2.row_ref = 1 -- & correction here
LEFT JOIN ctePrices as P3 ON A.BarangCode = P3.BarangCode
                         AND A.Satuan2 = P3.SatuanCode       /* NB link on Satuan2 */
                         AND P3.row_ref = 1 -- & correction here

Open in new window

0
 
emi_sastraAuthor Commented:
Hi All,

Using at SSMS, my query, the data instantly shown, but to completely show all data required more then 15 secs.

Using at SSMS, your query, the data not instantly shown, about 3-4 secs, but to completely show all data required less than 10 secs.

Why is that ?

Thank you.
0
 
PortletPaulCommented:
the 3 correlated subqueries slow your version down
mine is probably slower to 'start display' due to the cte (i.e. it has to gather that first)

overall 33% less time (10 v 15) isn't too bad is it? were you hoping for more?

Is there any where clause that could be applied to the cte? (i..e to reduce the number of records scanned into the cte) that would improve performance of the cte approach most probably - if it's logically possible of course.
0
 
PortletPaulCommented:
this variant MIGHT improve performance (??) although I doubt it - however by using real tables the optimizer might be able to make better use of indexes - so it's definitely worth trying.
SELECT
      A.BarangCode
    , A.FullName
    , Satuan
    , 1             AS Ratio1
    , P1.SalesPrice AS SalesPriceSatuan
    , Satuan1
    , Ratio1
    , P2.SalesPrice AS SalesPriceSatuan1
    , Satuan2
    , Ratio2
    , P3.SalesPrice AS SalesPriceSatuan2
FROM TMBARANG AS A
LEFT JOIN (
                SELECT
                  B.SalesPrice
                , B.BarangCode
                , B.SatuanCode
                , row_number() over (partition by B.BarangCode, B.SatuanCode order by B.PriceDate DESC) as row_ref
                /* NB: row_ref = 1 is "the most recent price" as it is sorted in DESCending order*/
                FROM TMPRICELIST AS B
                WHERE B.PriceDate <= getdate() -- correction
          ) as P1 ON A.BarangCode = P1.BarangCode
                         AND A.Satuan = P1.SatuanCode       /* NB link on Satuan */
                         AND P1.row_ref = 1 
LEFT JOIN (
                SELECT
                  B.SalesPrice
                , B.BarangCode
                , B.SatuanCode
                , row_number() over (partition by B.BarangCode, B.SatuanCode order by B.PriceDate DESC) as row_ref
                /* NB: row_ref = 1 is "the most recent price" as it is sorted in DESCending order*/
                FROM TMPRICELIST AS B
                WHERE B.PriceDate <= getdate() -- correction
          ) as P2 ON A.BarangCode = P2.BarangCode
                         AND A.Satuan1 = P2.SatuanCode       /* NB link on Satuan1 */
                         AND P2.row_ref = 1 -- & correction here
LEFT JOIN (
                SELECT
                  B.SalesPrice
                , B.BarangCode
                , B.SatuanCode
                , row_number() over (partition by B.BarangCode, B.SatuanCode order by B.PriceDate DESC) as row_ref
                /* NB: row_ref = 1 is "the most recent price" as it is sorted in DESCending order*/
                FROM TMPRICELIST AS B
                WHERE B.PriceDate <= getdate() -- correction
          ) as P3 ON A.BarangCode = P3.BarangCode
                         AND A.Satuan2 = P3.SatuanCode       /* NB link on Satuan2 */
                         AND P3.row_ref = 1 -- & correction here

Open in new window

0
 
emi_sastraAuthor Commented:
If we use datagridview virtual mode then my query is better.
Otherwise your code better to show all the data to datagridview

Thank you.
0
 
PortletPaulCommented:
did you try the last variant at all? this is more similar to your original and may not take as long to load as the cte based approach.

nb: to pursue this any further would require knowing more about your tables structures and indexing of these and quite possibly also delving into execution plans.

Hope it's been useful anyway. Cheers, Paul.
0
 
chaauCommented:
Just a small note. Everything that is in Paul's sub-queries can be saved as a separate view. That way the SQL server can optimize them.
0
 
emi_sastraAuthor Commented:
Hi PortletPaul,

-did you try the last variant at all? this is more similar to your original and may not take as long to load as the cte based approach.
Yes, I've tried it, slow showing data and so showing completed data.

Thank you.
0
 
emi_sastraAuthor Commented:
Hi  chaau,

-Just a small note. Everything that is in Paul's sub-queries can be saved as a separate view. That way the SQL server can optimize them.
Yes.

Thank you.
0
 
emi_sastraAuthor Commented:
Hi PortletPaul,

Thank you very much for your help.
0
 
emi_sastraAuthor Commented:
Hi  chaau,

Thank you very much for your comment.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 9
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now