What i meant is that there are some tasks where you can do SQL queries and others where code would be a better solution. In my opinion writing a program to get this information out is a much better way then just doing it in one SQL query where I don't think
it would be possible.
Not exactly what you asked for but you may try looking into the QUANTIZE option if you haven't already. I use that along with PROPCOUNT in a few reports to get a percentage breakdown of the various time-taken values for a given page (or set of pages). It
allows me to look at the outliers and see that for example 40% of my requests are coming in at 200ms while only 2% are taking 1000ms. I trim everything under 1% so I can make the graphs more readable but if you're dumping it to a csv then there is no need.
Here is an example of what I am talking about:
SELECT TOP 100
QUANTIZE(time-taken, 100) as quantTime,
MUL(PROPCOUNT(*) ON (TO_LOWERCASE(cs-uri-stem)), 100.0) as percent
INTO %outdirfile%
FROM %source%
WHERE TO_LOWERCASE(cs-uri-stem) = '%URI%' AND
sc-status < 400
GROUP BY TO_LOWERCASE(cs-uri-stem),quantTime
HAVING percent >= 1.0
ORDER BY quantTime ASC
SELECT TO_STRING(TO_LOCALTIME(TO_TIMESTAMP(date, time)),'MM/dd/yyyy, hh:00') AS [Local Date, Hour]
,TO_STRING(SUM([_<1sec])) AS [< 1 sec]
,TO_STRING(SUB(SUM([_<2sec]),SUM([_<1sec]))) AS [< 2 sec]
,TO_STRING(SUB(SUM([_<3sec]),SUM([_<2sec]))) AS [< 3 sec]
,TO_STRING(SUB(SUM([_<4sec]),SUM([_<3sec]))) AS [< 4 sec]
,TO_STRING(SUB(SUM([_<5sec]),SUM([_<4sec]))) AS [< 5 sec]
,TO_STRING(SUB(SUM([_<10sec]),SUM([_<5sec]))) AS [< 10 sec]
,TO_STRING(SUB(SUM([_<15sec]),SUM([_<10sec]))) AS [< 15 sec]
,TO_STRING(SUB(SUM([_<20sec]),SUM([_<15sec]))) AS [< 20 sec]
,TO_STRING(SUM([_>=20sec])) AS [>= 20 sec]
,COUNT(*) AS [Total Hits]
USING
CASE DIV(time-taken,1000) WHEN 0 THEN 1 ELSE 0 END AS [_<1sec]
,CASE DIV(time-taken,2000) WHEN 0 THEN 1 ELSE 0 END AS [_<2sec]
,CASE DIV(time-taken,3000) WHEN 0 THEN 1 ELSE 0 END AS [_<3sec]
,CASE DIV(time-taken,4000) WHEN 0 THEN 1 ELSE 0 END AS [_<4sec]
,CASE DIV(time-taken,5000) WHEN 0 THEN 1 ELSE 0 END AS [_<5sec]
,CASE DIV(time-taken,10000) WHEN 0 THEN 1 ELSE 0 END AS [_<10sec]
,CASE DIV(time-taken,150000) WHEN 0 THEN 1 ELSE 0 END AS [_<15sec]
,CASE DIV(time-taken,200000) WHEN 0 THEN 1 ELSE 0 END AS [_<20sec]
,CASE DIV(time-taken,200000) WHEN 1 THEN 1 ELSE 0 END AS [_>=20sec]
FROM %SOURCE%
GROUP BY [Local Date, Hour]
ORDER BY [Local Date, Hour]
KhinMZaw
2 Posts
Latency summary
Apr 24, 2009 12:48 AM|LINK
Hi,
I would like to create a hourly latency report based on the timetaken value as follows.
Hour 1 - < 1 sec, 1-2 sec, 2-3 sec, 3-4 sec, 4-5 sec, 5-10sec, 10-15 sec, 15-20 sec, > 20sec
I have tried using CASE with WHEN and BETWEEN but none work, any pointer is appreciated.
Thanks
Rovastar
3324 Posts
MVP
Moderator
Re: Latency summary
Apr 24, 2009 02:12 AM|LINK
To be honest this is probably better done in code rather than an SQL query.
KhinMZaw
2 Posts
Re: Latency summary
Apr 24, 2009 05:12 PM|LINK
Care to elaborate, I would like to explore more on the code side.
Rovastar
3324 Posts
MVP
Moderator
Re: Latency summary
Apr 24, 2009 05:30 PM|LINK
What i meant is that there are some tasks where you can do SQL queries and others where code would be a better solution. In my opinion writing a program to get this information out is a much better way then just doing it in one SQL query where I don't think it would be possible.
deathwagon
14 Posts
Re: Latency summary
Sep 16, 2010 07:02 PM|LINK
Not exactly what you asked for but you may try looking into the QUANTIZE option if you haven't already. I use that along with PROPCOUNT in a few reports to get a percentage breakdown of the various time-taken values for a given page (or set of pages). It allows me to look at the outliers and see that for example 40% of my requests are coming in at 200ms while only 2% are taking 1000ms. I trim everything under 1% so I can make the graphs more readable but if you're dumping it to a csv then there is no need.
Here is an example of what I am talking about:
SELECT TOP 100
QUANTIZE(time-taken, 100) as quantTime,
MUL(PROPCOUNT(*) ON (TO_LOWERCASE(cs-uri-stem)), 100.0) as percent
INTO %outdirfile%
FROM %source%
WHERE TO_LOWERCASE(cs-uri-stem) = '%URI%' AND
sc-status < 400
GROUP BY TO_LOWERCASE(cs-uri-stem),quantTime
HAVING percent >= 1.0
ORDER BY quantTime ASC
SteveC_01
3 Posts
Re: Latency summary
Feb 25, 2013 06:21 PM|LINK
SELECT TO_STRING(TO_LOCALTIME(TO_TIMESTAMP(date, time)),'MM/dd/yyyy, hh:00') AS [Local Date, Hour]
,TO_STRING(SUM([_<1sec])) AS [< 1 sec]
,TO_STRING(SUB(SUM([_<2sec]),SUM([_<1sec]))) AS [< 2 sec]
,TO_STRING(SUB(SUM([_<3sec]),SUM([_<2sec]))) AS [< 3 sec]
,TO_STRING(SUB(SUM([_<4sec]),SUM([_<3sec]))) AS [< 4 sec]
,TO_STRING(SUB(SUM([_<5sec]),SUM([_<4sec]))) AS [< 5 sec]
,TO_STRING(SUB(SUM([_<10sec]),SUM([_<5sec]))) AS [< 10 sec]
,TO_STRING(SUB(SUM([_<15sec]),SUM([_<10sec]))) AS [< 15 sec]
,TO_STRING(SUB(SUM([_<20sec]),SUM([_<15sec]))) AS [< 20 sec]
,TO_STRING(SUM([_>=20sec])) AS [>= 20 sec]
,COUNT(*) AS [Total Hits]
USING
CASE DIV(time-taken,1000) WHEN 0 THEN 1 ELSE 0 END AS [_<1sec]
,CASE DIV(time-taken,2000) WHEN 0 THEN 1 ELSE 0 END AS [_<2sec]
,CASE DIV(time-taken,3000) WHEN 0 THEN 1 ELSE 0 END AS [_<3sec]
,CASE DIV(time-taken,4000) WHEN 0 THEN 1 ELSE 0 END AS [_<4sec]
,CASE DIV(time-taken,5000) WHEN 0 THEN 1 ELSE 0 END AS [_<5sec]
,CASE DIV(time-taken,10000) WHEN 0 THEN 1 ELSE 0 END AS [_<10sec]
,CASE DIV(time-taken,150000) WHEN 0 THEN 1 ELSE 0 END AS [_<15sec]
,CASE DIV(time-taken,200000) WHEN 0 THEN 1 ELSE 0 END AS [_<20sec]
,CASE DIV(time-taken,200000) WHEN 1 THEN 1 ELSE 0 END AS [_>=20sec]
FROM %SOURCE%
GROUP BY [Local Date, Hour]
ORDER BY [Local Date, Hour]
-Steve