![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 9 of 9 total |
![]() |
Mon, Dec 12 2011 6:24 PM | Permanent 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? ![]() At present I believe the maximum number of indexes is 30. Cheers Adam. |
Wed, Dec 14 2011 3:51 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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? ![]() 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 PM | Permanent 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. ![]() 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 PM | Permanent Link |
Raul ![]() | 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 PM | Permanent 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 PM | Permanent Link |
Raul ![]() | > 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Adam,
<< Hmm... sounds like a good reason to develop DBISAM version 5. ![]() The problem with doing a DBISAM 5 is that I already did it. ![]() << 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 PM | Permanent 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 PM | Permanent Link |
Adam H. | Hi Tim,
> << Hmm... sounds like a good reason to develop DBISAM version 5. ![]() > > The problem with doing a DBISAM 5 is that I already did it. ![]() 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. |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |