SQLiteDatabase Slower than RealSQLDatabase

Databases
1

I am trying to convert my existing RealSQLDatabases to the new SQLiteDatabase. I don’t want to make this a real long post, but I do want to provide enough information for you to understand my situation.

In my application, I download a membership text file from the web and then store the data from the text file in a database. The application has been running great with a RealSQLDatabase class but runs much slower with a SQLiteDatabase class. The time for processing 10,600 membership records has changed from less than 2 seconds to well over 15 minutes.

I did not define a Primary Key in RealSQLDatabase and still do not in the SQLiteDatabase class. In fact, the ONLY changes I made to the code were to redefine the database object as a SQLiteDatabase class instead of a RealSQLDatabase class.

After the database object is instantiated and the file is associated with the database, then the following code creates the database table:

      sql_cmd = "Create Table Mbr_DB ( "
      sql_cmd = sql_cmd + "Mbr_Nr varchar, "
      sql_cmd = sql_cmd + "Mbr_Nr_Pre varchar, "
      sql_cmd = sql_cmd + "Mbr_Nr_Suf varchar, "
      sql_cmd = sql_cmd + "Mbr_Call varchar, "
      sql_cmd = sql_cmd + "Mbr_Name varchar, "
      sql_cmd = sql_cmd + "Mbr_City varchar, "
      sql_cmd = sql_cmd + "Mbr_SPC varchar, "
      sql_cmd = sql_cmd + "Mbr_PastCalls varchar "
      sql_cmd = sql_cmd + " )"
      G_Mbr_DB.SQLExecute( sql_cmd )
      G_Mbr_DB.SQLExecute( "Create Index Mbr_Index on Mbr_DB ( Mbr_Nr, Mbr_Call )" )

After this, the application starts parsing through each membership record in the text file to propagate each field in a Database record.

After the database record has been filled, then the database record is inserted into the database with the following line of code. So there is a loop to process each membership record that looks somewhat like this:

LOOP for each membership record
    mbr_db_rec = New DatabaseRecord
    File in the data into each field of the database record
   G_Mbr_DB.InsertRecord( "Mbr_DB", mbr_db_rec )
END of LOOP

The processing of this loop took less than 2 seconds when using a RealSQLDatabase Class and now takes over 15 minutes using a SQLiteDatabase Class.

Can someone see how to improve the performance ?

Is it wrong to NOT explicitly define a primary key ?
Is the creation of the index causing a problem ?

Thanks in advance.

Ron Bower
Ellicott City, MD

2

Indeed, index better create AFTER massive changes in table.

3

Perform mass inserts/changes in a transaction also, that’s a huge boost in speed.

4

Thanks for the replies. I just read that the SQLiteDatabase Class performs a “Commit” after each change. That is different from RealSQLDatabase and was causing the major slowdown. Adding a “BEGIN TRANSACTION” command before adding records significantly reduced the time - now back to around 2 seconds total.

Now, for the index operation… So, I need to create the indexes AFTER making changes to the database? Do I have to rindex after each change also ?

Thanks again.

Ron Bower

5

Once the index is created, it is maintained by itself. I do not think you will see any significant changes dropping an index, inserting a bunch of items, then recreating it afterwards. Just make sure you are doing the changes in a transaction and you should be fine.

6

Thank you, Jeremy.

Your response is most appreciated.

7

It depends… Most database engines keep the index up to date per insert… So if you are inserting a few items, the savings of dropping the index, inserting the new data, and re-indexing the entire table may not be worth it.

But if you are inserting a number of records where the count is greater than a significant percentage of the existing rows, and especially when the new data is distributed over the index range (not all grouped together)… than your time savings could be significant.

I have operations at work (ORACLE database) where we routinely insert Millions of records, the overhead to drop and re-index is 20 minutes… the extra time to NOT drop the index is a few hours… yes this is an very large scale batch oriented process… not an interactive user program… but the point is still valid.

8

Yes, that is surely valid. I should have clarified. There are certainly cases you wish to do this and on other database systems even drop possible triggers for huge data loads. The op’s operation in entirety was taking 2 seconds to complete, that is what I was speaking to directly but reading back through it could be taken as bad advise. Thanks for setting the record straight.

Being a DBA over a large database requires a lot of special training in the area. If you are going to be dealing with a database a lot, or dealing with very large databases, it really pays to get some training in your database of choice.

9

This is off topic, but very important, but will this Conversation roll off the Forum at some point? This is the kind of information that is so valuable, even two years from now. I am not sure what was eventually said about others complaining about not being able to see more than 2 screenfuls of conversations (roughly 6 days back)

Or let me put it another way that the Xojo staff can understand. If I can get to posts like this, I am likely to be able to find the answer to my question, get on with my work and not bother you. Otherwise I would have to contact support and try to get an answer on why something is not working as expected.

