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?

asked 04 Jan '11, 21:21

RobertDD's gravatar image

RobertDD
489161719
accept rate: 42%


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. :-)

permanent link

answered 05 Jan '11, 03:08

Mark%20Culp's gravatar image

Mark Culp
22.5k9129264
accept rate: 40%

I highly second that proposal - even if you're inserting billion rows per second (in case that is technically possible with current hardware:)

(05 Jan '11, 08:28) Volker Barth

I always learn something new here when posting a question. BigInt seems far more appropriate for what I want to do.

I had seen the original GUID as PK discussion (I actually commented on it) but I needed some specific advise in this specific case. Thank you!

(05 Jan '11, 14:22) RobertDD
3

UNSIGNED BIGINT... in case you want four million years :)

(05 Jan '11, 15:16) Breck Carter
Replies hidden

@Breck: Though an UNSIGNED BIGINT DEFAULT AUTOINCREMENT may make problems after about two million years (in case it matters for you) - cf. your blog:)


I thought I should mention this now and not wait for after the fact - just in case...

(26 Jan '12, 12:11) Volker Barth
2

The Y2G Problem! Run! Hide! :)

(26 Jan '12, 12:27) Breck Carter

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.

permanent link

answered 06 Jan '11, 22:06

carolstone's gravatar image

carolstone
3263717
accept rate: 22%

Comment Text Removed

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/)...

(08 Jan '11, 15:49) Volker Barth

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.

permanent link

answered 05 Jan '11, 15:20

RobertDD's gravatar image

RobertDD
489161719
accept rate: 42%

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.

(05 Jan '11, 15:31) Volker Barth

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

permanent link

answered 04 Jan '11, 22:48

Siger%20Matt's gravatar image

Siger Matt
3.1k486493
accept rate: 13%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×37
×6
×4
×2

question asked: 04 Jan '11, 21:21

question was seen: 1,666 times

last updated: 26 Jan '12, 12:27