![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Client/Server » View Thread |
Messages 1 to 8 of 8 total |
![]() |
Thu, Apr 11 2013 7:01 AM | Permanent Link |
Charles Bainbridge | The Background:-
We have a customer where a lot of period end reports are generated over several days using Excel via ODBC to a DBSRVR instance. When this is going on, performing updates in the main application (DBISAM-based, but file-sharing mode) can become so slow as to be almost unusable. Analysis of stats of their SAN show no data throughput bottlenecks, nor memory or CPU issues in the VMware machine the users are accessing as Terminal Services users. The Question(s):- Could the ODBC queries be blocking application updates because of Read Locks being applied? If so, am I going to dig a big hole for myself if I provide them with a customised DBISAM server which sets the server TableMaxReadLockCount to zero? The main application (file-sharing mode) would remain with standard settings. TIA, |
Thu, Apr 11 2013 7:33 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Charles
Slightly skewed thought - unless they're posting adjustments to the period then why not clone the tables (a few minutes) and run the period end reports from the cloned tables? Roy Lambert [Team Elevate] |
Thu, Apr 11 2013 7:44 AM | Permanent Link |
Charles Bainbridge | Roy Lambert wrote:
unless they're posting adjustments to the period then why not clone the tables (a few minutes) and run the period end reports from the cloned tables? Hi Roy, That's what tends to be going on; run a report to find what needs to be done; make an adjustment posting; run a report; etc,. |
Thu, Apr 11 2013 8:49 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Charles
>That's what tends to be going on; run a report to find what needs to be done; make an adjustment posting; run a report; etc,. Thought it might be - I presume accountants "cooking" the books <vbg> Since you're normally running fileserver adding in the DBSRVR shouldn't be causing to much aggro (just think of it as another user). It would be worthwhile running the queries, without ODBC or EXCEL, to see how much they impact on the system. Either as a straight query or outputting to a csv file. If that still stuffs performance then you know its the queries and you can start tuning those. If it doesn't then a two stage process might be in order. Or you could look at something like FastReport (which can produce Excel formatted reports), Flexcel (from TMS - shudder) or Mike Skolniks Export suite (heartily recommend). In all three cases no ODBC needed. There are other options but those are the ones that spring to mind. First thing though I'd make sure its not the queries doing it. Roy Lambert [Team Elevate] |
Fri, Apr 12 2013 7:52 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Charles,
<< Could the ODBC queries be blocking application updates because of Read Locks being applied? >> Yes, most definitely. Especially if the queries aren't particularly optimized or cause entire table scans to occur. << If so, am I going to dig a big hole for myself if I provide them with a customised DBISAM server which sets the server TableMaxReadLockCount to zero? The main application (file-sharing mode) would remain with standard settings. >> I wouldn't advise it. You'll probably end up causing so much locking activity over the network that you won't see any improvements at all. Have you tried moving everything to using the DBISAM Database Server ? That should help improve all-around performance. Also, what version of DBISAM are you currently using ? There was a recent performance improvement in 4.35 that will help immensely with these types of situations, albeit a lot more with the DBISAM Database Server. Thanks, Tim Young Elevate Software www.elevatesoft.com |
Tue, Apr 16 2013 5:22 AM | Permanent Link |
Charles Bainbridge | "Tim Young [Elevate Software]" wrote:
Charles, << Could the ODBC queries be blocking application updates because of Read Locks being applied? >> Yes, most definitely. Especially if the queries aren't particularly optimized or cause entire table scans to occur. << If so, am I going to dig a big hole for myself if I provide them with a customised DBISAM server which sets the server TableMaxReadLockCount to zero? The main application (file-sharing mode) would remain with standard settings. >> I wouldn't advise it. You'll probably end up causing so much locking activity over the network that you won't see any improvements at all. Have you tried moving everything to using the DBISAM Database Server ? That should help improve all-around performance. Also, what version of DBISAM are you currently using ? There was a recent performance improvement in 4.35 that will help immensely with these types of situations, albeit a lot more with the DBISAM Database Server. Thanks, Tim Young Elevate Software www.elevatesoft.com Thanks Tim. I had a feeling it was the queries blocking the writers. As things stand, logically, the data is sitting on the same server as the main application's clients (all Terminal Services-based). The DBSRVR access (running on the same server) is used for reporting via ODBC and Excel, as that's done on the users' desktop machines rather than in remote TS sessions. The users are 200 miles from the server with a pipe suffering from either not enough bandwidth; too much latency; or both. We're still using 4.27. It has appeared to be stable and moving forward to a later release would have required more testing resources than we could realistically find. |
Tue, Apr 16 2013 7:05 AM | Permanent Link |
Charles Bainbridge | Tim, sorry, another related question:- would the Read-Only ODBC driver offer any benefits?
|
Wed, Apr 17 2013 9:38 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Charles,
<< Tim, sorry, another related question:- would the Read-Only ODBC driver offer any benefits? >> Unfortunately, no. It's only benefit is that it completely prevents the user from writing to the database. That's its only benefit/difference. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Wednesday, August 14, 2024 at 03:12 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |