Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 13 total |
Slow JOINs with VIEWs? |
Sat, Feb 14 2009 10:37 AM | Permanent Link |
"Hedley Muscroft" | 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! |
Sat, Feb 14 2009 11:31 AM | Permanent Link |
"Hedley Muscroft" | 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! |
Sat, Feb 14 2009 10:31 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Sat, Feb 14 2009 10:47 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Sun, Feb 15 2009 4:46 AM | Permanent Link |
"Hedley Muscroft" | 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 |
Sun, Feb 15 2009 8:21 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sun, Feb 15 2009 11:29 AM | Permanent Link |
"Hedley Muscroft" | >> 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! |
Mon, Feb 16 2009 12:01 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Feb 16 2009 12:05 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Mon, Feb 16 2009 2:19 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |