Tim Young [Elevate Software] wrote:

> If not, then the issue is most likely with other queries in the system that are holding read locks on the target
> table for too long, thus preventing the deletions from acquiring the proper write locks to complete the operations
> quickly.  This is caused by un-optimized queries that perform bulk table row scans.

Hi Tim,
   What exactly am I looking for here pls?
Regards,
Ian

Ian

>> If not, then the issue is most likely with other queries in the system that are holding read locks on the target
>> table for too long, thus preventing the deletions from acquiring the proper write locks to complete the operations
>> quickly. This is caused by un-optimized queries that perform bulk table row scans.

That's one I never thought of, and probably never would have.

>    What exactly am I looking for here pls?

I think, if I'm interpreting correctly, any queries that are running at the same time as the LineItems.Delete and are taking a long time.

Roy

Ian

>    I am trialling a different approach today.
>    Simply changing the Key, JobNo, value to something that takes the LineItem record way out of scope of the original
>query. 9999999.
>    Then on the closure of the App deleting all lineitem records with a JobNo of 9999999 using the script.
>    
>    I will know this afternoon after I analyse this morning's stats if this is a better mechanism both for the application
>and the user.

I did something similar in my email sub-app basically set the mailbox link to something that made the email disappear from view then clean up later on.

If it doesn't work you could try dumping the whole lot to a thread.

This <<scope of the original query>> makes me wonder - I've been assuming that we're just talking a table is this operating on a sensitive result set? Not sure what if any difference it would make.

Roy

Roy Lambert wrote:

> I think, if I'm interpreting correctly, any queries that are running at the same time as the LineItems.Delete and are
> taking a long time.

Hi Roy,
   Not sure how to do that.
   There can be up to 18 Apps running at a single point in time.
   Of those 12 will be the Workflow App looking at JTs and the associated tables.  Adding, editing, reporting.
   A couple of people may be in Reporter running or creating reports.
   A couple may be in the Admin App doing data maintenance or reporting.
   A couple may be in the Finance App, billing and printing Invoices.
   A couple may be in Store adding, deleting, receipting or issuing parts.
   Etc.
   All are working basically the same Database/Tables.
   Operations are from 0700 to 1900 or so Fiji time weekdays and 0700 to 1400 Saturdays.
Regards,
Ian

Hi Tim,
>
> .....  If not, then the issue is most likely with other queries in the system
> that are holding read locks on the target table for too long, thus preventing the deletions from acquiring the proper
> write locks to complete the operations quickly.  This is caused by un-optimized queries that perform bulk table row
> scans.
>
IB - What sort of queries?  What should I be looking for?

> If you're using file-sharing, then that will also make the problem much worse as the number of users goes up, and
> eventually the whole thing is going to grind to a crawl.
>
IB - I don't understand you pose the question "If you are using file-sharing,..." - Isn't that necessary by definition
or are you referring to something else?

Regards,
Ian

On 2/4/2019 9:45 PM, Ian Branch wrote:
> IB - What sort of queries?  What should I be looking for?

Anything that uses that table and might have a read-lock unfortunately.

> IB - I don't understand you pose the question "If you are using file-sharing,..." - Isn't that necessary by definition
> or are you referring to something else?

In your case yes since you said you're using local mode. File sharing
performance with multiple users is not ideal and itself can introduce
longer locking etc.

C/S is the alternative and with as may users you have something you
should consider as it would likely help quite a bit.

Raul

Ian,

<< What exactly am I looking for here pls? >>

You're looking for queries that are un-optimized and take longer than a few seconds to run.  Anything longer on a transactional table that is being used heavily, and you're asking for trouble with the locking/performance because it is quite likely that the additional time is due to the querying having to perform a table row scan across many rows in order to satisfy the constraints of the query (WHERE, JOIN, etc.).  A query execution plan in EDB will tell you when the query encountered a row scan and could use an index.

The new statement logging is valuable for this, provided that you can at least run the queries in your environment against the EDB Server.  I'm looking into adding an additional option for this that includes statements that result in table row scans.

Also, the new global file I/O buffering in EDB includes different locking that is faster than going through the OS for locking (the default way) and can improve concurrency through the combination of faster locks and less I/O while locks are in place, due to the increased buffering.

Tim Young
Elevate Software
www.elevatesoft.com

Hi Team,
   Update.
   I have reverted to the original LineItems.Delete mechanism as it is the simplest.
   I already have performance figures for when there is nobody on the Client's system.
   On Monday, the Client is going to do two tests for me.
   1.   All staff logged in to their normal Apps, 18 sessions, viewing their normal data, but not doing anything else.  No
adding/deleting/editing.  One of them is then going to Add a lineitem and then somebody else will delete the same
lineitem.  Making a note of the Job # & Part #.
   2.   Next, several staff will go into edit mode in their various apps but not actually change anything.  Then the
Add/Delete test will be repeated.
   During these two tests, and aside from the Add/Delete, there should be no network/database activity.
   From this I hope to get an idea of what impact just those two conditions have on the LineItems.Delete performance.
   We'll see.
Regards,
Ian

Ian,

<< During these two tests, and aside from the Add/Delete, there should be no network/database activity.
From this I hope to get an idea of what impact just those two conditions have on the LineItems.Delete performance. >>

Per the test results that you sent me via email, the problem is definitely with the file-sharing usage and accessing the database via the SMB network redirector, as opposed to client/server.

The key indicator is *always* the massive slowdown when the *second* user starts accessing any database files being used by the first user.  The first user is seeing a "simulated" performance whereby all of the data is being cached locally.  When the second user logs in, the opportunistic locking that is being used with the SMB network redirector is broken, and the performance reverts to the real performance, which is really bad to start with (~1 second for a single row deletion is horrible).

The solution is to move to using the ElevateDB Server.  There isn't any other solution because we do not control the performance or behavior of the network redirector on Windows.

More information:

https://docs.microsoft.com/en-us/windows/desktop/fileio/opportunistic-locks

Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim,
   Appreciate your patience and looking at this.
   I can use this as further ammunition at the Client to go C/S mode.  He is halfway there att, this should this him over.
Regards,
Ian