I seem to be hitting quite a lot of performance issues when JOINing to VIEWs
in my queries in ElevateDB.

I imagine poor old Tim must be sick and tired of performance comparisons
between DBISAM4 and EDB, but here we go anyway...

DBISAM4 doesn't support VIEWs at all. My application can support multiple
back-ends (currently PGSQL & DBISAM and soon EDB!!! I needed VIEWs so I
wrote a SQL parser of my own for DBISAM4 which does the following :-

[1] Parses the SQL for any reference to a VIEW
[2] On finding a VIEW, it gets the SQL source and creates a MEMORY table for
the view
[3] It then replaces occurrences of the VIEW name in the SQL with
"MEMORY\some_view_name"
[4] Rinse-and-repeat until all views were processed

Somewhat surprisingly, this works incredibly well!

None-the-less I'm excited to move from DBISAM4 to EDB so that I can now use
'real' VIEWs rather than this kludge. Somewhat surprisingly though, I'm
running into some big performance problems.

---------------
EXAMPLE
---------------
The following query is used to read INVOICES into a grid. I've checked the
Query Execution plan in EDB - everything is indexed and looks fine. The
testing/timings were done using ElevateDB Manager (Unicode) and the DBISAM
Database Utility and using the same 'real world' database (migrated from
DBISAM 4 -> EDB).

---------------
select inv.id, invoicenum, datetime, inv.discpercent, discamount, nettotal,
taxtotal, total, printed,
inv.per_id as recipient_per_id, rtrim(recipper.lastname || ', ' ||
coalesce(recipper.firstname,'') || ' ' || coalesce(recipper.title,'')) as
_recipient,
inv.clinic_id, clinic.name as _clinicname, inv.staff_id, staff.knownas as
_staffmember,
inv.room_id, room.name as _roomname, inv.apptype_id, apptype.name as
_apptype,
inv.patient_per_id, rtrim(patper.lastname || ', ' ||
coalesce(patper.firstname,'') || ' ' || coalesce(patper.title,'')) as
_patient,
patper.code, patper.insuranceref, (coalesce(clinic.prefix, '') ||
cast(inv.invoicenum as varchar(20))) as _invoiceref,

coalesce(view_invamtalloc.amtalloc, 0) as amtpaid,
case
 when (inv.total = 0) or (view_invamtalloc.amtalloc >= inv.total) then
'Paid In Full'
 when (view_invamtalloc.amtalloc > 0) and (view_invamtalloc.amtalloc <
inv.total) then 'Part Paid'
 else 'Unpaid'
end as _status

from inv
inner join per as recipper on (inv.per_id = recipper.id)
inner join clinic on (inv.clinic_id = clinic.id)
left join staff on (inv.staff_id = staff.id)
left join room on (inv.room_id = room.id)
left join apptype on (inv.apptype_id = apptype.id)
left join per as patper on (inv.patient_per_id = patper.id)
left join view_invamtalloc on (inv.id = view_invamtalloc.inv_id)
---------------

Here's the DBISAM equivalent SQL (after it the SQL parser has converted the
VIEWs) :-

---------------
drop table if exists memory\view_invamtalloc;

select inv_id, per_id, sum(amount) as amtalloc into memory\view_invamtalloc
from alloc where (inv_id is not null) group by inv_id, per_id;

select inv.id, invoicenum, datetime, inv.discpercent, discamount, nettotal,
taxtotal, total, printed,
inv.per_id as recipient_per_id, rtrim(recipper.lastname || ', ' ||
coalesce(recipper.firstname,'') || ' ' || coalesce(recipper.title,'')) as
_recipient,
inv.clinic_id, clinic.name as _clinicname, inv.staff_id, staff.knownas as
_staffmember, inv.room_id, room.name as _roomname,
inv.apptype_id, apptype.name as _apptype, inv.patient_per_id,
rtrim(patper.lastname || ', ' || coalesce(patper.firstname,'') || ' ' ||
coalesce(patper.title,'')) as _patient, patper.code,
patper.insuranceref, (coalesce(clinic.prefix, '') || cast(inv.invoicenum as
varchar(20))) as _invoiceref,
coalesce("memory\view_invamtalloc".amtalloc, 0) as amtpaid,
(inv.total-coalesce("memory\view_invamtalloc".amtalloc, 0)) as outstanding,

case
 when (inv.total = 0) or ("memory\view_invamtalloc".amtalloc >= inv.total)
then 'Paid In Full'
 when ("memory\view_invamtalloc".amtalloc > 0) and
("memory\view_invamtalloc".amtalloc < inv.total) then 'Part Paid'
  else 'Unpaid'
end as _status

from inv
inner join per as recipper on (inv.per_id = recipper.id)
inner join clinic on (inv.clinic_id = clinic.id)
left join staff on (inv.staff_id = staff.id)
left join room on (inv.room_id = room.id)
left join apptype on (inv.apptype_id = apptype.id)
left join per as patper on (inv.patient_per_id = patper.id)
left join "memory\view_invamtalloc" on (inv.id =
"memory\view_invamtalloc".inv_id);
---------------

DBISAM 4 returns 12,000 rows in 2.5 seconds (0.5 secs for "select into..." +
2 secs for main select)
ElevateDB returns 12,000 rows in 20.063 seconds.

That's a big difference! Unfortunately, the problem gets a lot worse for me
because I have a finances screen which UNIONs 4 queries similar to the above
(invoices, payments, refunds and credit notes). Here are the results of the
big UNIONed query :-

