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.