New Posts New Posts RSS Feed: Inefficient NextID Allocation
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Inefficient NextID Allocation
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
MichaelP View Drop Down
Newbie
Newbie
Avatar

Joined: 07-Aug-2007
Location: Australia
Posts: 12 		Post Options Post Options   Quote MichaelP Quote  Post ReplyReply Direct Link To This Post Topic: Inefficient NextID Allocation
    Posted: 26-Aug-2007 at 10:11pm
The NumericIdGenerator class (version from Cabana) just uses the SQL statement:
   Select NextId from NextId where Name='GLOBAL'
 
This seems like Ids are allocated globally rather than being specific for each table. This means every Id is unique and won't be used as an Id in any other table. Does this seem inefficient to anyone? This limits the total number of records of all tables to the number limit of that field.
 
I planned to use SQL Server SmallInt for some Id fields to save space, but this would then severely limit my database total number of database records in all tables to only a maximum of 32767 rows, i.e. if one table had 32700 records in it, then all the other tables could only have a combined total of 67 rows.
 
Does anyone have a smarter version of this class that allows Ids to be allocated by tablename to get around this?
Back to Top
owais,zahid View Drop Down
Newbie
Newbie
Avatar

Joined: 22-Aug-2007
Location: Pakistan
Posts: 8 		Post Options Post Options   Quote owais,zahid Quote  Post ReplyReply Direct Link To This Post Posted: 27-Aug-2007 at 1:49am

You have pointed out a very good issue, this will definitly create problems. Well, the solution is simple.

1) Add the Table Name entry in NextId table and fill the NextId column with 1 (or any other value you want).
 
2) Now, modify the NumericIdGenerator class "AllocateMoreIds" function by changing the sqlselect string to add the name of your table.
 
I think this will work. I havn't tested it.
Back to Top
vkh75 View Drop Down
Newbie
Newbie


Joined: 15-Jun-2007
Location: Ukraine
Posts: 14 		Post Options Post Options   Quote vkh75 Quote  Post ReplyReply Direct Link To This Post Posted: 27-Aug-2007 at 2:14am
MichaelP, you can use PooledNumericIdGenerator class that comes with DevForce.

Edited by vkh75 - 27-Aug-2007 at 2:17am
Back to Top
davidklitzke View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 14-Jun-2007
Posts: 715 		Post Options Post Options   Quote davidklitzke Quote  Post ReplyReply Direct Link To This Post Posted: 27-Aug-2007 at 7:51am
There is another implementation that we have made available to developers that addresses your objection.  It's called the PooledNumericIdGenerator, and it can be found in Program Files\IdeaBlade DevForce\Sample Code\CSharp(or VB).
 
The PooledNumericIdGenerator allows you to have an entry in the NextId table for each table that you want to have its own pool of unique ids.  For more details read the comments in the source code.
Back to Top
MichaelP View Drop Down
Newbie
Newbie
Avatar

Joined: 07-Aug-2007
Location: Australia
Posts: 12 		Post Options Post Options   Quote MichaelP Quote  Post ReplyReply Direct Link To This Post Posted: 27-Aug-2007 at 6:01pm
Thanks - I knew this must have been addressed already as it seemed like a major potential problem that is very easily avoided.
 
I would hate to think what kind of mess a database would be in if you didn't discover this issue until after the Id number range had been used up over multiple tables.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down