• Welcome to SQLitening Support Forum.
 

SQL statement with DESC keyword that works only in Local mode

Started by Jean-Pierre LEROY, October 27, 2011, 12:47:02 PM

Previous topic - Next topic

Jean-Pierre LEROY

Dear all,

I'm working on a specific project for a customer.

I've a problem with a SQL statement that works in Local mode but doesn't work in Server mode; as you can see, the only difference is the fact that I specify DESC (descending) at the end of the statement on the second field used to sort the record.


SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois"                                   ' => Works fine in both mode
SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois"      ' => Works fine in both mode
SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois DESC" ' => Works only in Local mode ?


Here is the trace of the program in LOCAL mode; the 3 SQL Statements work fine; the third one is a bit longer and that is a bit strange because there is a specific index on these two fields : (CodeIsin, AnneeMois DESC)

Local Mode,SQL Statement: SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois
After the slSel() and before slGetRow()
After the last slGetRow(), records read=185650
Start at : 16:55:53
End      : 16:55:53
Computed in 0.37 secondes

Local Mode,SQL Statement: SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois
After the slSel() and before slGetRow()
After the last slGetRow(), records read=185650
Start at : 16:55:53
End      : 16:55:54
Computed in 0.50 secondes

Local Mode,SQL Statement: SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois DESC
After the slSel() and before slGetRow()
After the last slGetRow(), records read=185650
Start at : 16:55:54
End      : 16:55:59
Computed in 5.05 secondes


Here is the trace of the program in SERVER mode; only the first 2 SQL Statements work fine; I get error -18 "Error sending or receiving message" on the third SQL statement (with the keyword DESC at the end).

Server Mode,SQL Statement: SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois
After the slSel() and before slGetRow()
After the last slGetRow(), records read=185650
Start at : 16:58:50
End      : 16:58:54
Computed in 4.30 secondes

Server Mode,SQL Statement: SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois
After the slSel() and before slGetRow()
After the last slGetRow(), records read=185650
Start at : 16:58:54
End      : 16:58:59
Computed in 4.82 secondes

Server Mode,SQL Statement: SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois DESC


Here is the test program; I can provide the database with the table with the185650 records.


#COMPILE EXE
#DIM ALL     

#Include "WIN32API.INC"   
#Include "SQLitening.Inc"
Declare Function zTrace Lib "zTrace.DLL" Alias "zTrace" (zMessage As Asciiz) As Long
%rSet_Mens = 1   

' SQLitening - Modes
%SQLitening_LOCAL_MODE  = 1
%SQLitening_SERVER_MODE = 2

Function ElapsedTime(ByVal pSecondeFin As Double, pSecondeDeb As Double) As Double
    If pSecondeDeb <= pSecondeFin Then
        ElapsedTime = pSecondeFin-pSecondeDeb
    Else
       ' 24 heures = 86400 secondes
       ElapsedTime = (86400-pSecondeDeb)+pSecondeFin       
    End If
End Function   

