*** 4,24 ****
As a side note, these large databases were 3 million rows plus and SQLite still dealt with this much data gracefully
! I have also included a bonus "dumb thing to do" if you read far enough (or cheat and just skip to the last item). It's dumb, and it happened to me, so I felt the need to give fair waring to everyone!
These mostly relate to Windows and Delphi environments but may help others, and refers to SQLite 3.1.0:
! **One: Talk to the SQLite exported functions directly**
! I recommend you try and use the SQLite functions directly, ExecSQLing stuff down to the DB is better than trying to use some faked Locate or Seek function.
Even if you have to change the way you code, I recommend it. From what I experienced, the exported functions from the DLL were simple to use and did everything you wanted, though perhaps slightly differently from the Delphi DBE way some people may be used to
! I found a simple SqliteWrappers worked better than trying to use a bunch of components pretending to be the BDE, but of course, a lot depends on what you want to do and how confident you are using exported DLL functions
! SQL Statements like INSERT OR REPLACE INTO can replace entire wads of old _Locate IF Found Update ELSE Insert_ code, keeping the code neater AND speeding it up
**Two: Indexes and DB structure are important!**
--- 4,37 ----
As a side note, these large databases were 3 million rows plus and SQLite still dealt with this much data gracefully
! I have also included a bonus "dumb thing to do" if you read far enough (or cheat and just skip to the last item). It's dumb, and it happened to me, it was my fault, so I felt the need to give fair warning to everyone!
These mostly relate to Windows and Delphi environments but may help others, and refers to SQLite 3.1.0:
! **One: Talk to the SQLite directly**
! I recommend you try and use the SQLite directly, ExecSQLing stuff down to the DB is often better than trying to use some faked Locate or Seek function. (Since SQLite doesn't have these functions, component programmers often need to produce complex and CPU/HDD expensive code to emulate the BDE functionality)
Even if you have to change the way you code, I recommend it. From what I experienced, the exported functions from the DLL were simple to use and did everything you wanted, though perhaps slightly differently from the Delphi DBE way some people may be used to
! I personally found that a simple SqliteWrappers worked well for us, allowing us to send SQL to the DB and get back exactly what we wanted quickly. Of course, a lot depends on what you want to do and how confident you are using exported DLL functions
! If you want to Append, insert or update new data into a database, SQL Statements like:
+ DB.ExecSQL('INSERT OR REPLACE INTO tableName (Field1, Field2, Field 3) ' +
+ 'VALUES (Value1, Value2, Value3)');
+
+ can replace entire wads of old code like this:
+
+ IF Locate(Field1, Value1) THEN
+ UpdateTableFunction() // ...Lots of code here
+ ELSE
+ InsertIntoTableFunction(); // ...even more code HERE
+
+ Keeping the code neater AND speeding it up. Remember to wrap Begin Transactions and End Transactions around the update SQL. We run thousands of SQL INSERTS in one transaction to great effect
+
+ This is not to say that using componants is bad, and using them can significently speed up your development time. But please do have a look at accessing SQlite through SQL (-> ExecSQL) even if you are using componants. A fine tuned SQL statement that is specific to your applications needs could well be faster than a generic solution.
**Two: Indexes and DB structure are important!**
***************
*** 33,39 ****
The default cluster size for a Windows NTFS system seems to be 4096 bytes. Setting the SQLite database page size to the same size will speed up your database on systems where the cluster size is the same
! _(Note, Linux cluster I believe to 1024 which is the default for new SQLite
databases)_
Easiest way to tell your cluster size is to defragment your drive and analyse. It tells you in there
--- 46,52 ----
The default cluster size for a Windows NTFS system seems to be 4096 bytes. Setting the SQLite database page size to the same size will speed up your database on systems where the cluster size is the same
! _(Note, Linux cluster I believe to be 1024 which is the default for new SQLite
databases)_
Easiest way to tell your cluster size is to defragment your drive and analyse. It tells you in there
***************
*** 73,85 ****
Be *VERY, VERY* careful what you name your database, especially the _extension_
! For example, if you give all your databases the extension .sdb (SQLite Database, nice name hey?) you discover that the SDB extension is already associated with APPFIX PACKAGES.
Now, here is the cute part, APPFIX is an executable/package that Windows XP recognizes, and it will, (emphasis mine) *ADD THE DATABASE TO THE SYSTEM RESTORE FUNCTIONALITY*
This means, stay with me here, every time you write ANYTHING to the database, the Windows XP system thinks a bloody executable has changed and copies your ENTIRE 800 meg database to the system restore directory....
I recommend something like DB or DAT.
Have fun!
--- 86,99 ----
Be *VERY, VERY* careful what you name your database, especially the _extension_
! For example, if you give all your databases the extension .sdb (SQLite Database, nice name hey? I thought so when I choose it anyway...) you discover that the SDB extension is already associated with APPFIX PACKAGES.
Now, here is the cute part, APPFIX is an executable/package that Windows XP recognizes, and it will, (emphasis mine) *ADD THE DATABASE TO THE SYSTEM RESTORE FUNCTIONALITY*
This means, stay with me here, every time you write ANYTHING to the database, the Windows XP system thinks a bloody executable has changed and copies your ENTIRE 800 meg database to the system restore directory....
I recommend something like DB or DAT.
+
Have fun!
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|