SQL Query Comparison Processing Efficiency, Any Better Solution?

Question

I'm working in large set of data about 134 million line i would like to make a select query with a insert in a table. This is my table SQL script (SQL Fiddle).

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Id | Emitter    |   EmitterIBAN                         |  Receiver    |   ReceiverIBAN                           |         Adresss                          |     Value 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,   Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point         0002,        121.72
2,   Keene,         SK81 1004 7484 7505 6308 9259,        Torrance,       RO23 ZWTR OJKK VAU9 T5P4 2GDY,                  35197 Green Ridge Way,                   82.52
3,   Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point         0048,        51.81
4,   Korie,         ME43 9833 9830 7367 4239 60,Roy,      IL69            9686 1536 8102 2219 165,                        5 Swallow Alley,                         88.01
5,   Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point         0001,        133.99
6,   Charmine,      BG92 TOXX 8380 785I JKRQ JS,          Sarette,        MU67 RYRU 9293 5875 6859 7111 075X HR,          8 Sage Place,                            36.30
7,   Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point         0004,        186.99

And i select my data with this query

Select  count(1) as NumberOperation, 
        MAX(Emitter) as EmitterName, 
        EmitterIban, 
        MAX(Receiver) as ReceiverName, 
        ReceiverIban,
        MAX(ReceiverAddress) as ReceiverAddress,
        SUM([Value]) as SumValues
FROM TableEsperadoceTransaction
Group By EmitterIban,
         ReceiverIban

And i get the following result

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NumberOperation | Emitter    |   EmitterIBAN                         |  Receiver    |   ReceiverIBAN                           |         Adresss                          |     SumValue 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4,                Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point           0002,      494,51
1,                Keene,         SK81 1004 7484 7505 6308 9259,        Torrance,       RO23 ZWTR OJKK VAU9 T5P4 2GDY,                  35197 Green Ridge Way,                   82.52
1,                Korie,         ME43 9833 9830 7367 4239 60,Roy,      IL69            9686 1536 8102 2219 165,                        5 Swallow Alley,                         88.01
1,                Charmine,      BG92 TOXX 8380 785I JKRQ JS,          Sarette,        MU67 RYRU 9293 5875 6859 7111 075X HR,          8 Sage Place,                            36.30

I also have this solution

SELECT DISTINCT *
FROM   (SELECT Count(1)     AS NumberOperation, 
               emitteriban  AS _EmitterIban, 
               receiveriban AS _ReceiverIban, 
               Sum([value]) AS SumValues 
        FROM   tableesperadocetransaction 
        GROUP  BY emitteriban, 
                  receiveriban) tmp_T 
       LEFT JOIN tableesperadocetransaction 
              ON tableesperadocetransaction.emitteriban = tmp_T._emitteriban 
                 AND tableesperadocetransaction.receiveriban = 
                     tmp_T._receiveriban  

And i would like to know what's the best solution between this two and if there's query more efficient than that?

Thanks

Answer 1

The second query is slower because:

1. It has a LEFT JOIN
2. It has a sub-query
3. It has a SELECT DISTINCT
4. Has a * instead of column names

The first one is the most natural way of doing this.

There is a lot about how to improve performance of queries and what to avoid. See for example: MSDN on improving queries