Re: Is there a way to speed up Large database searches in SQL?

Michael Whisenant ( mwhisen@airnet.net )
Tue, 05 May 1998 12:11:32 -0500

Lar,

Care to post the script that is running? Many may find it useful.

At 09:24 AM 5/5/98 -0500, you wrote:
>I have a scheduled task that figures out all of my users
>time online once an hour then writes that to a table
>including the current month and login name. Then when the
>users check time online it comes from the much
>smaller/faster table.
>Lar
>-----Original Message-----
>From: Jeff Binkley <jeff.binkley@asacomp.com>
>To: ntisp@emerald.iea.com <ntisp@emerald.iea.com>
>Date: Monday, May 04, 1998 11:53 PM
>Subject: Is there a way to speed up Large database searches
>in SQL?
>
>
>>-> Maybe someone could help me on speeding up SQL Searches.
>We would like to
>>-> offer a from - to period search on our database so our
>clients can view
>>-> their time online. The one problem we are having due to
>our Huge database
>>-> is that it can take several minutes for the results to
>appear. Is there any
>>-> SQL Command that will stop searching after the specified
>date had passed?
>>-> For example:
>>->
>>-> Our database has information from December 97... and we
>would like to do a
>>-> search from say January to February. But when we run
>the SQL Query it scans
>>-> the WHOLE Database... Is there any way of telling SQL to
>stop searching
>>-> after it Finds March?
>>
>>What you are talking about is a table scan. That is not
>how you want to
>>search SQL databases. You need an index which covers the
>fields you are
>>searching on and a query which is written so as not to
>force the SQL server
>>to do table scans when looking for data. Without seeing
>the SQL code or
>>knowing what SQL server you are using (i.e. Sybase, MS SQL,
>oracle etc..), it
>>is difficult to be more specific.
>>
>>Now there is a downside to indexes, especially if you go
>"index happy", slow
>>inserts. This is beacuse every index needs to be updated
>when a record is
>>inserted. Also you may want to setup period index
>rebuilding on tables
>>which have regular large amounts of inserts. This can help
>with indexes
>>becoming fragmented on the drives.
>>
>>
>> Jeff Binkley
>> ASA Network Computing
>>
>> ----------------------------------------------------------
>> NTISP Mailing List listserver@emerald.iea.com
>>
>>
>
>
>
> ----------------------------------------------------------
> NTISP Mailing List listserver@emerald.iea.com
>
Michael J. Whisenant
Vice-President, Operations
AIRnet Internet Services, Inc.
ph: (256) 704-4692 fax: (256) 704-2329