SEO

I am creating a vb.net app that stores its data in mysql. There are
several lists of data that users access in drop down boxes and such.
The data does not change very much in the db. I was thinking that
when I needed to show one of these lists, I could check if there have
been any changes in the db first, if not I can just use the in-memory
data, saving the bandwidth of having to pull back an entire table. If
there has been a change, I can refresh the data with a call to the db.

pseudo code:
x = md5( in-memory list )

con.open
y = SELECT md5( sum of all rows in table ) FROM table;
con.close

if x = y then
'check sums are equal, no data changed, return in-memory data
else
'check sums are different, a change has been made, get new data
from table
con.open
dataset = SELECT * FROM table;
con.close
end if

The question is really: since i am opening a connection to the db when
checking the checksum, should I just bring back the table data since I
have a connection open and then check the differences in memory? Will
I save a bunch of performance if I do that preliminary check on the
db? Aren't I saving a bunch of bandwidth by not having to bring back
the whole table if the data has not changed?

Bryan schrieb:
> I am creating a vb.net app that stores its data in mysql. There are
> several lists of data that users access in drop down boxes and such.
> The data does not change very much in the db. I was thinking that
> when I needed to show one of these lists, I could check if there have
> been any changes in the db first, if not I can just use the in-memory
> data, saving the bandwidth of having to pull back an entire table.

Unless bandwidth or latencies is a real issue, Don't Do This.
Any optimizations that you apply will just lock your code into the
assumpations that the optimizations are based on. They will most likely
be the first thing you'll have to undo when the requirements change,
creating more work for you - and unless the optimizations are really
needed, you'll have done all the work for nothing.
Worse, the real bottlenecks are usually not where one expects them.

In other words: don't optimize just because you see an opportunity to do
so. It's not worth the trouble, most of the time.

> The question is really: since i am opening a connection to the db when
> checking the checksum, should I just bring back the table data since I
> have a connection open and then check the differences in memory?

In that case, you'll have saved nothing in performance.

You could let the database side do the MD5 checksumming, and just
request the checksum. That will trade network bandwidth for CPU load on
the server. (This is the kind of assumption that I meant. If the server
has more CPU time to spare than the network has bandwidth, this kind of
optimization is good; if CPU time is scarcer than network bandwidth,
it's a pessimization. Unfortunately, the relative costs of this kind of
resource can change very quickly, and the software won't even know.)

> Will
> I save a bunch of performance if I do that preliminary check on the
> db? Aren't I saving a bunch of bandwidth by not having to bring back
> the whole table if the data has not changed?

You can try to avoid updating the table if the data has not changed.
However, it's entirely possible that the mysql client library will
already do this for you (that's quite easy after all, and I dimly recall
that the mysql manual had a warning that UPDATE triggers might not fire
if data was written back unchanged).

In other words: first, check what the actual loads are that the software
produces; then analyze and look where the bottlenecks are; and, finally,
if you decide that it's worth the effort, optimize those that give the
most effect for your time.

HTH
Jo