SQLite query speed vs SQL server in LEFT OUTER JOIN with LIKE condition

Question

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.

Answer 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'