Iterating RowSet and Storing DatabaseRow in In-memory SQLite Database

Targets Windows
1

I am querying a MS SQL Server database on a server and then trying to store the data in memory for faster access in subsequent queries (filtering etc).
Creating the DatabaseRow object and writing it to an in-memory SQLite database is proving to be very slow (I have several thousand rows of data and 30-40 columns).
Can anyone suggest a faster way to do this?

If rs <> Nil Then
  While Not rs.AfterLastRow
    dbRow = New DatabaseRow
    For col = 0 To rs.ColumnCount - 1
      // create and populate row object
      ColName = cols(col)
      dbRow.Column(ColName).StringValue = rs.ColumnAt(col).StringValue.DefineEncoding(Encodings.WindowsLatin1).ConvertEncoding(Encodings.UTF8)
    Next
    
    // add row to sqlite db
    ResultsDB.AddRow("Results", dbRow)
    
    rs.MoveToNextRow
  Wend
End If
2

You might want to simply query the SQL server directly as it is probably caching your queries automatically behind the scenes.

1 Like
3

Forgive me @Samiul_Hassan1 , but I am trying to understand what you are trying to do.
Are you pulling the data into an in-memory SQLite DB and then going to run the rest of your queries there or are you going to use that data to somehow filter on the data in the SQL Server instance?

4

I am pulling a larger dataset into an in-memory SQLite DB as a way to cache it. This way if I want to query the dataset based on the user’s inputs etc, I can query the SQLite DB.

The SQL Server DB is on a remote server and hitting it with complex queries frequently can take a while plus add to server demand.

5

The original query is complex and can take a couple minutes to execute. I want to avoid running the original query over and over by saving the query result in memory, which I can then query based on user interaction.

Edit: Sorry, I just realised you mentioned the original query should cache in memory. I didn’t realise this.

6

Can we assume that you have worked with the DBA to optimize the query as much as possible or are you personally managing the database structures / performance?

7

I’m doing almost the same thing. I’m reading from a PostgreSQL database and writing an in-memory SQLite database. I just ran a test and it takes about 5 seconds to create an in-memory SQLite database with about 15 columns and 60k rows.

Are your writes within a transaction?
What do you consider slow in your situation?
Have you tried the profiler?

8

I just tried putting database writes in a transaction just now and it made no real difference.

Try
  If rs <> Nil Then
    ResultsDB.BeginTransaction

    While Not rs.AfterLastRow
      dbRow = New DatabaseRow
      For col = 0 To rs.ColumnCount - 1
        // create and populate row object
        ColName = cols(col)
        dbRow.Column(ColName).StringValue = rs.ColumnAt(col).StringValue.DefineEncoding(Encodings.WindowsLatin1).ConvertEncoding(Encodings.UTF8)
      Next
      
      // add row to sqlite db
      ResultsDB.AddRow("Results", dbRow)
      
      rs.MoveToNextRow
    Wend
  End If

  ResultsDB.CommitTransaction

Catch error As DatabaseException
  ResultsDB.RollbackTransaction

I tested with only 1242 rows of data, and it took 155 seconds to add all rows to the in-memory SQLite database! Any idea why it’s terribly slow?

9

I manage the queries and optimise for performance. There are things I can do to improve this, but I can see that currently the slowest part is not the database query itself, but writing the data to the in-memory SQLite db. But once the data is in the SQLite db, it’s much faster the query from it, instead of constantly querying the MS SQL Server db.

10

Try the profiler.

11

I would probably just use the loop to generate an SQL statement like:

INSERT INTO table1 (column1,column2 ,..)
VALUES 
   (value1,value2 ,...),
   (value1,value2 ,...),
    ...
   (value1,value2 ,...);

…and send that to the db.

12

image
image1366×78 3.01 KB

I tried it and I can’t explain with the WriteToSQLite method is taking so long to run. I moved the AddRow SQLite DB to a separate method and as you can see, it only took around 150ms to run 1333 times!

There is nothing else in the WriteToSQLite method apart from connecting to the SQLite DB and creating a table. So not sure where the rest of the time is being consumed.

Here is the WriteToSQLiteDB method:

Try
  // add data to in-memory SQLite database
  ResultsDB = New SQLiteDatabase
  ResultsDB.Connect
  
  ResultsDB.BeginTransaction
  
  ResultsDB.ExecuteSQL("DROP TABLE IF EXISTS Results")
  ResultsDB.ExecuteSQL("CREATE TABLE Results (Col1 VARCHAR, Col2 VARCHAR)") // and so on (30+ columns)
  
  For Each dbRow As DatabaseRow In rs
    // add row to db
    AddToDB(dbRow)
  Next
  
  CommitTrans
  
Catch error As DatabaseException
  MessageBox(error.Message)
  ResultsDB.RollbackTransaction
  
End Try
13

That is a lot work in a single transaction.
DROP / CREATE TABLE statements are not usually done within commits as they are difficult to rollback if there is an error.
As for the INSERTs (AddToDB) you may want to do the commits in chunks. Use a counter and for every X number of INSERTs commit them. The advantage here is that you can change the increments until you get the optimal number for performance. Start with 50 or 100 and go from there.

14

I just tried the following with no real change in performance:

Var RowCounter As Integer

Try
  // add data to in-memory SQLite database
  ResultsDB = New SQLiteDatabase
  ResultsDB.Connect
  
  ResultsDB.ExecuteSQL("DROP TABLE IF EXISTS Results")
  ResultsDB.ExecuteSQL("CREATE TABLE Results (Col1 VARCHAR, Col2 VARCHAR)") // and so on (30+ columns)
 
  ResultsDB.BeginTransaction
  RowCounter = 0
  For Each dbRow As DatabaseRow In rs
    RowCounter = RowCounter + 1
    // add row to db
    AddToDB(dbRow)
  
    // commit transaction every 50 records
    If RowCounter Mod 50 = 0 Then
      ResultsDB.CommitTransaction
      
      ResultsDB.BeginTransaction
    End If
  Next

  // commit transaction for any remaining records
  ResultsDB.CommitTransaction
  
Catch error As DatabaseException
  MessageBox(error.Message)
  ResultsDB.RollbackTransaction

End Try
15

I know the screenshot shows 50, but did you try other values and were the results more/less the same?

16

Sorry, I forgot to state I tried other values and the total time of execution is approximately the same each time.

17

No worries. :smiley:

I see in the notes that there are 30+ columns. Are one or more of those large text fields?
When you created the table in memory did you use a lot of FKs? (foreign keys)
Also, does the in memory table have a lot of indexes?
I am trying to think of things that I have had work around for large data loads.
It could be there is some underlying SQLite issue at play here that we are not aware of. :man_shrugging:

18

One other thing: I know you are using the API 2.0 objects, which is great. But have you tried just basic IINSERT strings?

strSQL = "INSERT INTO my_table (cola, colb) VALUES('A', 'B');
db.SQLExecute(strSQL)

That is just pseudo code from memory but should be close enough to communicate what I mean.

19

I tried this but it made no difference in performance unfortunately

20

The majority fields are within 20 characters. several fields are large text fields. But there are several fields that contain much more text.

Regarding the creation of the table, it is simple as I did above. That’s the only table in the in-memory DB. I didn’t create any FKs or indexes.

In terms of where the original data is coming from (the original query), several fields in the source database could be foreign keys etc but not sure how that would affect the performance of iterating and writing the contents to the in-memory DB?