sql server - Decrease execution time of SQL query -


i've got question in terms of processing , making query more efficient whilst maintaining accuracy. before display query i'd point out basics of it.

i've got case manipulates where-clause childs of parent. i've got 2 types of data need display; red , green type. red type has column (trk_trackergroup_lkid2) set null default, whereas green data has value in said column (ranging 5-7).

my problem need extract both types of data accurately count of outstanding issues in view, doing (by adding case) execution time goes < 1 second on 15 seconds.

this query (with mentioned case):

select ts.id        trackerstartdateid,        tsm.mappingtypeid,        tsm.maptoid,        tflk.trk_trackergroup_lkid,        count(tf.id) cnt   [dbo].[trk_startdate] ts        inner join [dbo].[trk_startdatemap] tsm                on ts.id = tsm.trk_startdateid                   , tsm.deletedflag = 0        inner join [dbo].[trk_trackerfeatures] tf                on tf.trk_startdateid = ts.id                   , tf.deletedflag = 0        inner join [dbo].[trk_trackerfeatures_lk] tflk                on tflk.id = tf.trk_feature_lkid  ts.deletedflag = 0        , tf.applicabletoproject = 1        , tf.readyforwork = case  -- here problem                                when tf.trk_trackerstatus_lkid2 null 0                                else 1                              end        , tf.datestamp = (select max(tf2.datestamp)                              [dbo].[trk_trackerfeatures] tf2                                   inner join [dbo].[trk_trackerfeatures_lk] tflk2                                           on tflk2.id = tf2.trk_feature_lkid                             tf.trk_startdateid = tf2.trk_startdateid                                   , tflk2.trk_trackergroup_lkid = tflk.trk_trackergroup_lkid) group  ts.id,           tsm.mappingtypeid,           tsm.maptoid,           tflk.trk_trackergroup_lkid,           tf.datestamp

it functions 'parent' in sense grabs latest inserted data-set (using datestamp) every single child-group. necessary produce parent-report in ssrs report @ later time, @ moment problem (as mentioned above) execution time.

i'd hear if there suggestions on how decrease execution time whilst maintaining accuracy of query.

expected output:

enter image description here

without case this:

enter image description here

your problem condition cant use index

and tf.readyforwork = case  -- here problem                         when tf.trk_trackerstatus_lkid2 null 0                         else 1                       end

try change 

and ( tf.readyforwork = 0 , tf.trk_trackerstatus_lkid2 null     or tf.readyforwork = 1 , tf.trk_trackerstatus_lkid2 not null     )

but again should check explain analize test if query using index or not.


Comments

Post a Comment

Popular posts from this blog

php - How to add and update images or image url in Volusion using Volusion API -

actually have store importing products store product data insert , update image or image url not insert , update. this xml code name datapro.txt. <products_joined> <productcode>3710_012t</productcode> <vendor_partno>eah5450silent/di/1gd3(lp)</vendor_partno> <productname>test product ta</productname> <hideproduct>n</hideproduct> <stockstatus>20</stockstatus> <lastmodified>1/5/2016 10:25:00 am</lastmodified> <lastmodby>2</lastmodby> <productweight>0.9</productweight> <productprice>100</productprice> <productmanufacturer>asus tek</productmanufacturer> <vendor_price>32.69</vendor_price> <numproductssharingstock>0</numproductssharingstock> <categoryids>107</categoryids> <producturl>http://tebkq.mvlce.servertrust.com/productdetails.asp?productcode=3710_012t</producturl>
Read more

javascript - jQuery UI Splitter/Resizable for unlimited amount of columns -

using jqueryui splitter or resizable functions want create layout allows unlimited amount of columns added , columns need resizable. so fiddle: https://jsfiddle.net/f3glh3mw/ but in fiddle html contains out of nested sets instead of inline. limits possibilities above fiddle can have equal widths 2,4,8,16,32 etc columns. in between shows crooked can see in fiddle. when researching came on question: jquery ui , splitter if @ top answer shows structure like: <div class="wrap"> <div class="resizable resizable1"></div> <div class="resizable resizable2"></div> </div> full fiddle: http://jsfiddle.net/8qztj/86/ which structure want javascript hardcoded 2 resizable items. so here question: how modify last fiddle works amount of .resizable divs. or know of plugin can this. thanks helping! so far found plugin: http://www.bacubacu.com/colresizable/ which says , close functionality want. in
Read more

javascript - IE9 error '$'is not defined -

Image
<!--[if lte ie 9]><html class="lte9"><![endif]--><!--[if gt ie 9]><html><![endif]--> <!--[if !ie]><!--> <html> <!--<![endif]--> blah blah <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.1/jquery.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script> <script src="{{ asset('js/mypage.js') }}" type="text/javascript"></script> </body> </html> when load page, ie9 brings error '$' not defined . how can solve error? +++ mypage.js $(function(){ //when load page, //ie9 tag not moving. $(window).load(function() { $('div.mask').hide(); }); // when click , willshow show. //for not ie 9 if (!$('html').hasclass('lte9')) { var = $('ul.about > li > span.activebtn');
Read more