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.
我们的应用程序允许用户(1)以存储在本地机器上的SQLite格式访问我们的数据库,或(2)使用同一网络上的服务器上的SQL服务器访问我们的数据库。出于这个问题的目的,它是一个或一个或另一个设置选择。
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 *, thanks!).
(对于此查询的SQLite版本,连接运算符是'||',我昨天从*中学到了,谢谢!)。
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.
因此#Temp_other只有ID的前几个字符,它们与Table_with_lots_of_data中的ID列匹配,结果保存在#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.
一切都运行良好,但SQLite版本的查询比查询SQL服务器要慢得多。还要记住,SQL Server版本还有在本地网络上运行的额外延迟(我推测),而SQLite数据库在同一台机器上。
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.
我们正在使用SQL Server Express 2014. Table_with_lots_of_data中的信息在我们的测试的SQLite和SQL服务器版本上完全相同。它包含大约150 000行,25列。
1 个解决方案
#1
1
In SQLite, a LIKE with a non-constant second operator cannot be optimized.
在SQLite中,无法优化具有非常量第二运算符的LIKE。
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):
如果你不需要不区分大小写,并且知道ID的结构,你可以使用这样的东西,它可以使用T2.ID上的索引(在两个数据库中):
SELECT T1.ID, T2.ID
FROM [...] T1
LEFT JOIN [...] T2 ON T2.ID BETWEEN T1.ID AND T1.ID || 'zzzzz'
#1
1
In SQLite, a LIKE with a non-constant second operator cannot be optimized.
在SQLite中,无法优化具有非常量第二运算符的LIKE。
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):
如果你不需要不区分大小写,并且知道ID的结构,你可以使用这样的东西,它可以使用T2.ID上的索引(在两个数据库中):
SELECT T1.ID, T2.ID
FROM [...] T1
LEFT JOIN [...] T2 ON T2.ID BETWEEN T1.ID AND T1.ID || 'zzzzz'