SQLite表别名影响查询的性能 [英] SQLite table aliases effecting the performance of queries

问题描述

SQLite如何在内部处理别名？

How does SQLite internally treats the alias?

创建表名别名是在内部创建同一表的副本还是仅引用同一表而没有创建副本？

Does creating a table name alias internally creates a copy of the same table or does it just refers to the same table without creating a copy?

当我在代码中创建同一表的多个别名时，查询性能会受到严重影响！

When I create multiple aliases of the same table in my code, performance of the query is severely hit!

在我的情况下，我有一个表，将其称为MainTable，即2列，即名称和值。
我想在一行中选择多个值作为不同的列。例如

In my case, I have one table, call it MainTable with namely 2 columns, name and value. I want to select multiple values in one row as different columns. for example

名称：a，b，c，d，e，f

值：p，q，r，s，t，u

使得a对应于p，依此类推。

Name: a,b,c,d,e,f
Value: p,q,r,s,t,u
such that a corresponds to p and so on.

我想在一行中选择名称a，b，c和d的值=> p，q，r，s
所以我写了一个查询

I want to select values for names a,b,c and d in one row => p,q,r,s So I write a query

SELECT t1.name, t2.name, t3.name, t4.name  
FROM MainTable t1, MainTable t2, MainTable t3, MainTable t4
WHERE t1.name = 'a' and t2.name = 'b' and t3.name = 'c' and t4.name = 'd';

通过这种方式，当表的大小增加时，如Larry所正确指出的那样，编写查询会破坏性能

This way f writing the query kills the performance when size of the table increases as rightly pointed above by Larry.

是否有任何有效的方法来检索此结果。我对SQL查询不满意：(

Is there any efficient way to retrieve this result. I am bad at SQL queries :(

推荐答案

假设您有表dbo。客户有一百万行

Assuming you have table dbo.Customers with a million rows

SELECT * from dbo.Customers A

不会导致创建该表的副本。

does not result in a copy of the table being created.

如Larry所指出的那样，查询本身在整个表4上都是笛卡尔积

As Larry pointed out, the query as it stands is doing a cartesian product across your table four times which, as you has observed, kills your performance.

更新的故障单指出，希望在同一行中包含来自不同查询的4个值。这很简单，假设此语法对sqllite有效。

The updated ticket states the desire is to have 4 values from different queries in a single row. That's fairly simple, assuming this syntax is valid for sqllite

您可以看到以下四个查询在串行运行时产生所需的值，但在4行中。

You can see that the following four queries when run in serial produce the desired value but in 4 rows.

SELECT t1.name
FROM MainTable t1
WHERE t1.name='a';

SELECT t2.name
FROM MainTable t2
WHERE t2.name='b';

SELECT t3.name
FROM MainTable t3
WHERE t3.name='c';

SELECT t4.name
FROM MainTable t4
WHERE t4.name='d';

技巧是简单地将它们作为子查询运行，因此有5个查询：1个驱动程序查询， 4子正在做所有工作。如果返回一行，则此模式仅有效。

The trick is to simply run them as sub queries like so there are 5 queries: 1 driver query, 4 sub's doing all the work. This pattern will only work if there is one row returned.

SELECT
(
    SELECT t1.name
    FROM MainTable t1
    WHERE t1.name='a'
) AS t1_name
,
(
    SELECT t2.name
    FROM MainTable t2
    WHERE t2.name='b'
) AS t2_name
,
(
    SELECT t3.name
    FROM MainTable t3
    WHERE t3.name='c'
) AS t3_name
, 
(
    SELECT t4.name
    FROM MainTable t4
    WHERE t4.name='d'
) AS t4_name

这篇关于SQLite表别名影响查询的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！