Last post Aug 31, 2016 09:38 AM by balu.devara
Aug 22, 2016 03:19 PM|dotnetdev02|LINK
Hello all -
Sort of an odd question here.
I'm thinking of doing an update to the batch application I support which would alter a Linq to SQL repository update to change to a regular SQL Server stored procedure update. it would be a regular update statement which created a new value in the identity
value for the key. Then there is another repository update to gather that new identity value and place it in a separate string column and do another repository update to update that column with the new identity value just generated.
My question is this. Is there a way to gather that new identity value JUST created and update the other column with that value in the same stored procedure? It's rather cumbersome. The current code updates through the repository update, gathers the new
identity column value just created, then does another repository save to update that other string column. Is there a more efficient way todo the update within the stored procedure to perform this scenario?
In the larger sense, I've been assigned the unenviable task of trying to get this batch process application to make it run considerably faster to accommodate larger batches of data. This means looking at operations that may take .3 or .4 seconds, time frames
perfectly acceptable in the past, and try to make them better. The process currently uses Linq to SQL repository updates. I'm looking to try to convert updates such as the one I just described to a stored procedure update, in an attempt to cut down on that
.3 to .4 second time frame. Any ideas on whether my effort may be in vain, as I suspect it may be, as performance improvement through stored procs may not be any better than repository updates? Any suggestions would be welcome, to avoid going too far down
the rabbit hole...
Aug 22, 2016 03:23 PM|limno|LINK
Can you mark up an example table and procedure? We can try to find a query that may work to fit your need.
Aug 25, 2016 09:46 AM|PatriceSc|LINK
My understanding is that you have to copy the identity value to another column when a row is created. I would consider:
- using an insert trigger to keep this server side
- if not needed right now, it could be done later using a single statement (for example if this copy is required for a later db side job)
- if never changed it could be a computed column
- ultimately reviewing the reason for this column to see if it is really needed
Aug 31, 2016 09:38 AM|balu.devara|LINK
can you call after insert query it returns current record identity column value.