Hi,

I've ran into an issue with speed on one particular table, that I was
hoping someone may be able to shed some light on.

The speed issues occur on various queries - but the weird thing is that
it only occurs the first time a query is ran. If another query is ran at
a later date (even if data has been changed in the table), it runs fast.

This is occuring using both DBSRVR, and also local access. (Local access
when the data is on the same computer as DBSYS).

The first time I ran the query, it took 2 mins to open. The second time
just over 1 second. In between I added a new record, so I figure that
result caching isn't the issue?

Occurs in DBISam 4.30 build 4 and also 4.34 build 5

The table does have an engine signature assigned to it. I'm not sure
whether this makes a difference or not?

Below are outputs of query plans.

Any suggestions would be greatly appreciated.

Cheers

Adam.




Here is an example of the query plan when it is first ran:

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

Select distinct AC.ID
From ApprenticeCourse AC
inner join ApprenticeVisits AV on (AV.AppCourseID = AC.ID)
where (AV.VisitType = 'PC')

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

ApprenticeCourse (AC) table opened shared, has 8714 rows
ApprenticeVisits (AV) table opened shared, has 208514 rows

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

Result set will be canned

Result set will consist of one or more distinct rows

Duplicate result set rows will be removed using a temporary index on all
of the
result set columns

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

The expression:

AV.VisitType = 'PC'

is OPTIMIZED, covers 25605 rows or index keys, costs 358470 bytes, and
will be
applied to the ApprenticeVisits table (AV) before any joins

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

The driver table is the ApprenticeCourse table (AC)


The ApprenticeCourse table (AC) is joined to the ApprenticeVisits table
(AV)
with the INNER JOIN expression:


AC.ID = AV.AppCourseID

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 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:

AC.ID = AV.AppCourseID

is OPTIMIZED

================================================================================
>>>>> 2545 rows affected in 119.516 seconds
================================================================================








Here is a copy of the query plan when ran again. (In between, I inserted
a new row):

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

Select distinct AC.ID
From ApprenticeCourse AC
inner join ApprenticeVisits AV on (AV.AppCourseID = AC.ID)
where (AV.VisitType = 'PC')

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

ApprenticeCourse (AC) table opened shared, has 8714 rows
ApprenticeVisits (AV) table opened shared, has 208515 rows

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

Result set will be canned

Result set will consist of one or more distinct rows

Duplicate result set rows will be removed using a temporary index on all
of the
result set columns

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

The expression:

AV.VisitType = 'PC'

is OPTIMIZED, covers 25605 rows or index keys, costs 358470 bytes, and
will be
applied to the ApprenticeVisits table (AV) before any joins

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

The driver table is the ApprenticeCourse table (AC)


The ApprenticeCourse table (AC) is joined to the ApprenticeVisits table
(AV)
with the INNER JOIN expression:


AC.ID = AV.AppCourseID

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 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:

AC.ID = AV.AppCourseID

is OPTIMIZED

================================================================================
>>>>> 2545 rows affected in 1.141 seconds
================================================================================

Adam


The only thing that comes to my mind is actual opening of the tables. It shouldn't take that long but since once DBISAM has the table opened it will, unless you've told it otherwise, maintain the handle which means subsequent accesses are faster.

Can you try a test. Run the query in DBSys but before running prepare the query. That obtains the necessary handles. If it runs quickly doing that then the problem is opening the tables.

Roy Lambert [Team Elevate]

Hi Roy,

Thanks for your suggestion. I gave preparing the query first a try. When
I clicked on Prepare - it prepared instantaneously.

After preparing I ran the query, and it took another 2 minutes to run again.

I tried something different after this. I restarted the computer, and
then started the query, and cancelling it as soon as I could (5% in by
the progress bar).

It gave me a limited result of what it had already prepared. When I ran
the query a second time, it jumped to 5% straight away as though it
already 'knew' what those records were, but then still took ages to
finish the query.

Not sure if this gives any indication as to what the issue might be?

I've tried disabling antivirus (running Eset) but that didn't make any
difference.

Windows Server 2003 is one of the machines with the issue. The other is
Windows 2007.

Cheers

Adam.

Adam

It looks a lot like an OS file caching - windows files caching is
transparent to DBISAM.

To verify you could run a simple select query on one of the tables first
(for example select * from ApprenticeVisits) and then run the query in
question - its first run should be lot faster now.

I don't believe there is anything you can do about it though.

Raul


On 12/20/2012 6:23 PM, Adam H. wrote:
> Hi Roy,
>
> Thanks for your suggestion. I gave preparing the query first a try. When
> I clicked on Prepare - it prepared instantaneously.
>
> After preparing I ran the query, and it took another 2 minutes to run
> again.
>
> I tried something different after this. I restarted the computer, and
> then started the query, and cancelling it as soon as I could (5% in by
> the progress bar).
>
> It gave me a limited result of what it had already prepared. When I ran
> the query a second time, it jumped to 5% straight away as though it
> already 'knew' what those records were, but then still took ages to
> finish the query.
>
> Not sure if this gives any indication as to what the issue might be?
>
> I've tried disabling antivirus (running Eset) but that didn't make any
> difference.
>
> Windows Server 2003 is one of the machines with the issue. The other is
> Windows 2007.
>
> Cheers
>
> Adam.
>
>

