1

The problem is maintaining in .net a list of rows that have a specific sequence where rows can be inserted, moved and deleted, and being able to save and restore that sequence from disk.

In memory I can use a .net list with insert and delete by index position. The alternatives that I see for converting that to a disk file that retains the list sequence are a)using "next" fields holding record IDs or b) more efficiently "prev" and "next" fields (what I think of as a linked list) or, c) very inefficiently, updating a sequence number in each data record for every change in the memory list.

What might be other common and efficient ways of accomplishing this?


"DB" = database...I need to move the data in and out of a database

"Insert...by index position" = insert anywhere in the list, i.e. top, middle, end (i.e. not a simple SQL insert meaning "add to end")

The application will involve hopefully thousands of separate, small groups each simultaneously working in real-time on separate lists. The lists, when they are fully built, will average perhaps 200 rows, maybe 300 bytes per row. The lists are part of a more complex data set that includes votes and other information. So for a rough estimate, say 100KB for a full list, and 50KB on average for a list during its construction phase. The lists will be updated, per group, perhaps once every ten seconds. So, again, very roughly, 50KB of list data written to disk per group every ten seconds, with the initial app designed for 10K groups.

  • Cant you just serialize the list? – JacquesB Jun 23 '16 at 6:07
  • @JacquesB I thought about serializing the list, however, I would need to write the whole list to disk each time the list was updated, which seems too expensive. – wayfarer Jun 23 '16 at 19:10
  • OK, so you use a database and you are concerned about performance? How large is the list going to be? – JacquesB Jun 24 '16 at 6:46
  • @JacquesB Please see Update #2 above on performance considerations. I believe what you are suggesting is to do the reads and updates on an in-memory list and to serialize that list for the writes to disk and loads from disk. Hopefully the above Update #2 provides enough information to assess whether performance would be an issue in that scheme. – wayfarer Jun 24 '16 at 20:57
  • DB - is that a relational (i.e. SQL) database? I am confused about why you talk about saving to a disk file then? – JacquesB Sep 18 '17 at 8:14
1

You are concerned about the performance of bulk-updating a sequence number, but I suspect this is premature. Bulk updating sequence numbers (e.g. increment a field for all items in a section of a list) can be done in a single SQL update. I if the average set is 200 items performance will most likely not be an issue.

A bigger issue might be sorting a list with previous/next fields, since this requires some recursive mechanism, perhaps CTE's depending on the database. But then again you can just sort on the client side, and it will probably not be an issue if the list average 200 items.

I short, I don't think you should make your decision based on performance consideration.

  • Due to budget I'm stuck with the "sequence number in each record". The "List" is actually divided into little sublists, which will rarely have more than ten items. Worst case will be to add something to the first place in a sublist and have to "push all the following ones down" (i.e. update their sequence numbers in the sublist). That should not happen often compared to other transactions. All in all it's good to hear that performance shouldn't be an issue. – wayfarer Sep 18 '17 at 21:31
0

being able to save and restore that sequence from disk

Are we talking about a file system, here, or a "DB", as in your posting's Title?

In the file system, just write your list items into a file. They will be stored in the order you want them and you can read them back in the same order. Notepad does something remarkably similar.

If you did want to put them into a Database, though, you would need to store that sequencing information as well. Why?

Because Rows in a Database Table have no Order.

The only way to absolutely and consistently guarantee the order in which rows are retrieved is through the "Order By" clause in your SQL. For that, you'll need the sequencing data as well as the items you're interested in.

  • - Added clarification update above, based on your answer. yes this is for a database. I appreciate that I need to store the sequencing information. What I'm hoping to learn is additional alternative ways to code the sequencing information, other than the ones I listed (a,b,c). The only way I could use "Order By" would be to use option "c", which would require re-updating sequence numbers in many record every time a record was inserted in the middle of the list. – wayfarer Jun 23 '16 at 19:13
  • Do the sequence numbers matter? In the database, you need to hold the sequence in which the records were stored. When you read them back into your program, do so in that order. In your code, I'm guessing that you'll hold the rows in a .Net "list" construct which will, inherently, hold them in the "right" order. The sequence numbers in the database are no longer relevant; you can overwrite them with new sequence numbers when you next save the data. – Phill W. Jun 24 '16 at 11:15
  • W - if I understand you correctly, I believe your comment about "overwrite them with new sequence numbers" is the same as my alternative c) in the original post. I'm looking for alternatives to sequence numbers, beyond the a), b), c) in my original post. – wayfarer Jun 24 '16 at 21:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.