Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

QUERY PRICE

Posted on 2013-06-17
19
Medium Priority
?
193 Views
Last Modified: 2013-06-18
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
Comment
Question by:emi_sastra
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 4
19 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39255210
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39255250
"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
 
LVL 25

Expert Comment

by:chaau
ID: 39255252
@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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 49

Expert Comment

by:PortletPaul
ID: 39255271
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39255282
>>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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39255294
@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
 
LVL 25

Expert Comment

by:chaau
ID: 39255296
@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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39255297
oops (the deliberate error?)

                WHERE B.PriceDate <- getdate()

should of course be:

                WHERE B.PriceDate <= getdate()

see line 13 of ID: 39255271
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39255311
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 39255365
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39255395
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39255417
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 39255456
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39255516
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
 
LVL 25

Expert Comment

by:chaau
ID: 39255561
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 39255569
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 39255570
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
 
LVL 1

Author Closing Comment

by:emi_sastra
ID: 39258037
Hi PortletPaul,

Thank you very much for your help.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 39258039
Hi  chaau,

Thank you very much for your comment.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question