Querying large databases | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Querying large databases

Hi, I’m running the following query in an asp page from an MS Access Table with 630000 Entries via a DNSless ADODB connection : sql = "SELECT companyId, companyName FROM companies WHERE id1 = " & iNumber1 & " AND id2 = " & iNumber2 & " AND active <> ‘n’ " The "companyId" field is an index. It takes about 50 seconds to get the result displayed on a page that shows 15 lines per page and the recordset RS.RecordCount value. Such a delay is not acceptable on a portal web site, and what i’m trying to do is to reduce this waiting time down to 5 or 10 seconds. I don’t use the cursors. I tried adding an index to the companyName field, but there was no improvement in performance. I tried to use the "getRows" method instead of displaying data directly from the Recordset, but there was no significant improvement. I know, i’m better off using SQL Server and stored procedures instead of an MS Access database, but my boss does not want to invest in new software. I would appreciate your help. Nicolas
use SQL Express, its free,
also, how many rows are returned?
also you want an index on a combination of id1, id2 and active (unless id1 and id2 is already so specific that active doesn’t really add much value to the index) Cheers
Actually, the query returns a recordset count of 630 000 entries and displays only the 15 first lines, and then then next 15 lines on next page, and so on. The exact query testing time is now down to 22 seconds, but i’m affraid that under real conditions this would still be very slow and penalizing. The companyId field is now an AutoNumber. Are there any limitations on SQL Express ? like a limited number of concurrent users ? Twan. Could you please explain further about having an index on a combination of id1, id2 and active ? what does it exactly mean, and how do i do it ? Thanks

my recommendation is such situations is to return about 4-10X the number of displayed records,
so for 15, try selecting only the top 60-150 rows,
then allow paging without going back to the DB server,
if more, then go back for the next batch but bringing back 630K will swamp both the DB and web server, no matter what db/web you are using see various articles on paging for SQL
read this article about paging:
Hi ya, Not 100% sure in Access but in SQL you can create an index on combinations of columns. The index is typically for fields that are in the where clauses rather than as part of the select clause Cheers
Hi, Twan also meant that you can add column "active" to your index. Just note, adding to index column stored repeated values (like yes/no, male/female etc) almost will not affect performance but still use disk space to store index. Index on "CompanyName" is unusefull in this case, this column does not exists in search conditions. One more issue could make retrieveing slowly – when string filed is filled by spaces at the end (for example company name include 15 characters and has 85 spaces at the end). In this case trimming returned data could help, especially for site performance. Last – its 100% correct that you don’t need to retrieve 630 000 rows incase you have to display only 15. You are using to much resources for nothing. Maksim
You could try SQL Optimizer for Visual Studio. It will automatically rewrite you SQL Statement in every possible way and determine the one with the best performance. You can download a free 30 day trial at http://www.extensibles.com/modules.php?name=Products&op=SSP Debugging is a state of mind. http://www.extensibles.com