Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I have a Web Part that connects to a SQL server and pulls summary data for the current user. (Think "dashboard".) Unfortunately this query is expensive to run so I'd like to cache the results to improve performance.

My first idea was to run the query for all users and populate a table in SQL. The SQL server would run a scheduled task to periodically refresh the data.

My other thought was to make a view from the query and populate a SharePoint External List. I can do this, but the database is queried on every display.

So, my question: is there a simple way to have SharePoint cache the results of the query for x minutes? Or, is there another best SharePoint-centric best practice for what I'm trying to achieve?

share|improve this question
    
Is this a custom web part that you control the code for? –  Dave Wise Mar 25 '13 at 16:40
    
@DaveWise yes, it's my code. –  longneck Mar 25 '13 at 17:02

3 Answers 3

up vote 3 down vote accepted

A simple way would be to make either a SharePoint list that contains all of the fields you need or an external sql table then have a timer job cache the results daily (or more?).

You could also (either in additional, or only) have the call on the page write the results to a table/list and query that table/list before making the expensive call.

The second option would allow you to set a timeout, where if the data grabbed is too old (by a data column), do the big query again and cache for however long the timeout is.

share|improve this answer
    
I was hoping for something simpler like "set this property on the External List and SharePoint will cache it for 60 minutes". –  longneck Mar 25 '13 at 18:19
    
You will need a little more logic to it. It would be simple to do the second part of my suggestion only where you make a sharepoint list or a table with a datatime column you check against for validity. –  thantos Mar 25 '13 at 18:45

have you thought about sessions state?

How to save variables per user session per page

you can also define how long the session state lasts for!

goto central admin -> application managment -> Configure Session State

make sure the "Enable Session State" is checked! and set a timeout in munites, so if you want it for every 3 hours put in 180

you can then performe a query to the DB that checks if the data is concurrent if not update or just update the data.

share|improve this answer
    
I was hoping for something simpler like "set this property on the External List and SharePoint will cache it for 60 minutes". –  longneck Mar 25 '13 at 18:20

Try to improve your sql query with indexes on the tables ;) If it's still too much implement pagination. Grab 1000 entries per page for example.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.