Hi Team,
   EDB 2.30b2 32bit.  Local mode.
   I have a database procedure that does various things with various tables.  Nothing special there.
   I have added time logging at various points in one particular procedure.
   All up the procedure takes approx 1488mS.
   Within that is a one liner that says 'LineItems.Delete;' to delete a record from the LineItems table after some
pre-processing.
   That action alone is taking approx 542mS or 36% of the procedure time.  The single largest time chunk in the overall
procedure.
   Now, that might not seem much but this is on a good day in the Customer's Test environment with just the one
application running.
   The times move out significantly when there are 18 Users/Apps working.
   I understand there are lots of 'variables' that are going to influence things, Server activity, Network activity, etc,
but is there a more efficient, read faster, way to delete the record?

Regards & TIA,
Ian
   

Ian


Thank you for the piece of string question. I really have no idea as to its length

There are far to many unknowns there for a sensible answer so I'll stick to some general comments / areas to think about.

1. First problem is the fact that you're running in local mode that means a lot of network activity - just what the "simple" delete is causing I don't know but it may be worth having a look.
2. If its from just one workstation where this happens see if the hardware (especially network) is OK
3. Are there any UI elements connected to the table(s) which can have massive implications?
4. Are there any triggers defined?
5. What's the speed like if you run it from the file server?
6. What's the speed like if you ignore all of the other processing and just do LineItems.Delete;
7. What happens if you do a database.Execute('DELETE FROM LineItems WHERE lineitemsPK = whateveritis)?
8. Is the table damaged? Have you tried running a repair and seen if that makes any difference?


Some more ideas may occur as the day progresses. If they do I'll let you know.


Roy Lambert

Hi Roy,
   Yes, there are multiple variables.  That's what makes this so hard to diagnose.
   I got some Live data today with 16 users..
   Lineitems.Delete times ranged from 17Sec to 38Sec.

   Pls see comments below..
>
> 1. First problem is the fact that you're running in local mode that means a lot of network activity - just what the
> "simple" delete is causing I don't know but it may be worth having a look.  
IB - What do you mean by 'causing'?

> 2. If its from just one workstation where this happens see if the hardware (especially network) is OK
IB - As best I can tell all is in good nick.  Their Guru went over it a few days ago.

> 3. Are there any UI elements connected to the table(s) which can have massive implications?  
IB - No, I disbale controls at the start of the procedure and reenable at the end.

> 4. Are there any triggers defined?
IB - No.

> 5. What's the speed like if you run it from the file server?
IB - Good question - Obviously I can't test that in the live data but the figures I quoted in the previous message were
on the Server in the Test area.

> 6. What's the speed like if you ignore all of the other processing and just do LineItems.Delete;
IB - I am ignoring the other processing.  The figure is purely for the Delete function.  The contribution to the
processing time of the code preceding and post the 'Delete' is minimal.

> 7. What happens if you do a database.Execute('DELETE FROM LineItems WHERE lineitemsPK = whateveritis)?
IB - Don't know, will have to identify and experiment.  ATT LineItems is an EDBTable.

> 8. Is the table damaged? Have you tried running a repair and seen if that makes any difference?
IB - No, it is checked and optimised regularly.

Regards,
Ian

Ian


>    Yes, there are multiple variables. That's what makes this so hard to diagnose.
>    I got some Live data today with 16 users..
>    Lineitems.Delete times ranged from 17Sec to 38Sec.

That says there is something going on.

>> 1. First problem is the fact that you're running in local mode that means a lot of network activity - just what the
>> "simple" delete is causing I don't know but it may be worth having a look.
>IB - What do you mean by 'causing'?

Local mode means there is far more network traffic than with c/s mode. You may be having to transfer dirty great chunks of a table to do anything. From your other comments <<That action alone is taking approx 542mS or 36% of the procedure time.>> and <<but the figures I quoted in the previous message were on the Server in the Test area.>> gives some idea of the network transport issues - c0.5 seconds to 17 seconds - lots of traffic. OK even 0.5 seconds for a simple delete seems way to high. Hmmm. How many and what type of indices defined?

>> 2. If its from just one workstation where this happens see if the hardware (especially network) is OK
>IB - As best I can tell all is in good nick. Their Guru went over it a few days ago.

Is this the same place you were talking about problems recently or was that someone else complaining about some other site?

>> 3. Are there any UI elements connected to the table(s) which can have massive implications?
>IB - No, I disbale controls at the start of the procedure and reenable at the end.

Not good enough if you're talking table.disablecontrols Nil the dataset property in any connected datasources and try again - it can make a major difference.

>> 4. Are there any triggers defined?
>IB - No.
>
>> 5. What's the speed like if you run it from the file server?
>IB - Good question - Obviously I can't test that in the live data but the figures I quoted in the previous message were
>on the Server in the Test area.

See above

>> 6. What's the speed like if you ignore all of the other processing and just do LineItems.Delete;
>IB - I am ignoring the other processing. The figure is purely for the Delete function. The contribution to the
>processing time of the code preceding and post the 'Delete' is minimal.

When you have this sort of problem the only thing to do is break it down to the very smallest pieces. You may think none of the other actions are having an impact but until you test you don't know.

And another thought - any events hooked up to the table component?

>> 7. What happens if you do a database.Execute('DELETE FROM LineItems WHERE lineitemsPK = whateveritis)?
>IB - Don't know, will have to identify and experiment. ATT LineItems is an EDBTable.

Doesn't matter if the table is linked to a TEDBTable (or subclass thereof) you can drop the sql in as a direct replacement. You will need to refresh the table after the delete

>> 8. Is the table damaged? Have you tried running a repair and seen if that makes any difference?
>IB - No, it is checked and optimised regularly.

Ian


Just another thought - what about anti-virus - could that be screwing things up?

Roy Lambert

Hi Roy,
   As always, thank you for your input.
   I am working on a work around and part of it is to run a script when the form exits.
{sql}
SCRIPT
BEGIN
Execute immediate 'Delete from LineItems where Jobno = 9999999';
END
{sql}

   It is called when the Close button is clicked by..
{sql}
 DelLineItems.Open;
 DelLineItems.Close;
 Close;
{sql}
   DelLineItems is the EDBScript component.
   It deletes the relevant Line Items OK but then I get a "Error creating table handle." error.
   Ummm Why?
Regards,
Ian

Oooops.  DelLineItems.ExecScript.
All good.

Ian,

<< Lineitems.Delete times ranged from 17Sec to 38Sec. >>

How many rows are being deleted in each case ?

Please try to be more descriptive when asking for help, it will help you get an answer quicker without requiring so many follow-up questions.

Tim Young
Elevate Software
www.elevatesoft.com

Ian,

Never mind, the talk about a script confused me as to what you were actually executing.

So, you're seeing times of 17 seconds to 38 seconds for a *single row deletion* ?  If so, then you've got some serious concurrency issues in your application.

Are you using manual transactions anywhere ?  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.

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.

Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,
   Yes, it is for a single row deletion.  
   No manual transactions anywhere in the Apps.
   I reached the same conclusion/understanding a while ago and was looking for an alternate mechanism.
   
   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.

Regards,
Ian