sql server - Optimizing lengthy SQL query for datatable -


i want retrieve logs of logins on basis of date. in addition need functionality of jquery-datatable sorting , searching in it! have worked on many queries , datatables 1 tougher 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 ) begin     select table1.email,table1.city,table1.latitude,table1.longitude,table1.first_log,     table1.last_log,table1.platform,table1.app      (select ll.email,        isnull(ll.city,'') city,        ll.latitude,        ll.longitude,        (select min(insertdate)        [loginlog]        email=ll.email) first_log,        ll.insertdate last_log,        case            when platform '%iphone%'                 or platform '%darwin%'                 or platform '%ipad%'                 or platform '%ios%' 'iphone'            else case                     when platform '%android%'                          or platform '%apache%' 'android'                     else 'iphone'                 end        end platform,        case            when app null 'consumer'            else app        end app     [loginlog] ll     id =         (select max(id)          [loginlog] ll2          ll2.email =ll.email          ,             (ll2.email '%'+@sp_search+'%'or             ll2.city '%'+@sp_search+'%'or             ll2.latitude '%'+@sp_search+'%'or             ll2.longitude '%'+@sp_search+'%'             )          )          , ll.email<>'' , ll.email<>'(null)'          , ll.insertdate>@sp_start_date , ll.insertdate<@sp_end_date          , loginsucess=1 , isnull(country, 'united states')='united states'     ) table1     where(             table1.first_log '%'+@sp_search+'%'or             table1.last_log '%'+@sp_search+'%'or             table1.platform '%'+@sp_search+'%'or             table1.app '%'+@sp_search+'%'                   )     order         case when (@sp_sort%100 = 01 , ((@sp_sort%1000)/100) = 1) table1.email end asc,         case when (@sp_sort%100 = 01 , ((@sp_sort%1000)/100) = 0) table1.email end desc,         case when (@sp_sort%100 = 02 , ((@sp_sort%1000)/100) = 1) table1.city end asc,         case when (@sp_sort%100 = 02 , ((@sp_sort%1000)/100) = 0) table1.city end desc,         case when (@sp_sort%100 = 03 , ((@sp_sort%1000)/100) = 1) table1.latitude end asc,         case when (@sp_sort%100 = 03 , ((@sp_sort%1000)/100) = 0) table1.latitude end desc,         case when (@sp_sort%100 = 04 , ((@sp_sort%1000)/100) = 1) table1.longitude end asc,         case when (@sp_sort%100 = 04 , ((@sp_sort%1000)/100) = 0) table1.longitude end desc,         case when (@sp_sort%100 = 05 , ((@sp_sort%1000)/100) = 1) table1.first_log end asc,         case when (@sp_sort%100 = 05 , ((@sp_sort%1000)/100) = 0) table1.first_log end desc,         case when (@sp_sort%100 = 06 , ((@sp_sort%1000)/100) = 1) table1.last_log end asc,         case when (@sp_sort%100 = 06 , ((@sp_sort%1000)/100) = 0) table1.last_log end desc,         case when (@sp_sort%100 = 07 , ((@sp_sort%1000)/100) = 1) table1.platform end asc,         case when (@sp_sort%100 = 07 , ((@sp_sort%1000)/100) = 0) table1.platform end desc,               case when (@sp_sort%100 = 08 , ((@sp_sort%1000)/100) = 1) table1.app end asc,         case when (@sp_sort%100 = 08 , ((@sp_sort%1000)/100) = 0) table1.app end desc             offset @sp_offset rows     fetch next @sp_count rows end

this works fine consumes lot of memory , time... can't wait 5 minutes more millions of records in it.

this table in case 1 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) )

thank you!

i think there not lot can do. problem case in sort if going kill optimization sql server may do. should @ query plan, , add recompile, @ end of day - query not going work efficient. dynamic sql efficient way go here - either client, or string manipulation in sp followed execute command execute sql string.

and non sargeable elements kill use of index - @ end, whoever designed database did extremely incompetent job here. there no proper way query efficiently.


Comments

Post a Comment

Popular posts from this blog

javascript - jQuery: Add class depending on URL in the best way -

i have html progress meter: <div class="col-md-3" style="margin-left: -20px;"> <div class="progress-pos active" id="progess-1"> <div class="progress-pos-inner"> login </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-2"> <div class="progress-pos-inner"> scan items </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-3"> <div class="progress-pos-inner"> confirm address </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-4"> <div class="progress-pos-in...
Read more

caching - How to check if a url path exists in the service worker cache -

i need check if particular url path exists in service worker cache. example, suppose url is: /myserviceworker/service?a=110&b=70 this url exists in cache, there many of them different values of , b. now, suppose want refresh of these urls, how can that? i want know how access key values service worker cache. if know key, plan follows: var url = new url(key); if(url.pathname === "\/myserviceworker/service") refetch key but not sure how cache key , in format is. mean, string or url? cache api has match() method returns promise resolving in response object if match or undefined if no match exists. second parameter object can specify ignoresearch not take account url parameters. the ignoresearch option supported firefox ( chrome status here ). in other hand, retrieve cache entries, can use keys() method.
Read more

Redirect to a HTTPS version using .htaccess -

i need modify .htaccess file redirect urls https version without "www". http://example.com --> https://example.com http://www.example.com --> https://example.com https://www.example.com --> https://example.com this how .htaccess file looks like: rewriteengine on rewritecond %{request_filename} !-d rewritecond %{request_filename} !-f rewriterule ^(.*)$ redirect.php?id=$1&page=$2 [qsa,l] what modifications need make .htaccess in order forward https without "www"? it depends on doing current code. looks file called redirect trying display php page seo friendly url. think might confusing people. anyway, need , force https without www, need 1 more rule above. also second rule needs two capture groups in rewriterule test string because wanting 2 different values using $1 , $2 references. rewriteengine on #rediect www non www and/or http https --- combinations. rewritecond %{http_host} !^example\.com [nc,or] rewritecond %{https} !^...
Read more