SIMPLIFIED QUERY

this is the way you must do it

There is no other way ?

Thank you.

no, this is the why sql works

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

Thank you.

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

How about use subquery to achieve it ?

Thank you.

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.

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.

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.

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

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

Hi jogos,

I know, but sometimes missed it.

Thank you.

Hi acperkins,

I don't want to change the structure.

Does a number of columns in grouping decrease the performance ?

Thank you.

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?

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.

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

Select allOpen 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.

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

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.

Hi acperkins,

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

Thank you.

You can certainly inspect the Execution plans for both.

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

Hi acperkins,

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

Please advise again.

Thank you.

PLAN-C.txt

It looks like Plan C should be better.

Why is better ?

Thank you.

How could I copy the execute plan data for the percentage?

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

Thank you.

>>Why is better ?<<
Educated guess, without seeing the table schema it is difficult to know for sure.

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.

>>Table schema ? You mean the 2 table structures ?<<
Yes, including all the Indexes and Constraints.

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.

I give up.

Good luck.

Ok.

Thank you very much for your help.