优化数据表的冗长SQL查询
我想根据日期检索所有登录日志。此外，我还需要JQuery的所有功能，在其中进行排序和搜索！我做过很多查询和数据处理，但是这个比我想象的要难。
CREATE PROCEDURE [dbo].[sp_login_logs]
(
@sp_start_date DATETIME,
@sp_end_date DATETIME,
@sp_offset INT,
@sp_count INT,
@sp_search VARCHAR(MAX),
@sp_sort INT
)
AS
BEGIN
SELECT table1.email,table1.city,table1.latitude,table1.longitude,table1.first_log,
table1.last_log,table1.platform,table1.app
FROM (SELECT ll.email,
ISNULL(ll.city,'') city,
ll.latitude,
ll.longitude,
(SELECT min(insertdate)
FROM [LoginLog]
WHERE email=ll.email) AS first_log,
ll.insertdate AS last_log,
CASE
WHEN platform LIKE '%iPhone%'
OR platform LIKE '%Darwin%'
OR platform LIKE '%iPad%'
OR platform LIKE '%iOS%' THEN 'iPhone'
ELSE CASE
WHEN platform LIKE '%Android%'
OR platform LIKE '%Apache%' THEN 'Android'
ELSE 'iPhone'
END
END AS platform,
CASE
WHEN app IS NULL THEN 'Consumer'
ELSE App
END AS app
FROM [LoginLog] ll
WHERE id =
(SELECT max(id)
FROM [LoginLog] ll2
WHERE ll2.email =ll.email
AND
(ll2.email like '%'+@sp_search+'%'OR
ll2.city like '%'+@sp_search+'%'OR
ll2.latitude like '%'+@sp_search+'%'OR
ll2.longitude like '%'+@sp_search+'%'
)
)
AND ll.email<>'' AND ll.email<>'(null)'
AND ll.insertdate>@sp_start_date AND ll.insertdate<@sp_end_date
AND loginsucess=1 and isnull(Country, 'United States')='United States'
) AS table1
WHERE(
table1.first_log like '%'+@sp_search+'%'OR
table1.last_log like '%'+@sp_search+'%'OR
table1.platform like '%'+@sp_search+'%'OR
table1.app like '%'+@sp_search+'%'
)
ORDER BY
CASE WHEN (@sp_sort%100 = 01 and ((@sp_sort%1000)/100) = 1) THEN table1.email END ASC,
CASE WHEN (@sp_sort%100 = 01 and ((@sp_sort%1000)/100) = 0) THEN table1.email END DESC,
CASE WHEN (@sp_sort%100 = 02 and ((@sp_sort%1000)/100) = 1) THEN table1.city END ASC,
CASE WHEN (@sp_sort%100 = 02 and ((@sp_sort%1000)/100) = 0) THEN table1.city END DESC,
CASE WHEN (@sp_sort%100 = 03 and ((@sp_sort%1000)/100) = 1) THEN table1.latitude END ASC,
CASE WHEN (@sp_sort%100 = 03 and ((@sp_sort%1000)/100) = 0) THEN table1.latitude END DESC,
CASE WHEN (@sp_sort%100 = 04 and ((@sp_sort%1000)/100) = 1) THEN table1.longitude END ASC,
CASE WHEN (@sp_sort%100 = 04 and ((@sp_sort%1000)/100) = 0) THEN table1.longitude END DESC,
CASE WHEN (@sp_sort%100 = 05 and ((@sp_sort%1000)/100) = 1) THEN table1.first_log END ASC,
CASE WHEN (@sp_sort%100 = 05 and ((@sp_sort%1000)/100) = 0) THEN table1.first_log END DESC,
CASE WHEN (@sp_sort%100 = 06 and ((@sp_sort%1000)/100) = 1) THEN table1.last_log END ASC,
CASE WHEN (@sp_sort%100 = 06 and ((@sp_sort%1000)/100) = 0) THEN table1.last_log END DESC,
CASE WHEN (@sp_sort%100 = 07 and ((@sp_sort%1000)/100) = 1) THEN table1.platform END ASC,
CASE WHEN (@sp_sort%100 = 07 and ((@sp_sort%1000)/100) = 0) THEN table1.platform END DESC,
CASE WHEN (@sp_sort%100 = 08 and ((@sp_sort%1000)/100) = 1) THEN table1.app END ASC,
CASE WHEN (@sp_sort%100 = 08 and ((@sp_sort%1000)/100) = 0) THEN table1.app END DESC
OFFSET @sp_offset ROWS
FETCH NEXT @sp_count ROWS Only
END这很好，但消耗了很多记忆和时间.不能再等5分钟了，里面有上百万的记录。
这是我的桌子以防有人需要：
CREATE TABLE [dbo].[LoginLog](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Email] [nvarchar](max) NULL,
[Platform] [nvarchar](max) NULL,
[Latitude] [nvarchar](max) NULL,
[Longitude] [nvarchar](max) NULL,
[InsertDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NULL,
[ipaddress] [nvarchar](55) NULL,
[City] [varchar](50) NULL,
[APP] [varchar](55) NULL,
[Country] [varchar](55) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)谢谢!
回答 2
Stack Overflow用户
发布于 2016-02-08 07:35:19
我认为你无能为力。THe的问题是，如果要终止server可能进行的任何优化，那么这种情况就会发生。您应该查看查询计划，并添加一个with重新编译，但最终--该查询将无法有效工作。动态SQL是这里唯一有效的方法--无论是从客户端，还是通过sp中的字符串操作，然后是执行命令来执行SQL字符串。
显然，非可增强的元素扼杀了任何索引的使用--最后，设计数据库的人在这里做了一个非常不称职的工作。没有适当的方法来有效地查询它。
Stack Overflow用户
发布于 2016-02-08 08:12:03
正如我在注释部分中提到的，您的存储过程存在许多问题，因此很难指出一件事并说：“在这里，这就是问题所在，修复它，您就很好了”。
我会列举几件让我不寒而栗的事情：
派生表中的派生表在派生表中混乱。
编译器通常擅长优化查询，前提是查询在派生表/子查询方面不太深入(即不太复杂)。如果您的查询变得太深入，您应该考虑在临时表中填充派生表(如果需要，可以适当地进行索引)。
我知道这是一个非常广泛的声明，很难确定你应该在什么时候采用这种工作方式。和往常一样，证据就是吃布丁。
强迫表扫描会破坏性能。
例如，您有一个确定MAX(id) FROM [LoginLog] ll2的子查询。在该子查询中，WHERE子句具有条件ll2.email=ll.email。当LoginLog.email上没有合适的索引时，这将迫使LoginLog上的表扫描找到相应的电子邮件地址。
该子句有一个附加的复杂查找，其中包含一系列强制进行表扫描的OR‘’ed语句。Server中没有布尔型短路，因此即使在LoginLog.email上提供索引，也可以进行表扫描以确定其他条件的状态。
如果包含用于查询的实际执行计划，则可以看到这些扫描。
试图在一个查询中完成这一切
这样做的查询通常过于复杂，无法快速执行。考虑拆分用例，为每个这样的用例创建一个更简单的查询。一般来说，参数很少的非复杂查询会执行得更快.
参数嗅探
我不打算详细解释，网上有很多文章解释了这一点(比如在我的搜索引擎中首先弹出的这一个 )。这种“参数嗅探”会损害存储过程的性能。第一次运行存储过程时，Server编译器将创建针对传递给它的参数进行优化的执行计划。缓存这些已编译的执行计划，这样编译器就不必每次执行存储过程时都重新编译它。存储过程的执行计划将在随后的调用中重用，但是对于其他参数，此执行计划可能完全无效。处理此问题的一种方法是为查询指定OPTION(OPTIMIZE FOR UNKNOWN)。
其他评论
ORDER BY子句太宽，如果结果集很大，可能会影响性能。您应该考虑创建动态SQL，以便只对实际需要的列进行排序。- 您使用
@sp_search查找first_log、last_log、platform和app中的文本.这看起来很傻，用例应该在这么多的文本列中找到不同的文本吗？ - 没有索引(至少没有显示任何索引)。如果您查询表，您应该提供适当的索引来加快它们的速度。否则，除非您正在查询查找ID，否则将强制进行表扫描。
https://stackoverflow.com/questions/35259329
复制相似问题
腾讯云开发者
