February 24, 2011 at 4:42 am
I agree, why Dynamic SQL AND Output?
What about SCOPE_IDENTITY function?
Cheers
February 24, 2011 at 5:04 am
irozenberg (2/24/2011)
I agree, why Dynamic SQL AND Output?What about SCOPE_IDENTITY function?
Cheers
In the application, there wasn't just one key. There were a few, and there were plans to put in many more in the future. To cope with this, I wrote the dynamic sql so that the stored procedure could insert and fetch the values for any given key.
The naming convention I used for the tables was tbl_vert_<key>, where <key> would be replaced by the name of the KVP key.
Agreed the output clause could have been avoided with a call to scope_identity().
Ajit Ananthram
Blog - http://ajitananthram.wordpress.com
February 24, 2011 at 5:07 am
But now you will have as many tables as you have keys in original table and, for each increment on some key, you will add a new register in the key-table... if you have thousands of increments by day, you will have thousands of new registers and only the last one is neccessary.
May be the solution could be:
CREATE PROCEDURE [dbo].[USP_Get_Value_For_Key]
(
@key NVARCHAR(50),
@value INT OUTPUT
)
AS
BEGIN
begin transaction
SELECT @value = 0
UPDATE tbl_kvp SET column_value += 1 WHERE column_key = @key
SELECT @value = column_value FROM tbl_kvp WHERE column_key = @key
commit tran
END
The outer procedures don't need to implement the transaction for get the last id.
February 24, 2011 at 5:12 am
arturmariojr (2/24/2011)
But now you will have as many tables as you have keys in original table and, for each increment on some key, you will add a new register in the key-table... if you have thousands of increments by day, you will have thousands of new registers and only the last one is neccessary.May be the solution could be:
CREATE PROCEDURE [dbo].[USP_Get_Value_For_Key]
(
@key NVARCHAR(50),
@value INT OUTPUT
)
AS
BEGIN
begin transaction
SELECT @value = 0
UPDATE tbl_kvp SET column_value += 1 WHERE column_key = @key
SELECT @value = column_value FROM tbl_kvp WHERE column_key = @key
commit tran
END
The outer procedures don't need to implement the transaction for get the last id.
I like newbies 🙂
You can always correct their sql statments 🙂
...
UPDATE tbl_kvp SET
@value = column_value+1,
column_value = @value
WHERE column_key = @key
...
(that avoids need of "begin transaction")
February 24, 2011 at 5:24 am
Many thanks for correction -and they are always welcomed - but, if in the mean time you get the new value and goes to actually update the column, there no risk the database updates the column by other update command?
Artur
February 24, 2011 at 5:31 am
arturmariojr (2/24/2011)
But now you will have as many tables as you have keys in original table and, for each increment on some key, you will add a new register in the key-table... if you have thousands of increments by day, you will have thousands of new registers and only the last one is neccessary.May be the solution could be:
CREATE PROCEDURE [dbo].[USP_Get_Value_For_Key]
(
@key NVARCHAR(50),
@value INT OUTPUT
)
AS
BEGIN
begin transaction
SELECT @value = 0
UPDATE tbl_kvp SET column_value += 1 WHERE column_key = @key
SELECT @value = column_value FROM tbl_kvp WHERE column_key = @key
commit tran
END
The outer procedures don't need to implement the transaction for get the last id.
The outer procedures in the actual application were quite intricate and had several DMLs in them and these certainly had to run in transactions. As I've said in previous posts, application developers would go about getting KVP values inside these transactions, and so one of the goals was to make the new architecture independent of long running transactions.
Agreed this meant having one table per key. But is that such a bad thing? And true, there could potentially be thousands of entries in these new tables at the end of each day. To cater for this, I created a simple job for clearing these tables each night.
Ajit Ananthram
Blog - http://ajitananthram.wordpress.com
February 24, 2011 at 5:38 am
Thanks to everyone who's viewed my article, and especially to those who've commented on the discussion board. Its great to see ideas flowing, and I've certainly learnt from them.
Its 11.30 in Sydney, so I'll leave the forum for now. If you have any questions/suggestions, please post them here and I will get to them tomorrow.
Thank you all...
Ajit.
Ajit Ananthram
Blog - http://ajitananthram.wordpress.com
February 24, 2011 at 5:46 am
arturmariojr (2/24/2011)
Many thanks for correction -and they are always welcomed - but, if in the mean time you get the new value and goes to actually update the column there no risk the database update column by other update command?Artur
Every UPDATE, INSERT, DELETE statements work in transaction mode. When you do UPDATE statement then SQL server does "BEGIN TRANSACTION", actual update and then "COMMIT TRANSACTION".
There is no risk that other user would overwrite the column.
February 24, 2011 at 7:04 am
Your method of analyzing blocking was interesting. Could you compare it to the "blocked process report" event that is available in system traces (i.e. profiler)
February 24, 2011 at 7:52 am
stephen.lear (2/24/2011)
Does your solution really solve the issue?What if the requirement was for no gaps in the key sequence? Doesn't using identities in this way lead to gaps in the key sequence if a transaction is rolled back.
There is often a requirement to keep a continuous sequence, such as when assigning invoice numbers, in which case the original USP was correct, but your replacement may lead to gaps in sequence.
Stephen, I am afraid you are confusing two different notions. Identity field is used to uniquely identify a record, disregarding any business-related value.
The "no-gaps-number" column is a business requirement and has nothing to do with the database structure. That one is usually managed through a trigger that selects next available value. It is usually the customer number, the invoice reference id, and so on. In my opinion is a mistake to use the customer number as primary key.
Microsoft MVP 2006-2010
February 24, 2011 at 8:12 am
adam.everett (2/24/2011)
Can I ask why in the original code the lock on the kvp was required for the duration of the whole buniness transaction. Could they just not of got the required key values into some temps using some short transactions before the main long running business process transaction took place?
I agree. I have not seen any code like this since a early 90's Paradox database application. Even that system was fast enought not to need a 10 second pause in the middle of a transaction before releasing Key Value table.
Did you discover that the wait for ten seconds statement was also causing blocking and locking?
Of course all of the Key generation tables and supporting code I used where refactered in the late 90's when Indentity and Scope Identity was made commonplace in RDBMS. I think that was SQL 92....
February 24, 2011 at 8:20 am
arty 15255 (2/24/2011)
I like newbies 🙂
You can always correct their sql statments 🙂
...
UPDATE tbl_kvp SET
@value = column_value+1,
column_value = @value
WHERE column_key = @key
...
(that avoids need of "begin transaction")
Is this guaranteed to be available in future versions of SQL server or is it an undocumented feature like:
<code="sql">
declare @myvar int
select @myvar = @myvar + intfield from table where [condition(s)]
</code>
Either way, it's a pretty slick trick 🙂
February 24, 2011 at 8:23 am
Mike Dougherty-384281 (2/24/2011)
arty 15255 (2/24/2011)
I like newbies 🙂
You can always correct their sql statments 🙂
...
UPDATE tbl_kvp SET
@value = column_value+1,
column_value = @value
WHERE column_key = @key
...
(that avoids need of "begin transaction")
Is this guaranteed to be available in future versions of SQL server or is it an undocumented feature like:
<code="sql">
declare @myvar int
select @myvar = @myvar + intfield from table where [condition(s)]
</code>
Either way, it's a pretty slick trick 🙂
BOL:
...
[ WITH <common_table_expression> [...n] ]
UPDATE
[ TOP ( expression ) [ PERCENT ] ]
{ <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
SET
{ column_name = { expression | DEFAULT | NULL }
| { udt_column_name.{ { property_name = expression
| field_name = expression }
| method_name ( argument [ ,...n ] )
}
}
| column_name { .WRITE ( expression , @Offset , @Length ) }
| @variable = expression
| @variable = column = expression [ ,...n ]
} [ ,...n ]
...
February 24, 2011 at 8:24 am
gdolghin (2/24/2011)
The "no-gaps-number" column is a business requirement and has nothing to do with the database structure. That one is usually managed through a trigger that selects next available value. It is usually the customer number, the invoice reference id, and so on. In my opinion is a mistake to use the customer number as primary key.
Thank you for clarifying what this was really all about. I am sure I was not the only DBA/DB Developer totaly confused about the need for this type of table.
The length of the waitfor statement is still a total confusion and actually reminds me of an excercise out of my 1992 beginning C++ text book. The excercise was about creating a database of customer information for electronic invoicing. 😎
February 24, 2011 at 9:38 am
SanDroid (2/24/2011)
adam.everett (2/24/2011)
Can I ask why in the original code the lock on the kvp was required for the duration of the whole buniness transaction. Could they just not of got the required key values into some temps using some short transactions before the main long running business process transaction took place?I agree. I have not seen any code like this since a early 90's Paradox database application. Even that system was fast enought not to need a 10 second pause in the middle of a transaction before releasing Key Value table.
Did you discover that the wait for ten seconds statement was also causing blocking and locking?
Of course all of the Key generation tables and supporting code I used where refactered in the late 90's when Indentity and Scope Identity was made commonplace in RDBMS. I think that was SQL 92....
Re: the ten-second wait....
That looks to me not to be a slap-dash fix ("better take a nap while the other thread finishes"), but rather an exagerated simulation of extensive business logic processing during which the KVP table would be locked and therefore block other processes trying to use it. Or did I completely misunderstand the test framework?
Viewing 15 posts - 16 through 30 (of 63 total)
You must be logged in to reply to this topic. Login to reply