DBISAM 4 returns 23,000 rows in 4.9 seconds
ElevateDB Manager hangs (consuming 25% of my QUAD Core CPU). I gave up after
5 minutes

The problem is clearly caused by the JOIN to the VIEW (which is only a
simple one as you can see from the DBISAM SQL above) because if I remove the
VIEW from the equation, here's the result :-

DBISAM 4 returns 12,000 rows in 1.0 sec
ElevateDB  returns 12,000 rows in 1.11 secs

So I guess the question is : what can I do to optimize JOINs to VIEWs in
ElevateDB?

Any suggestions would be most gratefully accepted!

To help troubleshoot this problem, I've uploaded the test databases as
follows :-

You can download the DBISAM 4 database here :-
http://files.pioneersoftware.co.uk/temp/dbisam_testdb.7z

You can download the ElevateDB UNICODE database here (to save migrating it
yourself) :-
http://files.pioneersoftware.co.uk/temp/edb_testdb.7z

The ElevateDB database is a straight migration of the DBISAM 4 one but I
forgot to add the VIEW, so you need to execute this on the ElevateDB before
testing :-

------------
create view view_invamtalloc AS
select inv_id, per_id, sum(amount) as amtalloc
from alloc
where (inv_id is not null)
group by inv_id, per_id;
------------

I've attached two SQL files one for ElevateDB and the other for DBISAM.

On my system, ElevateDB takes 20 seconds, DBISAM takes 2.5 seconds.

Remove the JOIN to the VIEW and they both take about 1 second.

Thanks in advance for any help!

Hedley,

<< I seem to be hitting quite a lot of performance issues when JOINing to
VIEWs in my queries in ElevateDB. >>

I found the issue.  It's related to the type of result set that is returned
for views vs. the raw table opens that are performed on the base tables for
purposes of usage in queries.

I'm going to have to do some more looking into this, but I should be able to
fix it for 2.02 B8.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hedley,

Okay, it's now fixed.  The same query now runs in 3.9 secs instead of ~50
secs.

--
Tim Young
Elevate Software
www.elevatesoft.com

Excellent! Looking forward to 2.02b8! (soon?!

I just wanted to say what an excellent job you're doing Tim. I've been using
your database engines since DBISAM 2 and every release has been superb. In
all those years not one of my customers has ever lost data due to corruption
caused by the database engine (unlike my prior experiences with Paradox and
MS Access!).

Now that I've just started working with ElevateDB, it's already clear that
it too is a superb product and will continue the trend set by DBISAM. Thanks
for all your hard work and excellent support Tim - it really is appreciated.

Regards,

Hedley

Hedley

>Now that I've just started working with ElevateDB, it's already clear that
>it too is a superb product and will continue the trend set by DBISAM.

Shame you have to spoil it by using .Net <vbg>

Roy Lambert

>> Shame you have to spoil it by using .Net <vbg>

Actually, if you'd have told me a few years ago that I would coding in MS
Visual Studio today, I would have laughed (as would any dedicated Delphi fan
boy)!

But about 2-3 years back, I decided to teach myself C# (very few Delphi
contracts available in the UK) and I haven't really looked back since. I
still have a number of projects I maintain which were written in either D3,
D5 or D7 (the odd numbers were always better!) and I still enjoy coding in
Delphi, but I'm afraid to say that C# has won me over!

I wouldn't speak too soon though Roy - chances are that future releases of
Delphi (or RAD Studio, or whatever it is) will end up dropping Win32 and be
..NET only soon enough!

Hedley,

<< I just wanted to say what an excellent job you're doing Tim. I've been
using your database engines since DBISAM 2 and every release has been
superb. In all those years not one of my customers has ever lost data due to
corruption
caused by the database engine (unlike my prior experiences with Paradox and
MS Access!).

Now that I've just started working with ElevateDB, it's already clear that
it too is a superb product and will continue the trend set by DBISAM. Thanks
for all your hard work and excellent support Tim - it really is appreciated.
>>

Thanks very much for the kind words.   It helps that we're starting to get
more and more people using ElevateDB with Visual Studio.  The .NET Data
Provider is starting to really mature and become a really good solution for
VS developers.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hedley,

<< I wouldn't speak too soon though Roy - chances are that future releases
of Delphi (or RAD Studio, or whatever it is) will end up dropping Win32 and
be .NET only soon enough!  >>

Actually, the opposite happened. CodeGear has essentially dropped the
..NET compiler in favor of (what used to be) Oxygene from RemObjects and is
now called Prism.  It works in the VS IDE directly.  The Delphi compiler is
now pretty much concentrated on delivering 100% native code apps.  I think
that this is the best situation for all involved.   There is simply going to
always be a segment of the developer population that needs/wants native
applications.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hedley

>But about 2-3 years back, I decided to teach myself C# (very few Delphi
>contracts available in the UK)

Its a hobby for me, my money earning activity has, for the last 15 years, been recruitment but that may well be changing with how industry and commerce seem to be heading down the nearest plughole.

> D3,
>D5 or D7 (the odd numbers were always better!)

So I've heard but I went D1,D2,D3,D6,D2006

>but I'm afraid to say that C# has won me over!

Don't be - I'm sure your mother still loves you

>I wouldn't speak too soon though Roy - chances are that future releases of
>Delphi (or RAD Studio, or whatever it is) will end up dropping Win32 and be
>.NET only soon enough!

The move to unicode has probably frozen my development environment at D2006, but I doubt your scenario unless CodeGear like the idea of cutting its throat to see the pretty blood.

Roy Lambert