1

We have been using MSSQL and Access up to this point. We have one application that once all the data has been imported then goes thru MANY update queries. This particular application requires an embedded database. Easy with the Inner Joins using MS Access.

We are switching to SQLite and obviously very new to SQLite.

Can't seem to get our Update Queries correct and/or efficient in SQLite. They are either taking forever (actually never finishing) or not working.

One example below is a many to one relationship. Trying to update one field in the "Many" table to a value in the "One" table based on a field that exist in both tables.

Table 1 (the one table)
Field (ID) - this field is unique
Field (Manager)

Table 2 (the many table)
Field (ID) - the values in this field are not unique and they link to the (ID) field in Table 1
Field (Contact)

Here is our query:

UPDATE [Table 2] SET [Contact] = (SELECT [Manager] FROM [Table 1]
                                  WHERE [Table 2].[ID] = [Table 1].[ID])

We've tried this within SQLite Expert Professional, and the query never stops running. There are only 53,391 records in Table 2 to be updated. We have to shut down the SQLite Expert application every time. It just hangs.

We put this into our Test Code captured within a Transaction as follows:

Using cmd as new SQLiteCommand(cnn)
    Using transaction = cnn.BeginTransaction
        cmd.Transaction = transaction
        cmd.CommandText  = "UPDATE [Table 2] SET [Contact] = (SELECT [Manager] FROM [Table 1] WHERE [Table 2].[ID] = [Table 1].[ID])"
        icount = cmd.ExecuteNonQuery
        transaction.Commit()
    End Using
End Using

This just never ends.

This worked:

UPDATE [Table 2] SET [Contact] = (SELECT [Manager] FROM [Table 1]
                                  WHERE [Table 2].[ID] = [Table 1].[ID])
WHERE EXISTS (Select [Manager] FROM [Table 1]
              WHERE [Table 2].[ID] = [Table 1].[ID])

not sure if there is a better or more faster approach? Not sure we understand why the WHERE EXISTS is required either.

We have another Update query issue trying to update a field within a table based on another field in that table. Kinds recursive.

CL.
  • 173,858
  • 17
  • 217
  • 259
midnite11
  • 81
  • 1
  • 10
  • We got this to run, somewhat efficient, however the field is being updated with the same value for all records: Update [Table 2] Set [Contact] = (SELECT [Table 1].[Manager] FROM [Table 1] INNER JOIN [Table 2] ON [Table 1].[ID] = [Table 2].[ID] ). – midnite11 Jul 20 '17 at 18:57
  • Show the actual table definition, and any indexes you have. – CL. Jul 20 '17 at 18:57
  • What exactly are you looking for in the Table Definitions. I mean these tables both have index's on the [ID] field and each have like 4 or 5 fields at the most. Since we got the above Query to "work" (e.g. finish) but the field is being updated with the same value for all records...just seems like we are missing something at then end or something...like a WHERE..but again we are new to SQLite. – midnite11 Jul 20 '17 at 19:13
  • This worked!!....UPDATE [Table 2] SET [Contact] = (SELECT [Manager] FROM [Table 1] WHERE [Table 2].[ID] = [Table 1].[ID]) WHERE EXISTS (Select [Manager] FROM [Table 1] WHERE [Table 2].[ID] = [Table 1].[ID]).....not sure if there is a better or more faster approach? Not sure we understand why the WHERE EXISTS is required either. – midnite11 Jul 20 '17 at 19:48
  • [Not being able to do an UPDATE query on a join](https://stackoverflow.com/questions/773441/how-do-i-make-an-update-while-joining-tables-on-sqlite) would make me think twice about using SQLite. – Andre Jul 21 '17 at 07:22
  • Was feeling that way at the beginning, however with the comments we have received just on this one question and other googling - we have so far overcome issues related to UPDATES/SQLite and have found that indeed it seems to be much faster than MS Access. We simply have been taking the more complex existing Queries in our current application using MS Access and rewriting those within a Test SQLite application to 1) make sure we can do it and 2) compare performance. At this point SQLite wins. More to come I suspect. The inability to remove columns is frustrating but not a show stopper. – midnite11 Jul 21 '17 at 13:05
  • @midnite11 - I was under the impression that you are now using Access as a front-end to a SQLite database whereas before you were using Access as a front-end to a SQL Server database. Is that not the case? – Gord Thompson Jul 21 '17 at 14:36
  • Very sorry for the confusion. The current configuration is a .net Windows UI front end to a MS Access back end. We are/want to move to SQLite back end. We are required in this case to use an embedded DB AND with some new requirements we anticipate (via testing already have) that we will be running into Access DB size limitations. The new requirements are requesting much more data. Based on a lot of research we went forward with installing and testing SQLite. It's been a little frustrating learning curve but people on this site have helped a lot..along with our own pain staking google research. – midnite11 Jul 21 '17 at 17:54
  • I should also mention that this application does not share the SQLite across users. Each installation is per user. Therefore we will not run across a multi-user accessing SQLite issue. – midnite11 Jul 21 '17 at 17:55

2 Answers2

2

When using an UPDATE statement with an inner join, MS Access modifies only rows for which the join matched.

When doing a plain UPDATE with a correlated subquery, like this:

UPDATE [Table 2] SET [Contact] = (...)

then the database modifies all rows. (When the subquery does not return a row, SQLite updates the field with NULL; other DBs might raise an error.)

If the number of rows with a match is much smaller than the number of rows in the table, then rewriting all rows is much slower. It makes then sense to restrict the number of rows that actually get modified:

UPDATE [Table 2] SET [Contact] = (...)
WHERE EXISTS (...same subquery...)

This has the same effect as the inner join.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • CL - thanx for this feedback. I admit, we've been spoiled with the inner joins over the years with MS SQL and MS Access related to Updates. We are learning as we go with SQLite and appreciate hearing back from the experts. – midnite11 Jul 21 '17 at 13:00
-1

SQLite is generally much faster than Access. It's impossible to know exactly where your trouble is occurring without seeing your DDL and data but the most likely bet is that you have not built indexes on your ID columns. Access builds indexes for you without asking, in all other databases you must explicitly define them yourself.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • We have an index's on both the [ID] fields of both tables. – midnite11 Jul 20 '17 at 19:03
  • 1
    re: "SQLite is generally much faster than Access" - you might be interested in [this answer](https://stackoverflow.com/a/35225563/2144390) – Gord Thompson Jul 21 '17 at 13:32
  • We have seen that post during our research. But we are running into MS Access DB Size limitations..and therefore we don't have a choice. We would like to move to MS SQL but we are required to have an Embedded database configuraion. – midnite11 Jul 21 '17 at 18:00