trying to minimize Write Conflicts in a multi-user database
in [General]
|
Prev: não acho meu orkut
Next: maike yordano pirela vera
From: Marshall Barton on 18 Jan 2010 13:07 John W. Vinson wrote: >Marshall Barton wrote: > >>More than a little intriguing is the show plan use of Not >>0=0 when the query used some other expression for False. >>This strongly implies that the query optimizer can recognize >>expressions that evaluate to False. Why it would then say >>it will use a full table scan seems contradictory to me. > >I wonder if it's the same effect that causes references to Rnd() in a query to >return the same value for all rows: the query engine determines that no table >field is included in the expression and evaluates it only once. Might the same >be the case with 0=0 or False as a criterion? Good thought John. Seems like very logical behavior, until they fail to use that info and do a table scan anyway (if that's still what happens?) -- Marsh MVP [MS Access]
From: Banana on 18 Jan 2010 13:12 Marshall Barton wrote: > I tried to search Google Groups, but that seems to have lost > its ability to find stuff in the archives. David, if you > are following this thread, we would appreciate it if you can > dredge up that test and re post your findings. Thanks for the efforts- I was worried I may have had missed something obvious in my attempt. I really wish there was a better archiving solution. Ironically enough, when I was searching the archive, I ran across a old thread where the poster said she couldn't find a cited thread even with deja.com, and that was a decade ago! Guess nothing much really has changed. > Banana, he performance results you and Tom are reporting are > very interesting, but I am not totally convinced at this > time. Performance testing can be very difficult and tricky, > especially with things that involve I/O. When caching is > used, the first run after a boot can be drastically > different than subsequent runs. I don't doubt there will be many variables that could skew the tests which was why I wanted to cross reference David's test to check what I could have had missed. FWIW- using OLE automation to start up an Access application and run only one query using CurrentDb.Execute (e.g. no saved query), then immediately closing, quitting and cleaning up does not appear to materially affect the time. The Showplan seems to suggest that a new instance of engine was started up via OLE Automation, so this should reflect the boot time/cold start/no caching, I'd think. > Today's multi core processors and seriously fast multi gigabyte memory may very > well make a cached table scan quick enough, maybe nearly as > fast as an index scan. I'm not so sure about multi-cores/CPU processing. AFAIK, Access continues to be single-threaded so it'd always use one core so the benefit of mulitple cores/CPU would be indirect in that OS may be able to allocate competing resources to other cores/CPUs but beyond that, not much difference. Note that while JET/ACE has a setting for numbers of threads, KB articles suggests that thread safety was achieved only if you use DAO or OLEDB provider and even so, thread-safety is limited. I also tried tweaking the threads setting from 1 to 4 (default is 3) and the timing was not affected at least slightest, suggesting to me that threading was not relevant here. KB Article: http://support.microsoft.com/kb/169395 http://support.microsoft.com/kb/299973 As for processor & memory enabling a table scan to be as fast as index scan, this was reason why I did extra check of doing a For...Next Loop to get a rough idea of how much time it would take to do the false comparisons that many time. If table scan actually were being done, the time should have had increased when I expanded the tables. It didn't while the For...Next loop increased just as much as the increase of records. As a further comparison, doing a "Not PK = PK" was ridiculously slow, around 40,000 milliseconds. It's also unfair because of comparing two variables rather than two hard coded values, but I think the point remains- if 1=0 actually did do a table scan, it should have been just as slow as the For...Next loop at least and certainly four times slower after the table expanded fourfold. > Then there is the issue of what other active processes are > also using the system's resources. A full table scan in > cached memory on a lightly loaded system might be pretty > fast, but when there are other processes making large > demands for processor cycles and memory, the result could be > a completely different story. I suppose I could try the test again by re-allocating only 512 MB to the VM and see what happens. > More than a little intriguing is the show plan use of Not > 0=0 when the query used some other expression for False. > This strongly implies that the query optimizer can recognize > expressions that evaluate to False. Why it would then say > it will use a full table scan seems contradictory to me. Well, I've kind of found Showplan to be quite lacking in the details. For instance, it still does not support showing plans when subqueries are used, despite having had went through 2 more JET versions and 2 ACE versions. Another telltale sign is that when the ShowPlan starts up, it records the Date of 0, suggesting to me that any bugs, issues and/or enhancements associated with ShowPlan wasn't considered high-priority and for those reasons, I think it is prudent to take the Showplan's output with a grain of salt.
From: John Spencer on 18 Jan 2010 14:31 Can you send me an email address so I can send you a zipped sample database? If you post it here make sure you "munge" it up so the spammers cannot harvest it. Do something like P aul// <AT> some-//-Domain.Remove.org Please remove extraneous spaces and characters. Or tell me what it is using Send the email to jspencer and the remainder of my address after the at is: hilltop.umbc.edu John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Paul wrote: >> If you need help building that code, post back. > > John, > > YES, PLEASE! I'd never be able to figure it out on my own. > > I've taken several runs at trying to understand DAO and ADO coding, but I > still struggle with it. The only MS Access book I ever found that explained > it in a way I could understand it was the manual for Access 2.0, which I > only borrowed temporarily. But I didn't use it right away, and didn't > manage to retain what I understood from it. > > If you know of any Web sites that explain the basics of DAO and ADO, I'd > love to check them out. > > And thanks for your help with this. > > Paul > >
From: Tom Wickerath AOS168b AT comcast DOT on 18 Jan 2010 15:16 > Well, I've kind of found Showplan to be quite lacking in the details. > For instance, it still does not support showing plans when subqueries > are used, despite having had went through 2 more JET versions and 2 ACE > versions. Another telltale sign is that when the ShowPlan starts up, it > records the Date of 0, suggesting to me that any bugs, issues and/or > enhancements associated with ShowPlan wasn't considered high-priority > and for those reasons, I think it is prudent to take the Showplan's > output with a grain of salt. I would sure like to see improvements made to the ShowPlan functionality. Perhaps several of us can put that forward as a suggestion to the Access Team. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ __________________________________________ "Banana" wrote: > Marshall Barton wrote: > > I tried to search Google Groups, but that seems to have lost > > its ability to find stuff in the archives. David, if you > > are following this thread, we would appreciate it if you can > > dredge up that test and re post your findings. > > Thanks for the efforts- I was worried I may have had missed something > obvious in my attempt. I really wish there was a better archiving > solution. Ironically enough, when I was searching the archive, I ran > across a old thread where the poster said she couldn't find a cited > thread even with deja.com, and that was a decade ago! Guess nothing much > really has changed. > > > Banana, he performance results you and Tom are reporting are > > very interesting, but I am not totally convinced at this > > time. Performance testing can be very difficult and tricky, > > especially with things that involve I/O. When caching is > > used, the first run after a boot can be drastically > > different than subsequent runs. > > I don't doubt there will be many variables that could skew the tests > which was why I wanted to cross reference David's test to check what I > could have had missed. > > FWIW- using OLE automation to start up an Access application and run > only one query using CurrentDb.Execute (e.g. no saved query), then > immediately closing, quitting and cleaning up does not appear to > materially affect the time. The Showplan seems to suggest that a new > instance of engine was started up via OLE Automation, so this should > reflect the boot time/cold start/no caching, I'd think. > > > Today's multi core processors and seriously fast multi gigabyte memory may very > > well make a cached table scan quick enough, maybe nearly as > > fast as an index scan. > > I'm not so sure about multi-cores/CPU processing. AFAIK, Access > continues to be single-threaded so it'd always use one core so the > benefit of mulitple cores/CPU would be indirect in that OS may be able > to allocate competing resources to other cores/CPUs but beyond that, not > much difference. Note that while JET/ACE has a setting for numbers of > threads, KB articles suggests that thread safety was achieved only if > you use DAO or OLEDB provider and even so, thread-safety is limited. > > I also tried tweaking the threads setting from 1 to 4 (default is 3) and > the timing was not affected at least slightest, suggesting to me that > threading was not relevant here. > > KB Article: > http://support.microsoft.com/kb/169395 > http://support.microsoft.com/kb/299973 > > As for processor & memory enabling a table scan to be as fast as index > scan, this was reason why I did extra check of doing a For...Next Loop > to get a rough idea of how much time it would take to do the false > comparisons that many time. If table scan actually were being done, the > time should have had increased when I expanded the tables. It didn't > while the For...Next loop increased just as much as the increase of > records. As a further comparison, doing a "Not PK = PK" was ridiculously > slow, around 40,000 milliseconds. It's also unfair because of comparing > two variables rather than two hard coded values, but I think the point > remains- if 1=0 actually did do a table scan, it should have been just > as slow as the For...Next loop at least and certainly four times slower > after the table expanded fourfold. > > > Then there is the issue of what other active processes are > > also using the system's resources. A full table scan in > > cached memory on a lightly loaded system might be pretty > > fast, but when there are other processes making large > > demands for processor cycles and memory, the result could be > > a completely different story. > > I suppose I could try the test again by re-allocating only 512 MB to the > VM and see what happens. > > > More than a little intriguing is the show plan use of Not > > 0=0 when the query used some other expression for False. > > This strongly implies that the query optimizer can recognize > > expressions that evaluate to False. Why it would then say > > it will use a full table scan seems contradictory to me. > > Well, I've kind of found Showplan to be quite lacking in the details. > For instance, it still does not support showing plans when subqueries > are used, despite having had went through 2 more JET versions and 2 ACE > versions. Another telltale sign is that when the ShowPlan starts up, it > records the Date of 0, suggesting to me that any bugs, issues and/or > enhancements associated with ShowPlan wasn't considered high-priority > and for those reasons, I think it is prudent to take the Showplan's > output with a grain of salt.
From: Tom Wickerath AOS168b AT comcast DOT on 18 Jan 2010 15:35
Hi Marsh, > I tried to search Google Groups, but that seems to have lost > its ability to find stuff in the archives. David, if you > are following this thread, we would appreciate it if you can > dredge up that test and re post your findings. I sent a private e-mail to David late last night, inquiring about any past test results. He replied as follows: "I don't recall anything of the sort, and don't see it in Google Groups. Nor do I have archives of my posts from way back when. Sorry I can't help. Maybe Marshall has a better reference?" > Performance testing can be very difficult and tricky, > especially with things that involve I/O. I agree. When I am doing such testing at work, I typically reboot several times, between each test, and I make sure to shut down as many applications as I can, including Outlook, to try to make a somewhat stable baseline. On my work PC, I cannot shut off the antivirus, so I just have to live with the possibility that it may be adding some network load. I think a good way to test the 1=0 question might be to start with a really large table, like Banana did, and monitor the amount of data transferred for: 1.) An intentional table scan Run some query that forces all records to be brought over a network wire, perhaps an aggregrate query to sum a numeric field. 2.) WHERE conditions that include 1=0, WHERE False, etc. One needs to first display the network monitor icon in the System Tray. In Windows XP, one does this via: Control Panel | Network Connections Double-click the connection of interest, and then left-click on Properties. Select the check box for the option that reads "Show icon in notification area when connected". When you double-click the resulting System Tray icon, you will see Packets Sent and Packets Received values. With a baseline that is as repeatable as possible (ie. Outlook and any other networked applications shut down), read the Packets Received value before and after each individual test. The difference (delta) represents how many packets of data was transferred to solve each query. Of course, one must keep in mind that some other application that you may not be able to shut down may have caused some of the traffic for a given test. So, one can run the test several times, in an attempt to make sure there is not a contributing influence from some other application. You need a split application, with the BE on a network share, in order to use this method. Several years ago, John Viescas recommended this method to me as a way of testing how much data actually comes over the network wire. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ __________________________________________ "Marshall Barton" wrote: > I tried to search Google Groups, but that seems to have lost > its ability to find stuff in the archives. David, if you > are following this thread, we would appreciate it if you can > dredge up that test and re post your findings. > > Banana, he performance results you and Tom are reporting are > very interesting, but I am not totally convinced at this > time. Performance testing can be very difficult and tricky, > especially with things that involve I/O. When caching is > used, the first run after a boot can be drastically > different than subsequent runs. Today's multi core > processors and seriously fast multi gigabyte memory may very > well make a cached table scan quick enough, maybe nearly as > fast as an index scan. > > Then there is the issue of what other active processes are > also using the system's resources. A full table scan in > cached memory on a lightly loaded system might be pretty > fast, but when there are other processes making large > demands for processor cycles and memory, the result could be > a completely different story. > > Tom's performance over a WAN (how fast?) is interesting and > warrants further analysis under varying user scenarios. > > More than a little intriguing is the show plan use of Not > 0=0 when the query used some other expression for False. > This strongly implies that the query optimizer can recognize > expressions that evaluate to False. Why it would then say > it will use a full table scan seems contradictory to me. > > -- > Marsh > MVP [MS Access] |