sql92 openedge 11.4 causing performance problems - Forum - OpenEdge RDBMS - Progress Community

sql92 openedge 11.4 causing performance problems

 Forum

sql92 openedge 11.4 causing performance problems

  • we've just moved an 11.1 64 bit database to 11.4.

    The clients are 11.4 , 32 bit.

    Prior to the update, performance was good. reports ran well and with no problems.

    However, since the move to 11.4, any client that runs an odbc query causes huge performance problems (100% cpu), abl clients getting disconnected from the database etc

    has anyone else seen problems like this ? were some parameters changed between 11.1 and 11.4 (I copied the 11.1 properties files and moved them back after installing 11.4)

    thanks ...

  • addendum : just installed the 64 bit odbc drivers (knowledgebase.progress.com/.../000052803) however, all we get now is an "odbc general error" when running the report.

  • Is it the same issue if you query a small table?

    With the new drivers are you seeing the connecting in the db.lg file?

    If yes, can you enable SQL logging on the server side?

    Can you connect via jdbc with sqlexp or Squrriel?

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://dbappraise.com/protop.html
  • I think that the problem is isolation level. user sqlrep-5 (for example) has a number of SHR locks in the locking table, even though the default isolation level of the odbc connection is set to READ UNCOMMITTED

    we have multiple queries running, and they seem to get into a deadly embrace. if I disconnect one of the users, then the other report completes ok

    can a query store the isolation level in it somewhere ? We are using excel 2013 with msquery

  •  whe

    when a sql user logs in and runs the query, it looks like it sets a SHR lock on a certain record, which goes when the report is finished.

    now, the table in question is _sec-authentication-domain

    is that right ?

  • Are you using an existing ODBC DSN? Did you remember to change the isolation level when you created the new one with the 64 bit drivers?  (Yes yes I know you probably did but this is for the benefit of the reader).

    Are you writing your own queries?  If yes you can add "WITH NOLOCK" to the query.

    From the documentation:

    The NOLOCK locking hint ensures records are not locked during the execution of a

    SELECT statement when the transaction isolation level is set to READ COMMITTED.

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://dbappraise.com/protop.html
  • Yes that looks right.  -207 is _sec-authentication-domain.  Regardless of whether or not this is good, this should not block anyone as no one should be trying to get an EXCL on _sec-authentication-domain.

    Are you seeing "Blocked Clients" in promon or are you guessing that there might be a deadly embrace?

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://dbappraise.com/protop.html
  • Since you upgraded and moved to a new version, has update statistics been performed against the "new/migrated" db ?

  • Libor,

    What makes you think the DB is new or migrated?  He just upgraded the DLC from 11.1.

  • still probably a good idea to update the statistics. with the table partitioning work, there have been sql query processor changes.

  • The suggestion from both Libor and Gus is good.

    After 11.1, there were changes to sql statistics to make the optimizer work better with non-uniform data distributions, and with situations with many duplicate key values (uniform or non-uniform).  So the statistics have a slightly different content now.  The old content should be OK, but the old statistics won't get the best optimizer performance.

    So definitely do UPDATE STATISTICS.

    And do  UPDATE INDEX STATISTICS - which  gives very  useful statistics for joining and searching.

    Note that the base UPDATE STATISTICS does not create index stats.

    For detailed syntax, the sql reference manual is a good resource (it is avaliable onlline).

    hope this helps,        ....steve pittman  [sql development software architect]

  • @Rob - I meant 'version has changed' - sorry for not expressing myself clearly.

  • Is it safe to say then, as a general rule, that we should run update statistics after installing a new version?

  • I run it whenever I upgrade and periodically in between.

    On 9/12/14, 1:38 PM, Rob Fitzpatrick wrote:
    Reply by Rob Fitzpatrick

    Is it safe to say then, as a general rule, that we should run update statistics after installing a new version?

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.



    -- 
    Tom Bascom
    603 396 4886
    tom@greenfieldtech.com

    --
    Tom Bascom
    tom@wss.com

  • In short, it never hurts to run it, so running it on a version change is a bit changing the battery in the smoke detector when the time changes.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com