I have an application with performance problems.
Many of them were in the application and some have been fixed, but one seems to be in Elevate:

Is it true that every query is first prepared, and that the Prepare step causes one client-server message;\; so executing a previously unprepared statement requires a minimum of two messages? If so, is there any way round this?

Bill.

Bill


>I have an application with performance problems.
>Many of them were in the application and some have been fixed, but one seems to be in Elevate:
>
>Is it true that every query is first prepared, and that the Prepare step causes one client-server message;\; so executing a previously unprepared statement requires a minimum of two messages? If so, is there any way round this?

It is true that all queries have to be prepared. How many messages that requires to/from a server I have no idea. Preparing a query covers the following steps (I guessing but I'm sure Tim will correct me if wrong):

1. Make sure the tables exist
2. Make sure the requested columns exist
3. Check the syntax
4. Tokenize the query
5. Get handles for the tables

not necessarily in that order.

There is no way round having to prepare the query. However, once a query is prepared then unless the sql changes it doesn't get prepared again. You can prepare a query and pass in different parameters and it will simply execute.

If you are executing lots of queries and the sql changes each time (note the sql not the parameters) then you're stuck.

However, I doubt that the need to prepare a query is the root of your problem. With c/s apps the single biggest killer is lookups which cause immense network traffic.

Without knowing your app its impossible to say where the real problem lies, however, since you seem to be leaning towards network congestion have you tried something like wireshark to see what's actually happening on the network?

Roy Lambert [Team Elevate]

Roy Lambert wrote:

Bill

:: It is true that all queries have to be prepared. How many messages that requires to/from a server I have no idea.
::...
::If you are executing lots of queries and the sql changes each time (note the sql not the parameters) then you're ::stuck.

That's the pattern

::However, I doubt that the need to prepare a query is the root of your problem. With c/s apps the single biggest
::killer is lookups which cause immense network traffic.

There is a combination of some badly-thought-out queries, not too many, and well-phrased requests which are phrased as a single query. The program startup sequence requires about 25 queries and I suspect each one causes two messages, which over a WAN is too many.

::Without knowing your app its impossible to say where the real problem lies, however, since you seem to be
::leaning towards network congestion have you tried something like wireshark to see what's actually happening
: the network?

::Roy Lambert [Team Elevate]

Performance on a LAN is ok. I am trying to make this work over a WAN @ 3mbps. Thanks for the Wireshark suggestion. I'll try it. I have hooked the Elevate query trace and the times I observe come from that; however I don't know how much is server time and how much is wire time.

I have other problems. One query is taking 29 seconds to complete on a trivial dataset -- a few thousand records in each table. Server CPU goes over 50% (2*2.2G x86). I think that is way too heavy. To be clear at the risk of tautology, that is one composite query, not a whole set of simple ones. Is there a query analysis and optimization mechanism for problems like this?

Bill.

<I have other problems. One query is taking 29 seconds to complete on a trivial dataset -- a few thousand records in each table. Server CPU goes over 50% (2*2.2G x86). I think that is way too heavy. To be clear at the risk of tautology, that is one composite query, not a whole set of simple ones. Is there a query analysis and optimization mechanism for problems like this?
>

Tim made a couple of recommendations on how to monitor performance in this thread.
http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_general&msg=15601&page=1

Check it out.

Barry

Bill

>:: It is true that all queries have to be prepared. How many messages that requires to/from a server I have no idea.
>::...
>::If you are executing lots of queries and the sql changes each time (note the sql not the parameters) then you're ::stuck.
>
>That's the pattern

Ouch

>There is a combination of some badly-thought-out queries, not too many, and well-phrased requests which are phrased as a single query. The program startup sequence requires about 25 queries and I suspect each one causes two messages, which over a WAN is too many.

Can those 25 queries be combined into a script? What do they do? If they're simple table opens can they be turned into VIEWS?

>Performance on a LAN is ok. I am trying to make this work over a WAN 3mbps.

Even with that speed I'm doubtful that the prepare / execute messages are causing much slowdown. Are these queries hooked up to visual controls? Tim has tried to be as clever as possible and only return as much data at a time as is needed for a control but maintaining the visual effect is generally the slowest part of a system.


>I have other problems. One query is taking 29 seconds to complete on a trivial dataset -- a few thousand records in each table. Server CPU goes over 50% (2*2.2G x86). I think that is way too heavy. To be clear at the risk of tautology, that is one composite query, not a whole set of simple ones. Is there a query analysis and optimization mechanism for problems like this?

The way to look at these performance issues is use EDBManager and the Execution Plan. Generally either the query is badly constructed or, most likely, there are indices missing. Using EDBManager (which is "just" a Delphi app written by Tim) removes anything your app does out of the equation so its one less set of variables. If you have a local copy of the data I'd start by using a local session because then network traffic is also out of it and you can concentrate on the performance of the query.

The more information you can provide us with on these ngs the better the chance that someone can help. At present I feel like I'm one of the blindfolded men feeling the elephant


Roy Lambert [Team Elevate]

Bill Meakin wrote:

>There is a combination of some badly-thought-out queries, not too many, and well-phrased requests which are phrased as a single query. The program startup sequence requires about 25 queries and I suspect each one causes two messages, which over a WAN is too many.

Roy Lambert wrote:

Can those 25 queries be combined into a script? What do they do? If they're simple table opens can they be turned into VIEWS?

I'm looking at that possibility.

>Performance on a LAN is ok. I am trying to make this work over a WAN 3mbps.

Even with that speed I'm doubtful that the prepare / execute messages are causing much slowdown. Are these queries hooked up to visual controls? Tim has tried to be as clever as possible and only return as much data at a time as is needed for a control but maintaining the visual effect is generally the slowest part of a system.

I'm looking at everything and so far the operation of the GUI is not significant to overall performance.

>I have other problems. One query is taking 29 seconds to complete on a trivial dataset -- a few thousand records in each table. Server CPU goes over 50% (2*2.2G x86). I think that is way too heavy. To be clear at the risk of tautology, that is one composite query, not a whole set of simple ones. Is there a query analysis and optimization mechanism for problems like this?

The way to look at these performance issues is use EDBManager and the Execution Plan. Generally either the query is badly constructed or, most likely, there are indices missing. Using EDBManager (which is "just" a Delphi app written by Tim) removes anything your app does out of the equation so its one less set of variables. If you have a local copy of the data I'd start by using a local session because then network traffic is also out of it and you can concentrate on the performance of the query.

The more information you can provide us with on these ngs the better the chance that someone can help. At present I feel like I'm one of the blindfolded men feeling the elephant

Roy Lambert [Team Elevate]

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

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.

---------------------------------
Select *,
3 as EntityType
From (
Select acct.AccountNumber,
acct.FirstName, acct.LastName, acct.Company,
Coalesce((Select Sum((svc.Cost + svc.StateTax + svc.CountyTax + svc.CityTax + svc.ServiceTax + svc.RoomTax) * NumUsed)
From tblguestservices svc
Where acct.AccountNumber = -svc.ResNumber
 and svc.DateOfService < DATE '2013-2-28' + Interval '1' Day), Cast(0.00 as Decimal))  totalServiceCost,
 Coalesce((Select Sum(Amount)
   from tblfinancial_l fin
    where fin.ResNumber = -acct.AccountNumber
      and fin.DateOfFinancial < DATE '2013-2-28' + Interval '1' Day),
       Cast(0.00 as Decimal)) totalPaid
from tblAccount acct
) data
Where Abs(TotalServiceCost - TotalPaid) > 0.01
Order By AccountNumber

================================================================================
SQL Query (Executed by ElevateDB 2.05 Build 9)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.
================================================================================

SELECT ALL
"data"."AccountNumber" AS "AccountNumber",
"data"."FirstName" AS "FirstName",
"data"."LastName" AS "LastName",
"data"."Company" AS "Company",
"data"."totalServiceCost" AS "totalServiceCost",
"data"."totalPaid" AS "totalPaid",
3 AS "EntityType"
FROM (SELECT ALL "acct"."AccountNumber" AS "AccountNumber", "acct"."FirstName"
AS "FirstName", "acct"."LastName" AS "LastName", "acct"."Company" AS "Company",
COALESCE((SELECT ALL SUM(("svc"."Cost" + "svc"."StateTax" + "svc"."CountyTax" +
"svc"."CityTax" + "svc"."ServiceTax" + "svc"."RoomTax") * "NumUsed") AS "SUM of
Expression" FROM "tblguestservices" AS "svc" WHERE "svc"."DateOfService" < DATE
'2013-2-28' + INTERVAL '1' DAY AND - "svc"."ResNumber" =
"acct"."AccountNumber"), CAST(0.00, DECIMAL)) AS "totalServiceCost",
COALESCE((SELECT ALL SUM("Amount") AS "SUM of Amount" FROM "tblfinancial_l" AS
"fin" WHERE "fin"."ResNumber" = - "acct"."AccountNumber" AND
"fin"."DateOfFinancial" < DATE '2013-2-28' + INTERVAL '1' DAY), CAST(0.00,
DECIMAL)) AS "totalPaid" FROM "tblAccount" AS "acct") AS "data"
WHERE ABS("TotalServiceCost" - "TotalPaid") > 0.01
ORDER BY "data"."AccountNumber"

Source Tables
-------------

data1 (data): 1208 rows

Result Set
----------

The result set was insensitive and read-only
The result set consisted of zero or more rows

Filtering
---------

The following filter condition was applied to the data1 (data) table:

ABS("TotalServiceCost" - "TotalPaid") > 0.01

Row scan (tblaccount): 1208 rows, 541184 bytes estimated cost


Result set I/O statistics
-------------------------

Total rows visited: 1

Row buffer manager

Max buffer size: 1048560 Buffer size: 136

Hits: 1   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

Index Page buffer manager

Max buffer size: 2097152 Buffer size: 4096

Hits: 1   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

================================================================================
1 row(s) returned in 29.391 secs
================================================================================

Bill


Queries with subselects are a "bit" more difficult to analyse than simple queries. What you have basically doesn't report on the subselects performance. You have two of them in there (if I'm counting correctly).

