What is the best practice for push database change notification
-
i am looking for the knowledge regarding pushing database change notification. i know that people will tell me to use sql dependency class but i used that it has some problem. if sql dependency class is monitoring table for long time and if no data change occurred for that table then sql dependency class throw exception. we can create max 100 instance of sql dependency class. i am looking for something which i can use in a situation where many user changing data in a table constantly and all change notification will send message to front end application. again i do not like to use polling technique rather i want to use push technique. it will be very helpful if someone discuss how advance developed handle this situation where sql server and c# is used. please guide me in details. thanks
tbhattacharjee
I think having the database push a notification every time a share changes a price will kill your server(s) very very fast. Instead what I'd do is make the client do the call to an exposed webservice that return the data requested, interesting part will be to be able to somehow cache the results if they haven't changed, so instead of calling the database every time you can serve a cached version of the data that will be extremely faster. Using the client to do the calls you can guarantee that only connected clients get the notification for the amount of time they remain connected, with the webservice + cache you reduce the load to the server(s) and the database. For a better idea visit google finance so you can see what they do there. Now if it has to be the server, I'd use a middleman, a server or component that gets the notification from the database, then multithreading all the push notifications to the registered clients, still I think this is an overkill, what happens if you have 3K clients and 500 are offline?, you will have to handle a timeout for the communication that will reduce the number of threads available for that amount of time, and what if additional to those 500 you have another 500 with very slow response times, this makes your push notifications not "live", go for the client solution
I want to die like my grandfather- asleep, not like the passengers in his car, screaming!
-
Tridip Bhattacharjee wrote:
what technology people use in share trading application
You'd have to be more specific than "share trading application". Doing buying/selling/orders in a database is not HFT.
Tridip Bhattacharjee wrote:
because each share price frequently goes up and down
Yes, but if you blow each fact (out of, say 10.000 shares?) to all 1000 users, your network would likely be down, without an up in sight.
Tridip Bhattacharjee wrote:
o discuss if you were in that kind of situation.
They'd get a webpage where they can hit F5 until the requirements dictate they need something else.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
i have notice that u have signature in your post like "Bastard Programmer from Hell" how you can dare to use vulgar words in public forum. codeproject site has good reputation. remove vulgar words ASAP.
tbhattacharjee
-
I think having the database push a notification every time a share changes a price will kill your server(s) very very fast. Instead what I'd do is make the client do the call to an exposed webservice that return the data requested, interesting part will be to be able to somehow cache the results if they haven't changed, so instead of calling the database every time you can serve a cached version of the data that will be extremely faster. Using the client to do the calls you can guarantee that only connected clients get the notification for the amount of time they remain connected, with the webservice + cache you reduce the load to the server(s) and the database. For a better idea visit google finance so you can see what they do there. Now if it has to be the server, I'd use a middleman, a server or component that gets the notification from the database, then multithreading all the push notifications to the registered clients, still I think this is an overkill, what happens if you have 3K clients and 500 are offline?, you will have to handle a timeout for the communication that will reduce the number of threads available for that amount of time, and what if additional to those 500 you have another 500 with very slow response times, this makes your push notifications not "live", go for the client solution
I want to die like my grandfather- asleep, not like the passengers in his car, screaming!
thanks for your time. suppose if i cached data and web service will pull data from cache and push those data to all connected clients. if i design that way then after every certain interval my web service need to pull data and push data to all connected clients. i guess web service constantly do the polling to hit the cache. i want a good mechanism like that i need to push only change data not all data from cache. really looking for details information that how people manage to developed this kind of apps with good scaling.
tbhattacharjee
-
You can always cache the results on a fixed expiry. So for each stock quote cache on 3 mins (or whatever) so that subsequent apps all get their value from the cache and your database is only hit once every 3 mins. If you need the data to be more dynamic than that then you can use sqldependency etc to kick off a task that clears the quote from the cache so that your cache is always up-to-date.
thanks for your time. suppose if i cached data and web service will pull data from cache and push those data to all connected clients. if i design that way then after every certain interval my web service need to pull data and push data to all connected clients. i guess web service constantly do the polling to hit the cache. i want a good mechanism like that i need to push only change data not all data from cache. really looking for details information that how people manage to developed this kind of apps with good scaling.
tbhattacharjee
-
i have notice that u have signature in your post like "Bastard Programmer from Hell" how you can dare to use vulgar words in public forum. codeproject site has good reputation. remove vulgar words ASAP.
tbhattacharjee
-
the word Bastard is consider very nice pleasing word in your locality? :^)
tbhattacharjee
-
the word Bastard is consider very nice pleasing word in your locality? :^)
tbhattacharjee
It's just an old english word to describe a child born out of wedlock.
-
i have notice that u have signature in your post like "Bastard Programmer from Hell" how you can dare to use vulgar words in public forum. codeproject site has good reputation. remove vulgar words ASAP.
tbhattacharjee
Did you just use the 'B' word in your post? How very dare you - I am currently holding up my handbag with two hands with a shocked look on my face.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
thanks for your time. suppose if i cached data and web service will pull data from cache and push those data to all connected clients. if i design that way then after every certain interval my web service need to pull data and push data to all connected clients. i guess web service constantly do the polling to hit the cache. i want a good mechanism like that i need to push only change data not all data from cache. really looking for details information that how people manage to developed this kind of apps with good scaling.
tbhattacharjee
All in all, it seems you are looking for a solution with "good scaling" that only involves software. You need to accept that software alone will only get you so far; and that at some point, you may need to throw more / better hardware at the problem; i.e. multiple application servers / server farms. One does not have to "host" either; one can buy as many "cloud cycles" as one needs without any long-term obligation. A solution that includes additional hardware may be cheaper, easier and quicker to implement than agonizing over the "best" software solution for who knows how long (and then trying to "optimize" it). I think you should start with a (small) prototype and go from there; simply because some of your assumptions are wrong or poorly defined: - Is this a Windows client or browser-based client-server app? - Do all the clients have to same bandwidth? - What sort of client "devices" are we talking about? - What is the client going to do with a given "notification"? - Even if an SqlDependency instance "threw an exception", why do you think this is a problem? (Just create a mechanism to restart it) - Why do you think that "100 SqlDependency instances" is a limitation? Or put another way, why do you think you need that many or even more? - What kind of response times are you thinking you need? Can notifications be "interleaved" among clients? If not, why not? - etc. (Do the prototype).
-
thanks for your time. suppose if i cached data and web service will pull data from cache and push those data to all connected clients. if i design that way then after every certain interval my web service need to pull data and push data to all connected clients. i guess web service constantly do the polling to hit the cache. i want a good mechanism like that i need to push only change data not all data from cache. really looking for details information that how people manage to developed this kind of apps with good scaling.
tbhattacharjee
You're over thinking the problem, define first what needs to be done, clients, devices, server(s), etc, you can scale an application as much as you want today, but scaling is not just software, also hardware, so if you have a poorly designed approach to your problem, your scaling will be very expensive. Think of this, you want to send push notifications on every share change, I don't know from which stock exchange, but if it is the american one you will have to scale really fast, a stock price can change several times every minute, so imagine I'm supposed to be getting those push notifications, 12 times in 1 minute, 20 times... that means your application has to create 20 instances just for me in 1 minute, now make that for 100 users... 2000 push notifications in 1 minute, now multiply for 500 users, 10K push notifications, how much resources will this require for your application?, what will the response time? how are you going to handle communication errors? slow communications? Like someone said, start with a good design and from there build a prototype, we can give you more questions than answers, we don't know your requirements, start with the basics
I want to die like my grandfather- asleep, not like the passengers in his car, screaming!
-
All in all, it seems you are looking for a solution with "good scaling" that only involves software. You need to accept that software alone will only get you so far; and that at some point, you may need to throw more / better hardware at the problem; i.e. multiple application servers / server farms. One does not have to "host" either; one can buy as many "cloud cycles" as one needs without any long-term obligation. A solution that includes additional hardware may be cheaper, easier and quicker to implement than agonizing over the "best" software solution for who knows how long (and then trying to "optimize" it). I think you should start with a (small) prototype and go from there; simply because some of your assumptions are wrong or poorly defined: - Is this a Windows client or browser-based client-server app? - Do all the clients have to same bandwidth? - What sort of client "devices" are we talking about? - What is the client going to do with a given "notification"? - Even if an SqlDependency instance "threw an exception", why do you think this is a problem? (Just create a mechanism to restart it) - Why do you think that "100 SqlDependency instances" is a limitation? Or put another way, why do you think you need that many or even more? - What kind of response times are you thinking you need? Can notifications be "interleaved" among clients? If not, why not? - etc. (Do the prototype).
thanks for your time. here i am giving answer for your question point wise. 1) Is this a Windows client or browser-based client-server app? yes Windows client. 2)Do all the clients have to same bandwidth? not necessarily and not known. 3) What sort of client "devices" are we talking about? a win client will run on normal pc at client side 4)What is the client going to do with a given "notification"? then moment notification comes at client side then client will insert or update that data in its gridview. 5) Even if an SqlDependency instance "threw an exception", why do you think this is a problem? (Just create a mechanism to restart it) - Why do you think that "100 SqlDependency instances" is a limitation? Or put another way, yes i can handle exception and start. i did it this way. here i am going to put a small code just to show how i am handling the exception but not sure that am i on right path? here is the code
void OnDataChange(object sender, SqlNotificationEventArgs e)
{
((SqlDependency)sender).OnChange -= OnDataChange;if (e.Source == SqlNotificationSource.Timeout) { var template = new MailTemplate() .WithBody("HI,<br><br>Part Indexer Service Exception Timeout occur " + DateTime.Now.ToLongDateString()) .WithSubject("Part Indexer Service Exception Timeout occur") .WithSender("bba-india@bba-reman.com") .WithRecepient("tridip@bba-reman.com") .Send(); RegisterNotification(); return; } else if (e.Source != SqlNotificationSource.Data) { var template = new MailTemplate() .WithBody("HI,<br><br>Part Indexer Service Exception SqlNotificationSource.Data " + DateTime.Now.ToLongDateString()) .WithSubject("Part Indexer Service Exception SqlNotificationSource.Data") .WithSender("bba-india@bba-reman.com") .WithRecepient("tridip@bba-reman.com") .Send(); Environment.Exit(1); } StartIndex(); RegisterNotification(); }i guess some where i read that sql dependency support upto 100 instance. so if more client need to run my win apps then
-
It's just an old english word to describe a child born out of wedlock.
Sorry i was not aware of. thanks
tbhattacharjee
-
You're over thinking the problem, define first what needs to be done, clients, devices, server(s), etc, you can scale an application as much as you want today, but scaling is not just software, also hardware, so if you have a poorly designed approach to your problem, your scaling will be very expensive. Think of this, you want to send push notifications on every share change, I don't know from which stock exchange, but if it is the american one you will have to scale really fast, a stock price can change several times every minute, so imagine I'm supposed to be getting those push notifications, 12 times in 1 minute, 20 times... that means your application has to create 20 instances just for me in 1 minute, now make that for 100 users... 2000 push notifications in 1 minute, now multiply for 500 users, 10K push notifications, how much resources will this require for your application?, what will the response time? how are you going to handle communication errors? slow communications? Like someone said, start with a good design and from there build a prototype, we can give you more questions than answers, we don't know your requirements, start with the basics
I want to die like my grandfather- asleep, not like the passengers in his car, screaming!
suppose think you are in same situation where you need to push data change in db to many windows client but you do not know how many client may run your apps in future. so tell me how do you write your application.....what kind of logic you would use for better performance and faster notification push to client. looking for your guidance. thanks
tbhattacharjee
-
suppose think you are in same situation where you need to push data change in db to many windows client but you do not know how many client may run your apps in future. so tell me how do you write your application.....what kind of logic you would use for better performance and faster notification push to client. looking for your guidance. thanks
tbhattacharjee
I can't give you an answer without knowing the requirements, so at this point the suggestion is, go to the drawing board, start doing your design, create your prototype, then come back with specific answers related to your development
I want to die like my grandfather- asleep, not like the passengers in his car, screaming!
-
suppose think you are in same situation where you need to push data change in db to many windows client but you do not know how many client may run your apps in future. so tell me how do you write your application.....what kind of logic you would use for better performance and faster notification push to client. looking for your guidance. thanks
tbhattacharjee
Right now, I would suggest that you search Google and here in CodeProject for details of a product called SignalR. You have a lot of reading ahead of you, but this should help show you better, what you need to consider.
-
suppose think you are in same situation where you need to push data change in db to many windows client but you do not know how many client may run your apps in future. so tell me how do you write your application.....what kind of logic you would use for better performance and faster notification push to client. looking for your guidance. thanks
tbhattacharjee
As Pete has suggested look at SignalR its a great library, have a look at this article Streaming live results to a web site using MSMQ/Duplex WCF/SignalR/jQuery[^] by Sacha Barber and Richard E King. might be overkill for your needs but it shows you what you could potentially achieve.
Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON
-
Tridip Bhattacharjee wrote:
what technology people use in share trading application
You'd have to be more specific than "share trading application". Doing buying/selling/orders in a database is not HFT.
Tridip Bhattacharjee wrote:
because each share price frequently goes up and down
Yes, but if you blow each fact (out of, say 10.000 shares?) to all 1000 users, your network would likely be down, without an up in sight.
Tridip Bhattacharjee wrote:
o discuss if you were in that kind of situation.
They'd get a webpage where they can hit F5 until the requirements dictate they need something else.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
You also make a reference to Hell; how dare you? Please change your signature to "Legitimate-Born Programmer from Heaven" ASAP, so that we, innocent developpers, are not freaked out anymore.
There are two kinds of people in the world: those who can extrapolate from incomplete data.
-
thanks for your time. here i am giving answer for your question point wise. 1) Is this a Windows client or browser-based client-server app? yes Windows client. 2)Do all the clients have to same bandwidth? not necessarily and not known. 3) What sort of client "devices" are we talking about? a win client will run on normal pc at client side 4)What is the client going to do with a given "notification"? then moment notification comes at client side then client will insert or update that data in its gridview. 5) Even if an SqlDependency instance "threw an exception", why do you think this is a problem? (Just create a mechanism to restart it) - Why do you think that "100 SqlDependency instances" is a limitation? Or put another way, yes i can handle exception and start. i did it this way. here i am going to put a small code just to show how i am handling the exception but not sure that am i on right path? here is the code
void OnDataChange(object sender, SqlNotificationEventArgs e)
{
((SqlDependency)sender).OnChange -= OnDataChange;if (e.Source == SqlNotificationSource.Timeout) { var template = new MailTemplate() .WithBody("HI,<br><br>Part Indexer Service Exception Timeout occur " + DateTime.Now.ToLongDateString()) .WithSubject("Part Indexer Service Exception Timeout occur") .WithSender("bba-india@bba-reman.com") .WithRecepient("tridip@bba-reman.com") .Send(); RegisterNotification(); return; } else if (e.Source != SqlNotificationSource.Data) { var template = new MailTemplate() .WithBody("HI,<br><br>Part Indexer Service Exception SqlNotificationSource.Data " + DateTime.Now.ToLongDateString()) .WithSubject("Part Indexer Service Exception SqlNotificationSource.Data") .WithSender("bba-india@bba-reman.com") .WithRecepient("tridip@bba-reman.com") .Send(); Environment.Exit(1); } StartIndex(); RegisterNotification(); }i guess some where i read that sql dependency support upto 100 instance. so if more client need to run my win apps then
Quote:
please answer my all question point wise.
My points were intended to get you thinking. Anyway … 1) If they are Windows clients, then you have more architectural possibilities: e.g. async callbacks; message queues; etc. 2) If they are all “Windows clients”, we can assume (hopefully) that bandwidth is not an issue. 3) OK; no “watches” then. 4) I see … “real-time” gridviews … with a person sitting there watching it change? With updates “as fast as possible”. Sounds like a bogus requirement. 5) I’m not writing your code for you … Do the prototype. You said yourself that it was only possible to run 100 instances of the SQLDependency … Why do “you” think this is a problem? You can have “one” running on the server; which then communicates with “all” clients … Why do you think you need more than one? 6) “As fast as possible” is a non-answer; how will you know if it is ever “fast enough”? 7) Instead of “broadcasting” to all clients at any given time, one or more clients can be notified on a rotating basis; spreading out the load. Do the prototype.