問(wèn)題描述

我想根據(jù)日期檢索所有登錄日志.此外，我需要其中的 JQuery 數(shù)據(jù)表排序和搜索的所有功能！我處理過(guò)很多查詢和數(shù)據(jù)表，但這個(gè)比我想象的要難.

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

這工作正常，但會(huì)消耗大量?jī)?nèi)存和時(shí)間......不能等待 5 分鐘，其中有超過(guò)數(shù)百萬(wàn)條記錄.

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

謝謝！

推薦答案

我認(rèn)為你能做的并不多.問(wèn)題是排序中的情況，如果要終止 sql server 可能執(zhí)行的任何優(yōu)化.您應(yīng)該查看查詢計(jì)劃，并添加一個(gè) with 重新編譯，但在一天結(jié)束時(shí) - 該查詢不會(huì)有效地工作.動(dòng)態(tài) SQL 是唯一有效的方法——從客戶端，或者通過(guò) sp 中的字符串操作，然后是執(zhí)行命令來(lái)執(zhí)行 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.

顯然，不可訪問(wèn)的元素會(huì)扼殺任何索引的使用——最后，設(shè)計(jì)數(shù)據(jù)庫(kù)的人在這里做了一個(gè)非常無(wú)能的工作.沒(méi)有正確的方法可以有效地查詢它.

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.

這篇關(guān)于優(yōu)化數(shù)據(jù)表冗長(zhǎng)的 SQL 查詢的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！