You need to extract the outer one from the main query and run it as a stand alone. Then extract the inner one and run that as a stand alone. Then you look at the results for each query to see what's happening.

If you can post the results of those we may be able to suggest something.

Roy Lambert [Team Elevate]

Bill,

<< Is it true that every query is first prepared, and that the Prepare step
causes one client-server message;\; so executing a previously unprepared
statement requires a minimum of two messages? >>

Yes, that is correct.  The prepare actually constructs the physical
statement handle by passing the SQL to the server, which returns the
statement handle.  This is how all client database APIs work (ODBC, etc.).

<< If so, is there any way round this? >>

Why do you think this is an issue ?  Two messages should not be a problem.
If it is, then you need to consider manually preparing the query once (call
Prepare method) and then executing it multiple times, which will avoid the
prepare step for each execution.

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

Tim Young
Elevate Software
www.elevatesoft.com

Bill,

<< Performance on a LAN is ok. I am trying to make this work over a WAN @
3mbps. Thanks for the Wireshark suggestion. I'll try it. I have hooked the
Elevate query trace and the times I observe come from that; however I don't
know how much is server time and how much is wire time. >>

Can you post the trace events ?  You should be seeing a request time and a
response time, and both will indicate (primarily) the network time since the
EDB Server time will normally be trivial with query preparation.

<< I have other problems. One query is taking 29 seconds to complete on a
trivial dataset -- a few thousand records in each table. Server CPU goes
over 50% (2*2.2G x86). I think that is way too heavy. To be clear at the
risk of tautology, that is one composite query, not a whole set of simple
ones. Is there a query analysis and optimization mechanism for problems like
this? >>

You can generate an execution plan for any query using this property:

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

It will tell you how the query is being executed, along with hints for
adding indexes where they may be needed.

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

Tim Young
Elevate Software
www.elevatesoft.com

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