MS SQL Server - SQL hash tables

Author

SQL hash tables

Roger

2006-03-08, 8:23 pm

I am currently using VB6 & MySQL for an application, but I have been trying
out the Express editions of VB.NET 2005 and the Express SQL server.

In my applications I make extensive use of MySQL hash tables, fast memory
based tables. The schema of these tables is permanent but the data is only
posted in RAM.

I use these because I have a lot of rapidly changing data that needs to be
visible to all clients but does not need to be stored. This data is related
to (and defined by) data in normal permanent tables. MySQL hash tables offer
me two advatages:

a) Only 1 connection for both types of data (not a critical requirement but
it is easier to deploy).

b) Critical, I can get datasets based on complex SQL queries which combine
both the rapidly changing tempory data and the permanent data, and I can do
inserts and updates using queries which traverse both data types.

The b) issue is a big one. Before I used this technique I had reams of VB
code to do things I can now acomplish with queries.

So I have been looking into doing this in the .NET framework. The SQL server
(at least the express edition) does not appear to have any equivalent type of
table (perhaps something exists at a higher level, I have looked but not
found anything in the full documentation).

I discussed the problem briefly with a developer at a customer site who
pointed out that .NET recordsets may be created and populated, that is local
memory data may then be SQL queried. This looks like an interesting option,
but how whould I serve up that data to client connections so that the tempory
data looks like any other table alongside the permanent data?

In a nutshell, how can I achieve something like:

Select TempData.DataVal, PermanentData.Description
from TempData,PermanentDa
ta
where PermanentData.Index=TempData.Index

I know I can attach one database to another, is it possible that one
database is created on a ramdisk? (do ramdisks still exist?).

Perhaps another workaround would be a database with a very lazy writeback
cache so that data only gets written once every few seconds even if it has
cheanged n times in the interim (and selects/triggers most always see the
most recent data).

I realise that this is not a 2 second question, and I am not expecting a
step by step solution, but I would appreciate being pointed in the best and
most appropriate direction, or indeed any direction that could actually solve
my problem.

Thanks.

Dan Guzman

2006-03-09, 3:23 am

SQL Server transactions must be written to disk in order to guarantee the
durability required for ANSI ACID compliance. However, only commits to the
log file need be synchronous. SQL Server writes to database files are
asynchronous. With write caching, you can achieve hundreds or even
thousands of requests per second.

Have you tried traditional SQL Server tables to see if performance meets
your needs? I would expect performance to meet most application
requirements with proper design and hardware.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Roger" <Roger@discussions.microsoft.com> wrote in message
news:7DB5EA30-D528-4B28-948A- F4E6F2F2CB79@microso
ft.com...
>I am currently using VB6 & MySQL for an application, but I have been trying
> out the Express editions of VB.NET 2005 and the Express SQL server.
>
> In my applications I make extensive use of MySQL hash tables, fast memory
> based tables. The schema of these tables is permanent but the data is only
> posted in RAM.
>
> I use these because I have a lot of rapidly changing data that needs to be
> visible to all clients but does not need to be stored. This data is
> related
> to (and defined by) data in normal permanent tables. MySQL hash tables
> offer
> me two advatages:
>
> a) Only 1 connection for both types of data (not a critical requirement
> but
> it is easier to deploy).
>
> b) Critical, I can get datasets based on complex SQL queries which combine
> both the rapidly changing tempory data and the permanent data, and I can
> do
> inserts and updates using queries which traverse both data types.
>
> The b) issue is a big one. Before I used this technique I had reams of VB
> code to do things I can now acomplish with queries.
>
> So I have been looking into doing this in the .NET framework. The SQL
> server
> (at least the express edition) does not appear to have any equivalent type
> of
> table (perhaps something exists at a higher level, I have looked but not
> found anything in the full documentation).
>
> I discussed the problem briefly with a developer at a customer site who
> pointed out that .NET recordsets may be created and populated, that is
> local
> memory data may then be SQL queried. This looks like an interesting
> option,
> but how whould I serve up that data to client connections so that the
> tempory
> data looks like any other table alongside the permanent data?
>
> In a nutshell, how can I achieve something like:
>
> Select TempData.DataVal, PermanentData.Description
> from TempData,PermanentDa
ta
> where PermanentData.Index=TempData.Index
>
> I know I can attach one database to another, is it possible that one
> database is created on a ramdisk? (do ramdisks still exist?).
>
> Perhaps another workaround would be a database with a very lazy writeback
> cache so that data only gets written once every few seconds even if it has
> cheanged n times in the interim (and selects/triggers most always see the
> most recent data).
>
> I realise that this is not a 2 second question, and I am not expecting a
> step by step solution, but I would appreciate being pointed in the best
> and
> most appropriate direction, or indeed any direction that could actually
> solve
> my problem.
>
> Thanks.
>

