PureBasic Forums - English

http://www.purebasic.com
https://www.purebasic.fr/english/

sqlite in memory vs IMPBDB

https://www.purebasic.fr/english/viewtopic.php?t=85929

Page 1 of 2

sqlite in memory vs IMPBDB

Posted: Wed Dec 18, 2024 11:02 am
by 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

Re: sqlite in memory vs IMPBDB

Posted: Wed Dec 18, 2024 8:34 pm
by infratec
For the SQLite test, you have to create an index over names and countries.

Re: sqlite in memory vs IMPBDB

Posted: Wed Dec 18, 2024 8:39 pm
by idle
infratec wrote: Wed Dec 18, 2024 8:34 pm 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.

Re: sqlite in memory vs IMPBDB

Posted: Wed Dec 18, 2024 9:35 pm
by 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

Re: sqlite in memory vs IMPBDB

Posted: Wed Dec 18, 2024 9:35 pm
by 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

Re: sqlite in memory vs IMPBDB

Posted: Wed Dec 18, 2024 9:38 pm
by idle
infratec wrote: Wed Dec 18, 2024 9:35 pm 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.

Re: sqlite in memory vs IMPBDB

Posted: Wed Dec 18, 2024 9:46 pm
by infratec
Modified the listing above, to get the results sorted.
But this makes no difference in time.

Re: sqlite in memory vs IMPBDB

Posted: Wed Dec 18, 2024 9:48 pm
by infratec
idle wrote: Wed Dec 18, 2024 9:35 pm 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

Re: sqlite in memory vs IMPBDB

Posted: Wed Dec 18, 2024 10:22 pm
by idle
infratec wrote: Wed Dec 18, 2024 9:48 pm
idle wrote: Wed Dec 18, 2024 9:35 pm 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.

Re: sqlite in memory vs IMPBDB

Posted: Thu Dec 19, 2024 3:58 am
by 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

Re: sqlite in memory vs IMPBDB

Posted: Thu Dec 19, 2024 7:52 am
by 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.

Re: sqlite in memory vs IMPBDB

Posted: Thu Dec 19, 2024 8:12 am
by infratec
Added save to disk.

Re: sqlite in memory vs IMPBDB

Posted: Thu Dec 19, 2024 3:33 pm
by ebs
idle wrote: Wed Dec 18, 2024 9:35 pm 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)'

Re: sqlite in memory vs IMPBDB

Posted: Thu Dec 19, 2024 7:55 pm
by 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 :lol:

Re: sqlite in memory vs IMPBDB

Posted: Thu Dec 19, 2024 8:55 pm
by PBJim
idle wrote: Thu Dec 19, 2024 7:55 pm 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 :lol:
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. :?
All times are UTC+01:00
Page 1 of 2

Powered by phpBB® Forum Software © phpBB Limited