-----Original Message-----
From: Lawrence Watkins Work <lwatkins-work@thepark.net>
To: ntisp@emerald.iea.com <ntisp@emerald.iea.com>
Date: May 5, 1998 2:30 PM
Subject: Re: Is there a way to speed up Large database searches in SQL?
>Here is the script. The table is called MTD and has an index
>column.
>Nothing pretty but works though! :-)
>Runs really zippy and I have a good 798,000+ rows in calls
>table.
>
>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
>username
>
>-----Original Message-----
>From: Michael Whisenant <mwhisen@airnet.net>
>To: ntisp@emerald.iea.com <ntisp@emerald.iea.com>
>Date: Tuesday, May 05, 1998 7:20 AM
>Subject: Re: Is there a way to speed up Large database
>searches in SQL?
>
>
>>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
>>
>> ----------------------------------------------------------
>> NTISP Mailing List listserver@emerald.iea.com
>>
>>
>
>
>
> ----------------------------------------------------------
> NTISP Mailing List listserver@emerald.iea.com
>