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

優(yōu)化數(shù)據(jù)表冗長(zhǎng)的 SQL 查詢

Optimizing lengthy SQL query for datatable(優(yōu)化數(shù)據(jù)表冗長(zhǎng)的 SQL 查詢)
本文介紹了優(yōu)化數(shù)據(jù)表冗長(zhǎng)的 SQL 查詢的處理方法,對(duì)大家解決問(wèn)題具有一定的參考價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)吧!

問(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)!

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

相關(guān)文檔推薦

Modify Existing decimal places info(修改現(xiàn)有小數(shù)位信息)
The correlation name #39;CONVERT#39; is specified multiple times(多次指定相關(guān)名稱“CONVERT)
T-SQL left join not returning null columns(T-SQL 左連接不返回空列)
remove duplicates from comma or pipeline operator string(從逗號(hào)或管道運(yùn)算符字符串中刪除重復(fù)項(xiàng))
Change an iterative query to a relational set-based query(將迭代查詢更改為基于關(guān)系集的查詢)
concatenate a zero onto sql server select value shows 4 digits still and not 5(將零連接到 sql server 選擇值仍然顯示 4 位而不是 5)
主站蜘蛛池模板: 久久精品综合 | 精品国产一区二区三区久久久蜜月 | 国产偷录叫床高潮录音 | 干干干操操操 | 羞羞午夜| 亚洲精品视频网站在线观看 | 天天爱av | 久久精品16| 天堂av中文 | 国产成人精品久久二区二区 | 毛片韩国| 国产片侵犯亲女视频播放 | 18成人在线观看 | 欧美精品综合在线 | 国产综合久久 | 欧美激情在线精品一区二区三区 | 亚洲一区二区中文字幕 | 精品欧美乱码久久久久久 | 激情91 | 97精品国产 | 一区二区三区免费观看 | 国产高清视频在线播放 | www.蜜桃av| 国产视频中文字幕在线观看 | 亚洲 欧美 综合 | 91精品国产综合久久福利软件 | 午夜精品福利视频 | 久久91精品久久久久久9鸭 | 免费在线观看毛片 | caoporn国产| 欧美在线观看一区 | 国产精品黄色 | 欧美精品1区 | 在线播放亚洲 | 精品一区二区免费视频 | 中文字幕 在线观看 | 久久久久久久久久久久久9999 | 精品欧美一区二区久久久伦 | 91久久网站 | 日韩乱码在线 | 91精品国产综合久久久密闭 |