[RadiusNT] Calls Query (semi-ot)

New Message Reply Date view Thread view Subject view Author view
Fox, Thomas (tfox@foxberry.com)
Thu, 28 Dec 2000 21:10:16 -0500



From: "Fox, Thomas" <tfox@foxberry.com>
Subject: [RadiusNT] Calls Query (semi-ot)
Date: Thu, 28 Dec 2000 21:10:16 -0500
Message-ID: <000a01c0713c$7e624600$de6ab0d1@foxberry.com>

Hello folks,

We recently converted our Access based Radius NT to
SQL, and for the most part things are working ok.

One problem is in a web form that lets our users
check their current month's usage online. When we
used MS Access, the query would take about 120
seconds to return the page. Using SQL server, it's
taking anywhere from 4 to 6 minutes. I'm wondering
if perhaps the query isn't optimized for SQL, or if
it is buggered up somehow. Any help would be appreciated.

These queries are all for Allaire's cold fusion.

Here are the pertinent queries:

This query loads the current month into a variable:

<DBQUERY NAME="GetCurrentTime" DataSource="UserFile"
SQL = "Select DatePart (Month, GetDate()) AS
CurrMonth FROM TodayTable">

This query pulls the user's current month's call
records:

<DBQUERY NAME="TimeOnLine" DATASOURCE="UserFile"
SQL = "SELECT NASIdentifier, FramedAddress, UserName,
CallDate, NASPort, AcctSessionTime, AcctStatusType,
CallerID, ConnectInfo,
AcctSessionTime/60.0000 as CallMinutes
FROM Calls
WHERE UserName = '#UserName#'
AND DatePart (Month, CallDate) = #GetCurrentTime.CurrMonth#
AND AcctStatusType = 2
GROUP BY NASIdentifier, FramedAddress, UserName,
CallDate, NASPort, AcctSessionTime, AcctStatusType,
CallerID, ConnectInfo
ORDER BY CallDate">

And this query totals their call records (seems to
me I ought to be able to do it all in one query,
but when I try, I get summary results for each
call record, not a total of all call records):

<DBQUERY NAME="TimeSummary" DATASOURCE="UserFile"
SQL = "SELECT Sum(AcctSessionTime)/3600.0000 as TimeHours
FROM Calls
WHERE UserName = '#UserName#'
AND DatePart (Month, CallDate) = #GetCurrentTime.CurrMonth#
AND AcctStatusType = 2">

Thanks!
Tom

.



New Message Reply Date view Thread view Subject view Author view
This archive was generated on Thu Dec 28 2000 - 17:55:57 Pacific Standard Time