Application Server on top of SQL Server

[manyuaditya]

Hi,

I'm new to programming in .Net so maybe this is a naive question.

The situation:

I am writing an application in C# .net which will be integrated into SQL server as an assembly and run in the form of managed code. Currently there is a function or 2 in my class and i can call this function using an sql command in the query analyzer. It takes as input 2 data tables, then reads them, and then returns a bunch of values in the form of a table ( IEnumerable ).

My problem is that the input table could be as large as 100 million lines and the idea is to eventually test the function on a table with 1 billion rows. Now how the function works is that it reads the first table, and creates a binary tree from it. Then it uses the second table row by row to query the binary tree. Then it returns the data for each row of the 2nd table.

the problem is that the second table at some point could be just a single row, ie it would be something a user could query one by one at whim. Now constructing the tree of 100 million rows takes a long time. So everytime the user sends a single row input query it would take a long time to return.

I was thinking if I could somehow make the data structure ( tree ) persistent in memory ( assuming the 1st table does not change ) so that 1 by 1 queries by the user do not have to build the tree again and again ( obv. 1 billon rows prolly wont fit in memroy but I can work out those details later ), but I need essentially an application server that can maintain the ( table, tree ) data for as long as possible in memory. Is this possible with managed code in SQL server.

Thanks alot in advance for even reading this.

[Shashi Sadasivan]

Hi,
Re-constructing a binary tree will definitely take time...
The best approach would be to create an index file, so even if the system is booted, you dont have to reconstruct it.

Either create index files of your own, or you may write this object to a file and then pick it up back into memory when you want(this might take much lesser time than rebuilding)

I am not aware how this fits into sql server, but as long as you are using the .net framework you should be fine)

You can also write events to add/delete items from the binary tree whenever a row is added/deleted/edited

cheers

[manyuaditya]

Hey, thanks for the reply. As of now I am considering this:

Since the tree will be created from the source data in an array format, I can store this directly in an indexed table. ( 1st row is 1st element and so on ). So next time when I have to read the binary tree into memory, I can simply read the whole table row by row into memory.

This kind of makes sense. The tree should be able to fit in memory. Assuming that it does. My questions are:

1. Is there a way to read a table directly into an array ( and visa versa ), without having to iterate row by row. ( I think row by row will be too slow ) and since its like a direct vertical to horizontal transformation. I would think this is a common scenario and there should be a solution to this other than SqlDataReader.Read().

2. If this option does not exist, then would reading the tree into an array from a flat file be faster as compared to the database?

3. From query to query is there a way not to read the tree into an array in memory again and again each time my managed code function is called. Can't i make the array persist somehow.

Thanks.

[Shashi Sadasivan]

1. Is there a way to read a table directly into an array ( and visa versa ), without having to iterate row by row. ( I think row by row will be too slow ) and since its like a direct vertical to horizontal transformation. I would think this is a common scenario and there should be a solution to this other than SqlDataReader.Read().

That is what a database does usually, Stores it in a B+ tree type / similar structure (but they have their own logic to make it more efficient) So reading a database would almost mean making your own database. (not what you want to do) moreover when u get a dataset, it is already an array , so format your SQL so that your dataset contains what you want.

2. If this option does not exist, then would reading the tree into an array from a flat file be faster as compared to the database?

A database stores data in a very structured format...a B+ tree and even better. So compare a flat file with a database and think about it yourself (why would databases exists then)

3. From query to query is there a way not to read the tree into an array in memory again and again each time my managed code function is called. Can't i make the array persist somehow.

I am not sure of this one, but i have been partially successful at this (and then i left it long ago) , you can serilalize and object to be written to a file and then read it back again, so you dont have to reindex/ re-read everything

cheers and good luck