Re: database design for fast client updates

T.H.N. (newsgroup.replies@xxxxxxx) writes:
I'm trying to work out a database design to make it quicker for my client
program to read and display updates to the data set. Currently it reads in
the entire data set again after each change, which was acceptable when the
data set was small but now it's large enough to start causing noticable
delays. I've come up with a possible solution but am looking for others'
input on its suitability to the problem.

I assume that these updates are not performed by your client program,
but by some other process?

Rather than using a datetime column, you could use a timestamp column.
A timestamp column is automatically updated with a monotonically
increasing value each time a row is inserted or updated. Each value is
unique in the database. Note that the name of the type is misleading.
The value is a binary(8) and has to relation to time.

You would still need that table for deleted rows. (Unless you add a
"deleted" bit to the table; the client program would then actually
perform the deletion once it has consumed the update.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Cannot update
    ... not install the updates you referred to. ... highly recommend way for the later one is to install SQL server, ... Microsoft CSS Online Newsgroup Support ...
    (microsoft.public.windows.server.sbs)
  • Re: Unique in-depth problem
    ... Standard date libraries don't work. ... Precompute all the dates and put them in a database (file, SQL server, ... a huge data set, yet manage to retrieve and show only the interesting ...
    (comp.lang.perl.misc)
  • RE: Ad hoc updates to system catalogs are not enabled
    ... Thank you for choosing Microsoft! ... system tables even after you disable allow updates. ... Prevent other users from accessing SQL Server while you are directly updating system ... This command starts an instance of SQL Server in single-user mode and enables allow ...
    (microsoft.public.sqlserver.security)
  • Re: Bulk inserting into database using ADO.NET... deadly slow?
    ... program (look in your SQL Server documentation for "BULK INSERT"). ... > the DataSet, so to reduce the DB updates, but I found that when reaching ... > about 100K rows the DataSet becomes slower and slower, ... why is it so painfully slow to insert big chunks ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Stored Procedure: Insert VS Update
    ... I have seen times where am insert followed by a couple of updates ran more ... > I have a stored procedure were I created a workfile. ... All the tables are indexed very efficiently and we are on SQL Server ...
    (microsoft.public.sqlserver.olap)