July 6, 2009 at 2:09 am
There's a chance that will cause duplicate key errors under heavy load
How can I improve my solution?
July 6, 2009 at 2:15 am
As I said...
To fix that, your select max needs a more restrictive lock. Updlock is the one usually used. That however is going to hurt concurrency.
Why are you insisting on this design? You're causing yourself a lot of extra work and potential future problems with this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2009 at 7:01 am
GilaMonster (7/6/2009)
Lynn Pettis (7/5/2009)
I concur, your db design is flawed, and fails the 3NF.It's violating BCNF if I'm not mistaken. There's no inter-data dependency (so passed 3NF) but there is a dependency between two of the columns in the key.
No, don't think so. For instance, Product specific information that is not related to Category is not dependent on the whole key, just the Product portion of the key. An example would be the Product Name or Description. Same with any information related only to Category such as Category Name or Description.
Based on a post in another thread I don't think this is even 2NF:
1NF - The Key
2NF - The Whole Key -- Shouldn't Product and Category be split at this point?
3NF - Nothing but The Key
July 6, 2009 at 7:19 am
Lynn Pettis (7/6/2009)
2NF - The Whole Key -- Shouldn't Product and Category be split at this point?
Could be, but we don't know if Name (the only non-key attribute that we've been shown) is dependent on part or all of the key. It's a potential 2NF violation. If not then it's definitely a BCNF violation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2009 at 11:06 am
There is one way to solve this without using a trigger and without hurting performance, while still keeping the data in the format you want. What you would need to do is create one table for each category, with an identity column in each table, and use a partitioned view on top of them.
Of course, that opens up the door for all kinds of structure management issues, but it does comply with the constraints given thus far.
Have to say, though, that I'd never do it.
I'd have a ProductID that was a simple identity column, and have category as one of the data columns, with an FK to a Categories table. CategoryID wouldn't be part of the PK, and ProductID wouldn't depend on CategoryID.
That would be well normalized, would scale well, be easy to work with, and would fulfil all the real-world requirements that any normal business has on this kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 6, 2009 at 10:59 pm
You could also use a 'sequence table' - that is, a separate table that holds the next sequence value for each combination.
I'm just saying you could.
Whether it makes sense or not might depend on how many products and categories per product there are.
At first look, I would probably look at doing something similar to G2's suggestion, which has the advantage of improving structure while keeping the 'interface' intact.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 7, 2009 at 2:16 am
Interesting solution, can you show me where can I find information how to implement 'sequence table'.
July 7, 2009 at 2:59 am
No, because I don't think it's a great fit for your problem.
Consider Gsquared's idea first.
Sequence tables can work well in some cases, but they require great care to do properly.
On reflection I really think you should try the view.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 7, 2009 at 6:47 am
You would simply have a table with CategoryID and NextProductID in it. As you use them, you increment the NextProductID column.
I've seen it used. It would do what you want, but it's a horrible solution. It will only work if you can guarantee non-concurrent access. That means you have to force table locks whenever you access it. Even then, it can get messy if transactions have to be rolled back at any point.
Really, you're better off with using a regular identity column, and if you need a sequential number in the front end or something, make the front end generate that. Make it so it doesn't matter in the database.
Every time I've seen someone implement something like what you're talking about, it's gone badly. It's one of those, "it sounded good at the time" type ideas. You'll almost certainly end up, later on, having to explain to your boss why you implemented it, and why it isn't working. It won't be his job on the line when that happens.
I can't stop you from using this kind of solution. I will, however, recommend strongly against it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 7, 2009 at 6:57 am
GSquared (7/7/2009)
That means you have to force table locks whenever you access it.
Exclusive table locks.
Sequence table has much the same problems as the MAX(ID)+1 solution. Either you get duplicate key violations (when two queries both read the same next value) or it becomes a performance issue at hight volumes of use.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2009 at 7:20 am
GilaMonster (7/7/2009)
GSquared (7/7/2009)
That means you have to force table locks whenever you access it.Exclusive table locks.
Sequence table has much the same problems as the MAX(ID)+1 solution. Either you get duplicate key violations (when two queries both read the same next value) or it becomes a performance issue at hight volumes of use.
The advantage of a sequence table over querying the max value is that the sequence table doesn't slow down as the main table gets more rows or as its indexes fragment (and they will fragment with this kind of scheme, probably quite badly).
Takes longer to query:
select max(ProductID) + 1
from dbo.Products
where CategoryID = @CategoryID;
Than it does to query:
select NextProductID
from dbo.ProductsSequences
where CategoryID = @CategoryID;
update dbo.ProductsSequences
set NextProductID = NextProductID + 1
where CategoryID = @CategoryID;
They'll both be fast so long as there are only a few rows in dbo.Products, but the first one will get slower and slower as you add more products, while the second one will only slow down slightly as you add more categories.
Which still doesn't make it a good idea. Just makes it a slightly less bad idea.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 7, 2009 at 8:35 am
I think I'd do it more like this:
declare @NextProductID int;
update dbo.ProductsSequence set
@NextProductID = ProductID = ProductID + 1
from
dbo.ProductSequence with (tablockx)
where
CategoryID = @CategoryID;
July 7, 2009 at 9:08 am
Yes, that's a more elegant solution. I didn't bother with the variable assignment and all that, was just trying to show the basic structure of the query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 7, 2009 at 9:26 am
I like it because you get in and get out as quickly as possible.
July 7, 2009 at 4:20 pm
GilaMonster (7/7/2009)
GSquared (7/7/2009)
That means you have to force table locks whenever you access it.Exclusive table locks.
Sequence table has much the same problems as the MAX(ID)+1 solution. Either you get duplicate key violations (when two queries both read the same next value) or it becomes a performance issue at hight volumes of use.
I could not agree less 🙂
A simple UPDATE statement with an OUTPUT clause to capture the updated value works atomically, perfectly well at high transaction volumes, and with just a single transient exclusive row lock assuming the correct index exists. Sequence tables work particularly well where a range of values is allocated to the caller.
Example code (unrelated to this thread):
UPDATEdbo.AllocationMaster -- WITH (XLOCK, ROWLOCK)
SETnext_available_value = next_available_value + @range_size
OUTPUT@range_name,
DELETED.next_available_value,
INSERTED.next_available_value - 1
INTO@Allocation (range_name, range_start, range_end)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply