Jim Dettman
jimdettman at earthlink.net
Wed Jun 11 12:59:14 CDT 2003
Jeanine, <<Is this correct? >> In this case no and you can prove it to yourself with SHOWPLAN and the ISAMStats function. Showplan writes into a text file how JET with execute a specific query. ISAMStats can tell you the number of disk reads/writes done by JET. Showplan does have limitations, such as the fact that it can't handle sub-queries, but for your example it would work well. Anytime you execute a SQL statement, JET comes up with a costing plan, which is the most efficient way to execute a query. It looks at things such as: a. WHERE or HAVING statements b. ORDER BY c. JOINS d. Available indexes and the uniqueness of those indexes (table scans can be faster then using an index). e. Table stats. In the case of querydefs, the plan is saved with the query. For the most part, this is beneficial, but it can hurt you in some situations (ie. table stats are way off). What would happen in the example that you provided, is that the index pages for Unique ID would be returned. The number of pages depends on the size of the key and the number of record in the table. For a 100,000 record table, it probably would return no more then 5-10 index pages (JET uses a B-TREE ISAM index structure). So 5-10 reads for the index, and then 1 read for the record itself. There would be some additional overhead in reading Msysobjects, locating the index and table, etc., but in this case JET would definitely NOT read 100,000 records and pull them over the wire. For additional info on SHOWPLAN and ISAMSTATS, see: http://support.microsoft.com/default.aspx?scid=kb;en-us;162701 Jim Dettman President, Online Computer Services of WNY, Inc. (315) 699-3443 jimdettman at earthlink.net -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jeanine Scott Sent: Wednesday, June 11, 2003 1:36 PM To: accessd at databaseadvisors.com Subject: [AccessD] Access Data Retrieval I need something explained to me - this just doesn't make any sense to me but I've had quite a few hard core vb developer's swear that the following scenario is how Access retrieves data. Scenario: FE on client BE on Server tblLoans has 100,000 records with 3 fields tblLoans UniqueID - AutoNumber LoanNo (indexed) LastName (indexed) You bind a form to a specified recordset such as "Select * from tblLoans where UniqueID = 1" or "Select * from tblLoans where LoanNo = 101" What I have been told is that when Access goes to the server it brings ALL the records 100,000 records back to the client and then applies criteria to find out which record you actually asked for. So, in effect you are returning 100,000 rows of data back to the client even though you may have only asked for 1. Is this correct? If it is why is this the case and how in the world can I ever justify using Access as a development tool to a hard core VB'r?? If it isn't correct how can I explain in the exact scenario above the fact that the LAN tests show mega tons (ok -technical term there...) of data being passed back to the client when this form is called? I'm understanding that SQL will work the same way? Thank you so much! _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com