I created a trigger for inserting on the CALLS table that inserts just AcctStatusType = 2
messages and a limited number of fields (5 I think) so that the data was small and fast.
I now can consolidate without losing my call history and I run reports off of that table
instead of the larger CALLS table.
Also, you can move your tempdb into RAM but you need to have the extra hardware.
Also, use the fastest drive you can possible find. I use an Ultra/Wide Barracuda for
my database drive and things seem to chug along relatively smoothly.
My .02
Tom
P.S. Let me know if you want the trigger, I'll post it.
-----Original Message-----
From: Mike Miller [SMTP:michael@abraxis.com]
Sent: Thursday, July 31, 1997 12:02 PM
To: emerald@emerald.iea.com
Subject: Anyone have any tips to speed up SQL querys?
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='206.155.199.52')
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 michael@abraxis.com=================================================---------------------------------------------------------- Emerald Mailing List listserver@emerald.iea.com
------ =_NextPart_000_01BC9DAC.657C1E20Content-Type: application/ms-tnefContent-Transfer-Encoding: base64
eJ8+IhYQAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNyb3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEEkAYAwAEAAAEAAAAQAAAAAwAAMAIAAAALAA8OAAAAAAIB/w8BAAAATQAAAAAAAACBKx+kvqMQGZ1uAN0BD1QCAAAAAGVtZXJhbGRAZW1lcmFsZC5pZWEuY29tAFNNVFAAZW1lcmFsZEBlbWVyYWxkLmllYS5jb20AAAAAHgACMAEAAAAFAAAAU01UUAAAAAAeAAMwAQAAABgAAABlbWVyYWxkQGVtZXJhbGQuaWVhLmNvbQADABUMAQAAAAMA/g8GAAAAHgABMAEAAAAaAAAAJ2VtZXJhbGRAZW1lcmFsZC5pZWEuY29tJwAAAAIBCzABAAAAHQAAAFNNVFA6RU1FUkFMREBFTUVSQUxELklFQS5DT00AAAAAAwAAOQAAAAALAEA6AQAAAB4A9l8BAAAAGAAAAGVtZXJhbGRAZW1lcmFsZC5pZWEuY29tAAIB918BAAAATQAAAAAAAACBKx+kvqMQGZ1uAN0BD1QCAAAAAGVtZXJhbGRAZW1lcmFsZC5pZWEuY29tAFNNVFAAZW1lcmFsZEBlbWVyYWxkLmllYS5jb20AAAAAAwD9XwEAAAADAP9fAAAAAAIB9g8BAAAABAAAAAAAAALpXwEEgAEAMQAAAFJFOiBBbnlvbmUgaGF2ZSBhbnkgdGlwcyB0byBzcGVlZCB1cCBTUUwgcXVlcnlzPwC4EAEFgAMADgAAAM0HBwAfAAwAFgAUAAQANAEBIIADAA4AAADNBwcAHwAMABIAOgAEAFYBAQmAAQAhAAAAQUFBQzhDMzc5ODA5RDExMTlCRkQwMDYwOTc3QjI4OUMANgcBA5AGANAKAAAhAAAACwACAAEAAAALACMAAAAAAAMAJgAAAAAACwApAAAAAAADAC4AAAAAAAMANgAAAAAAQAA5AMD0y+vNnbwBHgBwAAEAAAAxAAAAUkU6IEFueW9uZSBoYXZlIGFueSB0aXBzIHRvIHNwZWVkIHVwIFNRTCBxdWVyeXM/AAAAAAIBcQABAAAAFgAAAAG8nc3ryzeMrKsJmBHRm/0AYJd7KJwAAB4AHgwBAAAABQAAAFNNVFAAAAAAHgAfDAEAAAAMAAAAdG9tQHRkaS5uZXQAAwAGEHWVDxIDAAcQBQgAAB4ACBABAAAAZQAAAElDUkVBVEVEQVRSSUdHRVJGT1JJTlNFUlRJTkdPTlRIRUNBTExTVEFCTEVUSEFUSU5TRVJUU0pVU1RBQ0NUU1RBVFVTVFlQRT0yTUVTU0FHRVNBTkRBTElNSVRFRE5VTUJFUk8AAAAAAgEJEAEAAACkBwAAoAcAAA4NAABMWkZ1r6n6zHcACgEDAfcgAqQD4wIAY4JoCsBzZXQwIAcThwKDAFAO9nBycTIP9iZ9CoAIyCA7CW8yNWY1AoAKgXVjAFALA2MDAEELYG5nMTAzMxELpiBJIAUAZWF0IQmAIGEgdAUQZ2f9BJAgAhAFwAuAD7AAIAuADGcgAiAXEGhlIEPYQUxMBfABkWwY0BiwRxawF9UEIGp1cwVAQVBjY3RTAZB0GpBUxHlwGNA9IDIKogqAeQeBc2EXYAQgAHAW4mxlB3BpFsJudQbQF3FvJmYXkAiQbGQEICg1BxZRGLALgGspIHNvmxmkGLJkFrAXAHdhBCBacwDAbAMgHOJmILB0pi4b9BZgbm8H4GMDkfcFoACABvBpIFEY0APwGLCfCGAFQAkAAJAYUW15IoF/IREfMBqgBbAkkBziFmBy9nUDoAlwcAkRBCAeMB5Avx4xH8QZYhv0F+EWwGEW4Pcm4xjQC2ByF2IY+SHFG/TEQWwfkCwgeQhgIoM9BGB2GNArUQXAFsBtcBxkYhfRJTAH8EFNIPZiI+ErUm4J4BbgLQEPgHMr8RiyZXgXIBcAD4Fk/yCgCXAqLyswGpAupCGCB5B9BUBkBRAr9CKDJkAEEGnnGXIeYBzwLiAWUTESA5FkVWwvIS9XIzAY0ELzCsAvMGN1IFAXkxv1JJB9IFJiILAgMTIjHOIfImfbIMEJ4G0uMg9wdRhgB0C/AiAYYAlwC2AYMCvwbCSQuSDgb28YsDnwL+tNJJBsLjAb5Rv0VANwL/pQdC5TM8BMD8AkcBjQa/8iUgaQK0MgoAIwGKMXJSsw/EknIREy8RnhIbYv+Asw+R1AMzYBQBUQAUARQDpQhQWQdBCEMTYgLUOy/k8XMQuAB0AF0BxkQ7Mv9g9CxEKRCxNCxmktMTTGNAFAHUAxODABQAzQq0dTLMBGA2E6DINiD+B4TWlrGNBJcCEQF3Fb4FNNVFA6HWAPcR6AVkABoC8weAQALgWgbb5dL/UswAZgAjBI51Q40EsPoCBQeSswSnU58TNCMSswMTk5N05QMuI6O7AgUE1L1zyQSOenLIAEkAdAZEBQdS4IkMZhS4JL2HViakLxSOf8QW4rUC3wLmQAcCSQGDA2cAQgLQFzG5AW0XVwYQYAUUwgcQpQJVBzvj9FX0ZqQhQLthwDSB6AmwkAOQFsSOAwCURvHLP/U59Ur1W2M9Iucw+xVUIXAPdVwye2BdFRVdIuMiCwAJBfGqEakBfRFxEA0GsYQmTnImADoFxgYW1QgUtwM9D/NTAAkCSyJJAdcBlBSZAPQNMcAwORSVAW8GQyEBxhfxzTHUAaoBzBIREJAEQxc34vZfEj0QQgF6IZs2SWZNsIcRhRYRv0XGFjBpAIkP8uIQdxMuAGcQRwM8FNMGmh/wNgGXE4cAQAY0cW8AbgI+H7FZAuMjEe4B1gHcAxwRv0/y0BF2At8C8wI2EYsiYkM8P/PwQfoAuABaEmQW4WVcMs1H0ggWUMMBwDNuIW4CMwY/8rMC1yFrAschgzLQFhsB+Cf2TBTeAaUV9xFwEHcSPSd/8fMBmBIKAdcBhEbTVuY3R07wNSGKMYASvwci/ralJVw4FZy1NFTEVDVBjgdyEBS3B7UkQWsSswe1RVPRgBTmIgfCca3Bv0RlKSTy1QIkVQhDIuLLA8byJ7owQge1Mb9FdIGEVSRR7Ae1w+PVxKexpRJ05iLTCDQDOPD+BH4E7Qg9InXH1TYbsW4HtcPIKyG/SDCDGDunEfcEFORIGWSKB9AWSSQWSkPScB0DYubJAGNYmwTnAuNTInKbEb9E9SRIFgNSBZe03vL/p/VSLCHmBnCHA5oQIg93ocVgAscToHsHswBmF4gawgNDugKzBQTHFpHdCnTlAVsCswMjQF0EItIt15KG9pwTmhGFFzj0Mc0z9VgnhUFvAJcBhyWxJTQ95TXcILICBABABrKzB1pP8YsXqFVZE2p2sRGIEAcDpR/xdxlVxvhRaRMSFcYzPBjF//jWEPgAQgHfAJ8CXSAwAYUV8XomvUEXAEYAIwaGJyV/9bNZiBIsoLMWPzJoIrQD3h7yCxbyIEICPAcHOFSZAmMP9zAR0wmlFnUZ4kHiIJcAWh7x6hZuQEABv0cAhwMvEv0P0z0E8sQTERBcCXxgrACGD/HPEVkEfgM8GNFmsRHfAYQv8xESFhBbBj1RrgpzEYNTnx/xiBBGAaoR4xLDImQyswI8D/cZCQEmsRqQYW8CPgGMCQwf8ikI4iY9YW4an6FvEPgBzw9mZN4KtWWBFwJkNqJZeK/zWANVBMcTnwG/QakGgja+PcNzaRoR4xYgFjGZFcQf8lMW42pCMv61qfW6J0cBdRvxqgjjFcHKTCcLUc4S8Fsf8IYYy8eFRWJ7zfHANDsDYFPj2/L8A/wSwb9DPQL1zeXDPQwv/DdUlzQTPAScRdwjUvRTHC2MLAL8N8QdNLJAexdHcFsGsEIRwD/8bTwsGRsQ/wwpQWYAXwxijvwkXDecbdyxxOp+BNIJ6w+6XwycFLynlKnzymwS/ST//BTypYQ6TVb9Z/149FNqfn7k0LcB1AGFFMYILDeWVTL3hjUO8wCRIBAN3QAwAQEAAAAAADABEQAAAAAAMAgBD/////QAAHMGAbQnPNnbwBQAAIMGAbQnPNnbwBCwAAgAggBgAAAAAAwAAAAAAAAEYAAAAAA4UAAAAAAAADAAKACCAGAAAAAADAAAAAAAAARgAAAAAQhQAAAAAAAAMABYAIIAYAAAAAAMAAAAAAAABGAAAAAFKFAAC3DQAAHgAlgAggBgAAAAAAwAAAAAAAAEYAAAAAVIUAAAEAAAAEAAAAOC4wAAMAJoAIIAYAAAAAAMAAAAAAAABGAAAAAAGFAAAAAAAACwAvgAggBgAAAAAAwAAAAAAAAEYAAAAADoUAAAAAAAADADCACCAGAAAAAADAAAAAAAAARgAAAAARhQAAAAAAAAMAMoAIIAYAAAAAAMAAAAAAAABGAAAAABiFAAAAAAAAHgBBgAggBgAAAAAAwAAAAAAAAEYAAAAANoUAAAEAAAABAAAAAAAAAB4AQoAIIAYAAAAAAMAAAAAAAABGAAAAADeFAAABAAAAAQAAAAAAAAAeAEOACCAGAAAAAADAAAAAAAAARgAAAAA4hQAAAQAAAAEAAAAAAAAAHgA9AAEAAAAFAAAAUkU6IAAAAAADAA00/TcAAIs6
------ =_NextPart_000_01BC9DAC.657C1E20--