久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

優化數據表冗長的 SQL 查詢

Optimizing lengthy SQL query for datatable(優化數據表冗長的 SQL 查詢)
本文介紹了優化數據表冗長的 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.

這篇關于優化數據表冗長的 SQL 查詢的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網!

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題,如果有圖片或者內容侵犯了您的權益,請聯系我們刪除處理,感謝您的支持!

相關文檔推薦

Modify Existing decimal places info(修改現有小數位信息)
The correlation name #39;CONVERT#39; is specified multiple times(多次指定相關名稱“CONVERT)
T-SQL left join not returning null columns(T-SQL 左連接不返回空列)
remove duplicates from comma or pipeline operator string(從逗號或管道運算符字符串中刪除重復項)
Change an iterative query to a relational set-based query(將迭代查詢更改為基于關系集的查詢)
concatenate a zero onto sql server select value shows 4 digits still and not 5(將零連接到 sql server 選擇值仍然顯示 4 位而不是 5)
主站蜘蛛池模板: 免费看一区二区三区 | www.久久| 精品一区二区观看 | 91精品中文字幕一区二区三区 | 亚洲欧美日韩电影 | 91精品久久久久久久久中文字幕 | 欧美精品一区二区在线观看 | 欧美一区免费在线观看 | 瑟瑟免费视频 | 999久久精品 | 亚洲视频免费在线观看 | 一级做a| 久久久国产一区二区三区 | 国产精品久久国产精品 | 成人国产精品免费观看 | 欧美在线亚洲 | 做a视频在线观看 | 一二三区av | 美国十次成人欧美色导视频 | 国产精品1区| 综合第一页| 99pao成人国产永久免费视频 | 欧美一区二区三区在线免费观看 | 国产精品国产a级 | 日韩精品一区二区三区中文在线 | 青青草综合网 | re久久 | 久久成人人人人精品欧 | 午夜激情免费 | 免费一二区 | 亚洲国产在| 国产精品久久久久久福利一牛影视 | 国产美女在线播放 | 国产精品久久久乱弄 | 精品国产乱码久久久久久牛牛 | 国产精品a久久久久 | 久久久久久亚洲精品 | 亚洲精品中文字幕在线 | 国产精品一区二区三区四区 | 99久久久久久久 | 欧美一级二级三级视频 |