Function PBMain () As Long     
   
    Global gSQLiteningMode As Long     
    Global gTitle          As String : gTitle  = "SQLitening DESC issue"
    Local lDatabase        As String : lDatabase = "test.db3"
    Local lServerHostName  As String : lServerHostName =  "192.168.1.26"

    ' SQLite           
    Local lSql     As String
    Local lResult  As Long             
   
    ' Local or Server mode
    Select Case MessageBox(%HWND_DESKTOP, "Server Mode ?", ByCopy gTitle, _
                        %MB_YESNO Or %MB_ICONQUESTION Or %MB_DEFBUTTON1 Or %MB_APPLMODAL )                               
        Case %IDYES
            gSQLiteningMode = %SQLitening_SERVER_MODE                     
       
        Case %IDNO
            gSQLiteningMode = %SQLitening_LOCAL_MODE           
    End Select             
   
    ' Connect to the server -- omit this call to run in local mode
    ' or use the slSetProcessMods function to explicitly switch to
    ' local mode without dropping the server connection.  No server
    ' name will default to LocalHost. 
    If gSQLiteningMode = %SQLitening_SERVER_MODE Then slConnect lServerHostName, 51234   
           
    '----------------------------------
    ' open / create the SQLite Database
    '----------------------------------
    ' open the DataBase, create (C) if the DataBbase doesn't exist
    lResult = slOpen(lDatabase,"C")
    If lResult Then
        MessageBox(%HWND_DESKTOP," slOpen:"+slGetError+$CrLf, ByCopy gTitle, %MB_OK Or %MB_DEFBUTTON1 Or %MB_ICONERROR Or %MB_TASKMODAL)
    End If                     
       
    '--------------------                   
    ' create table 'Mois'
    '--------------------
    lSql = "CREATE TABLE IF NOT EXISTS Mois (" +_
           "CodeIsin  TEXT, " +_
           "AnneeMois TEXT, " +_
           "DateCours TEXT, " +_
           "Ouverture REAL, " +_
           "PlusHaut  REAL, " +_
           "PlusBas   REAL, " +_
           "Cloture   REAL, " +_
           "Volume    INT)  "
    lResult = slExe(lSql)
    If lResult Then
        MessageBox(%HWND_DESKTOP," slExe:"+slGetError+":"+lSql+$CrLf, ByCopy gTitle, %MB_OK Or %MB_DEFBUTTON1 Or %MB_ICONERROR Or %MB_TASKMODAL)
    End If
   
    '-------------------------------------                                                   
    ' create index 'MOISCodeIsinAnneeMois'
    '-------------------------------------
    lSql = "CREATE UNIQUE INDEX IF NOT EXISTS MOISCodeIsinAnneeMois ON Mois (CodeIsin, AnneeMois DESC)" 
    lResult = slExe(lSql)
    If lResult Then
        MessageBox(%HWND_DESKTOP," slExe:"+slGetError+":"+lSql+$CrLf, ByCopy gTitle, %MB_OK Or %MB_DEFBUTTON1 Or %MB_ICONERROR Or %MB_TASKMODAL)
    End If                       
   
    '==================================
    '============== TESTS =============
    '==================================
    Test("SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois")                                   ' => Works fine in both mode
    Test("SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois")      ' => Works fine in both mode
    Test("SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois DESC") ' => Works only in Local mode ?
   
    ' end of the program
    MsgBox "Test finalized", %MB_ICONINFORMATION Or %MB_TASKMODAL
                 
End Function

Sub Test(pSql As String)

    ' date & time
    Local lHeureDeb    As String
    Local lHeureFin    As String
    Local lSecondeDeb  As Double
    Local lSecondeFin  As Double
   
    ' SQLite           
    Local lResult  As Long
    Local lCounter As Long   

    ' trace
    lSecondeDeb = Timer
    lHeureDeb   = Time$ 
    ZTRACE(" ")
    ZTRACE(IIf$(gSQLiteningMode = %SQLitening_SERVER_MODE, "Server Mode,","Local Mode,")+"SQL Statement: "+pSql)
    lResult = slSel(pSql, %rSet_Mens,"E")
    If lResult Then
        MessageBox(%HWND_DESKTOP," slSel:"+slGetError+$CrLf, ByCopy gTitle, %MB_OK Or %MB_DEFBUTTON1 Or %MB_ICONERROR Or %MB_TASKMODAL)   
    End If     
    ZTRACE("After the slSel() and before slGetRow()")   
   
    ' while we have some records
    Do While slGetRow(%rSet_Mens)   
        Incr lCounter                               
    Loop
   
    ' close the RecordSet
    slCloseSet(%rSet_Mens)
               
    ' trace
    lSecondeFin = Timer
    lHeureFin   = Time$                                                           
    ZTRACE("After the last slGetRow(), records read="+Format$(lCounter))
    ZTRACE("Start at : "      +lHeureDeb)   
    ZTRACE("End      : "      +lHeureFin)
    ZTRACE("Computed in " +Format$(ElapsedTime(lSecondeFin,lSecondeDeb),"0.00")+" secondes")

End Sub


Any ideas ?

Thanks,
Jean-Pierre

Fred Meier

The probable causes of -18 error are:
Quote%SQLitening_SendOrReceiveError      = -18 - Error sending or receiving message
                                             -- Prior connect failed, server went away, TCP time-out, or message length error
You are probably getting a TCP time-out. The default time-out is 30 seconds. Increase it in the slConnect.
Quote'      Tn = Where n is the how long a TCP SEND/ RECV should
'           wait for completion, in milliseconds (mSec). The
'           default timeout is 30000 milliseconds (30 seconds).
'           This wait should be set to be at least 3 times as
'           long as the wait for a database lock which that
'           default is 10000 milliseconds (10 seconds) and is
'           set in slOpen and can be changed in slSetProcessMods.
There is something else in play -- why in local mode does the Desc take approx 5 times longer?  I ran some tests using the Sample.DB3 and the times are approx the same Asc vs Desc. I would try removing the Desc from the Create Index and see if the speed is approx the same. There is this in the SQLite Create Index doc that I don't completely understand.
QuoteEach column name can be followed by one of the "ASC" or "DESC" keywords to indicate sort order. The sort order may or may not be ignored depending on the database file format. The "legacy" file format ignores index sort order. The descending index file format takes index sort order into account. Only copies of SQLite newer than version 3.3.0 (released on 2006-01-10) are able to understand the newer descending index file format and so for compatibility with older versions of SQLite, the legacy file format is generated by default. Use the legacy_file_format pragma to modify this behavior and generate databases that use the newer file format. Future versions of SQLite may begin to generate the newer file format by default

