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

Jeff Binkley ( jeff.binkley@asacomp.com )
Tue, 05 May 1998 05:25:00 -0500

-> 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