SIMPLIFIED QUERY

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.
LVL 1
emi_sastraAsked:
Who is Participating?
 
Anthony PerkinsCommented:
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
 
EyalCommented:
this is the way you must do it
0
 
emi_sastraAuthor Commented:
There is no other way ?

Thank you.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
EyalCommented:
no, this is the why sql works
0
 
emi_sastraAuthor Commented:
I am sure, it should be another way.
For example by not joining the 2 tables.

Thank you.
0
 
Pratima PharandeCommented:
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
 
emi_sastraAuthor Commented:
How about use subquery to achieve it ?

Thank you.
0
 
sachinpatil10dCommented:
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
 
emi_sastraAuthor Commented:
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
 
jogosCommented:
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
 
hspoulsenCommented:
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
 
Anthony PerkinsCommented:
>>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
 
emi_sastraAuthor Commented:
Hi jogos,

I know, but sometimes missed it.

Thank you.
0
 
emi_sastraAuthor Commented:
Hi acperkins,

I don't want to change the structure.

Does a number of columns in grouping decrease the performance ?

Thank you.


0
 
jogosCommented:
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
 
emi_sastraAuthor Commented:
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
 
jogosCommented:
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
 
Anthony PerkinsCommented:
>>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
 
emi_sastraAuthor Commented:
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
 
emi_sastraAuthor Commented:
Hi acperkins,

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

Thank you.
0
 
Anthony PerkinsCommented:
You can certainly inspect the Execution plans for both.
0
 
emi_sastraAuthor Commented:
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
 
emi_sastraAuthor Commented:
Hi acperkins,

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

Please advise again.

Thank you.

PLAN-C.txt
0
 
Anthony PerkinsCommented:
It looks like Plan C should be better.
0
 
emi_sastraAuthor Commented:
Why is better ?

Thank you.
0
 
emi_sastraAuthor Commented:
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
 
Anthony PerkinsCommented:
>>Why is better ?<<
Educated guess, without seeing the table schema it is difficult to know for sure.
0
 
emi_sastraAuthor Commented:
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
 
Anthony PerkinsCommented:
>>Table schema ? You mean the 2 table structures ?<<
Yes, including all the Indexes and Constraints.
0
 
emi_sastraAuthor Commented:
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
 
Anthony PerkinsCommented:
I give up.

Good luck.
0
 
emi_sastraAuthor Commented:
Ok.

Thank you very much for your help.
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.