SQL Server Stored Procs vs Caching with a Table Visualization

#1

I have a stored proc that takes 5 seconds to execute.

I have a table visualization that on load collapses all of the hierarchies except the first one.

When I expand one of the hierarchies the stored proc is called again, and I have to wait 5 seconds.

Is there a cache setting I am missing?

#2

You could setup a data cube using the sproc as a source. In the data cube, you have the option of using in memory or warehouse storage. We typically use warehouse, set it on a schedule. We have a query that can take over 30 sec to complete, when it is cached in one of the storage modes the data displays instantly.

#3

Here is an article for more information on the data cube storage types. It also includes a note about the caching configuration setting, which you may find relevant.

#4

These options won't help in my case. I have public parameters. From the article data cube storage types: "Note A data cube that contains public parameters or user-specific filters cannot be built as a warehouse or in-memory cube."

Any other options?

#5

Your best course of action would be to modify (or split) the stored procedure and use two data cubes:

  • Create a data cube with as much of the data as possible, without introducing any public parameters. Warehouse this data cube.
  • Create a second data cube that takes the first (warehoused) data cube and introduces the public parameters.

This way the stored procedure with the public parameters will still have to run every time, but should take a lot less time to execute.

#6

Let's take one more stab at this. The potential public parameters are based on any combination of 3,500 accounts. Though I like the approach you suggest, and I can use it in other places, it won't work here.

I was hoping that there might be a setting somewhere that sees the stored proc call and it's parameters, and if it had been executed recently, just return the cached results. Is this possible?

#7

Here is how I solved this problem. Tell me if you think this is good.

I have long running stored procs with public parameters. None of the solutions suggested in this thread will work.

Thank you to Azar for pointing me to this solution.

I created a Cache Key table in the database. The cache key field is the proc name and params concatenated into a string.

I created a one Cache Result table for each long running proc based on its output.

When a long running stored proc is called:

Check for an existing cache key.

If the cache key exists, return the data result from the associated Cache Result table.

If the cache key does not exist, generate results as usual, store them in Cache Key and associated Cache Result tables.

Return data to the data cube.


Love to hear your thoughts on this.

#8

Interesting solution!

I have also added you to an open feature request for caching at the query level, which should meet your needs as well when implemented.

#9

By default, the Dundas BI caching will retain the results of your Stored Procedure if you are executing it with the exact same parameters (this caching is on by default for 20 minutes but the caching timeout can be changed at the application level or to be bypassed if needed at the metric set level). That being said, in your case you mentioned that you have multiple hierarchies involved so I believe that every time you expand one hierarchy, it's considered a different query thus the default caching mechanism doesn't apply to your case. With that in mind - I like your proposed solution - I think it should work so please let us know the outcome.

#10

This is insightful for me. Thanks. I will let you know.

#11

You are the man! I appreciate it.

#12

I created custom database caching and it works very well. Here is the relevant SQL code for anyone interested

create table dbo.Cache ( CacheKey binary(20), CacheDate datetime, CacheTable varchar(200) )

The CacheKey is created by concatenating the name of the stored proc and parameters.

The cache results table is built on the output of the stored proc, named "Cache_" + stored proc name

This is the code added to the existing long running stored procs.

declare @cacheKey binary(20)
set @cacheKey = HashBytes( 'SHA1', Object_Name(@@ProcID) + isnull( @ContatenatedParms, 'Null' ))

if exists ( select * from dbo.Cache where CacheKey = @cacheKey )
begin
select Field1, Field2, Field3
from dbo.Cache_GetAccountTrailingReturnsBI
where CacheKey = @cacheKey

return
end

-- Do long running stuff, load to @results in this example

-- Load cache table
insert into dbo.Cache ( CacheKey, CacheDate, CacheTable )
values ( @cacheKey, getdate(), 'dbo.Cache_GetAccountTrailingReturnsBI' )

-- Load cache results
insert into dbo.Cache_GetAccountTrailingReturnsBI
( CacheKey, Field1, Field2, Field3 )
select @cacheKey, Field1, Field2, Field3
from @results

-- Return results
select Field1, Field2, Field3
from dbo.Cache_GetAccountTrailingReturnsBI
where CacheKey = @cacheKey


I have a job that truncates all cache tables every night, because my data is daily.


If anyone knows how I can paste sql code where it keeps the colors and indentation I'll buy you a beer. :)

#13

This is great, thanks for sharing!


As for the formatting of the code, unfortunately that feature hasn't been implemented yet. You can use the "Preformatted Block" option when posting a reply rather than a comment, which will essentially put it in a box, but that's all for now. For the time being, it might be easier to just include screenshots of formatted code.

#14

Never even thought of screenshots. Cool cool