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.
LVL 1
emi_sastraAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
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
 
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
 
PortletPaulfreelancerCommented:
"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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
>>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
 
PortletPaulfreelancerCommented:
@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
 
PortletPaulfreelancerCommented:
oops (the deliberate error?)

                WHERE B.PriceDate <- getdate()

should of course be:

                WHERE B.PriceDate <= getdate()

see line 13 of ID: 39255271
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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.