?
Solved

SIMPLIFIED QUERY

Posted on 2011-10-10
32
Medium Priority
?
248 Views
Last Modified: 2012-05-12
Hi All,

I have below query :

SELECT A.NoTransaksi, A.TglTransaksi,
 SUM(B.NilaiTransaksi * (B.NilaiKurs / A.NilaiKurs))  AS NilaiTransaksi,
 A.NilaiKurs,
 SUM(B.NilaiTransaksi * (B.NilaiKurs / A.NilaiKurs)) AS NilaiTransaksiRupiah,
 A.Keterangan, A.CrtId, A.CrtDate, A.UpdId, A.UpdDate, BankAccNo
FROM THBANK2011 AS A  
LEFT JOIN TDBANK2011 AS B
ON A.NoTransaksi = B.NoTransaksi

WHERE RIGHT(TipeTransaksi, 1) = 'K'
AND BankAccNo = '002-323-288-7       ' AND CONVERT(Char(8), TglTransaksi, 112)
BETWEEN '20111010' AND '20111010'

GROUP BY A.NoTransaksi, A.TglTransaksi, A.NilaiKurs, A.Keterangan , A.CrtId, A.CrtDate, A.UpdId, A.UpdDate, BankAccNo

ORDER BY NoTransaksi ASC

Could it be made simpler ? So many column of grouping.

Thank you.
0
Comment
Question by:emi_sastra
  • 16
  • 8
  • 3
  • +4
32 Comments
 
LVL 15

Expert Comment

by:Eyal
ID: 36940981
this is the way you must do it
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36941009
There is no other way ?

Thank you.
0
 
LVL 15

Expert Comment

by:Eyal
ID: 36941054
no, this is the why sql works
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:emi_sastra
ID: 36941344
I am sure, it should be another way.
For example by not joining the 2 tables.

Thank you.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36941451
If you want the same result this is the way you can do it..

If you want less grouping you need remove soem columns from the list of select
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36941655
How about use subquery to achieve it ?

Thank you.
0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36941689
Disadvantages of using subquery

When subquery is used, the database server (actually the query optimizer) may need to perform additional steps, such as sorting, before the results from the subquery are used. If a query that contains subqueries can be rewritten as a join, you should use join rather than subqueries. This is because using join typically allows the query optimizer to retrieve data in the most efficient way. In other words, The optimizer is more mature for MySQL for joins than for subqueries, so in many cases a statement that uses a subquery can be executed more efficiently if you rewrite it as a join.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36941762
Ok.

How about using 2 queries :

1. Sum the Detail data (NoTransaksi, SUM())
2. Join with Header Data.

This needs more code, but how about its performance ?

Thank you.
0
 
LVL 25

Expert Comment

by:jogos
ID: 36941784
The thing I would advise you to change is always use table aliases even if it's not needed at that moment
For example A.BankAccNo in place of just BankAccNo , also in the order by.
So even when tables change your statement never becomes invallid (important) and (less important) nobody ever hast to doubt 2 seconds with thinking from wich table it comes.

0
 
LVL 3

Expert Comment

by:hspoulsen
ID: 36941787
if you are after speed, I will suggest that you change your where clauses:
from
WHERE RIGHT(TipeTransaksi, 1) = 'K'
to
WHERE TipeTransaksi like 'K%'

The latter is SARGable  (in SQL Server 2005 and above), if you are on SQL Server 2000
it could be WHERE TipeTransaksi >= 'K' and TipeTransaksi < 'L'

The same thing (SARGable) goes for your date range:
from
AND CONVERT(Char(8), TglTransaksi, 112)  BETWEEN '20111010' AND '20111010'
to
AND TglTransaksi BETWEEN '20111010' AND '20111011'

If you (like me) do not want to list the fields twice, then type
Max(fieldname1) as MaxFieldname1
and just group by the field you need to group by.

If I have to repeat this job, then I generally write a script to generate the SQL code. But that depends on the circumstances.

Best regards,
Henrik



0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36946023
>>Could it be made simpler ? So many column of grouping.<<
It is possible.  But we cannot tell for sure without seeing the table schema or knowing whether changing the table structure is an option.

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36946372
Hi jogos,

I know, but sometimes missed it.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36946379
Hi acperkins,

I don't want to change the structure.

