Solved

QUERY PRICE

Posted on 2013-06-17
19
188 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

631 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