运行速度(低效查询)

Question

我有下面的查询，这个查询效率不高，而且很多时候会带来内存不足的消息，有人能给出一些建议来帮助加快速度吗？谢谢吉姆

DECLARE @period_from INT
SET @period_from = 201400

DECLARE @period_to INT
SET @period_to = 201414

Declare @length INT
Set @length = '12'

DECLARE @query VARCHAR(MAX)
SET @query = '%[^-a-zA-Z0-9() ]%'

SELECT 'dim_2' AS field, NULL AS Length, * FROM table1 WHERE client = 'CL'AND period >= @period_from AND @period_to <= @period_to AND dim_2 LIKE @query
UNION
SELECT 'dim_3' AS field, NULL AS Length, * FROM table1 WHERE client = 'CL'AND period >= @period_from AND @period_to <= @period_to AND dim_3 LIKE @query
UNION
SELECT 'dim_4' AS field, NULL AS Length, * FROM table1 WHERE client = 'CL'AND period >= @period_from AND @period_to <= @period_to AND dim_4 LIKE @query
UNION
SELECT 'dim_5' AS field, NULL AS Length, * FROM table1 WHERE client = 'CL'AND period >= @period_from AND @period_to <= @period_to AND dim_5 LIKE @query
UNION
SELECT 'dim_6' AS field, NULL AS Length, * FROM table1 WHERE client = 'CL'AND period >= @period_from AND @period_to <= @period_to AND dim_6 LIKE @query
UNION
SELECT 'dim_7' AS field, NULL AS Length,* FROM table1 WHERE client = 'CL'AND period >= @period_from AND @period_to <= @period_to AND dim_7 LIKE @query
UNION
SELECT 'ext_inv_ref' AS field, NULL AS Length, * FROM table1 WHERE client = 'CL'AND period >= @period_from AND @period_to <= @period_to AND ext_inv_ref LIKE @query
UNION
SELECT 'ext_ref' AS field, NULL AS Length, * FROM table1 WHERE client = 'CL'AND period >= @period_from AND @period_to <= @period_to AND ext_ref LIKE @query
UNION
SELECT 'description' AS field, NULL AS Length, * FROM table1 WHERE client = 'CL'AND period >= @period_from AND @period_to <= @period_to AND description LIKE @query
UNION
SELECT 'Length dim_2' AS field,LEN(dim_2) as Length, * FROM table1 WHERE client = 'CL'AND period >= @period_from AND @period_to <= @period_to AND dim_2 is not null and len(dim_2) >@length
UNION
SELECT 'Length dim_3' AS field, LEN(dim_3) as Length, * FROM table1 WHERE client = 'CL'AND period >= @period_from AND @period_to <= @period_to AND dim_3 is not null and len(dim_3) >@length
UNION
SELECT 'Length dim_4' AS field, LEN(dim_4) as Length, * FROM table1 WHERE client = 'CL'AND period >= @period_from AND @period_to <= @period_to AND dim_4 is not null and len(dim_4) >@length
UNION
SELECT 'Length dim_5' AS field, LEN(dim_5) as Length, * FROM table1 WHERE client = 'CL'AND period >= @period_from AND @period_to <= @period_to AND dim_5 is not null and len(dim_5) >@length
UNION
SELECT 'Length dim_6' AS field, LEN(dim_6) as Length, * FROM table1 WHERE client = 'CL'AND period >= @period_from AND @period_to <= @period_to AND dim_6 is not null and len(dim_6) >@length
UNION
SELECT 'Length dim_7' AS field, LEN(dim_7) as Length, * FROM table1 WHERE client = 'CL'AND period >= @period_from AND @period_to <= @period_to AND dim_7 is not null and len(dim_7) >@length

Answer 1

您可以显着地减少工会的数量，但是工作会进入WHERE子句。SQL查询优化器应该知道，对于每个union语句，您只需要遍历表中的行一次，所以它应该会更快。像这样试试，看看！

SELECT 
CASE 
 WHEN dim_2 like @query Then 'dim_2' 
 WHEN dim_3 like @query Then 'dim_3' 
 WHEN dim_4 like @query Then 'dim_4' 
 WHEN dim_5 like @query Then 'dim_5' 
 WHEN dim_6 like @query Then 'dim_6' 
 WHEN dim_7 like @query Then 'dim_7' 
 WHEN ext_inv_ref LIKE @query Then 'ext_inv_ref'
 WHEN ext_ref LIKE @query Then 'ext_ref'
END AS field, 
NULL AS Length, 
* 
FROM table1 
WHERE client = 'CL'AND period >= @period_from AND @period_to <= @period_to 
AND (dim_2 LIKE @query 
     OR dim_3 LIKE @query 
     OR dim_4 LIKE @query 
     OR dim_5 LIKE @query 
     OR dim_6 LIKE @query 
     OR dim_7 LIKE @query
     OR ext_inv_ref LIKE @query
     OR ext_ref LIKE @query)

友联市

SELECT 
CASE 
 WHEN dim_2 is not null and len(dim_2) >@length Then 'Length dim_2' 
 WHEN dim_3 is not null and len(dim_3) >@length Then 'Length dim_3' 

 ....     

END AS field, 
LEN(dim_2) as Length,
* 
FROM table1 
WHERE client = 'CL'AND period >= @period_from AND @period_to <= @period_to 
AND ((dim_2 is not null and len(dim_2) >@length)
     OR
     (dim_3 is not null and len(dim_3) >@length)
     OR ....
     )

Answer 2

我不认为你能优化这么多。数据库将dim1到dim7作为一个表的列。现在你想把他们当作独立的专栏对待。因此，数据库设计不能满足您的要求。如果这只是一个例外，你将不得不忍受它。但是，如果这种使用成为典型的访问方式，那么应该考虑更改数据库设计，并为维度设置一个额外的表。

您不必要地做的一件事是使用UNION，它允许dbms查找副本。当您的记录以不同的常量开始时，每个工会组都不会有。改用UNION ALL。

Answer 3

这里有很多事情要做，但是您可以这样做：

CREATE TEMPORARY TABLE temp_table1 
SELECT * FROM table WHERE client = 'CL' 
AND period >= @period_from AND @period_to <= @period_to; 

然后使用此表作为其他工会的基础，然后删除它。如果经常这样选择，在创建临时表时，在period和client字段中引入复合索引可能也会有所帮助：

ALTER TABLE table1 ADD KEY period_client (client, period);