0

Our application allows the user to either (1) access our database in SQLite format stored on the local machine, or (2) access our database using SQL server on a server on the same network. For the purposes of this question it's an either-one-or-the-other setup choice.

I was testing both of these setups to check that everything was in order.

I am running a query that in essence is as follows:

INSERT INTO [#Temp_main]
SELECT T1.ID, T2.ID
FROM [#Temp_other] T1
LEFT OUTER JOIN [Table_with_lots_of_data] T2 ON ((T2.ID LIKE T1.ID+'%')

(For the SQLite version of this query, the concatenation operator is '||', which I learnt yesterday from Stackoverflow, thanks!).

So #Temp_other has only the first few chars of the ID, which get matched to the ID column in Table_with_lots_of_data, and the results are saved in #Temp_main.

It all works very well, but the SQLite version of the query is considerably slower than when querying SQL server. Bearing in mind also that the SQL server version also has the additional delay (I presume) of running over the local network, whereas the SQLite database is on the same machine.

I'm not sure if this is to be expected or not? Any help/advice/confirmation would be appreciated.

We are using SQL Server Express 2014. The information in Table_with_lots_of_data is exactly the same on both the SQLite and SQL server versions of our test. It contains approximately 150 000 rows, with 25 columns.

Alex
  • 543
  • 1
  • 9
  • 21
  • First step: Compare the explain plans and make sure they are the same. The only thing going across the network is your SQL statement and the result set. Everything else happens on the server(DB) itself. Depending on the performance differences between the local copy and your SQL server (especially how much of the DB is in cache) it would not amaze me that it is faster. – TheMadDBA Jun 19 '15 at 17:20
  • I would try with an index on table_with_lots_of_data(ID) on the sqlite database. – Jesús López Jun 19 '15 at 17:55
  • 150k rows and 25 columns, I would best tempdb is going to be slower on a client machine than server machine. – Greg Jun 19 '15 at 19:21
  • @TheMadDBA If I look at the execution plan on SQL server, the 'clustered index seek' on `Table_with_lots_of_data` cost is 57%, the `LEFT OUTER JOIN` costs 3%, and `INSERT INTO` `Temp_main` costs 40%. I'm not at all experienced with turning this information into anything useful, any pointers are helpful. Also, how do I get the equivalent information from SQLite? I tried the `EXPLAIN` as well as `EXPLAIN QUERY PLAN`, but this means nothing to me. – Alex Jun 22 '15 at 08:46
  • @JesúsLópez The `ID` column `Table_with_lots_of_data` has an index on it. – Alex Jun 22 '15 at 08:49
  • @Greg The server machine is simply a laptop in another room with Windows server 2012 installed on it, as well as SQL server express 2014. In other words, the machine itself is not notably more capable than the client machine, if I understood your comment correctly? – Alex Jun 22 '15 at 08:53

1 Answers1

1

In SQLite, a LIKE with a non-constant second operator cannot be optimized.

If you do not need the case insensitivity, and know the structure of the IDs, you can use something like this, which will able to use an index on T2.ID (in both databases):

SELECT T1.ID, T2.ID
FROM [...] T1
LEFT JOIN [...] T2 ON T2.ID BETWEEN T1.ID AND T1.ID || 'zzzzz'
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thanks very much. Is SQL server able to optimise this query, but not SQLite? Also, may I ask how you know that, is there a resource that I can look at so I could learn a bit more about this? – Alex Jun 22 '15 at 07:57
  • Your timings appear to indicate that MS SQL Server indeed can do some optimization. To check what happens, see [How do I obtain a Query Execution Plan?](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan) and [Is there a tool to profile sqlite queries?](http://stackoverflow.com/questions/3199790/is-there-a-tool-to-profile-sqlite-queries). – CL. Jun 22 '15 at 09:20