优化数据表冗长的 SQL 查询

问题描述

我想根据日期检索所有登录日志.此外，我需要其中的 JQuery 数据表排序和搜索的所有功能！我处理过很多查询和数据表，但这个比我想象的要难.

I want to retrieve all the logs of logins on the basis of date. In addition I need all the functionality of JQuery-datatable sorting and searching in it! I have worked on many queries and datatables but this one is tougher than I thought.

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 分钟，其中有超过数百万条记录.

This works fine but consumes a lot of memory and time... Can't wait for 5 minutes with more than millions of records in it.

这是我的桌子，以防有人需要:

This is my table in case any one needs :

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)
)

谢谢！

推荐答案

我认为你能做的并不多.问题是排序中的情况，如果要终止 sql server 可能执行的任何优化.您应该查看查询计划，并添加一个 with 重新编译，但在一天结束时 - 该查询不会有效地工作.动态 SQL 是唯一有效的方法——从客户端，或者通过 sp 中的字符串操作，然后是执行命令来执行 SQL 字符串.

I think there is not a lot you can do. THe problem is that the case in the sort if going to kill any optimization the sql server may do. You should look at the query plan, and add a with recompile, but at the end of the day - that query is not going to work efficient. Dynamic SQL is the only efficient way to go here - either from the client, or by string manipulation in the sp followed by a execute command to execute the SQL string.

显然，不可访问的元素会扼杀任何索引的使用——最后，设计数据库的人在这里做了一个非常无能的工作.没有正确的方法可以有效地查询它.

And obviously the non sargeable elements kill the use of any index - at the end, whoever designed the database did an extremely incompetent job here. There is no proper way to query it efficiently.