A Support Case of Investigating Elliott Database Performance Problem

A Support Case of Investigating Elliott Database Performance Problem

Release Date: 08/14/2018

A user reported that their shipping verification process has become slow.  They noticed that the scanning has become slow over time, and are wondering if there is a purge or rebuild or something they could do to speed things up. 

Basically, what is happening is this: The shipping clerk will take an order and scan the first item (this may be either a UPC or a GTIN code). It can take between 2 and 6 seconds for Elliott to register the scan. We see the screen change to the item scanned first, and then another delay to update the count. If the next scan is the same item, the count updates right away. However, if the next scan is a different item, things are slow again.

Diagnose Database Slow Down with CTL-SHIFT-D Key
Our theory is that there's a process in Elliott where the system needs to read through many, many records in this case to cause the slowdown. To debug this type of "database" slow down, the best way is to use the CTL-SHIFT-D key.  But it is likely the user does not have the security to use the CTL-SHIFT-D key.  So we advised them to verify if the particular user has proper security by going to Password Setup -> Global Security -> User Global Security. Go to the second screen, and answer “Y” to 
    5. Access Database Activities Window Ctl-Shift-D 
See sample screen below:


Once this is done, we asked this user go to Shipping Verification and repeat the process to duplicate the slowness.  Then, at the next available data entry field in Elliott, press CTL-SHIFT-D.  This brings up the database activities list window, which contains the last 1,000 entries of database activities in Elliott.  We then asked the user to print the content to a file and email to us.  See sample screen below:


In this example, out of the last 1,000 database activities, the last 26 records are normal.  For the the previous 974 records, they all look the same:
    Read Prev (Previous) of SYEVTQUE record.

So our response to this user is as follows:

It looks like you are using an event. In particular you are using the “SHPVEROR” (Shipping Verification by Order) event. There are tons of these kinds of events triggered and left in the event queue, and there’s no process doing anything with them. So these “orphan” event queue records slow down the system. Are you using “User Defined” event? “User Defined” events will leave records in the event queue and Elliott does not do anything with them once they are triggered. They are supposed to be processed (and deleted afterward) by a third party developer.

There’s a quick way you can solve this problem by renaming the following two files at the command prompt -- at, say M:\ELLIOTT7\DATA (substitute to whatever the path applies to you):
                REN SYEVTQUE.BTR SYEVTQUE.OTR
                REN SYEVTQUD.BTR SYEVTQUD.OTR
You will need to do this when users are out of Elliott and nobody has opened SYEVTQU?.BTR files. Verify this through the PSQL Monitor utility.

These two files that will be re-created automatically the next time the system needs to access them. Since all the records in the event queue files are gone after this, I think your performance will be back to normal.

This should be relatively easy to try and see if it resolves the problem. If not, let us know. The bigger issue is why do we have all these “orphan” records left in the event queue file? Will this problem resurface again in the near future?


The user later responded as follows:
Thanks - that is indeed what the problem is. I think I was trying to use the event for something but it didn't do what I expected, and forgot to remove it. I appreciate the help, as always! 

We are glad we can help.  The important lesson from this support incident is that you can diagnose a database slowness problem (if it only happens in a particular area of Elliott and the slowdown starts to happen over time) by using the CTL-SHIFT-D key. 

Elliott CTL-SHIFT-D window stores up to 1,000 last IO activities. You should press CTL-SHIFT-D immediately after you experiencing the slow down.  If you continue working, then the activities in CTL-SHIFT-D will be overridden and the data will not be valuable anymore.


EMK



    • Related Articles

    • Feature - View Database Activities Through Control-Shift-D Key

      Version 8.5 & Up Release Date: 7/7/2021 Control-Shift-D Support in the Past The Control-Shift-D key has been available in Elliott since V7.x. It is a very useful tool for debugging purposes. For example, let's say you started to notice performance ...

    • Elliott Database Naming Convention

      Release Date: 06/30/20 Version: 7.x & Up Listed below are examples of standard Elliott database naming conventions for current and future versions of Elliott: ELLIOTTDATA V7.X DDF ELIDATA V8.0-8.2 DDF ELI85DATA V8.5 DDF ELI86DATA V8.6 DDF ELI86ROOT ...

    • Hardware Recommendations for Your PSQL Database Server

      Release Date: 10/10/2017 This article was written by Bill Bach and is provided at the courtesy of Goldstar Software. Recommendations for hardware for your PSQL database environment One of the most common questions we get is “What kind of hardware ...

    • Slow PSQL Relational Engine Performance

      Release Date: 12/15/2017 Many users encounter situations where a particular relational engine SQL query can sometimes be slow. This can be complicated to diagnose due to a lot of reasons. Sometimes it's because of a SQL SELECT statement that's not ...

    • Problem with Printer Configuration If Running Elliott from Multiple Workstations

      Q - I have a user that comes into the office and uses Elliott from her desktop. That same user also uses Elliott from home using the remote desktop to the Elliott server. The user has Adobe Acrobat on her local desktop to generate and edit PDF files. ...