Opinions on fastest data lookup methods?

  • Thread starter Thread starter Jeff Wilson
  • Start date Start date
J

Jeff Wilson

Am working on a system that processes lots of records. Each record
requires maybe a dozen lookups into different SQL Server tables, some
containing 100,000 records. Since there are a lot of records to
process, I need these lookups to be as quick as possible. Which option
do you think would be the fastest?

1. Constantly query for the data out of SQL Server?
2. Download the tables into local DataSets and search them?
3. Download the look-up data into in-memory Hashtables?
4. Download the look-up data into ArrayLists in sorted order and perform
simple binary search?

I have some leeway as to processing in a connected or disconnected
fashion.

Am currently processing using PERL with in-memory hash tables and it can
process many millions of records a day. Can I get similar performance
with .NET?

Thanks.


Jeff Wilson
TelNet Worldwide, Inc.

Jeff Wilson
TelNet Worldwide, Inc.
 
Jeff,

In my idea an almost impossible question to answer
1. Constantly query for the data out of SQL Server?
Click to expand...
this depends on your connectionspeed, the load of your server, the
performance of your server
2. Download the tables into local DataSets and search them?
Click to expand...
This depends on the memory and processor which are handling that and for
this it should be that the search is done more than twice on the same
selecteddata, otherwise in my idea the answer is forever 1.
3. Download the look-up data into in-memory Hashtables?
Click to expand...
A dataset is not that different from a Hashtable. A dataset gives your more
search methods than a Hashtable and is easier to create.
4. Download the look-up data into ArrayLists in sorted order and perform
simple binary search?
Click to expand...
In my idea can this be clear, it will be almost forever slower than the two
methods above. (I do not know what you mean with a binary search on an
Arraylist. An arraylists is an object that only holds references to other
objects)
Just my thought about this

Cor
 
In my case, the search can be done repeatedly on the same DataSet, so
that's the way to go if in fact searches can be as fast as a Hashtable
or a binary search on a sorted array.

When you say there's not much difference between a DataSet and a
Hashtable, are you saying that the DataSet is somehow indexed similarly
to a Hashtable for searches? Or does a DataSet only search efficiently
on a specified key column?

I was told previously that no DB indexing is maintained in the DataSet.

Thanks for your opinions.

Jeff Wilson
TelNet Worldwide, Inc.
 
Jeff,

I cannot say which is the fastest, however because that the Dataset is in my
idea a more important and very much overthough class I suspect it will not
inferior to the hastable.

See for your needs this find method from the datarowcollection which is a
part of the dataset or better the datatable in that.
http://msdn.microsoft.com/library/d...systemdatadatarowcollectionclassfindtopic.asp

(As far as I know have the keys to be unique for that)

Beside this are the dataview.rowfilter (a dynamic filter system for rows)
and the datatable.select (what is a static select of rows).

When you say that the hashtable has an indexed key (where I think you mean
something as Tree structure in it, than do I not read/know that)

I read only this.
Represents a collection of key-and-value pairs that are organized based on
the hash code of the key

http://msdn.microsoft.com/library/d...frlrfsystemcollectionshashtableclasstopic.asp

I hope it helps somehow?

Cor
 
Back
Top