Advice with SLOW SELECTs against large SQL Server database 
Author Message
 Advice with SLOW SELECTs against large SQL Server database

I have a database containing about 3,000,000 records.

I need to select a large number of records (about 30,000) from this
database each day, and then loop through them all creating a file for
transmission to another system.  The SELECT statement needs to have a
number of parameters (about 4 or 5) in both its WHERE and ORDER BY
clauses.

Currently the SELECT is taking 30-60 minutes to complete.  Only then
can I start to loop through the records to create the file.

What can I do to speed up the process (if anything!)?

Will doubling the RAM from 128Mb to 256Mb make a significant
difference?

Should I first SELECT INTO a temporary table with just the WHERE
clause and then do another SELECT against this table with just the
ORDER BY clause?

Any help/advice/guidance/suggestions gratefully received!

I'm using VB4.0-32 bit with RDO/ODBC and SQL Server 6.5 all under
NT4.0.

RichardF



Fri, 24 Dec 1999 03:00:00 GMT  
 Advice with SLOW SELECTs against large SQL Server database

Quote:

>I have a database containing about 3,000,000 records.

>I need to select a large number of records (about 30,000) from this
>database each day, and then loop through them all creating a file for
>transmission to another system.  The SELECT statement needs to have a
>number of parameters (about 4 or 5) in both its WHERE and ORDER BY
>clauses.

Creating indexes for the parameters used in the WHERE clause should
help a great deal. Make sure each index only refers to one column,
since multi-column indexes aren't always useable by the query
optimizer.

Terry

--------------------

Terry Carmen
Meadowbrook Software

NOTE TO SPAMMERS:
BAD THINGS will happen to any site that sends me email spam.



Fri, 24 Dec 1999 03:00:00 GMT  
 Advice with SLOW SELECTs against large SQL Server database

Check and make sure that the fields in your WHERE clause are indexed. Also
make sure that you are not forcing a table-scan by including things like
UPPER, LEFT, against the fields in the table


You could also try running the query in ISQL/W and have the query plan
shown and see if the indexes are even being used.

--
Chad M. Hegerty

Web:    http://users1.ee.net/hegertyc



Quote:
> I have a database containing about 3,000,000 records.

> I need to select a large number of records (about 30,000) from this
> database each day, and then loop through them all creating a file for
> transmission to another system.  The SELECT statement needs to have a
> number of parameters (about 4 or 5) in both its WHERE and ORDER BY
> clauses.

> Currently the SELECT is taking 30-60 minutes to complete.  Only then
> can I start to loop through the records to create the file.

> What can I do to speed up the process (if anything!)?

> Will doubling the RAM from 128Mb to 256Mb make a significant
> difference?

> Should I first SELECT INTO a temporary table with just the WHERE
> clause and then do another SELECT against this table with just the
> ORDER BY clause?

> Any help/advice/guidance/suggestions gratefully received!

> I'm using VB4.0-32 bit with RDO/ODBC and SQL Server 6.5 all under
> NT4.0.

> RichardF




Fri, 24 Dec 1999 03:00:00 GMT  
 Advice with SLOW SELECTs against large SQL Server database

Richard,

Have you examined the query plan in ISQL?  Turn on the "No Execute" and
"Show Query Plan" options in an ISQL or SEM query window, then execute the
query.  If you see any TABLESCANs, determine which column is causing it,
then try building an index on that column, or that includes it.

Try to rephrase the query so that it takes advantage of the table's primary
key.  It might be worth looking at whether the existing PK is actually
"useful" in terms of how the data is being accessed.

Another approach is to build an index that includes the columns of the
WHERE and ORDER BY clauses in left->right order, and rephrasing the clauses
appropriately so that the optimizer will choose that index.

Also, try limiting your column comparison operators to =, >, >=, and
BETWEEN.  These are handled more efficiently than <>, <, and IN.

Alan Rueckgauer
Rueckgauer Systems
http://members.aol.com/rueckgauer



Quote:
> I have a database containing about 3,000,000 records.

> I need to select a large number of records (about 30,000) from this
> database each day, and then loop through them all creating a file for
> transmission to another system.  The SELECT statement needs to have a
> number of parameters (about 4 or 5) in both its WHERE and ORDER BY
> clauses.

> Currently the SELECT is taking 30-60 minutes to complete.  Only then
> can I start to loop through the records to create the file.

> What can I do to speed up the process (if anything!)?



Fri, 24 Dec 1999 03:00:00 GMT  
 Advice with SLOW SELECTs against large SQL Server database


Quote:
> Creating indexes for the parameters used in the WHERE clause should
> help a great deal. Make sure each index only refers to one column,
> since multi-column indexes aren't always useable by the query
> optimizer.

That's not precisely true.  If I have a multi-column index, it will be used
for the column in my WHERE clause if the column being used is listed FIRST
in the creation of the multi-column index.

        e.g.  CREATE INDEX name
               ON employees (first_name, last_name)
        This index will be used if I mention first_name in my WHERE clause, if
        I mention BOTH first_name and last_name in my WHERE clause, but
        not if I mention only last_name.

--Amy Hemphill/MCT



Sat, 25 Dec 1999 03:00:00 GMT  
 Advice with SLOW SELECTs against large SQL Server database

Quote:

>I have a database containing about 3,000,000 records.

>I need to select a large number of records (about 30,000) from this
>database each day, and then loop through them all creating a file for
>transmission to another system.  The SELECT statement needs to have a
>number of parameters (about 4 or 5) in both its WHERE and ORDER BY
>clauses.

Index the columns you are performing joins against (if you are performing
joins)

Update stats

If this is a stored proc:  Since you are providing parameters to the where
clause, you get a query plan based upon the parameters passed the first time
the proc is compiled (distinct from created).  Try defining the proc WITH
RECOMPILE.

Do use SET SHOWPLAN ON



Sun, 26 Dec 1999 03:00:00 GMT  
 Advice with SLOW SELECTs against large SQL Server database

On Tue, 08 Jul 1997 13:02:55 -0700, "Amy Hemphill"

Quote:


>> Creating indexes for the parameters used in the WHERE clause should
>> help a great deal. Make sure each index only refers to one column,
>> since multi-column indexes aren't always useable by the query
>> optimizer.

>That's not precisely true.  If I have a multi-column index, it will be used
>for the column in my WHERE clause if the column being used is listed FIRST
>in the creation of the multi-column index.

What's not precisely true? That multi-column indexes aren't always
useable by the optimizer?

I didn't suggest multi-column indexes because neither the database
structure or query were posted.

Terry

--------------------

Terry Carmen
Meadowbrook Software

NOTE TO SPAMMERS:
BAD THINGS will happen to any site that sends me email spam.



Sun, 26 Dec 1999 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Insert into SQL Server slow, slow, slow...

2. BUG: Visual Basic 6.0 T-SQL Debugger Fails Against SQL Server 2000 Named Instance (Q310647)

3. Large SQL Server 7 Database Problem from VB6

4. ADO and Large SQL Server Database?

5. Looking for advice: Retrieving large number of rows from SQL, to a web client

6. Looking for advice: Retrieving large number of rows from SQL, to a web client

7. Looking for advice: Retrieving large number of rows from SQL, to a web client

8. Looking for advice: Retrieving large number of rows from SQL, to a web client

9. Looking for advice: Retrieving large number of rows from SQL, to a web client

10. Slow SQL of large DB

11. Very Slow saves with large database in MS Access 2000

12. Parameter with ADODB.Command against SQL-server

 

 
Powered by phpBB® Forum Software