Jean-Pierre LEROY

Hi Fred,

I think you get the point.

Due to the fact that I use the "legacy" file format the index sort order "ASC" or "DESC" is ignore; it is the reason why in local mode the Desc takes approx 5 times longer.

Now I try to create a database with the legacy_file_format pragma initialized to OFF; in order to generate databases that use the newer file format.

Until now I try this code without success:


'----------------------------------------
    ' open / create an empty database
    ' in order to be able to apply the pragma
    '----------------------------------------
    lResult = slOpen("empty.db3","C")
    If lResult Then
        MessageBox(%HWND_DESKTOP," slOpen:"+slGetError+$CrLf, ByCopy lTitle, %MB_OK Or %MB_DEFBUTTON1 Or %MB_ICONERROR Or %MB_TASKMODAL)
    End If                             
   
    ' to set the "legacy file
    slExe("PRAGMA legacy_file_format=off")
    slSelStr("PRAGMA legacy_file_format")     
    slPushDatabase
       
    '----------------------------------
    ' open / create the SQLite Database
    '----------------------------------
    ' open the DataBase, create (C) if the DataBbase doesn't exist
    lResult = slOpen("test.db3","C")
    If lResult Then
        MessageBox(%HWND_DESKTOP," slOpen:"+slGetError+$CrLf, ByCopy lTitle, %MB_OK Or %MB_DEFBUTTON1 Or %MB_ICONERROR Or %MB_TASKMODAL)
    End If                     

Fred Meier

The following runs OK here but both files are created equal even though legacy format is off when second file is created so I don't yet understand.  I will do some more testing.  Post or email (If OK with you) your file with the 185650 records so I can test with it also.

   slOpen
   slOpen "LegOn.Sld","C"
   slExe "CREATE TABLE IF NOT EXISTS Mois (" +_
           "CodeIsin  TEXT, " +_
           "AnneeMois TEXT, " +_
           "DateCours TEXT, " +_
           "Ouverture REAL, " +_
           "PlusHaut  REAL, " +_
           "PlusBas   REAL, " +_
           "Cloture   REAL, " +_
           "Volume    INT)  "
   slExe "CREATE UNIQUE INDEX IF NOT EXISTS MOISCodeIsinAnneeMois ON Mois (CodeIsin, AnneeMois DESC)" 
   

   slExe "PRAGMA legacy_file_format=off"
   slOpen "LegOff.Sld","C"
   slExe "CREATE TABLE IF NOT EXISTS Mois (" +_
           "CodeIsin  TEXT, " +_
           "AnneeMois TEXT, " +_
           "DateCours TEXT, " +_
           "Ouverture REAL, " +_
           "PlusHaut  REAL, " +_
           "PlusBas   REAL, " +_
           "Cloture   REAL, " +_
           "Volume    INT)  "
   slExe "CREATE UNIQUE INDEX IF NOT EXISTS MOISCodeIsinAnneeMois ON Mois (CodeIsin, AnneeMois DESC)" 

Jean-Pierre LEROY

Hi Fred,

I tried your code but I have still the same results.

Also I checked with the software "SQlite Expert Professional" the properties of both databases; the flag "legacy_file_format" is "on" for the two databases "LegOn.Sld" and "LegOff.Sld".

The key point is to be able to create an empty database with the "legacy_file_format" pragma initialized to "off"; after that if we open this empty database, the newly created databases will use the newer file format instead of the legacy one; at least it is what it mentionned on the SQLite website http://www.sqlite.org/pragma.html#pragma_legacy_file_format

Thanks for your support.
Jean-Pierre

Fred Meier

This now works here.  I keep forgetting that a database is not actually created until the first table is added.  Therefore the pragma has to be after the slOpen.  Now that the index is desc beware your selects that are asc will take longer.

   slOpen "LegOff.Sld","C"
   slExe "PRAGMA legacy_file_format=off"
   slExe "CREATE TABLE IF NOT EXISTS Mois (" +_
           "CodeIsin  TEXT, " +_
           "AnneeMois TEXT, " +_
           "DateCours TEXT, " +_
           "Ouverture REAL, " +_
           "PlusHaut  REAL, " +_
           "PlusBas   REAL, " +_
           "Cloture   REAL, " +_
           "Volume    INT)  "
   slExe "CREATE UNIQUE INDEX IF NOT EXISTS MOISCodeIsinAnneeMois ON Mois (CodeIsin, AnneeMois DESC)" 