Hi Raul,

Thanks for your suggestion. Unfortunately this didn't work...

I tried executing them separately, and the same problem. I've then
combined them into the single SQL as per the below, and generated the
plan so you can see the results.

Both ApprenticeCourse and ApprenticeVisits seem to execute at a
reasonable speed, but then the combined one doesn't.

With this query that there is no need to actually have the 2nd table in
there - but I do want additional fields further down the track (I've
just simplified it here).

ie: I could simply have

Select distinct AppCourseID as ID
From ApprenticeVisits
where (VisitType = 'PC')

When running this the query runs in 1.8 seconds - however as I'm wanting
to add additional fields to the result (I've just simplified the query
here).

Cheers

Adam.



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

Select * from apprenticecourse

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

apprenticecourse (apprenticecourse) table opened shared, has 8714 rows

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

Result set will be canned

Result set will consist of one or more rows

================================================================================
>>>>> 8714 rows affected in 0.703 seconds
================================================================================

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

Select * from apprenticevisits

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

apprenticevisits (apprenticevisits) table opened shared, has 208515 rows

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

Result set will be canned

Result set will consist of one or more rows

================================================================================
>>>>> 208515 rows affected in 4.735 seconds
================================================================================

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

Select distinct AC.ID
From ApprenticeCourse AC
inner join ApprenticeVisits AV on (AV.AppCourseID = AC.ID)
where (AV.VisitType = 'PC')

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

ApprenticeCourse (AC) table opened shared, has 8714 rows
ApprenticeVisits (AV) table opened shared, has 208515 rows

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

Result set will be canned

Result set will consist of one or more distinct rows

Duplicate result set rows will be removed using a temporary index on all
of the
result set columns

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

The expression:

AV.VisitType = 'PC'

is OPTIMIZED, covers 25605 rows or index keys, costs 358470 bytes, and
will be
applied to the ApprenticeVisits table (AV) before any joins

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

The driver table is the ApprenticeCourse table (AC)


The ApprenticeCourse table (AC) is joined to the ApprenticeVisits table
(AV)
with the INNER JOIN expression:


AC.ID = AV.AppCourseID

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 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:

AC.ID = AV.AppCourseID

is OPTIMIZED

================================================================================
>>>>> 2545 rows affected in 44.641 seconds
================================================================================

That is curious.

As far as i can tell your indexes look ok.

Have you optimized tables just in case.

Also have you tried reversing the order of tables in the select to see
if it makes a difference :

Select distinct AC.ID
From ApprenticeVisits AV
inner join ApprenticeCourse AC
on (AV.AppCourseID = AC.ID)
where (AV.VisitType = 'PC')

Raul

On 12/20/2012 7:13 PM, Adam H. wrote:
> Hi Raul,
>
> Thanks for your suggestion. Unfortunately this didn't work...
>
> I tried executing them separately, and the same problem. I've then
> combined them into the single SQL as per the below, and generated the
> plan so you can see the results.
>
> Both ApprenticeCourse and ApprenticeVisits seem to execute at a
> reasonable speed, but then the combined one doesn't.

Hi Raul,

Thanks for the suggestion. I've given optimizing the tables a try - and
no go - same problem again. (It took quite some time to optimise the
ApprenticeVisits table).

It's really got me beat at the moment. Two different machines (OS's)

I've optimized the tables on my local machine, and have re-ran the query
(after closing and restarting) and the problem remains.

I'd like to be able to try the same query / situation without a
signature on the tables - but I don't think I can remove the signature.
(As the table contains BLOB fields - I can't simply reverse engineer the
table).

I'm suspicious that it's the join that's causing issues, and not the
actual where clause, as it runs slow even without the where in there

ie:
Select distinct AC.ID
From ApprenticeVisits AV
inner join ApprenticeCourse AC on (AV.AppCourseID = AC.ID)

Cheers

Adam.

Ada,

It's probably worthwhile to email elevate support (Tim) direct with this
- he'd be able to provide a definitive answer on what can be done.

Raul



On 12/20/2012 7:56 PM, Adam H. wrote:
> Hi Raul,
>
> It's really got me beat at the moment. Two different machines (OS's)
>
> I'm suspicious that it's the join that's causing issues, and not the
> actual where clause, as it runs slow even without the where in there
>
> Cheers
>
> Adam.

Adam H. wrote:

Hi Adam,

> Hi Raul,
>
> Thanks for the suggestion. I've given optimizing the tables a try -
> and no go - same problem again. (It took quite some time to optimise
> the ApprenticeVisits table).
>

and if you test this:

Select distinct AC.ID
From ApprenticeVisits AV
left outer join ApprenticeCourse AC on (AV.AppCourseID = AC.ID)
where (AV.VisitType = 'PC')

Ralf

Adam


I'd second Raul's suggestion of contacting Tim.

The only other thing I can suggest trying is a script. I've forgotten the DBISAM syntax so sorry if I get it wrong


Select * From ApprenticeVisits INTO Memory\T1 where (VisitType = 'PC');
Select distinct AC.ID
From ApprenticeCourse AC inner join Memory\T1 AV on (AV.AppCourseID = AC.ID)
where (AV.VisitType = 'PC');
DROP Memory\T1;


Roy Lambert [Team Elevate]