Hashtable faster than SQL ?!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi guys,

can a hashtable be faster than a sql server??
i made my own O/R mapping with an "entity cache" (caching all mapped
objects). So, when i get some data from my sql server, i map that data to an
entity-object and i leave a copy of it in the entity cache.

the entity cache is a simple hashtable, which stores as key:
the ID of the entity ( the same id as the primary key of the sql-table ) and
the entity type.. and as value: the object itself.

so, when i access the cache, i say: Contact contact = EntityCache.Get( 5109,
typeof( Contact ) )

i just made some tests and i filled the cache with 1 mio. contacts.
the result: searching the hashtable for one value, costs mostly ~0.000.007
sec! (nanoseconds).. thats amazing!! my sql server can never reach such
values! my dev. machine is just an amd 2 ghz with 1 gb ram.

of course i can search in the cache only by id, but it is the same way as
the sql server would search for an index, isn'it? i thought about, to setup
an extra caching-server... just read a big sql-table and store everything in
the entity-cache..(updating/inserting/deleting or searching by other values
is another topic).


i cant believe it!

steven.
 
Hi Steven,

There's nothing surprising. The Hashtable is (well, let's assume so) in the
RAM, and its searching algorithm is optimized for performance. The SQL
Server keeps indexes on disk, so even if the algorithm is similar, loading
the indexes from disk takes time (as well as transmitting the result set
over the network or over a named pipe).
 
Hi Dmytro,

without taking care about the network-time, namepiping etc., ms-sql/indexes
never get such results as the hashtable do.. i saw better results on
PostgreSQL...

are you sure the indexes are stored on the harddisk? i thought they were in
the RAM..

i am curious if sql server 2005 will conquer the speed as the hashtable do...

steven




Dmytro Lapshyn said:
Hi Steven,

There's nothing surprising. The Hashtable is (well, let's assume so) in the
RAM, and its searching algorithm is optimized for performance. The SQL
Server keeps indexes on disk, so even if the algorithm is similar, loading
the indexes from disk takes time (as well as transmitting the result set
over the network or over a named pipe).

--
Sincerely,
Dmytro Lapshyn [Visual Developer - Visual C# MVP]


Steven Wolf said:
Hi guys,

can a hashtable be faster than a sql server??
i made my own O/R mapping with an "entity cache" (caching all mapped
objects). So, when i get some data from my sql server, i map that data to
an
entity-object and i leave a copy of it in the entity cache.

the entity cache is a simple hashtable, which stores as key:
the ID of the entity ( the same id as the primary key of the sql-table )
and
the entity type.. and as value: the object itself.

so, when i access the cache, i say: Contact contact = EntityCache.Get(
5109,
typeof( Contact ) )

i just made some tests and i filled the cache with 1 mio. contacts.
the result: searching the hashtable for one value, costs mostly ~0.000.007
sec! (nanoseconds).. thats amazing!! my sql server can never reach such
values! my dev. machine is just an amd 2 ghz with 1 gb ram.

of course i can search in the cache only by id, but it is the same way as
the sql server would search for an index, isn'it? i thought about, to
setup
an extra caching-server... just read a big sql-table and store everything
in
the entity-cache..(updating/inserting/deleting or searching by other
values
is another topic).


i cant believe it!

steven.
Click to expand...
Click to expand...
 
Steven Wolf wrote:
[...snip...]
without taking care about the network-time, namepiping etc., ms-sql/indexes
never get such results as the hashtable do.. i saw better results on
PostgreSQL... [...snip...]
i am curious if sql server 2005 will conquer the speed as the hashtable
Click to expand...
do...
[...snip...]

Don't expect any database to be even remotely as fast as a "simple" memory
access...
We are using Oracle 9 /10 as a database and even loading 1,000,000 rows of
data from file into memory and searching for a particular object (including
object creation) is faster than a simple query (first execution) against the
index of 1,000,000 rows in the database.
 
Steven said:
can a hashtable be faster than a sql server??
Click to expand...

Try thinking about "when X can X be faster than a SQL server?....", well
certainly if the SQL server uses one for it's implementation :)

Index'es in SQL are usually done as some kind of sorted tree's, which
are pretty fast for lookup. Hashtables (with proper hash-functions) are
*faster*.

The reason for choosing a sorted tree is that it has other nice
properties. Most importantly, it allows sorted traversal of the
elements, a thing which every "order by" and "join", pretty much depend on.
 
It's possible that indexes reside in memory (provided they are small and
already loaded from DB), but even then, they are strored in the SQL server's
process space and searching an index from a client will be much slower than
searching a in process hashtable as it always involves a client/server
transition.
If however, you only consider SQL internal implementation of the index
tables and it's power compared to .NET's Hashtable lookup, I would expect
SQL to be on par.

Willy.


Steven Wolf said:
Hi Dmytro,

without taking care about the network-time, namepiping etc.,
ms-sql/indexes
never get such results as the hashtable do.. i saw better results on
PostgreSQL...

are you sure the indexes are stored on the harddisk? i thought they were
in
the RAM..

i am curious if sql server 2005 will conquer the speed as the hashtable
do...

steven




Dmytro Lapshyn said:
Hi Steven,

There's nothing surprising. The Hashtable is (well, let's assume so) in
the
RAM, and its searching algorithm is optimized for performance. The SQL
Server keeps indexes on disk, so even if the algorithm is similar,
loading
the indexes from disk takes time (as well as transmitting the result set
over the network or over a named pipe).

--
Sincerely,
Dmytro Lapshyn [Visual Developer - Visual C# MVP]


Steven Wolf said:
Hi guys,

can a hashtable be faster than a sql server??
i made my own O/R mapping with an "entity cache" (caching all mapped
objects). So, when i get some data from my sql server, i map that data
to
an
entity-object and i leave a copy of it in the entity cache.

the entity cache is a simple hashtable, which stores as key:
the ID of the entity ( the same id as the primary key of the
sql-table )
and
the entity type.. and as value: the object itself.

so, when i access the cache, i say: Contact contact = EntityCache.Get(
5109,
typeof( Contact ) )

i just made some tests and i filled the cache with 1 mio. contacts.
the result: searching the hashtable for one value, costs mostly
~0.000.007
sec! (nanoseconds).. thats amazing!! my sql server can never reach such
values! my dev. machine is just an amd 2 ghz with 1 gb ram.

of course i can search in the cache only by id, but it is the same way
as
the sql server would search for an index, isn'it? i thought about, to
setup
an extra caching-server... just read a big sql-table and store
everything
in
the entity-cache..(updating/inserting/deleting or searching by other
values
is another topic).


i cant believe it!

steven.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top