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
Solved

QUERY PRICE

Posted on 2013-06-17
19
176 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
  • 9
  • 6
  • 4
19 Comments
 
LVL 24

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 48

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 24

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 48

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 48

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 48

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 24

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 48

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 48

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 48

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 48

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 48

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 24

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

829 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