Table should be truncated before running the query
that way only up to date data is kept.
There probably is a better way to setup the table,
but this is sufficient for our needs now. :-)
Lar
-----Original Message-----
From: Rudy Komsic <rudyk@cyberglobe.net>
To: ntisp@emerald.iea.com <ntisp@emerald.iea.com>
Date: Tuesday, May 05, 1998 1:42 PM
Subject: Re: Is there a way to speed up Large database
searches in SQL?
>when I try to run this query, I get "invalid object name
'mtd' "
>Must I create this table beforehand?
>
>-----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 Listlistserver@emerald.iea.com>>>>>>>>>>>>>> ---------------------------------------------------------->> NTISP Mailing Listlistserver@emerald.iea.com>>>>>> ----------------------------------------------------------> NTISP Mailing List listserver@emerald.iea.com>>