Print Page | Close Window

Inefficient NextID Allocation

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=384
Printed Date: 13-Apr-2025 at 5:41am


Topic: Inefficient NextID Allocation
Posted By: MichaelP
Subject: Inefficient NextID Allocation
Date 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?


Replies:
Posted By: owais,zahid
Date 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.

Posted By: vkh75
Date Posted: 27-Aug-2007 at 2:14am
MichaelP, you can use PooledNumericIdGenerator class that comes with DevForce.

Posted By: davidklitzke
Date 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.

Posted By: MichaelP
Date 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.


Print Page | Close Window