Pardon me if this has been resolved or I am not doing something right, but I still can’t see more than a week’s worth of conversations, at least how I think I am supposed to find that information.

10

Too many indexes can be as bad a problem as not enough or not the right ones.
Designed well most databases can really fly - done poorly you can grind Oracle Sybase DB2 even on big iron to a a near halt.
I’ve seen both on some huge databases with high insert rates (70,000 rows a minute into one that started with over 200 million rows in one table)

11

[quote=18268:@Norman Palardy]Too many indexes can be as bad a problem as not enough or not the right ones.
Designed well most databases can really fly - done poorly you can grind Oracle Sybase DB2 even on big iron to a a near halt.
I’ve seen both on some huge databases with high insert rates (70,000 rows a minute into one that started with over 200 million rows in one table)[/quote]

This is where a more powerful database system can come into play. If you want to learn about databases, I’d really suggest installing PostgreSQL and learn it. What you learn there will apply to most other databases as well. Create your database and indexes you think you need in PostgreSQL, then use EXPLAIN SELECT blah, blah, blah and see what is really going on and adjust from there. Turn on the timing as well, might be that you don’t need to optimize anything.

12

Jeremy,

Oracle, DB2 and Sybase all are few bodies ahead to PostgreSQL. :slight_smile:

13

Ruslan, PostgreSQL is free and super easy to install/admin on Windows, Linux or OS X. Basically download, install and begin learning, and tons upon tons of informations, tutorials and how-to’s on the net making it, I believe, the best way to really learn the basics to semi-advanced database management/SQL/indexing, etc…

14

Jeremy,

Thank you for info of course.
Just please be aware, that Norman is developer with may be 20+ years experience in DBs, and me is developer of Valentina DB.

So, we have no need to “learn” databases :slight_smile:

Once again, you have answered to Norman’s post about DB2, Oracle, Sybase as: “use more powerful db - PostgreSQL”.
It is not more powerful.

Yet few years ago, postgreSQL was mentioned as turtle comparing to mySQL, in particular.
PostgreSQL only in 8.1 version have got bit set operations in the internal algorithms.
Other dbs have used them may be yet 20 years ago.
And so on.

I just argue to words “more powerful”.

15

[quote=18340:@Ruslan Zasukhin]Jeremy,

Thank you for info of course.
Just please be aware, that Norman is developer with may be 20+ years experience in DBs, and me is developer of Valentina DB.

So, we have no need to “learn” databases :slight_smile:
[/quote]

Hm, I wasn’t answering Normans post or yours, I was answering Ron’s. I quoted Norman’s comment about too many indexes being a problem and suggested to Ron that installing PostgreSQL would be a good thing to install to learn about indexing, etc…

Reading back, I can see how you would get that impression. That’s my bad, I was just using Norman’s comment to build upon for Ron, not Norman.

The more powerful was in regards to Ron’s question, as he is using SQLite. PostgreSQL is more powerful than SQLite, has the ability to do some indepth query explanation which I felt was a good tool for Ron to play around with for learning.

Sorry for the confusion, I was not questioning you or Norman, nor questioning your knowledge on the subject, just trying to give the op (Ron) an easy/free way to learn more about SQL, indexing, database structure, optimizing queries, etc…

16

I see, sorry then for miss-reading :slight_smile:

17

[quote=18266:@Melvyn Pate]This is off topic, but very important, but will this Conversation roll off the Forum at some point? This is the kind of information that is so valuable, even two years from now. I am not sure what was eventually said about others complaining about not being able to see more than 2 screenfuls of conversations (roughly 6 days back)

Or let me put it another way that the Xojo staff can understand. If I can get to posts like this, I am likely to be able to find the answer to my question, get on with my work and not bother you. Otherwise I would have to contact support and try to get an answer on why something is not working as expected.

Pardon me if this has been resolved or I am not doing something right, but I still can’t see more than a week’s worth of conversations, at least how I think I am supposed to find that information.[/quote]

Forum posts are kept indefinitely.

18

Yes but they are only browsabe fro a short time… On teh main page go to the bootom of the forun and click on “more”… You can only do that once… Go to the bottom of the new display and see the date of teh last post in that topic… And you don’t have the option to click on more again…

That makes browsing old post impossible.

You can find older posts if you search for something specific but not just browse…

That si what he means when he says “scrolls off”.

19

[quote=19146:@Karen Atkocius]Yes but they are only browsabe fro a short time… On teh main page go to the bootom of the forun and click on “more”… You can only do that once… Go to the bottom of the new display and see the date of teh last post in that topic… And you don’t have the option to click on more again…

That makes browsing old post impossible.

You can find older posts if you search for something specific but not just browse…

That si what he means when he says “scrolls off”.[/quote]
I consider that a bug which I have reported to Toby, the esoTalk developer.