The SQL Server 2005 Developer Edition installed on my Windows XP SP3 laptop had crashed over the weekend, so after a fresh reinstall including SP2 with hotfixes up to version 3073, I decided to re-run my SQL Server Migration Assistant for Access (SSMA) to create a new conversion file for testing.
The application performance was extremely poor, so I tried running SQL Profiler to try to find the bottlenecks, but I was seeing reads in the range of 20-50 which seemed trivial, however the Audit Login and Audit Logout events were literally taking minutes instead of seconds. I had saved the trace to a table, so I copied some of the SQL statements into SSMS and they ran very quickly, each barely taking a second.
I did a bit of research on connection pooling, but unfortunately that .NET option is not available in either SQL Server 2005 or MS-Access 2003. So why was Access connecting & disconnecting for every single database access?
Then I looked into the Access app and discovered that the SSMA was using the "SQL Server" provider in the connection strings. I changed that to the "SQL Native Client" and voila! the app performance sped up by a factor of at least 10x. Overall, the performance is now marginally slower than when using the native Access tables. That's fine, because this database will ultimately be running on a real Windows 2003 server with four dual-core CPU's and mucho RAM, waaay more powerful than the puny Centrino in my laptop.
I also took a couple of miscellaneous Access files & converted those as a further test, and they all converted using the old "SQL Server" intended for SQL 2000.
I've always run the SSMA using the wizard to save time, but now I'll need to revisit that to see if there is an option to use the correct provider for SQL 2005.
Saturday, November 29, 2008
Tuesday, November 18, 2008
Max Locks Per File
I have a replicated MS-Access database that is used to store archive data from the production system. This archive is Access 2002 format and is approx 140 MB in size.
The design master was recovered from a replica & saved to a local C: drive, and then a new replica was created & published to a network share. So far, so good.
However, when I attempted to sync the two files, I received an error that the MaxLocksPerFile setting in the registry was exceeded and the sync could not be done. The "Help" button said that the default was 9500 (decimal), and suggested that I use SetOption to change it, but SetOption() has no such option.
I found it curious that a brand-new replica would require the complete sync process even though nothing was changed in either design or data, but I digress...
In any event, I did find the key in the registry by that name, it is located at:
HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxLocksPerFile
=dword:0000251c
I changed the setting to 12,000 and the progress meter showed two bars and then froze with the error message. With 15,000 it reached five bars; and finally 25,000 which worked. The progress meter displayed the typical appearance & the sync was successful after a few seconds.
I then went back to experiment with a lower setting of 20,000 to try to find the go/no-go level but the sync was nearly instantaneous, so I suppose that the act of moving the replica forced Access to run the sync process on the entire contents of the file.
The design master was recovered from a replica & saved to a local C: drive, and then a new replica was created & published to a network share. So far, so good.
However, when I attempted to sync the two files, I received an error that the MaxLocksPerFile setting in the registry was exceeded and the sync could not be done. The "Help" button said that the default was 9500 (decimal), and suggested that I use SetOption to change it, but SetOption() has no such option.
I found it curious that a brand-new replica would require the complete sync process even though nothing was changed in either design or data, but I digress...
In any event, I did find the key in the registry by that name, it is located at:
HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxLocksPerFile
=dword:0000251c
I changed the setting to 12,000 and the progress meter showed two bars and then froze with the error message. With 15,000 it reached five bars; and finally 25,000 which worked. The progress meter displayed the typical appearance & the sync was successful after a few seconds.
I then went back to experiment with a lower setting of 20,000 to try to find the go/no-go level but the sync was nearly instantaneous, so I suppose that the act of moving the replica forced Access to run the sync process on the entire contents of the file.
Thursday, November 13, 2008
Excel prompts to save when no changes made
I have a large Excel workbook that is a catch-all for budgeting, time analysis & other miscellaneous items. When I would open the file to look at something, not change anything but simply close it, Excel would prompt me to save changes?
I found this article that explains "volatile formulas" in Excel such as CELL(), NOW(), RAND(), TODAY() and a few others. Here is the full article:
"Save changes in" prompt even if no changes are made
In the database world, expressions like NOW() are called non-deterministic functions, because they don't have a single repeatable value - NOW() always returns the current date / time which of course changes every time you invoke it.
If your worksheet uses those functions, the only way to avoid the "save" prompt is to turn off automatic recalculation & check the option for recalculate before save. But that makes your worksheet "dead" in the sense that changing cell values does not recalc, which is one of the great things about Excel. You'd have to either press F9 or click the Save icon to recalc after every edit.
Apparently this affects all versions of Excel from 97 up thru 2007.
I found this article that explains "volatile formulas" in Excel such as CELL(), NOW(), RAND(), TODAY() and a few others. Here is the full article:
"Save changes in
In the database world, expressions like NOW() are called non-deterministic functions, because they don't have a single repeatable value - NOW() always returns the current date / time which of course changes every time you invoke it.
If your worksheet uses those functions, the only way to avoid the "save" prompt is to turn off automatic recalculation & check the option for recalculate before save. But that makes your worksheet "dead" in the sense that changing cell values does not recalc, which is one of the great things about Excel. You'd have to either press F9 or click the Save icon to recalc after every edit.
Apparently this affects all versions of Excel from 97 up thru 2007.
Subscribe to:
Posts (Atom)