June 23, 2011 at 2:21 pm
I have pretty basic question that unfortunately I could not find clear answer for.
There is table myTbl (let say 10 M records) that we use for our web app (Portal).
There are 2 pages (from page1 User is redirected to page2): #1) populated by SP1- list of contents;
#2) populated by SP2- creates 5 charts.
Right now both SPs call myTbl, but essentially SP2 can use output of SP1 (small part of myTbl depending on the selected input parameters).
Is there any way (I mean Sql Server side) to preserve data while moving from web page1 to page2? Or it can be done inside application only (cache, etc)?
Really appreciate any help. Thanks,
June 23, 2011 at 2:55 pm
There is no way to Cache the data on the database side in memory.
You could setup a table and using a GUID store the information from page 1 for page 2. however, you would still need the GUID between pages and it doesn't really doesn't do much. Better of letting the application layer handle it.
Fraggle
June 23, 2011 at 3:05 pm
Frankly I thought almost same way, i.e. for each User_Customer pair (Portal User) create table with unique name and insert output from SP1 into it and use this table for SP2 call. But to call this table I should use dynamic sql (and table name as parameter) and not sure what I'll win in this case.
Anyhow thanks for help, Yuri
June 23, 2011 at 3:46 pm
Why would you create a table per user?
Add a column where you store the information what Portal User (or whatever you define as a unique connection) the data belong to.
But you'd need to make sure to install a "cleanup" routine (e.g. if a user simply closes the web browser instead of a clean sign out). We used an expiration_date column in the past for a similar scenario (we're now using indexed views and filtered indexes so the benefit to store those data is not that significant anymore - in our case...).
June 23, 2011 at 3:53 pm
wait, wait, wait.
I think you're confused about what is happening. When you start (before page 1), you have:
SQL Server: dataset needed for user 1 + other data
Web server: no data
Client: no data
You render page 1, and you have:
SQL Server: dataset needed for user 1 + other data
Web server: no data
Client: data for user 1
If the user moves to page 2, you have:
SQL Server: dataset needed for user 1 + other data
Web server: no data
Client: data for user 1
As you move to page 2, the client could potentially cache something and save resources, you could use something like a caching mechanism (session variable) on the webserver that you create with page 1 and access in page 2.
But doing anything else in SqL Server is silly. Unless this client is going to call this data a lot, this specific set, you're potentially storing the data in the buffer pool twice, once from the original table and once from a second table.
Lutz' suggestion makes sense if the user "owns" this data, but if it's a set of data based on parameters (Which is what I read), why wouldn't you just select it again?
June 23, 2011 at 4:13 pm
Sorry, I probably was not clear.
Data in myTbl does not belong to particular User bur rather denormalized storage for all Customers under our service. When UserA calls SP1 (to populate page1) dataset1 is returned- specific for this User, customer he belongs to and some other input. Then UserA can go to page2 where he can check some charts. To create these charts we need to call SP2 (with the same input as for SP1). My idea was not to call original big table myTbl but rather to use dataset1 from page1. But it seems to me that (preserving dataset1 for page2) can not be (or should not be) done on Sql Server side.
Thanks for your help. Yuri,
June 23, 2011 at 5:38 pm
You are correct. You gain nothing preserving it on SQL Server. Now depending on server memory, load, and the data set, it's likely in memory on SQL Server so it's not a hugely expensive call again, but you would be better to have this cached on the web server somehow, or even the client.
June 23, 2011 at 5:45 pm
Thanks, Steve.
Agree, I would rather try to speed up (tune) my queries for charts creation (calling main table) or saving dataset1 somewhere on web cache- will check what way is better. Cheers, Yuri
June 23, 2011 at 5:46 pm
How you cache it will depend on how much data there is.
You need to decide based on how much data there is to cache, how long you want to keep it for and where your bottleneck will be.
For example, say you want to take a subset of the data - say in the tens of Megabytes, then you might want to cache it in the web server.
Say you take 100s of MB, but want to repeatedly query that for summary information, you might want to cache it on the DB server.
Say you have a session token, you may want to cache that on the client.
...It depends...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 23, 2011 at 5:50 pm
This fact (size of data to cache) was original reason I started to look for sql server alternative. Probably I need to try both ways- caching on server (with max possible data size per Customer) or extra DB calls from 2-nd page. And compare. Thanks, Yuri
June 24, 2011 at 6:11 am
If you do a little searching around in the Visual Studio documentation, there is a database and set of code specifically designed to use SQL Server for caching data between web pages. That said, don't use it.
This is a case where some of the NoSQL databases can really shine. Take a look at Redis or Riak or MongoDB. These are all better suited for this type of work than SQL Server is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 24, 2011 at 8:35 am
Thanks Grant
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply