Stan, Claudius, your feedback on fc1 ?

[Claudius Sailer]

Hi Ruslan,

Am 27. Dez 2005 um 22:53 Uhr schrieb Ruslan Zasukhin:

> On 12/27/05 9:41 PM, "Claudius Sailer" <Claudius at sailer-online.de>  
> wrote:
>
>> V4RB1.10
>
>> Results in sec FIRSTPART / SECOND PART / THIRDPART
>>
>> 0,78 / 0,03 / 1,43
>> 0,70 / 0,05 / 1,31
>> 0,81 / 0,05 / 1,40
>> 0,73 / 0,06 / 1,30
>> 0,71 / 0,05 / 1,23
>>
>>
>> V4RB2.1fc1
>
>> Results in sec FIRSTPART / SECONDPART / THIRDPART
>>
>> 0,000 / 1,30 / 1,43
>> 0,016 / 1,11 / 1,56
>> 0,030 / 1,05 / 1,38
>> 0,016 / 1,53 / 1,08
>> 0,030 / 1,06 / 1,06
>
> Claudius,
>
> Do I read this correctly:
>
> * first column -- V2 wins. And Hmm, it wins A LOTS ?
>
>         0,700 / 0.016 = 43 times faster ?
>         0,810 / 0.030 = 27 times faster ?

you can't compare FIRSTPART V4RB1.10 with V4RB2.1


> * second column -- V2 is slower of V1 about 40 times.
>
> * third column -- speed comparable..
>
> So we need check your UNION query to see what we can speedup...

HISTORY:

I had same coding as I used in V4RB1.10 and I saw that V4RB2.X was  
2-3 times slower. But I knew V4RB2.X is much much more better in SQL  
as V4RB1.10. So I used following SQL but (V4RB2 Call #811) it isn't  
supported yet.

     SQLStringBuild="SELECT B.RecID, B.BDatum, KZ.Konto_Name,  
K.Kategorie_Name,"
     SQLStringBuild=SQLStringBuild + " CASE WHEN  
B.Ziel_Konto=KZ.RecID THEN B.Betrag ELSE null END,"
     SQLStringBuild=SQLStringBuild + " CASE WHEN  
B.Herk_Konto=KZ.RecID THEN B.Betrag ELSE null END,"
     SQLStringBuild=SQLStringBuild + " B.Kommentar, B.Blocked"
     SQLStringBuild=SQLStringBuild + " FROM Buchungen B, Kategorien  
K, Konten KZ"
     SQLStringBuild=SQLStringBuild + " WHERE B.Kategorie_ID=K.RecID"
     SQLStringBuild=SQLStringBuild + " AND ((B.Ziel_Konto=KZ.RecID  
AND B.Herk_Konto='"+CheckString+"')"
     SQLStringBuild=SQLStringBuild + " OR (B.Herk_Konto=KZ.RecID AND  
B.Ziel_Konto='"+CheckString+"'))"
     SQLStringBuild=SQLStringBuild + " AND B.Deleted='0'"
     SQLStringBuild=SQLStringBuild + " ORDER BY B.BDatum,  
KZ.Konto_Name, K.Kategorie_Name"


So I changed to UNION.

SQLStringBuild="SELECT B.RecID, B.BDatum, KZ.Konto_Name,  
K.Kategorie_Name, B.Betrag, null, B.Kommentar, B.Blocked"
     SQLStringBuild=SQLStringBuild + " FROM Buchungen B, Kategorien  
K, Konten KZ"
     SQLStringBuild=SQLStringBuild + " WHERE B.Kategorie_ID=K.RecID"
     SQLStringBuild=SQLStringBuild + " AND (B.Ziel_Konto=KZ.RecID AND  
B.Herk_Konto='"+CheckString+"') AND B.Deleted='0'"
     SQLStringBuild=SQLStringBuild + " UNION"
     SQLStringBuild=SQLStringBuild + " SELECT B.RecID, B.BDatum,  
KZ.Konto_Name, K.Kategorie_Name, null, B.Betrag, B.Kommentar, B.Blocked"
     SQLStringBuild=SQLStringBuild + " FROM Buchungen B, Kategorien  
K, Konten KZ"
     SQLStringBuild=SQLStringBuild + " WHERE B.Kategorie_ID=K.RecID"
     SQLStringBuild=SQLStringBuild + " AND (B.Herk_Konto=KZ.RecID AND  
B.Ziel_Konto='"+CheckString+"') AND B.Deleted='0'"
     SQLStringBuild=SQLStringBuild + " ORDER BY B.BDatum,  
KZ.Konto_Name, K.Kategorie_Name"


when you now say that UNION is much much slower than filling other  
table with 2 SQL-Statements and reading from this table I will check.  
Or do you wants to tell me too that UNION ALL could be a Solution?
At the moment I don't have experiences with RAM Table (temporary  
tables). I hope to find some examples in V4RB2 Installation...

I know from sybase that temporary tables with 2 or more filling SQL- 
Selects are much much faster as one UNION, but my solution wasn't  
UNION it was other SQL with Call #811.

What would be your idea for the best coding?


bye


Claudius

-- 
G4/733 QS / MacOS X 10.4de / RB 2005r4/ Valentina 1.10.0 & 2.0.4
Homepage    http://www.ClaSai.de
iChat        ryhoruk
RealBasic ListBoxes: [ I feel the need...the need for speed!!! ]