Delta compression of query results

[Russell Wallace]

Suppose a database server and client are separated by a low bandwidth
link such as DSL, and the client repeatedly issues a query for, say, a
current product list.

Suppose the product list is large, but only a handful of entries have
typically changed between queries. It would be nice if only the changes
from last query to current one could be sent, saving bandwidth.

Is there any way to do this?

Thanks,

--
"Always look on the bright side of life."
To reply by email, replace no.spam with my last name.

[Robert Klemme]

Re: Delta compression of query results

On 14.02.2007 16:47, Russell Wallace wrote:

Suppose a database server and client are separated by a low bandwidth
link such as DSL, and the client repeatedly issues a query for, say, a
current product list.
>
Suppose the product list is large, but only a handful of entries have
typically changed between queries. It would be nice if only the changes
from last query to current one could be sent, saving bandwidth.
>
Is there any way to do this?

Create a three tier application (instead of two tier) and do intelligent
communication with your own protocol between clients and app server.

The second best option is probably to track changes in the table (for
example by adding a timestamp column) and make sure that only changed
records get queries.

Regards

robert

[BJMurphy]

Re: Delta compression of query results

On Feb 14, 10:47 am, Russell Wallace <russell.no.s.. .@gmail.com>
wrote:

Suppose a database server and client are separated by a low bandwidth
link such as DSL, and the client repeatedly issues a query for, say, a
current product list.
>
Suppose the product list is large, but only a handful of entries have
typically changed between queries. It would be nice if only the changes
from last query to current one could be sent, saving bandwidth.
>
Is there any way to do this?
>
Thanks,
>
--
"Always look on the bright side of life."
To reply by email, replace no.spam with my last name.

Not sure if this is what you mean, but you could make a second/table
or view on the database server with all the entries and the data that
has been sent to the client. Then, you could screen this table for
differences with the existing/current product table and get a subset
of rows to send to the client.

[Erland Sommarskog]

Re: Delta compression of query results

Russell Wallace (russell.no.spa m@gmail.com) writes:

Suppose a database server and client are separated by a low bandwidth
link such as DSL, and the client repeatedly issues a query for, say, a
current product list.
>
Suppose the product list is large, but only a handful of entries have
typically changed between queries. It would be nice if only the changes
from last query to current one could be sent, saving bandwidth.
>
Is there any way to do this?

You would need to add a column to track changes. One way to do this is
to use a timestamp column. A timestamp column is automatically updated
when a row is touched with a database-unique 8-byte value that grows
monotonically. Thus, you can save the highest timestamp value client-
side, and then send this as a parameter the next time.

Note that this scheme as I descibed it, is not suitable for tables with
a high transaction-rate, as you may fail to read some updates.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

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