|
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, ENDDTTM
FROM 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 Average
SELECT PRCSNAME, avg(jobtime)AS Average, max(jobtime)AS Maximum, min(jobtime)AS Minimum
from #JobTimings
group by PRCSNAME
order by 1
This is an example of the output:
RESULTS:
PRCSNAME AVG MAX MIN
BCIH185 2366 8577 90
BEN050 264 284 240
HR_FASTVIEW 1621 11523 54
PER099 1415 10977 16
PERS_REFRESH 4364 7020 65
PHHR0011 153 419 18
PH_DYNROLE 10 124 2
PH_PURGE 42 42 42
PH_REQ_EMPLS 1238 2915 904
PH_SND_EMAIL 6 43 1
POS006A 278 520 15
PT_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 %Improvement
BCIH185 2366 8577 90
BEN050 264 284 240
HR_FASTVIEW 1621 11523 54
PER099 1415 10977 16
PERS_REFRESH 4364 7020 65
PHHR0011 153 419 18
PH_DYNROLE 10 124 2
PH_PURGE 42 42 42
PH_REQ_EMPLS 1238 2915 904
PH_SND_EMAIL 6 43 1
POS006A 278 520 15
PT_AMM_WF 19 218 1
Appreciate insight |
|
Query help/design