I have found that regular pagination methods (for example, 2 nested SELECT staments that use ROW_NUMBER() to create a temporary field) do not allow SqlCacheDependency to work properly when we set the dependency to work on a command (it only works well if it depends on the table which is not the desired effect).
The same happens when I use SELECT TOP (@TopLines) FieldName1, FieldName2 FROM ThisTable.
We need to put things in cache in order to make the web application run faster. But it is also necessary that the data is updated! That's why the SQL dependency is so important.
First question: Does anyone know how to query the database using pagination or selecting N top elements in a way that the SqlCacheDependency object is not immediately invalidated after the query is run?
In case you did not answer the first question, proceed:
I am trying to find a way through this and thought of this (I understand that this may shock you because it may not make sense to more experienced developpers, but please just consider it for a moment).
*What if we add to the table the row numbers generated by the ROW_NUMBER() function when paginating results?*
What I mean is this,
Consider this table of products called Products :
ID Name Date Rating Price #Date #Rating #Price
1 Mouse XYZ Sep 22nd 2009 4.5 $26 3 3 3
2 Mouse ABC Sep 3rd 2009 4.9 $79 4 1 2
3 Mouse XPTO Aug 15th 2009 3.5 $10 5 6 5
4 Mouse JKL Sep 25th 2009 4.1 $15 2 4 4
5 Mouse 123 Jul 25th 2009 4.7 $100 6 2 1
6 Mouse QWE Out 14th 2009 3.9 $5 1 5 6
Thus, #Date column contains the number of the rows when the table is ordered by Date DESC
#Rating column contains the number of the rows when the table is ordered by Rating DESC
#Price column contains the number of the rows when the table is ordered by Price DESC
Now, for example, when we want to get page 3 (suppose page size 10) of the most recent products all we have to do is:
SELECT ID, Name, Date, Rating FROM Products WHERE #Date BETWEEN 21 AND 30
Maybe (maybe...) this query would be faster and I am sure you are asking yourself how the hell those values ended up in the Products table.
I thought of a Windows service to update those values on a periodic basis.
Remember the initial problem?
The SqlCacheDependency being invalidated immediately after the query was run when we were using TOP and pagination queries using ROW_NUMBER() (also tested others such as using TOP and EXCEPT - works fine, but not with the dependency)?
I tested and, although the SQL cache dependency in SQL SERVER 2008 is not perfect, at least it works in simple queries such as the one bolded above.
If we have these fields containing the row numbers, we will be able to cache top results and pages from listings with valid sql cache dependencies which is our goal.
The second question actually are several questions:
a) When new rows are inserted or updated by the web application users, those fields (obviouly have to be indexed) are not touched. So, I guess the inserts and updates will still be fast, right?
b) The Windows service I mentioned above updates those values periodically (new rows do not appear in listings until ordered). Is it going to penalize the end users when those updates are happening?
c) Is there a way to be sure that the changes made to the database table are treated as a low priority task and do not affect the end-user experience?
d) Should we use SQL Server 2008 services and queues? Why or why not?
e) What do you think of this? Any critics? Suggestions or better ideas?
This is specially important to show TOP results.
For example, you want to display a profile and cache it dependent on the database results. If the user has a listing of latest actions (I mean, we need to use TOP or my latest invention), how do you do it?
Hope you have answers for this. Thank you for reading all of it!
(We are using SQL Server 2008 - not sure of the version but it is NOT the Express version which does not support this kind of thing)