Need to derive avg execuion time for 3 tables

Question

Post reply

Need to derive avg execuion time for 3 tables

Express12

SSCoach

Points: 18514
More actions
July 2, 2013 at 11:23 am

#274637

[font="Courier New"]
Need to derive avg execution time per row in table for each tablename. Sample table layout
TableName BeginTime EndTime
========= ========= =======
T1 2013-06-02 00:30.10.073 2013-06-02 00:30.17.463
T1 2013-05-14 00:40.09.283 2013-05-14 00:42.10.640
T1 2013-06-22 00:52.02.073 2013-06-22 01:03.10.727
T1 2013-07-01 00:03.07.487 2013-07-01 00:04.03.120
T2 2013-03-06 00:16.10.763 2013-03-06 02:21.10.133
T2 2013-07-02 00:22.10.880 2013-07-02 01:12:04.282
T2 2013-05-12 00:01.10.113 2013-05-12 00:44.15.797
T3 2013-01-18 00:14.10.577 2013-01-18 02:02.19.120
T3 2013-07-02 00:23.10.560 2013-07-02 01:42.03.480
So I need avg time for T1, T2, and T3... just these 3 values.
thanks for any suggestions
[/font]
BT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

I think this will do the trick:

-- (1) Create table and sample data:

DECLARE @x TABLE

(TableName varchar(3) not null,

BeginTime datetime not null,

EndTime datetime not null);

INSERT @x

SELECT 'T1', '2013-06-02 00:30:10.073','2013-06-02 00:30:17.463' UNION ALL

SELECT 'T1', '2013-05-14 00:40:09.283','2013-05-14 00:42:10.640' UNION ALL

SELECT 'T1', '2013-06-22 00:52:02.073','2013-06-22 01:03:10.727' UNION ALL

SELECT 'T1', '2013-07-01 00:03:07.487','2013-07-01 00:04:03.120' UNION ALL

SELECT 'T2', '2013-03-06 00:16:10.763','2013-03-06 02:21:10.133' UNION ALL

SELECT 'T2', '2013-07-02 00:22:10.880','2013-07-02 01:12:04.282' UNION ALL

SELECT 'T2', '2013-05-12 00:01:10.113','2013-05-12 00:44:15.797' UNION ALL

SELECT 'T3', '2013-01-18 00:14:10.577','2013-01-18 02:02:19.120' UNION ALL

SELECT 'T3', '2013-07-02 00:23:10.560','2013-07-02 01:42:03.480';

-- (2) Query

WITH exe_times AS

(SELECTTableName, DATEDIFF(SECOND,BeginTime,EndTime) AS exeTime

FROM @x)

SELECT TableName, AVG(exeTime) AS [Execution Time(seconds)]

FROM exe_times

GROUP BY TableName;

Note that I had to fix your date format in your sample data for the query to work; there was only one value (The second endtime for T2) that was in the correct format.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Express12 SSCoach Points: 18514 More actions · Answer 2

AJB, thank you very much. This is great. (apologize for the bad date formats as I hand entered these from a PDF doc) Thx Again!!!

BT

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

No problem!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 4

I think Alan and I had the same idea but he's just a little quicker on the keyboard. 🙂

select *

from

(

select tablename, avg(datediff(ms,starttime,endtime)) over (partition by tablename) averageMS from timetest

) source

group by tablename, averageMS