Some of the SQL Server tables i work on , using ADO, typically have over 

5000 rows. I use getrows() extensively but I'm interested in knowing abt 

other optimization techniques that would speed up the retrieval of records 

on the web pg.



I would also like to hear abt experiences of others who work with huge 

tables & appreciate if they share their suggestions.





:-)rk

5,000 records is a pittance. Try 100,000 or 1,000,000+ records.

When you ask how to speed up retrieval you should specifiy the

context in which the recordset will be selected.



Is this a simple SELECT, a SELECT with aggregate values,

a complex series of joins, a SELECT on the primary key,

a SELECT on a dependent key, a SELECT with sub-queries,

etc?



The answer to each of these SELECTS is somewhat different.



One thing to keep in mind is that a web application should use

a de-normalized database. Why?, the fewer joins, the quicker

the response.



If you anticipated cross-tabulated SELECTS, the use aggregation

tables, particularly, if the database is loaded only once per day.

Right after the data load, re-calculate the values in the aggregation

tables. Then use these tables to satisfy cross-tabulated SELECTS.



Avoid the use of CURSORS in your sprocs. CURSORS use a lot

of resources and are slow. Recently, I participated in re-writing the

stored procs in an application that used CURSORS. The application

was written by an "EXPERT" development firm (!!!). I replaced all

the CURSORS by using temp tables and SELECTS with sub-queries

in my sprocs. Performance improved by a factor over 50-1.



Also, try to use ANSI SQL-92 syntax in your selects. Avoid

the older form WHERE a.pk_id=b.fk_id etc.

Instead use this form:

FROM table1 a

JOIN table2 b ON a.pk_id = b.fk_id

WHERE .......

in the WHERE clause the ordering of the conditions can affect the

speed of the SELECT.

Place the conditions that would return the smallest sub-set first in the

list,

place the conditions that would return the largest sub-set last.

At least this is true is SQL Server. With Oracle the reverse is true.



Hope this helps,



Dallas











----- Original Message -----

From: <speedguru@m...>

To: "ASP Databases" <asp_databases@p...>

Sent: Saturday, July 21, 2001 1:20 PM

Subject: [asp_databases] size Matters





> Some of the SQL Server tables i work on , using ADO, typically have over

> 5000 rows. I use getrows() extensively but I'm interested in knowing abt

> other optimization techniques that would speed up the retrieval of records

> on the web pg.

>

> I would also like to hear abt experiences of others who work with huge

> tables & appreciate if they share their suggestions.

>

>

> :-)rk

>