<<Yes, that would be very much appreciated.  Please let me know what you find
out.>>

OK. The 4.25b5 server I compiled with FastMM was able to stay up all day with no reports of slowdowns. One thing I did notice was that the
memory being used (according to taskmanager) did not fluctuate as much as before. I saw this with the 4.26b2 server the other day as well. With
that said, when I last checked, there was still one person in the system and the server was showing 563,788k being used. The most I would
expect one user to have would be 20-30k. It does not appear to be releasing memory as quickly, or as much as it used to .

The most I saw it using today was around 830,000k, which is a bit less than the 1,1 - 1,200,000k or so the old 4.25b5 was getting up to.

I am not ready to say with certainty that I believe there is an issue with 4.26b2 because I cannot guarantee that the activity today completely
replicated that from the other day; However, there sure appears to be something happening. There were no 10227 cannot write lock the table...
errors that showed up each time we tried the 4.26b2 server.

And of course there could be differences in FastMM between my build and the 4.26b2 server. I had FastMM on my machine from a while back
when I was testing with it. I believe the version of FastMM I have is 4.78. I cannot be certain of any .inc settings that may or may not be set to
default.

I would like to test 4.26b2 again, one more time to verify, but I might get lynched.

When the last person got out, it dropped to ~341,000k. I don't know if that was immediate or not. Later, after 5 or so people got back on, it was
around 343,000k.

Is this something I should be worried about? Is there some configuration for FastMM I need to be aware of that frees the memory quicker, or at
all? I turned off memory leak reporting, should I turn it back on?

Thanks for all of your help.

Vincent,

<< OK. The 4.25b5 server I compiled with FastMM was able to stay up all day
with no reports of slowdowns. One thing I did notice was that the memory
being used (according to taskmanager) did not fluctuate as much as before. I
saw this with the 4.26b2 server the other day as well. With that said, when
I last checked, there was still one person in the system and the server was
showing 563,788k being used. The most I would expect one user to have would
be 20-30k. It does not appear to be releasing memory as quickly, or as much
as it used to . >>

Windows takes it good 'ole sweet time when it comes to reclaiming memory and
updating the memory stats that you see in the task manager, so you can't
really use that as a gauge.  You can us this procedure:

procedure GetMemoryManagerState(var AMemoryManagerState:
TMemoryManagerState);

in FastMM to get the memory stats.

<< I am not ready to say with certainty that I believe there is an issue
with 4.26b2 because I cannot guarantee that the activity today completely
replicated that from the other day; However, there sure appears to be
something happening. There were no 10227 cannot write lock the table...
errors that showed up each time we tried the 4.26b2 server. >>

I can't see what it would possibly be - the only things changed have been
extremely minor bug fixes.  And write lock errors are certainly possible in
any extremely busy environment.

<< And of course there could be differences in FastMM between my build and
the 4.26b2 server. I had FastMM on my machine from a while back when I was
testing with it. I believe the version of FastMM I have is 4.78. I cannot be
certain of any .inc settings that may or may not be set to default. >>

We're compiling with 4.278 also.

--
Tim Young
Elevate Software
www.elevatesoft.com

In diagnosing a different issue, I have been able to replicate a problem using the following SQL:

SELECT T1.PF1, T1.PF2, T1.RF1, T1.RF2
FROM Table1 AS T1
LEFT OUTER JOIN Table2 AS T2 ON (T1.PF1=T2.PF1) AND (T1.PF2=T2.PF2)
WHERE  (T1.PF1 = 'ANUMBER') AND  (upper(T1.RF1) LIKE  upper('ANAME%'))
ORDER BY T1.PF1, T1.RF1
JOINOPTIMIZECOSTS

I have 2 versions of dbsys. I am running both from the same folder using file-sharing pointing to the same data folder. One is version 4.25 b3 the
other 4.26 b2. Version 4.25 b3 takes 1.5-2 seconds to run. Version 4.26 b2 takes 250-400 seconds to run. If I remove the JOINOPTIMIZECOSTS
from the SQL, 4.26 b2 takes 1.5-2 seconds to run. Maybe this has something to do with causing my slowdown when using 4.26 b2?

Indexes on T1.PF1, T1.PF2, T1.RF1. Index on T1.RF1 is case-insensitive.

I am having problems with my joins in 426b2 with or without joinoptimizecosts, my previous message was not completely correct.

The query:

I can run this exact query pointing to the same data using dbsys build 4.25 build 5 and its result is 14 rows in .36 seconds.
I can run this exact query pointing to the same data using dbsys build 4.26 build 2 and its result is 14 rows in 158.266 seconds.

I ran them both without JOINOPTIMIZECOSTS and they were both longer:
4.25 build 5 14 rows in 245.641 seconds.
4.26 build 2 14 rows in 243.281 seconds.

All of the key fields are strings. As a last test, I added a unique integer key on the needed tables and had the tables join
each other using the unique integer fields. In 4.26 build 2, the time it took to complete the new query was 11.375 seconds.

================================================================================
SQL statement (Executed with 4.26 Build 2)
================================================================================

