Hashtable faster than SQL ?!!

[Steven Wolf]

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.

[Dmytro Lapshyn [MVP]]

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" <ap****@gmx.net> wrote in message
news:51**********************************@microsof t.com...

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.

[Steven Wolf]

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 [MVP]" wrote:

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" <ap****@gmx.net> wrote in message
news:51**********************************@microsof t.com...

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.

[Michael Voss]

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

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.

[Helge Jensen]

Steven Wolf wrote:

can a hashtable be faster than a sql server??

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.

--
Helge Jensen
mailto:he**********@slog.dk
sip:he**********@slog.dk
-=> Sebastian cover-music: http://ungdomshus.nu <=-

[Willy Denoyette [MVP]]

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" <ap****@gmx.net> wrote in message
news:DF**********************************@microsof t.com...

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 [MVP]" wrote:

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" <ap****@gmx.net> wrote in message
news:51**********************************@microsof t.com...

> 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.

[Steven Wolf]

thank you all for the answers.

"Steven Wolf" wrote:

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.