sqlite in memory vs IMPBDB

[idle]

I would like to get an unbiased test of sqlite using this file customers-100000.csv
From github repo https://github.com/datablist/sample-csv ... me-ov-file

1st query. First names beginning with "a" from New Zealand

2nd query customers from France

Code: Select all

Structure CUSTOMER 
  Index.s
  CustomerId.s
  FirstName.s
  LastName.s
  Company.s
  City.s
  Country.s
  Phone1.s
  Phone2.s
  Email.s
  SubscriptionDate.s
  Website.s
EndStructure 

Procedure loadCVS(*db.IIMPBDB,file.s) ;import the CVS to DB 
  
  Protected fn,str.s,ct,ct1 
  fn = ReadFile(#PB_Any,file.s) 
  If fn
    str = ReadString(fn,#PB_UTF8) 
    Debug str 
    While Not Eof(fn) 
      str = ReadString(fn,#PB_UTF8) 
      *cust.Customer = AllocateStructure(customer)
      If *cust 
        *cust\Index = StringField(str,1,",") 
        *cust\CustomerId = StringField(str,2,",") 
        *cust\FirstName = StringField(str,3,",") 
        *cust\LastName = StringField(str,4,",") 
        *cust\Company = StringField(str,5,",")
        *cust\City = StringField(str,6,",")
        *cust\Country = StringField(str,7,",")
        *cust\Phone1  = StringField(str,8,",")
        *cust\Phone2 = StringField(str,9,",")
        *cust\Email = StringField(str,10,",")
        *cust\SubscriptionDate = StringField(str,11,",")
        *cust\Website = StringField(str,12,",")
        
        ;insert into sqlite
        
        
      Else 
        Debug "error"
      EndIf 
    Wend
  EndIf   

EndProcedure 

Global DBfile.s = GetTemporaryDirectory() + "customers.json"

*db.IIMPBDB = IMPBDB_OpenDB("customers",DBfile,0) 

If FileSize(DBfile) < 1 
  loadCVS(*db,GetPathPart(ProgramFilename())+"customers-100000.csv")  
EndIf 

;The pb query's 
*ls.IMPBDB_List = *db\Enum(0,"names:A") ;query names beginning with A 
ForEach *ls\results()                      
  *cust.customer = *ls\results()\value
  If UCase(*cust\Country) = "NEW ZEALAND"  ;filter the results for country   
    PrintN(*cust\FirstName + " " + *cust\LastName)  
  EndIf   
Next   
FreeStructure(*ls) 

*ls.IMPBDB_List = *db\Enum(0,"countries:FRANCE") 
ForEach *ls\results() 
  *cust.customer = *ls\results()\value
  PrintN(*cust\FirstName + " " + *cust\LastName)
Next   
FreeStructure(*ls) 

IMPBDB results single thread on 2.75ghz I5

Customers Names beginning with "A" from New Zealand
Query time 2ms
Aaron Woodward
...
Austin Schmitt

Customers from France
Query time 0ms
Billy Benjamin
...
Jackie Phelps

I'm just curious to see how SQLite performs

[infratec]

For the SQLite test, you have to create an index over names and countries.

[idle]

For the SQLite test, you have to create an index over names and countries.

I'm doing a SQLite version but I probably wont do it as efficient as it should. I posted before I went to sleep, will post the sqlite today if I get time to do it.

[infratec]

Both needs 0ms:

Code: Select all

;
; https://drive.google.com/uc?id=1ZCVi_08A8W0f6q-2sVHDPyGzC0S7o4r4&export=download
;

EnableExplicit

Structure ListStructure
  ;Index$
  ;Customer_Id$
  First_Name$
  Last_Name$
  ;Company$
  ;City$
  Country$
  ;Phone_1$
  ;Phone_2$
  ;Email$
  ;SubscriptionDate$
  ;Website$
EndStructure

Define.i DB, File, SaveDB, SaveHandle
Define.q StartTime, EndTime
Define SQL$, Line$

NewList CustomerList.ListStructure()

UseSQLiteDatabase()

ImportC ""
  sqlite3_backup_init(pDest, zDestName.p-utf8, pSource, zSourceName.p-utf8)
  sqlite3_backup_step(backupHandle, nPage)
  sqlite3_backup_finish(backupHandle)
EndImport


DB = OpenDatabase(#PB_Any, ":memory:", "", "")
If DB
  
  SQL$ = "CREATE TABLE customers ("
  SQL$ + "'Index' INTEGER,"
  SQL$ + "'Customer_Id' text,"
  SQL$ + "'First_Name' text,"
  SQL$ + "'Last_Name' text,"
  SQL$ + "'Company' text,"
  SQL$ + "'City' text,"
  SQL$ + "'Country' text,"
  SQL$ + "'Phone_1' text,"
  SQL$ + "'Phone_2' text,"
  SQL$ + "'Email' text,"
  SQL$ + "'Subscription_Date' text,"
  SQL$ + "'Website' text)"
  
  If DatabaseUpdate(DB, SQL$)
    File = ReadFile(#PB_Any, "customers-100000.csv")
    If File
      Line$ = ReadString(File)
      While Not Eof(File)
        Line$ = ReadString(File)
        Line$ = ReplaceString(Line$, "'", "''")
        SQL$ = "INSERT INTO customers VALUES ("
        SQL$ + StringField(Line$, 1, ",") + ","
        SQL$ + "'" + StringField(Line$, 2, ",") + "',"
        SQL$ + "'" + StringField(Line$, 3, ",") + "',"
        SQL$ + "'" + StringField(Line$, 4, ",") + "',"
        SQL$ + "'" + StringField(Line$, 5, ",") + "',"
        SQL$ + "'" + StringField(Line$, 6, ",") + "',"
        SQL$ + "'" + StringField(Line$, 7, ",") + "',"
        SQL$ + "'" + StringField(Line$, 8, ",") + "',"
        SQL$ + "'" + StringField(Line$, 9, ",") + "',"
        SQL$ + "'" + StringField(Line$, 10, ",") + "',"
        SQL$ + "'" + StringField(Line$, 11, ",") + "',"
        SQL$ + "'" + StringField(Line$, 12, ",") + "')"
        ;Debug SQL$
        If DatabaseUpdate(DB, SQL$) = 0
          Debug DatabaseError()
        EndIf
      Wend
      CloseFile(File)
      
      SQL$ = "CREATE INDEX First_Name_Index ON customers (First_Name)"
      If DatabaseUpdate(DB, SQL$) = 0
        Debug DatabaseError()
      EndIf
      
      SQL$ = "CREATE INDEX Country_Index ON customers (Country)"
      If DatabaseUpdate(DB, SQL$) = 0
        Debug DatabaseError()
      EndIf
      
    EndIf
    
    
    If DatabaseQuery(DB, "SELECT count(*) FROM customers")
      If NextDatabaseRow(DB)
        Debug "Records in DB: " + GetDatabaseString(DB, 0)
      EndIf
    EndIf
    
    Debug ""
    
    StartTime = ElapsedMilliseconds()
    SQL$ = "SELECT First_Name, Last_Name, Country FROM customers WHERE First_Name LIKE 'A%' AND Country = 'New Zealand' ORDER BY First_Name, Last_Name"
    DisableDebugger
    If DatabaseQuery(DB, SQL$)
      While NextDatabaseRow(DB)
        AddElement(CustomerList())
        CustomerList()\First_Name$ = GetDatabaseString(DB, 0)
        CustomerList()\Last_Name$ = GetDatabaseString(DB, 1)
        CustomerList()\Country$ = GetDatabaseString(DB, 2)
      Wend
      EndTime = ElapsedMilliseconds()
    Else
      Debug DatabaseError()
    EndIf
    EnableDebugger
    Debug "Time: " + Str(EndTime - StartTime) + "ms"
    ForEach CustomerList()
      Debug CustomerList()\First_Name$ + " " + CustomerList()\Last_Name$ + " " + CustomerList()\Country$
    Next
    
    
    Debug ""
    
    ClearList(CustomerList())
    
    StartTime = ElapsedMilliseconds()
    SQL$ = "SELECT First_Name, Last_Name, Country FROM customers WHERE Country = 'France' ORDER BY First_Name, Last_Name"
    DisableDebugger
    If DatabaseQuery(DB, SQL$)
      If DatabaseQuery(DB, SQL$)
        While NextDatabaseRow(DB)
          AddElement(CustomerList())
          CustomerList()\First_Name$ = GetDatabaseString(DB, 0)
          CustomerList()\Last_Name$ = GetDatabaseString(DB, 1)
          CustomerList()\Country$ = GetDatabaseString(DB, 2)
        Wend
        EndTime = ElapsedMilliseconds()
      EndIf
    Else
      Debug DatabaseError()
    EndIf
    EnableDebugger
    Debug "Time: " + Str(EndTime - StartTime) + "ms"
    ForEach CustomerList()
      Debug CustomerList()\First_Name$ + " " + CustomerList()\Last_Name$ + " " + CustomerList()\Country$
    Next
    
  Else
    DatabaseError()
  EndIf
  
  
  
  If FileSize("FromMemory.sq3") < 0
    File = CreateFile(#PB_Any, "FromMemory.sq3")
    If File
      CloseFile(File)
    EndIf
  EndIf
  SaveDB = OpenDatabase(#PB_Any, "FromMemory.sq3", "", "")
  If SaveDB
  
    SaveHandle = sqlite3_backup_init(DatabaseID(SaveDB), "main", DatabaseID(DB), "main")
    If SaveHandle
      sqlite3_backup_step(SaveHandle, -1)
      If sqlite3_backup_finish(SaveHandle) = 0
        Debug "Save to dis Ok"
      Else
        Debug "Save to dis failed."
      EndIf
    EndIf
    
    CloseDatabase(SaveDB)
    
  Else
    Debug DatabaseError()
  EndIf
  
  
  CloseDatabase(DB)
EndIf

[idle]

dumb question what's wrong with this query string?

CREATE TABLE customers (id INTEGER PRIMARY KEY, customerid CHAR(64), firstname CHAR(64), lastname CHAR(64), company CHAR(64), city, CHAR(64), country CHAR(64), phone1 CHAR(64), phone2 CHAR(64), email CHAR(128), subscriptiondate CHAR(64), website CHAR(64))
error inserting data! near "(": syntax error

[idle]

Both needs 0ms:

Code: Select all

EnableExplicit

Structure ListStructure
  First_Name$
  Last_Name$
  Country$
EndStructure

Define.i DB, File
Define.q StartTime, EndTime
Define SQL$, Line$

NewList CustomerList.ListStructure()

UseSQLiteDatabase()


DB = OpenDatabase(#PB_Any, ":memory:", "", "")
If DB
  
  SQL$ = "CREATE TABLE customers ("
  SQL$ + "'Index' INTEGER,"
  SQL$ + "'Customer_Id' text,"
  SQL$ + "'First_Name' text,"
  SQL$ + "'Last_Name' text,"
  SQL$ + "'Company' text,"
  SQL$ + "'City' text,"
  SQL$ + "'Country' text,"
  SQL$ + "'Phone_1' text,"
  SQL$ + "'Phone_2' text,"
  SQL$ + "'Email' text,"
  SQL$ + "'Subscription_Date' text,"
  SQL$ + "'Website' text)"
  
  If DatabaseUpdate(DB, SQL$)
    File = ReadFile(#PB_Any, "customers-100000.csv")
    If File
      Line$ = ReadString(File)
      While Not Eof(File)
        Line$ = ReadString(File)
        Line$ = ReplaceString(Line$, "'", "''")
        SQL$ = "INSERT INTO customers VALUES ("
        SQL$ + StringField(Line$, 1, ",") + ","
        SQL$ + "'" + StringField(Line$, 2, ",") + "',"
        SQL$ + "'" + StringField(Line$, 3, ",") + "',"
        SQL$ + "'" + StringField(Line$, 4, ",") + "',"
        SQL$ + "'" + StringField(Line$, 5, ",") + "',"
        SQL$ + "'" + StringField(Line$, 6, ",") + "',"
        SQL$ + "'" + StringField(Line$, 7, ",") + "',"
        SQL$ + "'" + StringField(Line$, 8, ",") + "',"
        SQL$ + "'" + StringField(Line$, 9, ",") + "',"
        SQL$ + "'" + StringField(Line$, 10, ",") + "',"
        SQL$ + "'" + StringField(Line$, 11, ",") + "',"
        SQL$ + "'" + StringField(Line$, 12, ",") + "')"
        ;Debug SQL$
        If DatabaseUpdate(DB, SQL$) = 0
          Debug DatabaseError()
        EndIf
      Wend
      CloseFile(File)
      
      SQL$ = "CREATE INDEX Last_Name_Index ON customers (Last_Name)"
      If DatabaseUpdate(DB, SQL$) = 0
        Debug DatabaseError()
      EndIf
      
      SQL$ = "CREATE INDEX Country_Index ON customers (Country)"
      If DatabaseUpdate(DB, SQL$) = 0
        Debug DatabaseError()
      EndIf
      
    EndIf
    
    If DatabaseQuery(DB, "SELECT count(*) FROM customers")
      If NextDatabaseRow(DB)
        Debug "Records in DB: " + GetDatabaseString(DB, 0)
      EndIf
    EndIf
    
    
    StartTime = ElapsedMilliseconds()
    SQL$ = "SELECT First_Name, Last_Name, Country FROM customers WHERE Last_Name LIKE 'A%' AND Country = 'New Zealand'"
    DisableDebugger
    If DatabaseQuery(DB, SQL$)
      While NextDatabaseRow(DB)
        AddElement(CustomerList())
        CustomerList()\First_Name$ = GetDatabaseString(DB, 0)
        CustomerList()\Last_Name$ = GetDatabaseString(DB, 1)
        CustomerList()\Country$ = GetDatabaseString(DB, 2)
      Wend
      EndTime = ElapsedMilliseconds()
    Else
      Debug DatabaseError()
    EndIf
    EnableDebugger
    Debug "Time: " + Str(EndTime - StartTime) + "ms"
    ForEach CustomerList()
      Debug CustomerList()\Last_Name$ + " " + CustomerList()\First_Name$ + " " + CustomerList()\Country$
    Next
    
    
    ClearList(CustomerList())
    
    StartTime = ElapsedMilliseconds()
    SQL$ = "SELECT First_Name, Last_Name, Country FROM customers WHERE Country = 'France'"
    DisableDebugger
    If DatabaseQuery(DB, SQL$)
      If DatabaseQuery(DB, SQL$)
        While NextDatabaseRow(DB)
          AddElement(CustomerList())
          CustomerList()\First_Name$ = GetDatabaseString(DB, 0)
          CustomerList()\Last_Name$ = GetDatabaseString(DB, 1)
          CustomerList()\Country$ = GetDatabaseString(DB, 2)
        Wend
        EndTime = ElapsedMilliseconds()
      EndIf
    Else
      Debug DatabaseError()
    EndIf
    EnableDebugger
    Debug "Time: " + Str(EndTime - StartTime) + "ms"
    ForEach CustomerList()
      Debug CustomerList()\Last_Name$ + " " + CustomerList()\First_Name$ + " " + CustomerList()\Country$
    Next
    
  Else
    DatabaseError()
  EndIf
  
  CloseDatabase(DB)
EndIf

Thanks for that I was having a hard time even creating the table.

[infratec]

Modified the listing above, to get the results sorted.
But this makes no difference in time.

[infratec]

dumb question what's wrong with this query string?

CREATE TABLE customers (id INTEGER PRIMARY KEY, customerid CHAR(64), firstname CHAR(64), lastname CHAR(64), company CHAR(64), city, CHAR(64), country CHAR(64), phone1 CHAR(64), phone2 CHAR(64), email CHAR(128), subscriptiondate CHAR(64), website CHAR(64))
error inserting data! near "(": syntax error

There is no datatype CHAR() in SQLite only CHARACTER() or VARCHAR()
But they are all 'converted' to TEXT

https://www.sqlite.org/datatype3.html

[idle]

dumb question what's wrong with this query string?

CREATE TABLE customers (id INTEGER PRIMARY KEY, customerid CHAR(64), firstname CHAR(64), lastname CHAR(64), company CHAR(64), city, CHAR(64), country CHAR(64), phone1 CHAR(64), phone2 CHAR(64), email CHAR(128), subscriptiondate CHAR(64), website CHAR(64))
error inserting data! near "(": syntax error

There is no datatype CHAR() in SQLite only CHARACTER() or VARCHAR()
But they are all 'converted' to TEXT

https://www.sqlite.org/datatype3.html

That would explain that then.
The sqlite in memory speed is easily double what I'm getting at the moment.

[idle]

queries looped 10 times repeated 10 times
impbdb
CPU Frequency 2.72 ghz
q1 sum time 55 ms avg cycles 13,472,035
q2 sum time 5 ms avg cycles 1,159,745 4.999 x faster

sqlite
CPU Frequency 2.72 ghz
q1 sum time 15 ms avg cycles 3,638,837 3.702 x faster
q2 sum time 23 ms avg cycles 5,797,548

[infratec]

My first index was wrong. Still set to Last_Name, but First_Name was needed.

Corrected the listing above.

Now the second query is much faster.
Please test again.

[infratec]

Added save to disk.

[ebs]

dumb question what's wrong with this query string?

CREATE TABLE customers (id INTEGER PRIMARY KEY, customerid CHAR(64), firstname CHAR(64), lastname CHAR(64), company CHAR(64), city, CHAR(64), country CHAR(64), phone1 CHAR(64), phone2 CHAR(64), email CHAR(128), subscriptiondate CHAR(64), website CHAR(64))
error inserting data! near "(": syntax error

You have an incorrect comma in 'city, CHAR(64)'

[idle]

You might be surprised that I can't see the error at all, that's dyslexic for you. I had to look at it 10 times just to find city

[PBJim]

You might be surprised that I can't see the error at all, that's dyslexic for you. I had to look at it 10 times just to find city

I understand and sympathise with you Idle, as I have recently found that a limitation with my sight is preventing me from spotting things like this quickly, as I used to be able to do. It's frustrating because I don't see things in my code. Wearing specs. helps, but they don't give me the broad view of my VDU screen that I used to enjoy.