SELECT T1.PFK1, T1.PKF2, T1.RF1, T1.RF2, T3.RF1, T4.RF1, T5.RF1 FROM Table1 AS T1
LEFT OUTER JOIN Table2 AS T2 (T2.PKF1=T1.PKF1) AND  (T2.PKF2=T1.PKF2)
LEFT OUTER JOIN Table3 AS T3 ON (T3.PKF1=T2.PKF1) AND (T3.PKF2=T2.PKF2) AND (T3.PKF3=T2.PKF3)
LEFT OUTER JOIN Table4 AS T4 ON (T4.PKF1=T2.PKF1) AND (T4.PKF2=T2.PKF2) AND (T4.PKF3=T2.PKF3)
LEFT OUTER JOIN Table5 AS T5 ON (T5.PKF1=T2.PKF1) AND (T5.PKF2=T2.PKF2) AND (T5.PKF3=T2.PKF3)
WHERE (PKF1='111111') AND (upper(RF1) LIKE upper('ARK%'))
JOINOPTIMIZECOSTS

Tables Involved
---------------

Table1 (T1) table opened shared, has 662578 rows
Table2 (T2) table opened shared, has 679461 rows
Table3 (T3) table opened shared, has 72773 rows
Table4 (T4) table opened shared, has 609447 rows
Table5 (T5) table opened shared, has 1155126 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

WHERE Clause Execution
----------------------

The expression:

PFK1 = '111111' AND upper(RF1) LIKE upper('ARK%')

is PARTIALLY-OPTIMIZED, covers 39 rows or index keys, costs 47946 bytes, and
will be applied to the Table1 table (T1) before any joins

Join Ordering
-------------

The driver table is the Table1 table (T1)

The Table1 table (T1) is joined to the Table2 table (T2) with the LEFT OUTER
JOIN expression:

T1.PKF1 = T2.PKF1 AND T1.PKF2 = T1.PKF2

The Table2 table (T2) is joined to the Table3 table (T3) with the LEFT
OUTER JOIN expression:

T2.PKF1 = T3.PKF1 AND T2.PKF2 = T3.PKF2 AND T2.PKF3 = T3.PKF3

The Table2 table (T2) is joined to the Table4 table (T4) with the LEFT
OUTER JOIN expression:

T2.PKF1 = T4.PKF1 AND T2.PKF2 = T4.PKF2 AND T2.PKF3 = T4.PKF3

The Table2 table (T2) is joined to the Table5 table (T5) with the LEFT
OUTER JOIN expression:

T2.PKF1 = T5.PKF1 AND T2.PKF2 = T5.PKF2 AND T2.PKF3 = T5.PKF3

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

The joins are already in optimal order and cannot be optimized any further

Join Execution
--------------

Costs ARE being taken into account when executing this join
Remove the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force
the optimizer to stop considering costs when optimizing this join

The expression:

T1.PKF1 = T2.PKF1 AND T1.PKF2 = T2.PKF2

is PARTIALLY-OPTIMIZED and is estimated to cost 218 bytes per candidate row

The expression:

T2.PKF1 = T3.PKF1 AND T2.PKF2 = T3.PKF2 AND T2.PKF3 = T3.PKF3

is PARTIALLY-OPTIMIZED and is estimated to cost 0 bytes per candidate row

The expression:

T2.PKF1 = T4.PKF1 AND T2.PKF2 = T4.PKF2 AND T2.PKF3 = T4.PKF3

is PARTIALLY-OPTIMIZED and is estimated to cost 1090 bytes per candidate row

The expression:

T2.PKF1 = T5.PKF1 AND T2.PKF2 = T5.PKF2 AND T2.PKF3 = T5.PKF3

is PARTIALLY-OPTIMIZED and is estimated to cost 978 bytes per candidate row

================================================================================
>>>>> 14 rows affected in 158.266 seconds
================================================================================


One thing I noticed in the plans was the estimated cost differences for each of the joins. 4.26 b2 estimated higher costs than 4.25 b5 and 4.25 b5
joins were "rewritten":


===============================================================================
SQL statement (Executed with 4.25 Build 5)
================================================================================


SELECT T1.PFK1, T1.PKF2, T1.RF1, T1.RF2, T3.RF1, T4.RF1, T5.RF1 FROM Table1 AS T1
LEFT OUTER JOIN Table2 AS T2 (T2.PKF1=T1.PKF1) AND  (T2.PKF2=T1.PKF2)
LEFT OUTER JOIN Table3 AS T3 ON (T3.PKF1=T2.PKF1) AND (T3.PKF2=T2.PKF2) AND (T3.PKF3=T2.PKF3)
LEFT OUTER JOIN Table4 AS T4 ON (T4.PKF1=T2.PKF1) AND (T4.PKF2=T2.PKF2) AND (T4.PKF3=T2.PKF3)
LEFT OUTER JOIN Table5 AS T5 ON (T5.PKF1=T2.PKF1) AND (T5.PKF2=T2.PKF2) AND (T5.PKF3=T2.PKF3)
WHERE (PKF1='111111') AND (upper(RF1) LIKE upper('ARK%'))
JOINOPTIMIZECOSTS