Does a number of columns in grouping decrease the performance ?

Thank you.


0
 
LVL 25

Expert Comment

by:jogos
ID: 36946961
Everything that you want in your SELECT where it is not accompanied with a min/max/sum ..  must be in your group by.   What else must sql do with it when there are different values?
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36947828
I know.

Thus, what I asked is if grouping with 1 column and 10 columns. Could be a big different isn't it ?

If it is the split it into 2 queries with less grouping columns should be faster ?

Thank you.
0
 
LVL 25

Expert Comment

by:jogos
ID: 36948090
This is how you can write it with an easier group by. If it will be faster you have to measure.
SELECT A.NoTransaksi, A.TglTransaksi, 
 C.NilaiTransaksi,  A.NilaiKurs, 
 C.NilaiTransaksiRupiah,
 A.Keterangan, A.CrtId, A.CrtDate, A.UpdId, A.UpdDate, A.BankAccNo 
FROM THBANK2011 AS A  
LEFT JOIN 
 (select SUM(B.NilaiTransaksi * (B.NilaiKurs / A.NilaiKurs))  AS NilaiTransaksi,
   SUM(B.NilaiTransaksi * (B.NilaiKurs / A.NilaiKurs)) AS NilaiTransaksiRupiah
  FROM TDBANK2011 AS B
  WHERE A.NoTransaksi = B.NoTransaksi
  group by B.NoTransaksi
 ) AS C
ON A.NoTransaksi = C.NoTransaksi
WHERE RIGHT(A.TipeTransaksi, 1) = 'K' 
AND A.BankAccNo = '002-323-288-7       ' 
AND CONVERT(Char(8), A.TglTransaksi, 112) BETWEEN '20111010' AND '20111010' 
GROUP BY A.NoTransaksi
ORDER BY A.NoTransaksi ASC

Open in new window

But surely because of the functions RIGHT and CONVERT a possible index containing these columns won't be used -> there you can gain on date.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36949188
>>Thus, what I asked is if grouping with 1 column and 10 columns. Could be a big different isn't it ?<<
More than likely.  Unfortunately as I stated before without knowing your schema the best we can do is make educated guesses as to any improvements you can make.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36949763
Hi jogos,

Your code is nice.

(select  SUM(B.NilaiTransaksi * (B.NilaiKurs / A.NilaiKurs))  AS NilaiTransaksi,
   SUM(B.NilaiTransaksi * (B.NilaiKurs / A.NilaiKurs)) AS NilaiTransaksiRupiah
  FROM TDBANK2011 AS B
  WHERE A.NoTransaksi = B.NoTransaksi
  group by B.NoTransaksi
 ) AS C

should be :


(select  B.NoTransaksi, SUM(B.NilaiTransaksi * (B.NilaiKurs / A.NilaiKurs))  AS NilaiTransaksi,
   SUM(B.NilaiTransaksi * (B.NilaiKurs / A.NilaiKurs)) AS NilaiTransaksiRupiah
  FROM TDBANK2011 AS B
  WHERE A.NoTransaksi = B.NoTransaksi
  group by B.NoTransaksi
 ) AS C


I've got run error :

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "A.NoTransaksi" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "A.NilaiKurs" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "A.NilaiKurs" could not be bound.

I can not see the problem.

What's wrong ?

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36949968
Hi acperkins,

I would like to test the 2 queries for comparison.
Is there tools for testing it ?

Thank you.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36950074
You can certainly inspect the Execution plans for both.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36952761
Hi acperkins,

Below are the query and its execute plans :

;WITH CTE AS (

SELECT B.NoTransaksi, SUM(B.NilaiTransaksi / B.NilaiKurs )  AS NilaiTransaksi,
   SUM(B.NilaiTransaksi / B.NilaiKurs ) AS NilaiTransaksiRupiah
  FROM TDBANK2011 AS B
  GROUP by B.NoTransaksi
)
 
SELECT  A.NoTransaksi, A.TglTransaksi,
  (C.NilaiTransaksi / A.NilaiKurs) AS NilaiTransaksi,  A.NilaiKurs,
 (C.NilaiTransaksiRupiah / A.NilaiKurs) AS NilaiTransaksiRupiah,
 A.Keterangan, A.CrtId, A.CrtDate, A.UpdId, A.UpdDate, A.BankAccNo
