[MSSQL][C#] SQL notification met SqlDependency

[maxtz0r]

Ik heb een web applicatie gebouwd die op basis van SQL Notificitions en SignalR berichten kan versturen naar alle web clients, een soort webchat. Er wordt gekeken naar een tabel met daarin berichten. Zodra er een update van deze tabel plaatst vind door bijvoorbeeld een insert stuurt sql een notificatie die door middel van SignalR wordt verzonden naar de web clients. Door middel van jQuery worden de berichten gepresenteerd naar de bezoekers.
Nu loop ik tegen de limieten van SqlDependency aan. SQL stuurt namelijk een notificatie die de OnChange event triggerd. De SqlDependency query moet voldoen aan de volgende eisen: MSDN: Creating a Query for Notification om ook daadwerkelijk de OnChange event te triggeren.

Het probleem is dat mijn tabel behoorlijk groot is en voor mijn basis query die SqlDependency gebruikt kan ik moeilijk een select doen op de volledige tabel. Daarbij komt dat ik alleen geïnteresseerd ben in de laatste 10 berichten(als soort van historie wanneer je de pagina eerste keer bezoekt) en alle nieuwe berichten die vervolgens toegevoegd worden.

De vraag is of deze oplossing uberhaupt gaat werken of dat ik wellicht naar een andere manier moet gaan zoeken om dit voor elkaar te krijgen. Ik heb geen ervaring op dit gebied en zoek dus een goeie manier om een trigger te krijgen van de database op het moment dat er wat gebeurt in mijn tabel.

Ter info:

De tabel met berichten wordt gevuld door een externe applicatie en daar heb ik verder geen invloed op.

Mijn oplossing is gebasseerd op:
signalr - http://signalr.net/
database change notification - http://techbrij.com/datab...net-signalr-sqldependency

[_js_]

Waarom maakt het jou uit op welke query die notifications zijn gebaseerd? Of worden de oude berichten ook vaak gewijzigd?

[maxtz0r]

_js_ schreef op donderdag 11 juli 2013 @ 10:52:
Waarom maakt het jou uit op welke query die notifications zijn gebaseerd? Of worden de oude berichten ook vaak gewijzigd?

Nee, sterker nog er vinden geen wijzigingen plaats. Het enige wat gebeurt is inserts, deze inserts wil ik via singalr tonen. Maar ik zal toch een query moeten hebben om een onchange event te triggeren toch?

[_js_]

Die query voor de notifications is alleen om te zorgen dat je notifications krijgt. Aan de hand van die notifications kun je zelf gaan beslissen wat je doet om de nieuwe informatie uit de database te halen, bijvoorbeeld door een nieuwe query te doen die er uit ziet als "select top 10 * from berichten order by time desc", waar je dus wel alleen de relevante informatie kunt ophalen.

[RobIII]

Waarom überhaupt die notificatie-verantwoordelijkheid in SQL stoppen? Het kan en heeft zeker in een aantal gevallen z'n nut en is een hele gave feature van MSSQL, maar wat als je ooit besluit een ander backend te gebruiken?

De chat-tekst komt via je webapplicatie binnen, die mikt 't in SQL, SQL notificeert je webapplicatie waar 't bericht met SignalR weer "gedistribueerd" wordt.

Waarom zit SQL daar tussen? Ik zou de chat-tekst op 't moment van binnenkomen in je APP naar SQL sturen én naar SignalR/Clients. Dan heb je heel die (MSSQL specifieke) notifications niet (meer) nodig. Dat maakt 't later, als "bonus", makkelijk(er!) om van RDBMS te wisselen (hoewel dat zelden tot nooit echt makkelijk is).


Die notifications zijn handig als er wijzigingen in de DB worden gemaakt vanuit een andere applicatie:

[ Voor 28% gewijzigd door RobIII op 11-07-2013 14:57 ]

[maxtz0r]

_js_ schreef op donderdag 11 juli 2013 @ 14:01:
Die query voor de notifications is alleen om te zorgen dat je notifications krijgt.

Moet het resultaat van de query dan niet betrekking hebben op mijn notificatie? Met andere woorden stel ik doe een query op de tabel waarvan ik weet dat deze geen resultaat geeft maar wel super snel is, werkt mijn notificatie dan wel wanneer er een insert plaats vind?

RobIII schreef op donderdag 11 juli 2013 @ 14:18:
Waarom überhaupt die notificatie-verantwoordelijkheid in SQL stoppen? Het kan en heeft zeker in een aantal gevallen z'n nut en is een hele gave feature van MSSQL, maar wat als je ooit besluit een ander backend te gebruiken?

De chat-tekst komt via je webapplicatie binnen, die mikt 't in SQL, SQL notificeert je webapplicatie waar 't bericht met SignalR weer "gedistribueerd" wordt.
[afbeelding]
Waarom zit SQL daar tussen? Ik zou de chat-tekst op 't moment van binnenkomen in je APP naar SQL sturen én naar SignalR/Clients. Dan heb je heel die (MSSQL specifieke) notifications niet (meer) nodig. Dat maakt 't later, als "bonus", makkelijk(er!) om van RDBMS te wisselen (hoewel dat zelden tot nooit echt makkelijk is).
[afbeelding]

Die notifications zijn handig als er wijzigingen in de DB worden gemaakt vanuit een andere applicatie:
[afbeelding]

De wijzigingen in de DB worden gemaakt vanuit een niet web applicatie waar ik geen invloed op heb, zie TS. Het is vanuit die applicatie helaas niet mogelijk om singalr aan te roepen om de berichten te distribueren. Vandaar dat ik het op deze manier wil doen.

[RobIII]

maxtz0r schreef op donderdag 11 juli 2013 @ 15:21:
De wijzigingen in de DB worden gemaakt vanuit een niet web applicatie waar ik geen invloed op heb, zie TS.

Ik heb de TS nogmaals gelezen, en met de kennis die ik nu heb in 't achterhoofd kun je dat ook in de TS lezen, maar dat had je wel wat specifieker mogen vermelden.

...Zodra er een update van deze tabel plaatst vind....

Vs:

...Zodra een andere applicatie een update op deze tabel uitvoert....

(waarbij update overigens ook nog eens, later, alleen maar insert blijkt te zijn).

De tabel met berichten wordt gevuld door een externe applicatie en daar heb ik verder geen invloed op.

Las/interpreteerde ik als in: het schema van de tabel staat vast omdat ApplicatieX die maakt (en/of initieel "vult"). Niet als: ApplicatieX blijft die tabel aan 't aanvullen.

Wil je goed geholpen (kunnen) worden dan is 't natuurlijk wel belangrijk je probleem duidelijk over te brengen Maar goed; misschien las ik 't ook wel vanuit een heel ander perspectief en had een ander 't wel/beter begrepen

[ Voor 60% gewijzigd door RobIII op 11-07-2013 15:31 ]

[maxtz0r]

RobIII schreef op donderdag 11 juli 2013 @ 15:25:
[...]

Ik heb de TS nogmaals gelezen, en met de kennis die ik nu heb in 't achterhoofd kun je dat ook in de TS lezen, maar dat had je wel wat specifieker mogen vermelden. Lees 't maar eens zoals ik 't interpreteerde

Wellicht inderdaad een niet heel gelukkig zinsbouw keuze gemaakt

[_js_]

maxtz0r schreef op donderdag 11 juli 2013 @ 15:21:
[...]


Moet het resultaat van de query dan niet betrekking hebben op mijn notificatie? Met andere woorden stel ik doe een query op de tabel waarvan ik weet dat deze geen resultaat geeft maar wel super snel is, werkt mijn notificatie dan wel wanneer er een insert plaats vind?

Je mag er best een voorwaarde bijzetten als "where time > [starttijd van signalr server of laatste keer dat je notifications hebt gehad]" zodat de query snel is, en wanneer er wijzigingen zijn niet dezelfde query opnieuw uitvoeren (of iig de gegevens niet ophalen van de server) maar de query die je daadwerkelijk nodig hebt om de gegevens op te halen.

[ Voor 16% gewijzigd door _js_ op 11-07-2013 16:33 ]

[maxtz0r]

Dat is dus één van de problemen die ik tegenkom. Een Where clausule met een datetime vergelijking zou voor mij een goede oplossing zijn echter als mijn query er als volgt uit ziet:

code:

Select [ColumnA],[DateColumnB] 
FROM [DBO].[Table] 
where [ColumnA] is null 
AND cast([DateColumnB] as DATE) = cast(GETDATE() as Date) 
ORDER BY [DateColumnB] desc

geeft hij bij de eerste keer laden alle records die uit deze query komen, zoals verwacht. Zodra ik een record in de tabel toevoeg of bewerk(om te testen) wordt de OnChange event niet getriggerd.

Gebruik ik de volgende query:

code:

Select [ColumnA],[DateColumnB] 
FROM [DBO].[Table] 
where [ColumnA] is null 
ORDER BY [DateColumnB] desc

Dan triggert de OnChange bij zowel bewerken als bij een nieuw record.

[TallManNL]

The statement must not use any nondeterministic functions, including ranking and windowing functions.

Creating a Query for Notification

These built-in functions from other categories are always nondeterministic:
[..]
GETDATE

Deterministic and Nondeterministic Functions

Ofwel ditch die GetDate en probeer het eens met een parameter die je clientside vult. Denk dan even aan je datumgrenzen voor de notifications en gebruik >=

code:

..
Where Cast(DateTimeColumnB as Date) >= @MyTodayParameter

[ Voor 14% gewijzigd door TallManNL op 11-07-2013 20:46 ]

[sig69]

Ik weet niet hoeveel records er in de tabel zitten, maar die cast is ook niet optimaal:

SQL:

Where DateTimeColumnB) > @MyTodayParameter - 1 dag

[maxtz0r]

Ter info:

Die cast was inderdaad zwaar belabberd en was ook eigenlijk meer ter test om te kijken of de onchange werd getriggert. Ik heb het uiteindelijk opgelost door inderdaad een parameter te gebruiken.

Ik zit nu met een hele hoop andere problemen maar ook daar kom ik wel uit :-)