Tables Involved
---------------

Table1 (T1) table opened shared, has 662578 rows
Table2 (T2) table opened shared, has 679461 rows
Table3 (T3) table opened shared, has 72773 rows
Table4 (T4) table opened shared, has 609447 rows
Table5 (T5) table opened shared, has 1155126 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

WHERE Clause Execution
----------------------

The expression:

PKF1 = '111111' AND upper(RF1) LIKE upper('ARK%')

has been rewritten and is PARTIALLY-OPTIMIZED, covers 39 rows or index keys,
costs 47946 bytes, and will be applied to the Loan table (L) before any joins


Join Ordering
-------------

The driver table is the Table1 table (T1)

The Table1 table (T1) is joined to the Table2 table (T2) with the LEFT OUTER
JOIN expression:

T1.PKF1 = T2.PKF1 AND T1.PKF2 = T1.PKF2

The Table2 table (T2) is joined to the Table3 table (T3) with the LEFT
OUTER JOIN expression:

T2.PKF1 = T3.PKF1 AND T2.PKF2 = T3.PKF2 AND T2.PKF3 = T3.PKF3

The Table2 table (T2) is joined to the Table4 table (T4) with the LEFT
OUTER JOIN expression:

T2.PKF1 = T4.PKF1 AND T2.PKF2 = T4.PKF2 AND T2.PKF3 = T4.PKF3

The Table2 table (T2) is joined to the Table5 table (T5) with the LEFT
OUTER JOIN expression:

T2.PKF1 = T5.PKF1 AND T2.PKF2 = T5.PKF2 AND T2.PKF3 = T5.PKF3

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

The joins are already in optimal order and cannot be optimized any further

Join Execution
--------------

Costs ARE being taken into account when executing this join
Remove the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force
the optimizer to stop considering costs when optimizing this join

The expression:

T1.PKF1 = T2.PKF1 AND T1.PKF2 = T2.PKF2

has been rewritten and is PARTIALLY-OPTIMIZED and is estimated to cost 218
bytes per candidate row

The expression:

T2.PKF1 = T3.PKF1 AND T2.PKF2 = T3.PKF2 AND T2.PKF3 = T3.PKF3

has been rewritten and is PARTIALLY-OPTIMIZED and is estimated to cost 0 bytes
per candidate row

The expression:

T2.PKF1 = T4.PKF1 AND T2.PKF2 = T4.PKF2 AND T2.PKF3 = T4.PKF3

has been rewritten and is PARTIALLY-OPTIMIZED and is estimated to cost 570
bytes per candidate row

The expression:

T2.PKF1 = T5.PKF1 AND T2.PKF2 = T5.PKF2 AND T2.PKF3 = T5.PKF3

has been rewritten and is PARTIALLY-OPTIMIZED and is estimated to cost 514
bytes per candidate row

================================================================================
>>>>> 14 rows affected in 0.36 seconds
================================================================================

Vicent,

<< I am having problems with my joins in 426b2 with or without
joinoptimizecosts, my previous message was not completely correct.

The query:

I can run this exact query pointing to the same data using dbsys build 4.25
build 5 and its result is 14 rows in .36 seconds.
I can run this exact query pointing to the same data using dbsys build 4.26
build 2 and its result is 14 rows in 158.266 seconds. >>

I found the problem, and it's a dumb one.  Sometime before 4.26 B2 was
released, the expression rewrites were commented out during testing and not
uncommented.

Do you have the source code ?  If not, send me a private email and I'll send
you the corrected unit.

This will be fixed in the next DBISAM build.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks!

I have the source code. When I get a chance I will move b2 back into place.

Vincent,

<< I have the source code. When I get a chance I will move b2 back into
place. >>

The line that you want to uncomment is in the dbisamen.pas unit:

constructor TFilter.Create(Owner: TDataCursor;
                          NextToken: TExpToken;
                          FreeTokens: Boolean;
                          UseExisting: Boolean;
                          CalculateCosts: Boolean;
                          ClientFilterData: Integer;
                          CallbackFilterFunction: pFilterFunction);
begin
  FDataCursor:=TDataCursor(Owner);
  if (not Assigned(CallbackFilterFunction)) then
     begin
     FIsCallbackFilter:=False;
     FCallbackFunction:=nil;
     FClientData:=0;
     FFreeFilterTokens:=FreeTokens;
     FFilterTokens:=NextToken;
     if (not FDataCursor.IsRemote) then
        begin
        FOptimizeLevel:=FDataCursor.GetExpressionOptimizeLevel(FFilterTokens,
                                                               CalculateCosts);
        { If the filter expression is rewritten, then we need to
recalculate
          the optimization level and costs }
        FRewritten:=FDataCursor.RewriteExpression(FFilterTokens,CalculateCosts);
<<<<<<<<<< Here


--
Tim Young
Elevate Software
www.elevatesoft.com