Turns out listening to instructions is a good idea sometimes
-
I took up a job interview a couple of days back where the interviewer wanted to know how I'd implement a requirement where it turns out to be useful to cache some piece of data in-memory with the actual data store being a SQL server database with the twist being that the cache should automatically get refreshed when the relevant records are updated in the database. Obviously, the interviewer wanted to see if I knew some specific technology solution that already exists out there to deal with this. And well, I didn't and I told him so! Further, I came up with a solution on-the-fly and told him that I'd either have to periodically poll for changes - which is a rather bad idea - or alternatively, set up an update/insert trigger on the DB and have some .NET code execute in response which communicates with the cache system via remoting! After a bit he gave up and moved on to the next question! I figured I'll post a question on the .NET forum on CP to find out what the right way to do it would have been and happened to notice this rather terse reminder - Please remember to search our articles, the forums, MSDN and Google before posting your question. I thought I'll listen to that for a change and did a search here on CP and found this thread[^]! Peter O'Hanlon[^]'s comment especially, seems to indicate that SQL Server Notification Services[^] is what I should have been talking about! :) Now, if you're thinking that that still didn't save CP from another post on the topic, well, you'd be sort of right. But then this post isn't really about implementing cache refresh notifications using SQL Server Notification Services you know. PS:- As for the interview itself I cleared it! :) But there's another one next week - so, fingers crossed!
-- gleat http://blogorama.nerdworks.in[^] --
gleat wrote:
Further, I came up with a solution on-the-fly and told him that I'd either have to periodically poll for changes - which is a rather bad idea - or alternatively, set up an update/insert trigger on the DB and have some .NET code execute in response which communicates with the cache system via remoting!
That actually sounds workable. Simplistically, this is what Notification Services does (there's a lot more going on, but you have a good idea there). Good luck with next weeks interview.
Deja View - the feeling that you've seen this post before.
-
I took up a job interview a couple of days back where the interviewer wanted to know how I'd implement a requirement where it turns out to be useful to cache some piece of data in-memory with the actual data store being a SQL server database with the twist being that the cache should automatically get refreshed when the relevant records are updated in the database. Obviously, the interviewer wanted to see if I knew some specific technology solution that already exists out there to deal with this. And well, I didn't and I told him so! Further, I came up with a solution on-the-fly and told him that I'd either have to periodically poll for changes - which is a rather bad idea - or alternatively, set up an update/insert trigger on the DB and have some .NET code execute in response which communicates with the cache system via remoting! After a bit he gave up and moved on to the next question! I figured I'll post a question on the .NET forum on CP to find out what the right way to do it would have been and happened to notice this rather terse reminder - Please remember to search our articles, the forums, MSDN and Google before posting your question. I thought I'll listen to that for a change and did a search here on CP and found this thread[^]! Peter O'Hanlon[^]'s comment especially, seems to indicate that SQL Server Notification Services[^] is what I should have been talking about! :) Now, if you're thinking that that still didn't save CP from another post on the topic, well, you'd be sort of right. But then this post isn't really about implementing cache refresh notifications using SQL Server Notification Services you know. PS:- As for the interview itself I cleared it! :) But there's another one next week - so, fingers crossed!
-- gleat http://blogorama.nerdworks.in[^] --
Actually my understanding of Notification Services is that it's more to do with sending out emails or pager notifications when something changes. If you want to have SQL Server 2005 tell you when the results of a query have changed, you can use the
SqlDependencyobject in .NET 2.0. I'm not surprised you didn't know this as keeping a cache of data retrieved from a database server is actually quite an advanced topic - you can get very good performance for most enterprise applications with no caching at all. In n-tier systems keeping caches synchronised (coherent) can be a nasty problem, if the data being cached is updatable, so that each server will provide the same results if a user ends up moving from one server to another, e.g. in a failover scenario. If it's not updatable, what's the point of watching for changes?DoEvents: Generating unexpected recursion since 1991
-
Actually my understanding of Notification Services is that it's more to do with sending out emails or pager notifications when something changes. If you want to have SQL Server 2005 tell you when the results of a query have changed, you can use the
SqlDependencyobject in .NET 2.0. I'm not surprised you didn't know this as keeping a cache of data retrieved from a database server is actually quite an advanced topic - you can get very good performance for most enterprise applications with no caching at all. In n-tier systems keeping caches synchronised (coherent) can be a nasty problem, if the data being cached is updatable, so that each server will provide the same results if a user ends up moving from one server to another, e.g. in a failover scenario. If it's not updatable, what's the point of watching for changes?DoEvents: Generating unexpected recursion since 1991
Mike Dimmick wrote:
Actually my understanding of Notification Services is that it's more to do with sending out emails or pager notifications when something changes.
It can do a lot more for you, it's just an absolute sod to get it to do so. The problem is that all of the examples just talk about using email - it takes quite a bit of contorting to get it to do something useful.
Deja View - the feeling that you've seen this post before.
-
I took up a job interview a couple of days back where the interviewer wanted to know how I'd implement a requirement where it turns out to be useful to cache some piece of data in-memory with the actual data store being a SQL server database with the twist being that the cache should automatically get refreshed when the relevant records are updated in the database. Obviously, the interviewer wanted to see if I knew some specific technology solution that already exists out there to deal with this. And well, I didn't and I told him so! Further, I came up with a solution on-the-fly and told him that I'd either have to periodically poll for changes - which is a rather bad idea - or alternatively, set up an update/insert trigger on the DB and have some .NET code execute in response which communicates with the cache system via remoting! After a bit he gave up and moved on to the next question! I figured I'll post a question on the .NET forum on CP to find out what the right way to do it would have been and happened to notice this rather terse reminder - Please remember to search our articles, the forums, MSDN and Google before posting your question. I thought I'll listen to that for a change and did a search here on CP and found this thread[^]! Peter O'Hanlon[^]'s comment especially, seems to indicate that SQL Server Notification Services[^] is what I should have been talking about! :) Now, if you're thinking that that still didn't save CP from another post on the topic, well, you'd be sort of right. But then this post isn't really about implementing cache refresh notifications using SQL Server Notification Services you know. PS:- As for the interview itself I cleared it! :) But there's another one next week - so, fingers crossed!
-- gleat http://blogorama.nerdworks.in[^] --
Notification Service can do that, but it adds a hell of a lot of overhead. It typically creates several tables, another fifty SP's and countless views. And after doing all this, all it does in the background is still poll the table. Cache dependency is good, but requires a live connection to the db always.
SG Cause is effect concealed. Effect is cause revealed.
-
Mike Dimmick wrote:
Actually my understanding of Notification Services is that it's more to do with sending out emails or pager notifications when something changes.
It can do a lot more for you, it's just an absolute sod to get it to do so. The problem is that all of the examples just talk about using email - it takes quite a bit of contorting to get it to do something useful.
Deja View - the feeling that you've seen this post before.
I admit I'm so far restricted to the ten minute browse of 'is it worth installing this thing?' So far the answer has been, no, not worth it. We pretty much just install the Database Engine and Reporting Services. We're very heavily focused on OLTP systems, I'm not very confident on what a Data Warehouse means nor on Extract/Transform/Load from the OLTP system into a Data Warehouse for archiving and analysis. I might have to re-evaluate Notification Services at some point in the future. Right now I'm actually not learning much new tech. Microsoft have been clamping down on Action Pack subscribers recently, to limit it to people who are actually trying to resell their products (as of course you get a lot of software for a very low price). My employer asked me to take the online qualifying exam - you have to pick one of about 40 online multiple-choice tests. I opted for the SQL Server 2005 exam, going into it cold as I felt I knew a lot about SQL Server. Within a few minutes I'd opened up Books Online so I could cheat ;) The exam was largely about new stuff they'd added to SQL Server 2005 over 2000, so my core product knowledge (of, you know, delivering working systems) was useless.
DoEvents: Generating unexpected recursion since 1991
-
I took up a job interview a couple of days back where the interviewer wanted to know how I'd implement a requirement where it turns out to be useful to cache some piece of data in-memory with the actual data store being a SQL server database with the twist being that the cache should automatically get refreshed when the relevant records are updated in the database. Obviously, the interviewer wanted to see if I knew some specific technology solution that already exists out there to deal with this. And well, I didn't and I told him so! Further, I came up with a solution on-the-fly and told him that I'd either have to periodically poll for changes - which is a rather bad idea - or alternatively, set up an update/insert trigger on the DB and have some .NET code execute in response which communicates with the cache system via remoting! After a bit he gave up and moved on to the next question! I figured I'll post a question on the .NET forum on CP to find out what the right way to do it would have been and happened to notice this rather terse reminder - Please remember to search our articles, the forums, MSDN and Google before posting your question. I thought I'll listen to that for a change and did a search here on CP and found this thread[^]! Peter O'Hanlon[^]'s comment especially, seems to indicate that SQL Server Notification Services[^] is what I should have been talking about! :) Now, if you're thinking that that still didn't save CP from another post on the topic, well, you'd be sort of right. But then this post isn't really about implementing cache refresh notifications using SQL Server Notification Services you know. PS:- As for the interview itself I cleared it! :) But there's another one next week - so, fingers crossed!
-- gleat http://blogorama.nerdworks.in[^] --
gleat wrote:
SQL Server Notification Services[^] is what I should have been talking about!
Using SQL notification often detours around any external transaction logging that is in place (e.g. for restoring after a back-up, or for keeping replicated systems up to date). If that's the case, then your trigger option is safer. That's obviously why you didn't suggest it ;)
-
Yeah, I think SQL Caching is built into .NET. You have to run that aspreg or something to install the triggers.
xacc.ide - now with TabsToSpaces support
IronScheme - 1.0 alpha 4a out now (29 May 2008)Yep, you're right. More info here[^].
-- gleat http://blogorama.nerdworks.in[^] --
Number Two's eyes narrowed and became what are known in the Shouting and Killing People trade as cold slits, the idea presumably being to give your opponent the impression that you have lost your glasses or are having difficulty keeping awake. Why this is frightening is an, as yet, unresolved problem. -- HHGTG
-
gleat wrote:
Further, I came up with a solution on-the-fly and told him that I'd either have to periodically poll for changes - which is a rather bad idea - or alternatively, set up an update/insert trigger on the DB and have some .NET code execute in response which communicates with the cache system via remoting!
That actually sounds workable. Simplistically, this is what Notification Services does (there's a lot more going on, but you have a good idea there). Good luck with next weeks interview.
Deja View - the feeling that you've seen this post before.
Thanks Pete. Good to know that the trigger idea might not have been a completely idiotic suggestion!
-- gleat http://blogorama.nerdworks.in[^] --
Number Two's eyes narrowed and became what are known in the Shouting and Killing People trade as cold slits, the idea presumably being to give your opponent the impression that you have lost your glasses or are having difficulty keeping awake. Why this is frightening is an, as yet, unresolved problem. -- HHGTG
-
Notification Service can do that, but it adds a hell of a lot of overhead. It typically creates several tables, another fifty SP's and countless views. And after doing all this, all it does in the background is still poll the table. Cache dependency is good, but requires a live connection to the db always.
SG Cause is effect concealed. Effect is cause revealed.
Yeah, I do hear that setting notification services up can be a bit of a drag. But then you'd expect that it'd actually notify and not poll. With SQL Server 2005 at least, it appears possible to have the server notify when a database update will cause the result of a query that we're interested in to change. As for the live connection, yes, that would really have to be the case. I wonder if the performance gains that you get at the application layer with caching in this case, will only be at the expense of some scalability at the data layer!
-- gleat http://blogorama.nerdworks.in[^] --
Number Two's eyes narrowed and became what are known in the Shouting and Killing People trade as cold slits, the idea presumably being to give your opponent the impression that you have lost your glasses or are having difficulty keeping awake. Why this is frightening is an, as yet, unresolved problem. -- HHGTG
-
gleat wrote:
SQL Server Notification Services[^] is what I should have been talking about!
Using SQL notification often detours around any external transaction logging that is in place (e.g. for restoring after a back-up, or for keeping replicated systems up to date). If that's the case, then your trigger option is safer. That's obviously why you didn't suggest it ;)
Mark Wallace wrote:
Using SQL notification often detours around any external transaction logging that is in place (e.g. for restoring after a back-up, or for keeping replicated systems up to date).
I am not sure I understand. Are you suggesting that SQL notification would not work if a database is restored or if the data update is the result of replication?
Mark Wallace wrote:
That's obviously why you didn't suggest it [Wink]
Oh yes! Naturally (poster nods affirmatively and generally tries to convey the impression that he knows what he's talking about).
-- gleat http://blogorama.nerdworks.in[^] --
Number Two's eyes narrowed and became what are known in the Shouting and Killing People trade as cold slits, the idea presumably being to give your opponent the impression that you have lost your glasses or are having difficulty keeping awake. Why this is frightening is an, as yet, unresolved problem. -- HHGTG