Jim Dettman
jimdettman at earthlink.net
Wed Jun 11 20:15:47 CDT 2003
Stuart, <<As Drew said, Jet pulls the Index, not the data across the network. It then decides which records to retreive and pulls just those. Still with 100,000 records, just that one Index will show as noticeably traffic on the network. >> It doesn't even pull the entire index. Probably 5-10 pages at most. As I said in my post, Jeanine can easily check what's going on with SHOWPLAN and ISAMSTATS. 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 Stuart McLachlan Sent: Wednesday, June 11, 2003 6:22 PM To: Jeanine Scott; accessd at databaseadvisors.com Subject: RE: [AccessD] Access Data Retrieval As Drew said, Jet pulls the Index, not the data across the network. It then decides which records to retreive and pulls just those. Still with 100,000 records, just that one Index will show as noticeably traffic on the network. A straigth VB app using an Access BE will do exactly the same. If you move to SQL Server, the engine on the BE will do all the work and ONLY pass back the required data. Again it doesn't matter whether the FE is Access or VB - they will both get their data in the same way and generate the same amount of network traffic On 11 Jun 2003 at 13:58, Jeanine Scott wrote: > I don't know how much traffic. All they will tell me is that the network > usage "spikes" and I don't know what questions to ask to find out more. What > I'm concerned with is when we move to SQL that it will still be a problem > because "access sucks" - their viewpoint - not mine. > > I'm working as a consultant for a Fortune 500 company which has it's own > extremely large internal IT department as well as consultants from other > companies and it's like pulling teeth to get any kind of information. I have > a lot of experience in designing relational databases and believe it or not > (considering my questions to this list) a fair amount of experience with vba > (thanks in a large part to this list!) but very little experience on how > data transfers work or the infrastructure of the network and such. So, any > information you all can give me is extremely helpful! > > > Jeanine > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Drew Wutka > Sent: Wednesday, June 11, 2003 1:19 PM > To: 'accessd at databaseadvisors.com' > Subject: RE: [AccessD] Access Data Retrieval > > First, the VBer's are wrong. You are actually going to pull the index for > the Autonumber field across the network. It is then going to pull the chunk > it needs for actual tables. How much traffic are you actually seeing? > > Drew > > -----Original Message----- > From: Jeanine Scott [mailto:jscott at mchsi.com] > Sent: Wednesday, June 11, 2003 12: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 > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com -- Lexacorp Ltd http://www.lexacorp.com.pg Information Technology Consultancy, Software Development,System Support. _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com