Re: [RadiusNT] Re: RadiusNT Digest

Dale E. Reed Jr. ( (no email) )
Sat, 07 Oct 2000 18:55:44 -0700

Edsonet wrote:
>
> >My calls table has a terrible response time. All other tables perform
> >excellent. Even when I stop my radius service the calls performance is
> >unsatisfactory. I need to purge the calls occasionally, but it is
> >difficult to keep up with when it takes so long. I reindexed on the
> >"calldate", but it did not seem to make a difference (with the thought that
> >my index was possibly a problem).
> >
> >Any ideas? Should I re-create the calls database over again, or is there
> >something else I can do?
> >
> **************** REPLY SEAPRATER ******************
> We run our own accounting program, and part of the program is to save the
> call data in a separate database. Periodically, the program runs a query on
> the Calls table for anything older than 24 hours, transfers it to the new
> database, and then deletes it from the Radius database. That way, the
> Radius database only has the last 24 hours of call info and is kept small
> and responsive. When we have finsihed with the Call info in the new
> database, it is archived and we start all over again.

But once again, its all about the query. For example, lets say you have
an index on calldate. If you want to delete records older than 24
hours,
you could (although bad) do this:

Delete From Calls
Where DateDiff(d, GetDate(), CallDate) > 1

However, this is VERY expensive, since it has to compare every record
and compute the difference. However, its better to do this:

DECLARE @deldate datetime
Select @deldate = DateAdd(d, -1, GetDate())
Delete From Calls Where CallDate < @deldate

What this does is figure out the date ahead of time (which, all dates
are just numbers) and can use the index to find the candidates. There
is
no computation with every record and can speed up select/delete calls
incredibly.

-- 

Dale E. Reed Jr.    Emerald and RadiusNT/X__________________________________________IEA Software, Inc.   www.iea-software.com

For more information about this list (including removal) go to:http://www.iea-software.com/support/maillists/liststart