Manipulate SQL data in memory?

Question

I have tables and a lot of sotred procedure that work with sql database.
For demonstration purposes I want to load data into memory (maybe dataset that i can then store in session- the demnonstration is limited so server memory cap won't be a problem ?) from my sql tables and manipulate it with my stored procedures.

Is it possible? Or i need to rewrite all my stored procedures or even replace them with code that works with data set?

score 1 · Answer 1 · answered Apr 17 '12 at 02:33

Stored Procedures are stored in the database. There's no way to make one of them, all the way in the DB server, operate on some in-memory structure back on the web server--a completely different place!

What you're asking to do is a bit like saying you ordered a pizza, and now that it's been delivered to your house you want the pizza parlor to switch the crust for you on the fly, without the pizza leaving your house. Without some seriously advanced technology that can selectively distort the space-time continuum and perhaps even effect time travel, this will never happen.

If you must change the pizza once it is at your house, you either have to order a new one, or modify it yourself with your own tools.

but ... but I *want* a new crust :( – Fionnuala Apr 20 '12 at 14:53 — Fionnuala, Apr 20 '12 at 14:53

score 0 · Answer 2 · answered Sep 15 '12 at 11:31

As a mater of fact, you can do it. It's silly but possible.

You load all tables from SQL server into app.
Create XML document containing data.
Send XML to SQL stored procedure (you have to create new ones) and process it

Talking about pizzas, this is like ordering all possible ingredients to be sent from Domino to your house, you open all boxes, reorder them, and then send evrithing back to Domino to make you pizza you want.

Good luck :D

score 0 · Answer 3 · edited May 23 '17 at 10:32

0

If you wish for SQL Server to manage your table in memory, there is no guaranteed way to.

You can create a table variable and fill it with the dataset, and it'll probably be stored in memory.

Check this answer for more information:

Does MS-SQL support in-memory tables?

Aditionally, with some server configuration you may be able to put your tempdb into a ramdisk, which would effectively give you leeway to operate not just with table variables and hope, but you can store your dataset in a temporary table and be sure it'll be in RAM. Check this Microsoft's article:

http://support.microsoft.com/?scid=kb;en-us;917047&x=17&y=9

EDIT: I would expect that if the dataset fits in server memory (and their configured per process limit) it would be stored in server's memory. But that's just an educated guess as I'm not familiar with ASP.NET's architecture

edited May 23 '17 at 10:32

Community

1
1

answered Aug 23 '09 at 21:45

Vinko Vrsalovic

330,807
53
334
373

It doesn't have to in SQL server's memory but in ASP server memory. I just want to read the initial data from sql, fill in memory tables like dataset and manipulate it, but i want to manipulate it with existing stored procedure. If it;s not possible what can i do to to avoid rewriting all store procedures? – Aug 23 '09 at 21:50
@lupital The trouble is at that point the data set is in asp.net's memory, not sql server's, so you'd now have to keep transferring the data to and from sql server to manipulate it. Assuming you mean .net's DataSet class? – Chris Chilvers Aug 23 '09 at 23:04
@KeeperOfTheSoul, I need to trasnfer data from sql server only once the initial dataset population. all manipulations will be on the dataset. when the session is will be over the dataset will be gone as well. – Aug 24 '09 at 06:03

score 0 · Answer 4 · answered Aug 23 '09 at 22:46

0

You can in theory load your entire database in memory and even store a copy of the database for each session. Whether that is a sound thing to do, is a different discussion. All successful ASP applications I hear of are stateless.

Its absolutely impossible to have SQL Server manipulate your process memory where the ADO.Net data sets reside, through Transact-SQL procedures or any other technology. You are going to have to rewrite all your procedures as CLR methods in your ASP.Net application to operate on the ADO.Net data sets.

answered Aug 23 '09 at 22:46

Remus Rusanu

288,378
40
442
569

Can you actually do that in practice with SQL Server? (Make whole databases reside in memory) – Vinko Vrsalovic Aug 23 '09 at 23:58
If the the database fits in memory, sure, SQL Server will load each page is is hit and never evict them. – Remus Rusanu Aug 24 '09 at 01:59
"store a copy of the database for each session" will wirk for me. Haw it can be done? any online documentation? – Aug 24 '09 at 05:45

score 0 · Answer 5 · edited May 23 '17 at 12:18

0

Have you considered an in-memory DB like SQLite? Check out my answer to this SO thread. There are other alternatives too.

edited May 23 '17 at 12:18

Community

1
1

answered Aug 23 '09 at 22:54

JP Alioto

44,864
6
88
112

Manipulate SQL data in memory?

5 Answers5