Tim


>I'll also use this query as a test case to see if I can get the sub-query
>plans merged into the outer query's execution plan.

Hurrah

Although I have become quite proficient at dismantling and testing compound queries. In fact I generally optimise the inner ones before building the full query.

Roy

"Tim Young [Elevate Software]" wrote:

Bill,

<< Here's the query and execution plan. The execution plan doesn't mean much
to me, but maybe you will see something. Your observations will be most
welcome. Bill. >>

Can you email me your database (.zip please) ?  I'll take a look and tell
you what is taking so long.  I suspect that the issue is the inner
subqueries that are being executed on every row navigation.

I'll also use this query as a test case to see if I can get the sub-query
plans merged into the outer query's execution plan.  There are some issues
with doing so, most notably that such a plan is only a sampling of the many
times such a query might be executed in the course of the outer query, but
it should give the necessary optimization information that you need.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

==========================

I will be glad to, but the data isn't mine. It is confidential to my customer's customer, and includes passwords and credit card data. I will see if I can get clearance.

In the meantime I will try an earlier suggestion of unwrapping the nested queries and see if I can identify a hot spot.

Thanks also for your earlier note re: prepare. What's a reasonable limit on the number of prepared queries to hold simultaneously? I have over 200 simple queries -- maybe 400 -- where the prepare round trip is a significant proportion of total execution cost, and I suppose I could manufacture a JIT query preparer and local prepared query cache. Would the entries in such a cache survive a disconnection?

Bill.


Bill.

Bill,

<< Thanks also for your earlier note re: prepare. What's a reasonable limit
on the number of prepared queries to hold simultaneously? >>

No, there's no limit, but you'll be increasing the memory usage per session
on the EDB Server side, so please take that into account if you've got
hundreds of users on the same EDB Server.

<< I have over 200 simple queries -- maybe 400 -- where the prepare round
trip is a significant proportion of total execution cost, and I suppose I
could manufacture a JIT query preparer and local prepared query cache. >>

Are all of these queries necessary at all times ?  If they're used in any
sort of batch process, then I would definitely consider moving them to a
script/stored procedure that can be executed on the EDB Server.  Any time
that you start to encounter issues with the network being a problem, those
are good candidates for evaluating whether can be moved to the server-side
of the equation.

<< Would the entries in such a cache survive a disconnection? >>

If by "disconnection", you mean a temporary one, then yes, they'll survive a
disconnection as long as the session isn't removed by the EDB Server
(controlled by the dead session expiration time setting on the EDB Server).

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:

Are all of these queries necessary at all times ?  If they're used in any
sort of batch process, then I would definitely consider moving them to a
script/stored procedure that can be executed on the EDB Server.  Any time
that you start to encounter issues with the network being a problem, those
are good candidates for evaluating whether can be moved to the server-side
of the equation.

----------------------------------------

For your interest, I have replaced the Elevate built-in client/server communications stream with one of mine own devising. Compared to Elevate out of the box, performance over the Internet varies from 1:1 to 5:1. The 1:1 cases are (a) complex queries where query execution time dominates, and (b) simple queries returning large result sets where result set transmission time dominates. For everything else, there is a marked improvement and it looks like we have a product.

I don't pretend this to be a general solution; it covers only the use cases in the application I am mending, in which  the solutions you suggest are not a short-term option. I have 500,000 lines of code in 1200 units to not change, so I had to go in underneath.

Bill.

Bill,

<< For your interest, I have replaced the Elevate built-in client/server
communications stream with one of mine own devising. Compared to Elevate out
of the box, performance over the Internet varies from 1:1 to 5:1. The 1:1
cases are (a) complex queries where query execution time dominates, and (b)
simple queries returning large result sets where result set transmission
time dominates. For everything else, there is a marked improvement and it
looks like we have a product. >>

Please send me an email with the changes that you made.  I would be very
interested in knowing what you did to improve the performance.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:

Bill,

Please send me an email with the changes that you made.  I would be very
interested in knowing what you did to improve the performance.

Tim Young
Elevate Software
www.elevatesoft.com

---------------------------------

I am trying, but the email keeps bouncing. I am troubleshooting. AT&T are being elusive about support, and I can send emails anywhere else. So ???

jwtm


>I am trying, but the email keeps bouncing. I am troubleshooting. AT&T are being elusive about support, and I can send emails anywhere else. So ???

You could try something like https://www.wetransfer.com/ or simply post to the binaries.

Roy Lambert [Team Elevate]

Bill,

<< I am trying, but the email keeps bouncing. I am troubleshooting. AT&T are
being elusive about support, and I can send emails anywhere else. So ??? >>

If you're sending an email with an attachment, then you have to be sending
from an email address that we have on file or have used when conversing with
you in the past.

Just send me an email without the attachment, wait for my response, and then
send the email with the attachment.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:

Just send me an email without the attachment, wait for my response, and then
send the email with the attachment.

Tim Young
Elevate Software
www.elevatesoft.com

That's not working either. so here it is, not very large anyway:

I haven't changed Elevate in any way.

What I did was: in the client application, replace Elevate with a connector and a simple query object containing a stringlist for the query text, TParams, TClientDataset. The application treats this exactly like an Elevate query or script: sets the query text, calls Prepare, optionally sets parameters, executes the query, uses the returned data.

In this object, Prepare() is a dummy function. On Execute(), the query object parcels up the query text and any parameters and transmits them to a server.

The server contains an Elevate engine running in local mode. The server prepares an Elevate query, sets parameters, executes the query, parcels up output parameters and the result dataset, and returns these (maybe in a number of chunks) to the client. The client loads its ClientDataSet and returns it to the application.

All very basic and with limited functionality, but the net effect is, most queries are satisfied by a single short outgoing message and a single response of small to moderate size. Works over HTTP too, so I can proxy the server behind Apache. Given the pattern of use for this particular application, (which wasn't originally written for use with a DBMS at all), the performance improvements have been marked.

Bill Meakin.

Bill,

<< What I did was: in the client application, replace Elevate with a
connector and a simple query object containing a stringlist for the query
text, TParams, TClientDataset. The application treats this exactly like an
Elevate query or script: sets the query text, calls Prepare, optionally sets
parameters, executes the query, uses the returned data.

In this object, Prepare() is a dummy function. On Execute(), the query
object parcels up the query text and any parameters and transmits them to a
server.

The server contains an Elevate engine running in local mode. The server
prepares an Elevate query, sets parameters, executes the query, parcels up
output parameters and the result dataset, and returns these (maybe in a
number of chunks) to the client. The client loads its ClientDataSet and
returns it to the application.

All very basic and with limited functionality, but the net effect is, most
queries are satisfied by a single short outgoing message and a single
response of small to moderate size. Works over HTTP too, so I can proxy the
server behind Apache. Given the pattern of use for this particular
application, (which wasn't originally written for use with a DBMS at all),
the performance improvements have been marked. >>

I can't see how saving one round-trip is going to save you that much time,
unless you're executing literally hundreds of these.  But, you indicated
that you're only using 25 queries at startup, correct?

Did you actually do a remote session trace in ElevateDB from your client
application to see where the bulk of the time was being spent ?

http://www.elevatesoft.com/manual?action=viewprop&id=edb2&product=delphi&version=7&comp=TEDBSession&prop=RemoteTrace

The problem could very well have been an issue with the row
retrieval/navigation *after* the query was opened/executed, and had nothing
to do with the Prepare step.  EDB does load the rows in a separate step, but
I just looked at the code and this can definitely be improved to load the
rows during the "execute" response without breaking any existing code.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com