[kemparaju]

Hi i want to maintain wait list in database table.
Just like linked list.
I need to maintain the wait list position,whenever i want add new entry to wait list with postion,then i need to arrange rearrange the existing position. how can you do that in better performance way.

Ex: wait list have below entries.

1
2
3
4
5

i need to add the new entry to the position 3 and need to move the existing 3 and4 to 4 and 5.

[Trogdor]

Guessing this would best be done in a stored procedure.
1: lock the table / start transaction
2: renumber the entries that are equal or above to the item you want to insert
3: insert the new item
4: release the lock / commit.


Dont forget to think about how to remove items. Perhaps you will need to do that in a similar way, unless the continuity of the numbers are not important (but i guess they are)

[baavgai]

kemparaju, on 22 Aug, 2008 - 06:47 AM, said:

whenever i want add new entry to wait list with postion

My first question would be why?

Understood that you want an ordered list, but is 1,2,3,4..N somehow meaningful? The easiest way to insert between 3 and 4 is to have a float field and add 3.5. Between 3 and 3.5, 3.25, and so on. Is this sufficient?

If you must see 1 through whatever as integers, you could probably fake it with a view, depending on your database.

[Trogdor]

baavgai, this is a mistake i saw before here at DIC. If you work with floats you will eventualy run out of precisionspace. You realy need to renumber, at least every once in a while.

[baavgai]

Trogdor, on 24 Aug, 2008 - 08:28 AM, said:

baavgai, this is a mistake i saw before here at DIC. If you work with floats you will eventualy run out of precisionspace. You realy need to renumber, at least every once in a while.

If you work with ints, you'll eventually run out of numbers... renumbering is just not a natural function for the data layer to perform. If it is a requirment, you need to reconsider your schema.

Another approach would be to have a id number and a prior id number in a table. This will avoid a precision crash, but is real ugly to display with sql and requires touching two records.

[mikeblas]

You don't need a stored procedure, and you don't need to manually handle locking; just let the database do it.

BEGIN TRANSACTION
UPDATE YourTable SET Rank = Rank + 1 WHERE RANK > 3
INSERT INTO YourTable (Rank) VALUES (3)
COMMIT TRANSACTION

And that's all there is to it. This isn't great for performance, but if it's what you need to do, it's what you need to do.

[Trogdor]

Exactly what i had in mind, just wanted him to think of it himself.
Only in a SP it would come natural to pass the rank value as a parameter, i would see it as a more elegant sollution.

If worried about the performance hit (depends mostly on the size of the list) you can do a mix-form of baavgai's float idea with a renumbering if the value before and after are too close. But then arrises the question: how are you going to determine the rank of something you insert?
Is it only depending on the ranks of other values, or do you have another way to calculate a new rank?

This post has been edited by Trogdor: 25 August 2008 - 06:56 AM

[mikeblas]

A stored procedure is no more elegant; in fact, it has disadvantages that shouldn't be ignored.

[Trogdor]

Like what...

[mikeblas]

I don't know why it's more elegant -- that was your assertion.

The disadvantages are well-documented; perhaps the most important is the execution plan going stale over time.

Perhaps the viStored procedures go stale and need to be occasionally recompiled. This is a problem for databases that are growing or changing. Recompiles of the procedure cause metadata locks on the objects, so simple solutions don't really get around this issue.

There are also issues surrounding option contexts; the stored procedure takes SQL Server's configuration as something of a snapshot at the time the procedure is created. If that configuration changes--either globally or in the context of the calling batch--then the procedure might behave unexepctedly.

It's hard for me to see anything substantially more elegant about stored procedures in general, and impossible for me to do so in this case. The batch in question has no local or temporary variables and doesn't invovle any conditional or procedural code.

As is frustratingly typical for these forums, we haven't heard back from kemparaju after his original post. His problem is vaguely described, but as far as I can tell, using a database for the task he describes is the wrong idea.

This post has been edited by mikeblas: 31 August 2008 - 07:32 AM

[baavgai]

mikeblas, on 31 Aug, 2008 - 10:29 AM, said:

The disadvantages are well-documented; perhaps the most important is the execution plan going stale over time.

All the issues you offer for stored procedures are issues with just passing SQL. The execution plan of a stored procedure is cached until it must be recompiled while statements are cached for an indeterminate amount of time. Portraying this behavior as a negative of stored procedures seems disingenuous.

The stored procedure allows logic to be maintained in the database. Allows the database to enforce that logic at the source. They almost always result in less traffic, as a parameterized call will be smaller than the equivalent block of code being passed.

The advantages of stored procedures are well-documented... They are accepted as preferable for most applicable senarios.

[mikeblas]

baavgai, on 31 Aug, 2008 - 09:09 AM, said:

All the issues you offer for stored procedures are issues with just passing SQL.

Nope. A passed statement works in the context of the current session, not in the context that was established for the proc when it was created, for example.

baavgai, on 31 Aug, 2008 - 09:09 AM, said:

The stored procedure allows logic to be maintained in the database. Allows the database to enforce that logic at the source. They almost always result in less traffic, as a parameterized call will be smaller than the equivalent block of code being passed.

In this case, the command to execute the stored proc would be about 20 characters (depending on the name chosen for the proc), and about 80 characters. Both of these are exclusive of the parameters themselves. I guess this is a savings, but is it relevant by any stretch of the imagination? And how is it any more elegant?

Is the database really the right spot to implement application logic?

baavgai, on 31 Aug, 2008 - 09:09 AM, said:

The advantages of stored procedures are well-documented... They are accepted as preferable for most applicable senarios.

Unfortunately, the disadvantages aren't as widely-understood. Acceptance doesn't mean superiority; it just means acceptance. In many situations, the advantages of stored procedures overcome the disadvantages. In some, they don't. When you run into those cases, I'm sure you'll learn to see a study of the disadvantages as more than disingenuous.

This post has been edited by mikeblas: 05 September 2008 - 07:52 PM

[Trogdor]

mikeblas, on 6 Sep, 2008 - 04:49 AM, said:

And how is it any more elegant?

Is the database really the right spot to implement application logic?

I would say yes.
You can shield the whole renumbering process off from the application, just offer it an insert / delete mechanism.
I would say that both makes a lot of sense, is probably less error-prone, and is way more elegant then doing it in the application code or the application-generated sql.

[kemparaju]

Sorry i was sick for couple of days, i could not check the mail.

Thanks for your inputs, unfortunately we are not suppose to use the stored procedure in our frame work.

Anyway I have tried double linked list implementation. It works, but still I am trying for best possible solution.

[Trogdor]

The best possible solution is not doing it in the database.