Whoops... sorry about that.

I was having error messages come up when I was sending them - and they
were just staying on the screen.. I guess they were getting through
after all.

> Adam
>
>
> Bad case of hiccoughs there.
>
> Roy Lambert
>

That was their data. Everything is identical except for the machine it's
running on.

It really causes DBISam to have issues too. Once I start the query - the
app (or DBSYS) doesn't respond. I can crash it - but even well after the
time out period - the server doesn't disconnect the session - it's still
there chugging away trying to retrieve the result (I guess).

If I try to force a disconnect using SVRADMIN - it then hangs up too.
(The SRVADMIN application). Effectively I need to close and restart the
service to stop it from running, otherwise it just keeps going forever
(it seems).

Very odd...

Cheers

Adam.

> Adam
>
>
> You've tried the query on your machine. Was that with your data or their data?
>
> Roy Lambert Roy Lambert [Team Elevate]
>

Hi Eduardo,

Thanks for the suggestion. I tried that on my machine - and oddly it
actually made the query run slower.

Thanks for the suggestion.


> Adam
>
> Have you tried to use "NOJOINOPTIMIZE" at the end of query statement ?
> And, some times JOINOPTIMIZECOSTS helps too.
>
> Eduardo
>
>

Adam


Well that leaves either their machine or the version of DBISAM as the culprit. I'd drop an email to Tim asking if there could be a variance between their version of DBISAM and yours which could cause it. If not it sounds like its the machine. Probably some software installed rather than hardware.

Just thinking about the latter. Asking users what's installed is generally non-productive. I know there are tools out there which will take a complete inventory. Why not track one down and have your customer run it. Something may be surfaced.

Roy Lambert [Team Elevate]

Thanks Roy,

They should be running the same version of DBISam that I am running -
which effectively leaves the machine. (Either installed software as
you've suggested, or possible hardware)

As this application is pretty important to them and business critical,
I'm considering the option of getting a second server solely to run the
data on this.

While I thought it was a bit of an overkill - I know that they would be
more than happy to pay for a new dedicated server given how reliant they
are on the uptime of the application.

Cheers

Adam.



> Adam
>
>
> Well that leaves either their machine or the version of DBISAM as the culprit. I'd drop an email to Tim asking if there could be a variance between their version of DBISAM and yours which could cause it. If not it sounds like its the machine. Probably some software installed rather than hardware.
>
> Just thinking about the latter. Asking users what's installed is generally non-productive. I know there are tools out there which will take a complete inventory. Why not track one down and have your customer run it. Something may be surfaced.
>
> Roy Lambert [Team Elevate]
>

Adam,

<< My clients are running 4.30 Build 4 whereas I still am running 4.28 Build
4 through DBSYS. (Although I have 4.30 in my IDE and it works the same) >>

What does the query plan look like if you run the query using 4.30 B4 on
your machine ?  I suspect that the difference is with the version of DBISAM
and how the tables and joins are being ordered.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Thanks for your reply...

> Adam,
>
> << My clients are running 4.30 Build 4 whereas I still am running 4.28
> Build 4 through DBSYS. (Although I have 4.30 in my IDE and it works the
> same) >>
>
> What does the query plan look like if you run the query using 4.30 B4 on
> your machine ? I suspect that the difference is with the version of
> DBISAM and how the tables and joins are being ordered.

The original query plan when using 4.30 B4 on my machine executes fine.
It appears as per below.

Best Regards

Adam

------------------------------
================================================================================
SQL statement (Executed with 4.30 Build 4)
================================================================================

Select AC.ID as AppCourseID, AC.EstEndDate, AM.ModuleID,
AM.Hrs HrsClaimable, Sum(Coalesce(AV.Hrs,0)) as HrsClaimed
From ApprenticeCourse AC
inner join apprenticemodules am on (am.appcourseid = ac.id)
inner join apprenticevisits av on (av.appcourseid = am.appcourseid) and
(av.moduleid = am.moduleid)
Group By AC.ID, AM.ModuleID

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

ApprenticeCourse (AC) table opened shared, has 19821 rows
apprenticemodules (am) table opened shared, has 19267 rows
apprenticevisits (av) table opened shared, has 290640 rows

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

Result set will be canned

Result set will consist of one or more grouped rows

Result set will be grouped by the following column(s) using a temporary
index:

AppCourseID
ModuleID

Result set will be ordered by the temporary index created for the grouping

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

The driver table is the ApprenticeCourse table (AC)


The ApprenticeCourse table (AC) is joined to the apprenticemodules table
(am)
with the INNER JOIN expression:


ac.id = am.appcourseid


The apprenticemodules table (am) is joined to the apprenticevisits table
(av)
with the INNER JOIN expression:


am.appcourseid = av.appcourseid and am.moduleid = av.moduleid

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

Optimized Join Ordering
-----------------------

The driver table is the apprenticemodules table (am)


The apprenticemodules table (am) is joined to the ApprenticeCourse table
(AC)
with the INNER JOIN expression:


am.appcourseid = ac.id


The apprenticemodules table (am) is joined to the apprenticevisits table
(av)
with the INNER JOIN expression:


am.appcourseid = av.appcourseid and am.moduleid = av.moduleid

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

Costs ARE NOT being taken into account when executing this join
Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to
force the
optimizer to consider costs when optimizing this join

The expression:

am.appcourseid = ac.id

is OPTIMIZED

The expression:

am.appcourseid = av.appcourseid and am.moduleid = av.moduleid

is OPTIMIZED

================================================================================
>>>>> 18864 rows affected in 10.125 seconds
================================================================================

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