Anyone have any tips to speed up SQL querys?

Mike Miller ( )
Thu, 31 Jul 1997 12:02:18 -0400

Hello all:

Does anyone have any tips to speed up SQL querys? I have set up a query
in MSQuery to assist us in tracking down spammers. Basically it takes
an IP address and lists all logins/logouts for that address during a
specified time period. The problem is it takes about 10 to 15 minutes
to generate the report. I want to incorporate the query into a web
based idc, but attempting to do so results in a timeout while waiting on
the results from the server.

The query:

SELECT Calls.CallDate, Calls.UserName, Calls.AcctStatusType
FROM "Emerald2.dbo".Calls Calls
WHERE (Calls.CallDate>={ts '1997-07-30 00:00:00'} And Calls.CallDate<{ts
'1997-07-31 00:00:00'}) AND (Calls.FramedAddress='')
ORDER BY Calls.CallDate

Emerald configuration:

System: NT Server 4.0, Pentium 133, 24 MB RAM
The operating system and SQL server are on one SCSI hard disk, while the
SQL database is on another SCSI hard disk to increase speed.

Emerald has been running for about 2 months. We have not consolidated
as of yet as I was hoping to keep at least a month of records for this
purpose. Our user base is around 1000. Emerald is being used for
accounting only on most of our ports, however is used for authentication
and accounting on a handful of our X2 ports. The database is currently
using about 76MB of space to store the records.

Does anyone have any suggestions to speed up this query and/or our
Emerald server?

-- =================================================  /\                          Mike A. Miller /--\         \/              Abraxis Networks /    \ B R A  /\ I S                       /                      N E T W O R K S