sql server - How Can I speed up my SQL Query -

-

i'm running query:

select  time_id,     site_type_id,     abandoned_id,     worktype_id,     sum (staging.dbo.incoming_measure.ring_time) ring_time,     sum (staging.dbo.incoming_measure.hold_time) hold_time,     sum (staging.dbo.incoming_measure.talk_time) talk_time,     sum (staging.dbo.incoming_measure.acw_time) acw_time,     count(*) callcount incoming_measure inner join datamartend.dbo.time_dim          on incoming_measure.starttimedate between time_dim.time_start ,                                                    time_dim.time_end inner join datamartend.dbo.site_type_dim          on incoming_measure.dbid = site_type_dim.site_type_code inner join datamartend.dbo.abandoned_call_dim          on incoming_measure.abandoned = abandoned_call_dim.abandoned_value inner join datamartend.dbo.work_type_dim          on incoming_measure.dbid = work_type_dim.mig_site_id ,            work_type_dim.work_type_code = incoming_measure.queue ,            incoming_measure.starttimedate between work_type_dim.dimeffectivestartdtm ,                                                   work_type_dim.dimeffectiveenddtm group time_id, site_type_id, abandoned_id, worktype_id

it returning correct results taking around 8minutes run , wondered if had advice on how can speed query? main issue if part of project in have demonstrate end result client , allowed 10 minutes demonstrate (university rules) , query 30% of project.

the bulk of estimated execution sort 57%.

you may find query performs better if rewritten this:

select     time_id,     site_type_id,     abandoned_id,     worktype_id,     sum (im.ring_time) ring_time,     sum (im.hold_time) hold_time,     sum (im.talk_time) talk_time,     sum (im.acw_time) acw_time,     count(*) callcount incoming_measure im inner join datamartend.dbo.time_dim td         on dateadd(mi,                    15*floor(datediff(mi,                                      dateadd(dd, datediff(dd,0,im.starttimedate), 0),                                      im.starttimedate ) / 15),                    dateadd(dd, datediff(dd,0,im.starttimedate), 0)                    ) = td.time_start inner join datamartend.dbo.site_type_dim std         on im.dbid = std.site_type_code inner join datamartend.dbo.abandoned_call_dim acd         on im.abandoned = acd.abandoned_value inner join datamartend.dbo.work_type_dim wtd         on im.dbid = wtd.mig_site_id ,            im.queue = wtd.work_type_code ,            im.starttimedate between wtd.dimeffectivestartdtm , wtd.dimeffectiveenddtm group time_id, site_type_id, abandoned_id, worktype_id

- time dimension joined on equals value, rather on value between range of values.

if not improve performance, suggest creating indexed view on existing query, , selecting indexed view new query - can find more creating indexed views here, while there information on limitations here.


Comments

Post a Comment

Popular posts from this blog

Delphi XE2 Indy10 udp client-server interchange using SendBuffer-ReceiveBuffer -

-
i use delphi xe2 , indy10 udp protocol. can't receive server echo on client side if use receivebuffer method. got "socket error # 10040" although send small echo message server client. console application illustrate problem below. in advance. program project1; {$apptype console} {$r *.res} uses system.sysutils, idglobal, idbasecomponent, idcomponent, idsockethandle, idudpclient, idudpserver, idudpbase, idstack; type tudp_serv = class(tidudpserver) procedure udpsvudpread(athread: tidudplistenerthread; adata: tidbytes; abinding: tidsockethandle); end; var udpserver: tudp_serv; udpcl: tidudpclient; bsnd, brcv: tbytes; s: string; k: integer; //============================================================================== procedure tudp_serv.udpsvudpread(athread: tidudplistenerthread; adata: tidbytes; abinding: tidsockethandle); begin writeln(' server read: ' + tohex(adata, length(adata))); abinding sendto(peerip, peerport, adat...
Read more

Qt ActiveX WMI QAxBase::dynamicCallHelper: ItemIndex(int): No such property in -

-
i new windows programming. here goes code objiwbemlocator = new qaxobject("wbemscripting.swbemlocator"); objwmiservice = objiwbemlocator->querysubobject("connectserver(qstring&,qstring&)",qstring("."),qstring("root\\cimv2")); qaxobject* returnlist = objwmiservice->querysubobject("execquery(qstring&)", qstring("select * %1").arg(domain)); qaxobject* result = returnlist->querysubobject("itemindex(int)", 0); i getting error on runtime qt activex wmi qaxbase::dynamiccallhelper: itemindex(int): no such property in but itemindex method exists msdn says the itemindex method not work collections not contain swbemobjects, such swbemmethodset, swbemnamedvalueset, swbemprivilegeset, swbempropertyset, , swbemqualifierset. what selecting ? have tried iterating on collection ? plus need minimum windows vista itemindex method. hope helps
Read more

Enable autocomplete or intellisense in Atom editor for PHP -

-
i'm first time user of atom editor php code. atom can autocomplete built-in functions of php. possible make editor autocomplete custom function written in separate file? function.php <?php function printsomething() { echo "hello world"; } index.php <?php require_once "function.php"; printsomething(); // autocompleted atom editor while typing thanks in advance. there text file in %userprofile%.atom called snippets.cson. in there can add snippet. more this: http://flight-manual.atom.io/using-atom/sections/snippets/ but need think can use later on other files well. otherwise need copy , paste.
Read more