Jean-Pierre LEROY

Thank you Fred, everything is ok now.

Here is the trace of the program in LOCAL mode; the 3 SQL Statements work fine.
Now the second query is a bit longer; it's normal because there is no specific index on (CodeIsin, AnneeMois).
For the third query the response is perfect; this time the index (CodeIsin, AnneeMois DESC) is used by SQlite.


Local Mode,SQL Statement: SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois
After the slSel() and before slGetRow()
After the last slGetRow(), records read=181969
Start at : 23:58:54
End      : 23:58:54
Computed in 0.52 secondes

Local Mode,SQL Statement: SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois
After the slSel() and before slGetRow()
After the last slGetRow(), records read=181969
Start at : 23:58:54
End      : 23:58:58
Computed in 3.76 secondes

Local Mode,SQL Statement: SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois DESC
After the slSel() and before slGetRow()
After the last slGetRow(), records read=181969
Start at : 23:58:58
End      : 23:58:59
Computed in 0.47 secondes


In Sever Mode we have now the same behavior:


Server Mode,SQL Statement: SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois
After the slSel() and before slGetRow()
After the last slGetRow(), records read=181969
Start at : 00:08:19
End      : 00:08:23
Computed in 4.41 secondes

Server Mode,SQL Statement: SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois
After the slSel() and before slGetRow()
After the last slGetRow(), records read=181969
Start at : 00:08:23
End      : 00:08:43
Computed in 19.52 secondes

Server Mode,SQL Statement: SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois DESC
After the slSel() and before slGetRow()
After the last slGetRow(), records read=181969
Start at : 00:08:43
End      : 00:08:48
Computed in 4.73 secondes


Thank you very much for your support.
Jean-Pierre

D. Wilson

There was a post 'SQLite speed issue' (about 5 posts back). It was related to a access speed when opening a database. There is an updated DLL that makes access faster. You may want to look at that post and see if it is related to your issue

Jean-Pierre LEROY

#8
Thank you all for your support solving this issue.

For Fred and Rolf (I think you're maintaining the help file) it could be useful for other users to add in the user manual a specific section that could be called  :
----------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE INDEX that support indexes in descending order (DESC) with the PRAGMA legacy_file_format

Two extracts from SQlite documentation

Quote
Each column name can be followed by one of the "ASC" or "DESC" keywords to indicate sort order. The sort order may or may not be ignored depending on the database file format. The "legacy" file format ignores index sort order. The descending index file format takes index sort order into account. Only copies of SQLite newer than version 3.3.0 (released on 2006-01-10) are able to understand the newer descending index file format and so for compatibility with older versions of SQLite, the legacy file format is generated by default. Use the legacy_file_format pragma to modify this behavior and generate databases that use the newer file format. Future versions of SQLite may begin to generate the newer file format by default.

Quote
PRAGMA legacy_file_format;
PRAGMA legacy_file_format = boolean

This pragma sets or queries the value of the legacy_file_format flag. When this flag is on, new SQLite databases are created in a file format that is readable and writable by all versions of SQLite going back to 3.0.0. When the flag is off, new databases are created using the latest file format which might not be readable or writable by versions of SQLite prior to 3.3.0.

When the legacy_file_format pragma is issued with no argument, it returns the setting of the flag. This pragma does not tell which file format the current database is using; it tells what format will be used by any newly created databases.

The legacy_file_format pragma is initialized to OFF when an existing database in the newer file format is first opened.

The default file format is set by the SQLITE_DEFAULT_FILE_FORMAT compile-time option.

With SQLitening, you have to set the PRAGMA legacy_file_format to off, right after opening the database and before creating the tables; this database and the tables created with the newer file format will support indexes sorted in a descending order (CREATE INDEX ... DESC).

Code Example:


slOpen "MyDatabase.db3","C"
slExe "PRAGMA legacy_file_format=off"
slExe "CREATE TABLE IF NOT EXISTS MyTable (" +_
         "Field1 TEXT, " +_
         "Field2 TEXT) "
slExe "CREATE UNIQUE INDEX IF NOT EXISTS MyIndex ON MyTable (Field1, Field2 DESC)" 

----------------------------------------------------------------------------------------------------------------------------------------------------------------

Hope that helps,
Jean-Pierre

Rolf Brandt

I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu