Recommendation for storing metadata

[Kieran Benton]

Hi,
I have quite a lot of metadata in a WinForms app that I'm currently storing
within a hashtable, which is fine as long as I know the unique ID of the
track (Im storing info on media files). Up until now I've been sending the
track info to a seperate server app using serialization/sockets which stores
the metadata in a mysql db so that I can query on Artist/Album etc as well.
However, I'm looking to remove the server and move into a more P2P situation
with individual clients sending broadcast requests to each other and doing
their own queries.

Obviously I dont want to setup a heavyweight db on each client machine so
I'm looking at my options...
1. Keep in hashtable and just iterate through matching on artist/album
(slow)
2. Store in a dataset (i am unsure as to its performance? could someone shed
some light on this?)
3. Store as a oledb db (not too keen on this, as it means I've got to store
a blank db as a resource/cant change schema easily etc.)

What do you think would be my best option out of these? (or any others I've
missed?) I very much would like to hear people's views on this!

Thanks,
Kieran

 

[Daniel O'Connell]

Hi,
I have quite a lot of metadata in a WinForms app that I'm currently storing
within a hashtable, which is fine as long as I know the unique ID of the
track (Im storing info on media files). Up until now I've been sending the
track info to a seperate server app using serialization/sockets which stores
the metadata in a mysql db so that I can query on Artist/Album etc as well.
However, I'm looking to remove the server and move into a more P2P situation
with individual clients sending broadcast requests to each other and doing
their own queries.

Obviously I dont want to setup a heavyweight db on each client machine so
I'm looking at my options...
1. Keep in hashtable and just iterate through matching on artist/album
(slow)
2. Store in a dataset (i am unsure as to its performance? could someone shed
some light on this?)
3. Store as a oledb db (not too keen on this, as it means I've got to store
a blank db as a resource/cant change schema easily etc.)

What do you think would be my best option out of these? (or any others I've
missed?) I very much would like to hear people's views on this!

Ideally, I would probably use a DataTable and the .Select() call and
DataView's(of course, all wrapped up in a DataSet as needed). It would be
far easier implementation wise to use a DataTable than to write your own
wildcard matching search routine(not that its hard, but why spend the time
doing it?). I use DataTable in several places in an app I'm working on and
it performs fine in the DataTables and DataBinding (now, object construction
is slower than i'd like, but thats a symptom of a screwed data layer, not
the storage objects).

But, as it is with all things, you should write up a test and see which
approach fits your needs best, performance should be ok with any of them, if
written well (iterating through a hash table shouldn't be that bad either).

 

[Kieran Benton]

Thankyou for your comments Daniel, what you recommended is what I'm leaning
towards already Just out of interest how many records are you handling?
I'm looking at about 10,000 currently but may be going up to 100,000 or even
more (if I decide to perform some record cacheing to improve network
performance). Do datatables still work effectively up to these sizes (Im
concerned about the amount of memory the app may consume)? Lol I probably
will do some benchmarking when I get round to it but I'm lazy and also at
work right now (this is my pet project).

Again many thanks for your comments,
Kieran

 

[Daniel O'Connell]

Thankyou for your comments Daniel, what you recommended is what I'm leaning
towards already Just out of interest how many records are you handling?
I'm looking at about 10,000 currently but may be going up to 100,000 or even
more (if I decide to perform some record cacheing to improve network
performance). Do datatables still work effectively up to these sizes (Im
concerned about the amount of memory the app may consume)? Lol I probably
will do some benchmarking when I get round to it but I'm lazy and also at
work right now (this is my pet project).

Thats a good question. Depending on the table, they run between 1 record and
about 2000 in usage.
I have tested under a higher load, although I know the app willl never reach
beyond...oh...maybe 3000-4000 entries in a given table(which wouldn't EVER
be databound in full, too much info for the user), most views of the main
tables in this app are under 20 records. However, I can't recall how many
records I tested with or precisely what my results were, I do recall it was
satisfactory under that runtime, but it was a beta so I don't know how it
stands now...I should rerun that test.
I'll post back when I get done, I changed database schemas since so now I'll
have to write a new test even if I can find the old one, -_-.

 

[Kieran Benton]

Lol yeah, makes me laugh when some people in my office (no names mentioned)
fill a listview with half a million records and expect it to be useful to a
user!

Thanks for taking the time to run the test again. I've got a feeling that
the DataTable is going to be too big to keep in memory and that I'm going to
have to stump for an OleDb which adds overheads of its own as well as
more install issues. i just wish there was an easy and relatively
lightweight solution, maybe something like persisting the datatable out to
disk and searching it incrementally, only loading a few megs at a time
(sounds like a whole lot of work to me!)

Thanks again
Kieran

 

[Daniel O'Connell]

Hi,
I have quite a lot of metadata in a WinForms app that I'm currently storing
within a hashtable, which is fine as long as I know the unique ID of the
track (Im storing info on media files). Up until now I've been sending the
track info to a seperate server app using serialization/sockets which stores
the metadata in a mysql db so that I can query on Artist/Album etc as well.
However, I'm looking to remove the server and move into a more P2P situation
with individual clients sending broadcast requests to each other and doing
their own queries.

Obviously I dont want to setup a heavyweight db on each client machine so
I'm looking at my options...
1. Keep in hashtable and just iterate through matching on artist/album
(slow)
2. Store in a dataset (i am unsure as to its performance? could someone shed
some light on this?)
3. Store as a oledb db (not too keen on this, as it means I've got to store
a blank db as a resource/cant change schema easily etc.)

What do you think would be my best option out of these? (or any others I've
missed?) I very much would like to hear people's views on this!

Ideally, I would probably use a DataTable and the .Select() call and
DataView's(of course, all wrapped up in a DataSet as needed). It would be
far easier implementation wise to use a DataTable than to write your own
wildcard matching search routine(not that its hard, but why spend the time
doing it?). I use DataTable in several places in an app I'm working on and
it performs fine in the DataTables and DataBinding (now, object construction
is slower than i'd like, but thats a symptom of a screwed data layer, not
the storage objects).

But, as it is with all things, you should write up a test and see which
approach fits your needs best, performance should be ok with any of them, if
written well (iterating through a hash table shouldn't be that bad either).

 

[Daniel O'Connell]

Ok, I ran a couple basic tests, I should note that I didn't give the runtime
time to warm up and I didn't run multiple runs because, honestly, in this
case first run probably is all that matters, as this is a desktop app.
it took 46 milliseconds to select about 5700 rows from a table containing
10000. The select operated over a range of key values (Col001 > 4188 AND
Col001 < 9943).
An additional test, using a sort on another column(Col001 > 4188 AND Col001
< 9943, Col002 DESC), resulted in 202 milliseconds.
Loading the 10000 rows into the datatable from an sql server took between
3.5 and 3.7 seconds.
I'll run some other usability tests, hopefully more useful ones, when i have
time. Thats all I really had time to whip up today.