Realtime data vs ADO 2.7

[TerraSamba]

Hi guys,

I'm kinda stuck with this problem:

I want to implement a real time data solution with SQL Server 2000 and VB6, by using ADO 2.7 objects. I've come a long way allready. Tables, classes, stored procedures etc are all ready. But I am struggeling a bit with my data refreshes on screen.

Perfect would be if the data provider would push the added records etc to my recordset automaticly. But this is utopia, because cursorlocation: server and cursortype: dynamic returns a recordcount of -1, this problem has existed since 1998, with ADO 1.5 and has not been solved ever since ... Any other type of cursor will not detect newly added records ... so to me there seems to be only one solution:

Adding a lastupdate field to every table and ansynchronously with a timer look if something has changed since we last loaded data from this table, and implement the changes to my local recordset.

I am not to keen on my solution, it will work, but I wonder if if anyone has a better solution for me, that requires someshat less work/overhead.

Thankx 4 replies,

Terra

 

[chriscorran]

Hi I'm very interested to find out if you got this working as I too would like to write an app like this.

If if you could pass on any good web pages that tackle this kind of app I'd greatly appreciate it, or even if you could point me in the right direction. Thankyou in advance,

Chris

 

[MichaelRed]

hmmmmmmmmmmmmm,

I am certainly in no position to proffer any help. I really do not even understand the concept of "real time" with respect to database operation. To me, "real time" denotes a MAXIMUM latency of not perceptible (at least to the human eye). This has, in a variety os situations been defined or set as 1/4 sec. I'm pretty sure you are collectively discussing some alternate definition, I'm just certain that I do not know either what that definition is, or how it is / was derived or set.


Database operations are "set" orientated processes which are generically targeted towards accuracy and completeness (data integrity). This is not generallly consistient with placing time bounds on the response. Of course, db engine vendors make great efforts to have their dbengines as efficient (read FAST), consistient with the PRIMARY goals of accuracy and completness (data integrity) - however the speed is generally secondary to the other goals.

On the other side of the issue, to achieve 'my' definition of real time no one would consider any 'media' storage currently available, as O.S. and physical latency would preclude any semblance of deterministic behaviour. The only pratical approach would appear to be some mechanisim which stoired all data in memory (array of UDT), and was commonly and con-currently accessible to the community of users. I am not aware of any current implementations of such devices (although there should be many), but the use of 'virtual' disc would appear to be a reasonable approach for small to moderate ammounts of data.

In closing, I would also point out that ADO is, perhaps, the slowest methods currently available for database access, so (again?) an approach to any 'real' time or deterministic processing should choose one of the methods with less latency.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[vb5prgrmr]

Ahhh, errr, hmmm, I agree with MichaelRed but I can also offer this advice. With SQL2K you can create a trigger on your data table that can activate a dll or exe. I also think that you can pass this executable informaion as in command line parameters. With this said you could then have your table with the update/insert triggers watching, activate the executable with the supplied information from the trigger which will push the information to your application and with a disconnected recordset you could then add the pushed data to it and refresh without having to requery the db and putting more strain on it. Then again you could use MSMQ if your database is very busy and your application may have problems keeping up. This of course would add some latency but you would be polling your local machine as fast as you could but not putting a strain on your data server.

I Hope This Helps. Good Luck

 

[SemperFiDownUnda]

As vb5prgrmr mentioned the best you can do is use a trigger with some extended stored procedure call in it though performance isn't very good.

Push capability of data is something that is fudged into SQL type servers.

I personally have a special table that client apps poll at a given interval and only grab what has changed since the last polling then if needed (ie a view with data that has changed is viewable) the client pulls down the new data.
The table has the primary key information of what data has changed. This is done via a insert and update trigger.

I have designed an application that used a common instance of a class to push out an event to all apps when data changes. It is hard to explain via words but I'll try.

When a client app starts up it creates a instance of a certian class. This class checks for the exsistance of this class passes the reference to it back. This means all client apps actually point to the same exact instance of the class. The Extended Stored Procedure then creates its instance and calls a method that fires an event off to all the connected clients.

This event could be the trigger for the client to repole or it could send down the new information.

If your application is a read only app then updating is no problem but if it is used to edit the data then you have to think about what happens if data has changed while someone is still editing it.

 

[TerraSamba]

I've got it working for now. I have tried the solution of vb5prgrmr in an earlier stage, but was disappointed by the lack of performance, as SemperFiDownUnda rightfully indicated.

So I got down to SemperFiDownUnda's solution of polling a special table for only the changes, making use of an active exe on a different thread. The changes table is fed by triggers and sp's.

I've got it down to 0.2 sec (that's real time enough for me MichaelRed, let's not strand in a definition discussion here). The server can take up to 0.1 - 0.05 max with 10 users. But every client polls the database at this interval, and I am afraid for the near future, as more users are coming up, that the db is gonna have some problems with all this traffic.

So I would love to have one shared class doing the polling and then notify the other clients. This would reduce the db traffic by a lot of factors.

The app only needs read-only for the real time part. It is a ticketsales/reservation app. with multiple theaters and about 10 salespoints. So working on the current solution and extending it with a 'shared class' sounds like a perfect plan to me.

Does anyone have any suggestions how to create such a class? (SemperFiDownUnda ...?) Where does it live? How do I create/reference it. Will it work in version 2 (the .NET version).

Thanks for all the input guys, I appreciate it.

 

[SemperFiDownUnda]

look at the coffee demo microsoft provide. It shows how you can have many clients connect up to the same object and pass events back.

Does it work in .Net? hmmmm probably a way to do it in .Net native but if not just have unmanaged code.

 

[TerraSamba]

Thanks for the info SemperFi, I'll have a look at it.

The gap between theory and practice is not as wide in theory as it is in practice.