Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 11 to 20 of 20 total |
Performance in client/server, particularly Prepare |
Wed, Mar 6 2013 3:19 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Mar 6 2013 5:23 PM | Permanent Link |
jwtm | "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. |
Thu, Mar 7 2013 10:19 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Mar 21 2013 1:07 PM | Permanent Link |
jwtm | "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. |
Thu, Mar 21 2013 2:51 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Mar 25 2013 6:45 PM | Permanent Link |
jwtm | "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 ??? |
Tue, Mar 26 2013 5:10 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Tue, Mar 26 2013 3:08 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Mar 28 2013 4:23 PM | Permanent Link |
jwtm | "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. |
Fri, Mar 29 2013 1:40 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |