|
netwerkassist
Starting Member
13 Posts |
 Posted - 2011-12-30 : 13:34:23
|
| We had recently purchased some new Servers. We run a ERP system that run a number of jobs daily. I'm trying to report back to management to illustrate the improvements made. Most of the improvements have been faster running jobs on the new Servers.Here is a simple query I wrote that gathers the jobs run in a 2 week window and reports back their avg,min, max run times for each job:---Query specific job times, and populate to temp table, then get average by querying temp table---Step 1.--CREATE TABLE #JobTimings(jobtime int, PRCSINSTANCE int , PRCSNAME char (30), RUNSTATUS char (30))CREATE TABLE #JobTimings(jobtime int, PRCSINSTANCE int , PRCSNAME char (30), PRCSTYPE char (30), RUNSTATUS char (30), OPRID char (30), RUNCNTLID char (30), BEGINDTTM char (30) , ENDDTTM char (30))INSERT INTO #JobTimings (jobtime, PRCSINSTANCE, PRCSNAME , PRCSTYPE, RUNSTATUS, OPRID, RUNCNTLID, BEGINDTTM, ENDDTTM)SELECT DATEDIFF(ss,BEGINDTTM,ENDDTTM) AS jobtime, PRCSINSTANCE, PRCSNAME, PRCSTYPE, RUNSTATUS, OPRID, RUNCNTLID, BEGINDTTM, ENDDTTMFROM PSPRCSRQST WHERE BEGINDTTM BETWEEN '2011-12-18 00:00:00.000' AND '2011-12-29 23:59:09.837' ORDER BY BEGINDTTM--Step 2.---Give you runtime Max/Min and AverageSELECT PRCSNAME, avg(jobtime)AS Average, max(jobtime)AS Maximum, min(jobtime)AS Minimumfrom #JobTimingsgroup by PRCSNAMEorder by 1This is an example of the output:RESULTS:PRCSNAME AVG MAX MINBCIH185 2366 8577 90BEN050 264 284 240HR_FASTVIEW 1621 11523 54PER099 1415 10977 16PERS_REFRESH 4364 7020 65PHHR0011 153 419 18PH_DYNROLE 10 124 2PH_PURGE 42 42 42PH_REQ_EMPLS 1238 2915 904PH_SND_EMAIL 6 43 1POS006A 278 520 15PT_AMM_WF 19 218 1 This data above is for a two week period. I have a couple of requirements I need help with:a)I want to compare two-two week periods. First period is the timings from the old Servers, and second period is the timings from the New Servers. I'd like to show the percentage faster between each job (have one more column called %improvement).b)Alot of these jobs run more than once daily, and I want to show in an additional column the total amount of times each of these jobs execute in the two week period (i.e. SELECT COUNT (*)AS #Executions FROM PSPRCSRQST WHERE BEGINDTTM BETWEEN '2011-12-18 00:00:00.000' AND '2011-12-29 23:59:09.837'AND PRCSNAME = 'HR_FASTVIEW') for each process. Ideally look like:PRCSNAME AVG MAX MIN #Executions %ImprovementBCIH185 2366 8577 90BEN050 264 284 240HR_FASTVIEW 1621 11523 54PER099 1415 10977 16PERS_REFRESH 4364 7020 65PHHR0011 153 419 18PH_DYNROLE 10 124 2PH_PURGE 42 42 42PH_REQ_EMPLS 1238 2915 904PH_SND_EMAIL 6 43 1POS006A 278 520 15PT_AMM_WF 19 218 1 Appreciate insight |
|
Query help/design