Bob Bedell
bobbedell15 at msn.com
Thu Jun 12 09:01:15 CDT 2003
Hi Jim, Any idea how to get Jet 4.0 to kick out a query plan? The SHOWPLAN documentation at your link is for Jet 3.5. The registry keys changed a bit for 4.0. The ISAMStats function works fine with 4.0 (just DAO stuff, no registry settings involved). Thanks, Bob >From: "Jim Dettman" <jimdettman at earthlink.net> >Reply-To: accessd at databaseadvisors.com >To: <accessd at databaseadvisors.com> >Subject: RE: [AccessD] Access Data Retrieval >Date: Wed, 11 Jun 2003 21:15:47 -0400 > >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 > > >_______________________________________________ >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com _________________________________________________________________ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963