David Browne

2006-03-09, 3:23 am

"Roger" <Roger@discussions.microsoft.com> wrote in message
news:7DB5EA30-D528-4B28-948A- F4E6F2F2CB79@microso
ft.com...
>I am currently using VB6 & MySQL for an application, but I have been trying
> out the Express editions of VB.NET 2005 and the Express SQL server.
>
> In my applications I make extensive use of MySQL hash tables, fast memory
> based tables. The schema of these tables is permanent but the data is only
> posted in RAM.
>
> I use these because I have a lot of rapidly changing data that needs to be
> visible to all clients but does not need to be stored. This data is
> related
> to (and defined by) data in normal permanent tables. MySQL hash tables
> offer
> me two advatages:
>
> a) Only 1 connection for both types of data (not a critical requirement
> but
> it is easier to deploy).
>
> b) Critical, I can get datasets based on complex SQL queries which combine
> both the rapidly changing tempory data and the permanent data, and I can
> do
> inserts and updates using queries which traverse both data types.
>
> The b) issue is a big one. Before I used this technique I had reams of VB
> code to do things I can now acomplish with queries.
>
> So I have been looking into doing this in the .NET framework. The SQL
> server
> (at least the express edition) does not appear to have any equivalent type
> of
> table (perhaps something exists at a higher level, I have looked but not
> found anything in the full documentation).
>

No there isn't. You would use a regular table for this in SQL Server.

> I discussed the problem briefly with a developer at a customer site who
> pointed out that .NET recordsets may be created and populated, that is
> local
> memory data may then be SQL queried. This looks like an interesting
> option,
> but how whould I serve up that data to client connections so that the
> tempory
> data looks like any other table alongside the permanent data?
>

You don't. This approach works well where multiple clients share an
application (eg ASP.NET), but for multiple different client applications you
want a server-side table.

> In a nutshell, how can I achieve something like:
>
> Select TempData.DataVal, PermanentData.Description
> from TempData,PermanentDa
ta
> where PermanentData.Index=TempData.Index
>
> I know I can attach one database to another, is it possible that one
> database is created on a ramdisk? (do ramdisks still exist?).
>
> Perhaps another workaround would be a database with a very lazy writeback
> cache so that data only gets written once every few seconds even if it has
> cheanged n times in the interim (and selects/triggers most always see the
> most recent data).

Welcome to SQL Server, you already have that. Table data is only ever read
from and written to memory. When you make a change to a table the log
record of your change is written to disk, but the change to the table is
only made in memory. Later, background processes (Lazywriter, checkpoint)
will write the change into the database files.

Grab

Inside Microsoft SQL Server 2000
http://www.microsoft.com/MSPress/books/4297.asp

For a complete explanation of SQL Server's memory and IO architecture.

Other than a regular table your database the only thing I might try is a
regular table created in TempDB. TempDB is a special database that is
recreated every time the server starts up, and changes to objects in TempDB
generate somewhat less log data than changes to objects in user databases.
The trick there is that you have to recreate the objects every time the
server starts up (eg with an Autostart stored procedure ( see Automatically
Executing Stored Procedures
http://msdn.microsoft.com/library/d...create_4hk5.asp)).
This is what the ASP.NET shared session stuff does. Look at the file

C:\WINDOWS\Microsoft
.NET\Framework\v2.0. 50727\InstallSqlStat
e.sql

For a complete example of how to create and use regular tables in TempDB.

David

Sponsored Links