on 2011 Jan 04 9:21 PM
I have a table in the database that has no primary key because there is so much data being inserted and deleted that this table would run out of auto-incrementing integers. This is part of a legacy design.
This has started to be a problem. When we went over to SQL Anywhere 11 we also decided to implement a high availability cluster. Unfortunately, when we do a big delete, lets say 30,000 records, even though that runs in seconds on the primary database, in the transaction log every deleted record generates a transaction, which means that the secundary database is doing 30,000 table scans, which takes a while. As a result we have seen database "hang-ups" of 40 minutes or more during which the database does not accept any request from any clients, which is obviously unacceptable during business hours.
The obvious solution is to implement a primary key, and my thought was to just add a column of type UniqueIdentifier with a NewID() default. Is there any reason that that is a bad idea?
Request clarification before answering.
First I do not understand your premise that you will run out of integers if you were to add a DEFAULT AUTOINCREMENT integer primary key column on your table. If you make the column a BIGINT then even if you were to add 10000 rows per second you will not run out of integers for more than 2 million years!
As you are likely aware, the reason that you are seeing table scans being done on your mirror is that you do not have a primary key on your table and therefore for every row the server must find the row that matches exactly every column of the row that was deleted. The other negative side effect of not having a primary key is that you transaction log file is going to be much much larger and grow faster.
The question referenced by Siger answers your question about whether to use a GUID, but I would recommend that you simply add a BIGINT DEFAULT AUTOINCREMENT PRIMARY KEY column to your table and your issue will go away and you won't need to worry about it for a few million years. 🙂
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
UNSIGNED BIGINT... in case you want four million years 🙂
The Y2G Problem! Run! Hide! 🙂
If I'm reading this correctly, you're assuming your primary key must be a single numeric column. There's no question that's the best solution. However, I've been forced to use primary keys based on a combination of columns guaranteed to be unique and not null. We've got tables with waaaaay too many columns in the primary key and the only thing that guarantees it to be unique is a timestamp - but it still improves performance, because the rest of the primary key still cuts it down a lot, so we live with it. So I'd encourage you not to overlook a halfway-decent primary key, if it gets the job done.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Better an unhandy PK than none - that's an important point and remindes me of Glenn's favourite saying: "There are no right answers, only tradeoffs." (cf. http://iablog.sybase.com/paulley/2008/05/i-dont-have-any-silver-bullets/)...
I really like the BigInt solution.
The one thing that is a problem with both the GUID and the BigInt solutions is that those types are unknown in the ODBC98 legacy software we have. Back then, in SQL anywhere 5.5, they just did not exist. The software does not handle this gracefully: "select *" statements generate access violations. While we have the source code available, it is not easy to understand and I'd like to stay away from adapting it. I also did not see another legacy datatype that was both big enough and would give me an easy autoincrementing primary key.
For the table we are dealing with here this turned out not to be too much of an issue as it gets referenced with explicit fieldnames everywhere I checked. If it was a problem, rather than try and rewrite all legacy code (and likely messing up somewhere) we would just rename the table, and then create a view with the original tablename that just selected the original fields and not the new one. We might still have to use that strategy on some other tables that also do not have primary keys.
I thought it was worth mentioning this problem and solution.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Interesting point! If you really have to select that BIGINT field from withion the application, but won't change the value, a conversion to DECIMAL/NUMERIC (or DOUBLE, if the API doesn't handle the former types) might be feasible, too. Or possibly declaring the PK as DECIMAL... (Note, we all won't recommend floating point PKs, cf. Breck's blog: http://sqlanywhere.blogspot.com/2011/01/be-very-afraid-of-floating-point.html.
You probably have seen this already, as you posted one of the answers, but this was the discussion of GUID as primary key that I remembered first:
http://sqlanywhere-forum.sap.com/questions/284/guid-as-primary-key
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
76 | |
30 | |
10 | |
8 | |
8 | |
7 | |
7 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.