Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 9 of 9 total
Thread DBISam Enhancement Request
Mon, Dec 12 2011 6:24 PMPermanent Link

Adam H.

Hi Tim,

I was just wondering - how difficult would it be to increase the maximum
number of indexes allowed for a table, and would you be willing to make
this change in an upcoming release?  Smile

At present I believe the maximum number of indexes is 30.

Cheers

Adam.
Wed, Dec 14 2011 3:51 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Adam,

<< I was just wondering - how difficult would it be to increase the maximum
number of indexes allowed for a table, and would you be willing to make this
change in an upcoming release?  Smile>>

Unfortunately, due to the way that the indexes are stored in the header, I
cannot increase the count without making the tables incompatible with older
versions of DBISAM.  So, this would amount to a format change, which I am
very against at this point in time for the product.

--
Tim Young
Elevate Software
www.elevatesoft.com
Wed, Dec 14 2011 6:04 PMPermanent Link

Adam H.

Hi Tim,

Thanks for your reply...

> Unfortunately, due to the way that the indexes are stored in the header,
> I cannot increase the count without making the tables incompatible with
> older versions of DBISAM. So, this would amount to a format change,
> which I am very against at this point in time for the product.

Hmm... sounds like a good reason to develop DBISAM version 5.  Wink


Seriously though - I've hit a bit of a snag with one of my existing
applications. One of the main tables has many fields that are lookup
fields to other tables.

Most of the indexes in that table are solely used for Query performance
on these lookups.

I've reached a point where the lookup fields total more than 30 - and
all these fields are used as constraints in one query or another, as
well as joins. (Not all in the same query though).

As performance takes a big hit if these fields don't have an index - I
was wondering how you would suggest that I proceed?

Before you say it - I would LOVE to move this application to EDB - but
it's impossible. It's an application that constantly has enhancements
made (every few days)- and I have too many queries in there that would
require complete SQL re-writes which would take months to complete. (I
can't afford the 'downtime').

Do you have any suggestions on what I can do - or am I at a point where
any new links I require are just going to take a significant performance
hit, and there's nothing I can do?

Cheers

Adam.
Wed, Dec 14 2011 6:44 PMPermanent Link

Raul

Team Elevate Team Elevate

Adam,

One of things I've done in the past is to create a 2nd table that acts
as an extension of the main table and start putting new fields in there.
Idea is to use same primary id and then you would have a record in each.

Depending on what queries you run additional join might be needed now to
this new table  (though in some cases copying couple of additional
fields over in addition to ID might allow you to query one of the tables
only in queries - depends on app and design. Can use a trigger for
example to keep tables in sync or do it at code-level.

Alternative to that is building some intermediate tables that are used
by queries directly - think flattened reporting type tables - that can
be updated again by triggers or using some regularly running maintenance
routine (data could be out of date here).

Raul


> I've reached a point where the lookup fields total more than 30 - and
> all these fields are used as constraints in one query or another, as
> well as joins. (Not all in the same query though).
>
> As performance takes a big hit if these fields don't have an index - I
> was wondering how you would suggest that I proceed?
Wed, Dec 14 2011 6:57 PMPermanent Link

Adam H.

Hi Raul,

And thanks for your reply.

So effectively I'm going with a 1:1 join with another table - spread out
my fields between multiple tables.

As this application works both on Local and Remote - trigers are
unfortuantely not an option for me. However - I might be able to
maintain it like such.

Will having to inner join between these two tables for most queries
cause much performance issues? (ie - should I try and restructure so the
'more used' fields are in the main table - or will it not make much
difference)?

Cheers

Adam.
Wed, Dec 14 2011 7:44 PMPermanent Link

Raul

Team Elevate Team Elevate

> As this application works both on Local and Remote - trigers are
> unfortuantely not an option for me. However - I might be able to
> maintain it like such.
You can still do it - triggers belong to engine, not server.
For local app can assign the appropriate Engine.Trigger function (e.g.
Egine.AfterUpdateTrigger, etc) when your app starts or just drop engine
on your app form and add the event from object inspector. For remote you
do need a custom dbsrvr compiled and deployed but only change there is
same as for local - in engine object add the trigger events. Since code
will be identical you can create a unit with the appropriate functions
and attach those for local and remote so you have single codebase.


> Will having to inner join between these two tables for most queries
> cause much performance issues? (ie - should I try and restructure so the
> 'more used' fields are in the main table - or will it not make much
> difference)?

It might be bit slower than not doing a join. Only way to know for sure
is to copy the existing table into new table so you have 2 and run some
existing complex queries against single and 2 tables and see what the
actual results are.
'more used' depends very much on usage. For example if you always need
couple of them then i'd include it in both - something like description
or such is a good target for this. However if you need most of the
fields most of the time then just do 2 joins.
Also make sure you have a nice link between the 2 - if you're primary
key is string or multi-column then up creating a new autoinc field on
main table and using that as a key between the 2 tables might be quicker
from join perspective.

Raul
Thu, Dec 15 2011 4:46 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Adam,

<< Hmm... sounds like a good reason to develop DBISAM version 5.  Wink>>

The problem with doing a DBISAM 5 is that I already did it. Wink

<< As performance takes a big hit if these fields don't have an index - I
was wondering how you would suggest that I proceed? >>

You can easily modify DBISAM to accept more indexes and recompile everything
that you're using (dbsys, dbsrvr, etc.).  It's just that none of the tables
will then be inter-operable with the normal DBISAM engine, and you'll have
to do the recompile for every new build.  Plus, you'll have to figure out a
way to upgrade the existing tables that you have at your customer sites.

As I said, the problem is that the index definitions are static structures
that are stored in the header of each table.  Therefore, a limit had to be
picked that balanced size with usability.  This is exactly why EDB has a
catalog and doesn't store the structures in the table header.

<< Do you have any suggestions on what I can do - or am I at a point where
any new links I require are just going to take a significant performance
hit, and there's nothing I can do? >>

Raul probably has the best suggestion.  It's about the only way to "extend"
the index count without modifying the engine.


--
Tim Young
Elevate Software
www.elevatesoft.com
Thu, Dec 15 2011 4:17 PMPermanent Link

Adam H.

Thanks again for your reply Raul.

Seems like the best option for me is to go down this path, and have all
the 'less used' fields in the second table. (Meaning that the majority
of reports and queries won't need the link).

Cheers

Adam.
Thu, Dec 15 2011 4:25 PMPermanent Link

Adam H.

Hi Tim,

> << Hmm... sounds like a good reason to develop DBISAM version 5. Wink>>
>
> The problem with doing a DBISAM 5 is that I already did it. Wink

Ahh - but it's buggy... it doesn't run my SQL statements. <vbg>


> You can easily modify DBISAM to accept more indexes and recompile
> everything that you're using (dbsys, dbsrvr, etc.).

I didn't realise this! Thanks for the information! That gives me
something to think about and another option!

It makes sense why you haven't intentionally gone above 30 before now,
and also why you have the catalogue in EDB.

> It's just that none
> of the tables will then be inter-operable with the normal DBISAM engine,
> and you'll have to do the recompile for every new build.

I can probably live with that. I'll sleep on it for a couple of days and
toss up the pro's and con's before making a decision.

Cheers!

Adam.
Image