SQL Update Question

Hi
We have a number of tables where at data entry time we write the row number to the file as the line number. If a row is subsequently deleted (or inserted) we adjust the row numbers in the file. On a row by row basis this is OK but I'm not sure what the effect is if we do a single SQL update statement such as (where line 3 is deleted)

UPDATE DET 
SET REFLINE = REFLINE -1 WHERE DET.REFERENCE = HED.REFERENCE
AND DET.REFLINE > 3

So far in my testing this has worked fine but I am concerned as to whether we can rely on SQL always processing it in refline order. We have an index on Reference/Refline, but the UPDATE statement doesn't allow an order clause. Because of the potential size of the tables and the usage we really don't want to do it on a line by line basis for performance reasons. Databases are HFCS and MSSQL (via OLED.
Does anyone know whether this is reliable or a time-bomb waiting to go off? If the latter any other suggestions would be appreciated.

Thanks

David

David,

this would run perfectly fine. With 1, 3, 100, million records. Doens't matter.
SQL will update your 3 records seperately form each other. So your statement reads as:
UPDATE DET SET REFLINE.newvalue_of_this_record = REFLINE.oldvalue_of_this_record -1

From the scope of "one" record there isn't such thing as "another" record, until you say so using an extra select-statement. Something like
UPDATE DET SET REFLINE.newvalue_of_this_record = (SELECT REFLINE.value_of_another_record WHERE some_filter_to_get_that_other_record)

Arie

Hi all...

I don't know about the SQL part, but I'm afraid of the logic of the thing...

Even if SQL manages that query perfectly, it will take some time for 1.3 million records to be updated (looks like a BIG load on the server to me)....

And what will happen if/when another user (or several) deletes another record of the same file while this query is running?

Another question cames to mind... If the purpose of this is to never have a hole in the IDs, then what will happen WHILE your query is running... If any other user do anything during this time relying on the fact that there is no hole, there will be a problem, because till the end of the query, there IS a hole, and it's moving...

So if there is not a VERY good reason for that, I would advise that instead you design your other process to work correctly even if there are holes in the sequence of ID (which has always been my normal way of working)

best regards

Fabrice Harari
WinDev, WebDev, WinDev Mobile video courses

Hi Arie
What I'm concerned about is the potential for duplicate records. If the SQL update statement doesn't follow it strictly in refline sequence I could end up, temporarily at least, having 2 reflines the same (if line 8 is renumbered ahead of line 7 for instance), or do you mean that this could never happen because of the way the engine works?

David

Hi Fabrice
You may have misunderstood my 'table' reference. I am referring to a Windev table which is constrained by the transaction reference. In this context we would rarely if ever have more than a couple of thousand lines in our table. Whilst I have no problem with your suggestion of leaving holes in the sequence number how do you handle intermediate inserts? If the client is editing a docket (Windev table) and needs to insert a new row between rows 8 & 9 for instance. We currently do the reverse of my delete line routine to provide a sequential row in the database.

David

David,
a few things about this. A single query will be exectued in a transaction. All rows are update or none. So the query you suggest cannot create duplicate ID's.

Fabrice mentioned another problem. Dirty reads. While your query is running, other users could read some of your already updated records AND some not-updated-yet-records. Moreover, when you do a rollback on the end, those others users have data which doesn't exist. But this depends on your locking strategy. Which is of your own hoice.
You can configure your database such, that others users read your new values after ALL updated records have been comitted. Before that, they wil read the old values. For every record. (this is the way Oracle works, for years now. MSSQL can do this just from V2005 I believe). But you can leave that principle. And allow dirty reads. It is up to you.

In your case, I think, you are stuck to the requirement of "no holes". The only way to do that is to lock the whole table on every update-action. And then do your updates.
Finally I would use a exclusive index or something like that, to be sure you don't get dulpicates. Leave that task to the database-engine, while it is desgined for that. And just trap errors.

Arie

Hi All...

I think I understand better what you are trying to do: just keep the lines of a table in a specific order (like the lines of an invoice, where the order information is the line number)...

To do that and not renumber each time, here is what I do:
- I just use a Currency field instead of an integer for the line number...
- I do not display the information (line number)
- I originally set the line number with values from 10 to 10
- if I delete a line, I do not renumber
- if I insert a line, the line number is the medium value between previous line and next line
- if I add a line at the end, the line number is biggest number+10

With that technique, lets say by example that I want to insert 4 lines in position 2, one after the other:
- Originally line1=10 and line2=20
- 1rst insert is done with line=15
- 2nd insert is done with line=12.5
- 3rd insert is done with line=11.25
and so on

only the lines modified or added are physically modified, no overhead, no dirty data... And very simple to implement

Best regards

Fabrice Harari
WinDev, WebDev, WinDev Mobile Video Courses

Thanks guys,
You've convinced me that my way of doing things is not really satisfactory. Thanks Arie for your very clear explanation of how it works and what potential problems I could have and thanks Fabrice for sharing your way of doing this; I like that suggestion and will follow suit.
Cheers

David

I use about the same method as Fabrice, only I use integers.
Disadvantage : I take steps of hundred and if there is no more 'room' between two rows,
I have to recalculate the whole table anyway (locking it completely during this operation)
Advantage : I thought that integers are more reliable and quicker when they are used in a key. (am I wrong here?)

What i never do is using such a key as a foreign key in another table. It would create to much overhead. (disable the constraints, adapt the changed values, reenable the constraints, in all depending tables)




Edited 1 time(s). Last edit at 03/25/2008 01:59PM by Leo Voet.

Hi Leo...

I cannot really answer in a 100% way about the integer vs currency field in a key being more or less reliable or fast... What I CAN tell you is that:

- I never had any problems with currencies in a key
- Considering that I use this kind of things to manage/display invoices and such, the overall performance of the key was never really an issue for me... In theory, I would think that there shouldn't be any visible difference... Any key is stored in the index as binary value, so the original type shouldn't make a difference... But of course, the length of the key WILL mean an index a little slower...

Best regards

Fabrice Harari
WinDev, WebDev, WinDev Mobile Video Courses