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,

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]

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,.

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]

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

"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.

Tim, sorry, another related question:- would the Read-Only ODBC driver offer any benefits?

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