Monday, September 06, 2010
Support » Support Forum
Register Login
HomeWhat is NDO?- Executive Overview- NDO for DevelopersHow NDO Works- Persistent Classes- Mapping- Queries- Reverse Engineering- Inheritance, Polymorphism- Distributed ApplicationsLicensingSupport- Support Forum- FAQ- Solutions- Downloads- E-Mail Support- Tutorial VideosOnline ShopContact
Welcome to the NDO support forum!
Please register/login at the site if you want to post a thread. We sometimes copy support mails of common interest to the forum. They appear with anonymous sender. Please post your messages in English language if possible. You can do your posts also in German, we will translate your post and our answer so that all users of the forum can read your posts. The forum software requires that javascript is enabled. Please do not insert licence information or licence keys in forum posts. If you know a good answer to a post of another user, feel free to reply! The forum is monitored. We reserve the right to remove posts we consider not to be useful for other forum users.
Hi,
I am evaluating NDO objects and are looking at the mapping from classes to tables. I use SQL server 2005.
After building the database and looking at the generated tables I would like to change some things. I use the NDOOidType(typeof(Guid) for the ID columns. NDO makes this the primary key, but also the clustered index. To have the clustered index on the primary key on a uniqueidentifier columns gives bad performance because of the rebuilding of the b-tree for every insert. Instead I would like to have a version column in the database of the SQL server timestamp type and make my clustered index there. The SQL timestamp is normally mapped to a byte array (byte[] ).
How do I do that?
Another question. Since NDO knows relations between classes I wonder why no constrains are added to the database?
thanks for your interest in NDO. There are several articles about Sql Server (and Oracle too) stating, that due to the caching strategies used in these modern database servers indexes may slow down the data access instead of accelerating it. The rule is not to index colums with a selectivity much lower than 1. Additional there are a lot of circumstances influencing the decision, whether to index a column or not. These circumstances can't be automatically determined. So we decided not to index any columns (except the primary key column which is always indexed) and leave the decision of whether to index a column to the user.
Indexing the time stamp columns wouldn't make any sense, because there are no search operations on time stamp columns. Note, that the "time stamp" columns actually don't contain time stamps, but Guids.
As to the relations and foreign key constraints: NDO has it's own mechanims to ensure the data integrity. NDO works with foreign key constraints but it doesn't need it. Since foreign key constraints may cause a lot of headache in some situations, we decided to not generate foreign key constraints.
So, bottom line is: Out of good reasons we don't create indexes and foreign key constraints and won't create it in the future.
Best Regards:Mirko
Thanks for you answer.
The timestamp datatype in SQL server is not a GUID. The timestamp data type is just an incrementing number and does not preserve a date or a time or a GUID. It therefore gives a lot of meaning to have the clustered index on the timestamp column since inserts does not require the index to be reorganized. If you have tables with millions of rows having the clustered index on a GUID column is really bad for inserts. So how can I avoid having NDO create the clustered index on the GUID column?
having clustered indexes on Guid Columns might result in bad performance. We have considered this to be an issue and will investigate in this matter. One attempt to a solution might be to use the NONCLUSTERED keyword in the primary key constraint. That's what you can do as a workaround at the moment, if you can't use autoincremented ints. To do that, you have to alter the scripts generated by NDO before you execute it. Another solution might be to use Comb Guids. NDO produces the Guids internally. The Guids might be produced that way, that new records have Guids with "higher" values so that new records will be appended at the last data page (no resorting) but still can be searched using a clustered index. The latter solution might cause some resorting during inserts in distributed applications or applications with a lot of parallel access, but might result in better search times. We'll do some performance measurements before we decide which attempt we'll use.
As to the time stamp discussion: NDO has an own collision detection mechanism using so called "Time Stamp" columns containing Guids. These columns don't have any relationsship to the Sql Server Timestamps. As you mentioned the Time Stamps I thought, you spoke about the NDO Time Stamps. As far as I understand the matter, creating a clustered index on (Sql Server) Timestamp columns doesn't make sense, since clustered indexes are only useful in situations where queries have to determine a number of records based on the indexed column (like a BETWEEN statement or ORDER and GROUP BY). So, before you create a timestamp column solely to use it for a clustered index I think you'd be better off not to use a clustered index in a table even though some database gurus say, that every table has to have at least one clustered index.
Some people say, that it might make sense to put a clustered index on columns like country, city etc. and also to index foreign key columns. I don't think, that this makes sense. There are articles in the web (both from Microsoft and Oracle) stating, that indexes shouldn't be used on columns which a low selectivity. In other words: if in average a few rows share the same value, no index should be used.
Best RegardsMirko
Ok, thanks for the answer.
(Just returned from vacation)
Regards,
Carsten