FROM THBANK2011 AS A  
LEFT JOIN CTE AS C
ON A.NoTransaksi = C.NoTransaksi
 
ORDER BY A.NoTransaksi ASC


PLAN B:

SELECT  A.NoTransaksi, A.TglTransaksi,
  (C.NilaiTransaksi / A.NilaiKurs) AS NilaiTransaksi,  A.NilaiKurs,
 (C.NilaiTransaksiRupiah / A.NilaiKurs) AS NilaiTransaksiRupiah,
 A.Keterangan, A.CrtId, A.CrtDate, A.UpdId, A.UpdDate, A.BankAccNo
FROM THBANK2011 AS A  
LEFT JOIN
 (SELECT B.NoTransaksi, SUM(B.NilaiTransaksi * B.NilaiKurs )  AS NilaiTransaksi,
   SUM(B.NilaiTransaksi * B.NilaiKurs ) AS NilaiTransaksiRupiah
  FROM TDBANK2011 AS B
  GROUP by B.NoTransaksi
 ) AS C
ON A.NoTransaksi = C.NoTransaksi
WHERE RIGHT(A.TipeTransaksi, 1) = 'K'
AND A.BankAccNo = '002-323-288-7       '
AND CONVERT(Char(8), A.TglTransaksi, 112) BETWEEN '20111010' AND '20111010'
 
ORDER BY A.NoTransaksi ASC


PLAN C :

USE SB_FIN_SBA_JKT

SELECT A.NoTransaksi, A.TglTransaksi,
 SUM(B.NilaiTransaksi * (B.NilaiKurs / A.NilaiKurs))  AS NilaiTransaksi,
 A.NilaiKurs,
 SUM(B.NilaiTransaksi * (B.NilaiKurs / A.NilaiKurs)) AS NilaiTransaksiRupiah,
 A.Keterangan, A.CrtId, A.CrtDate, A.UpdId, A.UpdDate, BankAccNo
FROM THBANK2011 AS A  
LEFT JOIN TDBANK2011 AS B
ON A.NoTransaksi = B.NoTransaksi

WHERE RIGHT(A.TipeTransaksi, 1) = 'K'
AND A.BankAccNo = '002-323-288-7       '
AND CONVERT(Char(8), A.TglTransaksi, 112) BETWEEN '20111010' AND '20111010'
GROUP BY A.NoTransaksi, A.TglTransaksi, A.NilaiKurs, A.Keterangan , A.CrtId, A.CrtDate, A.UpdId, A.UpdDate, BankAccNo

ORDER BY NoTransaksi ASC

I have no idea how to read the execute plans.

Would you please explain about it ? Which plan is the best.

Please rename attached file extension to sqlplan

Thank you.

PLAN-A.TXT
PLAN-B.TXT
PLAN-C.TXT
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 36952978
Plan A and B are practically the same, except for the fact that Plan B is warning you that you are missing an index on THBANK2011.  Perhaps if you added that index, Plan B would be best.  Plan C contains no relevant information.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36953352
Hi acperkins,

Please see the C plan. Previous file was wrongly saved.

Please advise again.

Thank you.

PLAN-C.txt
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36959773
It looks like Plan C should be better.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36959874
Why is better ?

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36959885
How could I copy the execute plan data for the percentage?

If I could then I could present it here for comparison.

Thank you.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36961578
>>Why is better ?<<
Educated guess, without seeing the table schema it is difficult to know for sure.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36961611
Educated guess, without seeing the table schema it is difficult to know for sure.
- We can not just see from the execution plan alone to judge the performance ?.

Table schema ? You mean the 2 table structures ?

Thank you.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36961675
>>Table schema ? You mean the 2 table structures ?<<
Yes, including all the Indexes and Constraints.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36961764
Those tables are simple :

1. THBANK2011  with primary key NoTransaksi.

2. TDBANK2011 with primary key NoTransaksi + NoSeq

3. No indexes

4. TDBANK2011 AS FOREIGN KEY OF THBANK2011  

Is it enough ?

Thank you.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36963684
I give up.

Good luck.
0
 
LVL 1

Author Closing Comment

by:emi_sastra
ID: 36965942
Ok.

Thank you very much for your help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

831 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