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

Lawrence Watkins Work ( (no email) )
Tue, 5 May 1998 13:21:28 -0500

Here is the script. The table is called MTD and has an index
Nothing pretty but works though! :-)
Runs really zippy and I have a good 798,000+ rows in calls

insert into mtd (login,hours,month) select username
login,hours =
(sum(acctsessiontime)/3600),DATENAME(mm,getdate()) from
calls where acctstatustype is not null and
DATENAME(mm,CallDate) LIKE DATENAME(mm,getdate()) group by

-----Original Message-----
From: Michael Whisenant <>
To: <>
Date: Tuesday, May 05, 1998 7:20 AM
Subject: Re: Is there a way to speed up Large database
searches in SQL?

> Care to post the script that is running? Many may find it
>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.
>>-----Original Message-----
>>From: Jeff Binkley <>
>>To: <>
>>Date: Monday, May 04, 1998 11:53 PM
>>Subject: Is there a way to speed up Large database
>>in SQL?
>>>-> Maybe someone could help me on speeding up SQL
>>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
>>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
>>date had passed?
>>>-> For example:
>>>-> Our database has information from December 97... and
>>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
>>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
>>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
>>with indexes
>>>becoming fragmented on the drives.
>>> Jeff Binkley
>>> ASA Network Computing
>>> --------------------------------------------------------

-->>> NTISP Mailing>>>>>>>>>>>>>> ---------------------------------------------------------->> NTISP Mailing>>>Michael J. Whisenant>Vice-President, Operations>AIRnet Internet Services, Inc.>ph: (256) 704-4692 fax: (256) 704-2329>> ----------------------------------------------------------> NTISP Mailing List    >>