0

I was discussing about database layout/design with a friend and I got some questions about performance and best practices. I'm mainly a software programmer, so I may lack "common sense" regarding databases, so if you can please explain as you would for a newbie.

I have a table that records the transactions of users and their credit card usages. It has a few millions of records per day. People in my country have a 11 digit number that is supposedly unique.

I've always designed tables with an auto-increment int column as identity. In this case, the identity column is a composite of the user's social number + the date, which is a huge number column, and the fact that it's non-incremental seems like it's worse when doing a query with WHERE. What's the best option in this case?

Another question is about primary keys, stores do need to look up the table and they usually send the social number or, most commonly, the user's name. Would making the user name column as primary key enhance performance? And why is that?

Improve this question
5
  • Where did you ever get the idea to combine a credit card number and a date into a bigger number? For what possible purpose? To avoid having two columns? P.S. What happens if a customer places two orders on the same day?
    – Aaron Bertrand
    Commented Jan 4, 2015 at 20:51
  • @AaronBertrand Oh no, the columns are there, I meant that just the unique key identifier of each row is the user's number + date (with milliseconds), since the same user can't place multiple orders physically all at once.
    – Danicco
    Commented Jan 4, 2015 at 23:00
  • I don't think you mean you made your own "identity", but properly your own primary key out of social number + date? If I were to guess without execution plans/statistics and sample data - then the reason you experience problems with performance is likely two fold; because the size of the data is larger and possible requires some data manipulation and also because of the non-sequential nature, you spend a lot of extra time adding new rows to your data pages and maybe end up with more fragmented indexes/bad statistics.
    – Allan S. Hansen
    Commented Jan 5, 2015 at 7:30
  • @AllanS.Hansen By identity I mean an auto-incrementing, unique column. My main doubt is if using a unique identifier for a table using the social number + date (both columns as primary keys) is better or worse than using a single int auto-incrementing column. I'd guess yes from what I think it's more logic to do (auto-incrementing seems easier and lighter to manipulate and find), but I can't be sure since I don't really know how SQL works in the "inside".
    – Danicco
    Commented Jan 5, 2015 at 22:47
  • I would never make "my own" identity unless I needed a sequence that was unique across multiple databases. And even in that - I'd properly still stick to a normal number and not combine a date and social security number (essentially two different datatypes). if you suspect you need a lot of rows, then just make it a "bigint" datatype and seed it to the minimum of the datatype. (-9223372036854775808). I'd properly make that primary key, but I wouldn't make it the clustered index. That index should (normal) be your most important way of extracting data, so it requires some insight into usage.
    – Allan S. Hansen
    Commented Jan 6, 2015 at 6:50

1 Answer 1

Reset to default
1

On tables with a large number of rows being inserted having a ever-increasing clustered index generally improves write performance as it ensures that new records are added together "at the end" of the table. (Note that clustered indexes and primary keys are not the same thing, you can actually have a different primary key from your clustered index. Its the clustered index which matters here, not the primary key).

If you have another auto-incrementing column in your table then you could use this as your clustered index and keep your primary key the same, however your clustered index needs to be unique (so you can't use a DATETIME), which generally means an auto-incrementing column, which may as well be your primary key.

That said, you should run some performance / stress tests and try it for yourself. In the past I worked on a system which intentionally clustered on a GUID on a frequently updated table, the theory being that spreading out the writes actually reduced lock contention and improved performance (I wasn't part of that change so to be honest I was always kind of skeptical). Try it out and see which is faster with your data.

To answer your second question, looking up a user by its username should be very fast with proper indexing - its possible that clustering the the username could improve performance, however its going to be very marginal.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.