| Author |
Topic |
|
graabein
Starting Member
16 Posts |
 Posted - 2008-03-06 : 07:55:23
|
Hi, I'm developing a client application where users work on the same workspace and thus need to be aware of other users changes. Whenever a user makes a change to the workspace I insert a row in a table (Changes) with username, timestamp, workspace (there are about 10 different) and timespan (either a day or a week, I have fromDate and toDate). I have a timer that checks for changes in the current workspace within the selected timespan, changes that are newer than the last check, and committed by other users than the current. The timer is set to check every 5th or 10th second, I can't remember right now. I don't have a good feeling about this solution. It's too heavy. There are about 500-1000 (maybe more) concurrent users. I figure it should be more like memory paging techniques or maybe put in another table or view between the change table and the stored procedure, along with some caching to lighten the load? The workspaces are built on server with stored procedures querying different tables and returning a table that is drawn on the workspace. Maybe I could cache these temp tables and just redraw them on server when a user inserts a change?All tips or links are welcome!  |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-06 : 10:41:26
|
| have you thought about using replication?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
graabein
Starting Member
16 Posts |
Posted - 2008-03-06 : 11:12:36
|
| How so? I have just the one database. I thought replication was copying and synchronising data between databases? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-06 : 11:15:47
|
| aha, ok. understood it wrong then.but if you have only one db, then what's the problem?all changes that are commited are available to other users under default isolation level.or are you having problems with auditing changes?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
graabein
Starting Member
16 Posts |
Posted - 2008-03-06 : 11:26:17
|
| The current solution with inserting a row in the Changes table and letting every client read from that table at a certain interval, I think I could do that better. Surely there are common techniques for this kind of functionality? Cause every client regenerates the workspace when they're told there are changes in the workspace. If I maybe put that on the server side and have the client causing the change mark the workspace as changed, and the server then rebuilds the workspace instantly, so when the clients see the workspace is updated they just read that workspace.Problem is if I have too many workspaces. They are tied up to either a certain date (today) or some days ahead (planning). I have different types of workspaces also, not just one workspace at a different date.Is it much load to create a table on the server for each type of workspace? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-06 : 11:49:40
|
| you could create partitioned views to virtualy split your table datewise.if i understand correctly what you would like to have is for the server to notify the clients of the changes to the workspace table?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
graabein
Starting Member
16 Posts |
Posted - 2008-03-06 : 13:53:51
|
| Yes and no, I'm not sure that helps much, considering the changes are very many on the most used workspaces. It's the whole process of notifying a change from one client to correctly update the clients with the shared workspace. Splitting the work up into two or more tables -- one for page index, and one for each type of workspace -- having the page index table have timestamp and a flag for rebuild, letting the clients poll that table, and if the relevant table's been updated just select all from the table type with the given index. Then the server builds the workspace just once and I need some garbage collector to remove dated indexes or just start over on each day.I have to think about it and do some googling. I don't know what key words to use but thanks for helping. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-06 : 15:00:19
|
| emm... have a trigger on the workspace table that updates the page indes table's rebuild flag.you can use SqlDependency to send a notification to clients when this table updates which will happen after the trigger fires.is that even remotely helpfull? _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
graabein
Starting Member
16 Posts |
Posted - 2008-03-06 : 16:24:12
|
| I'm taking notes LOL Thanks! |
|
|
|
|
Best practice for client sync