MAX() and DATEDIFF() question

I've written some software that listens to meters on a specified port and
writes XML files when any of those meters "speak." There are some 200 of
them, and each is programmed to "speak" at 10 second intervals, so there's a
fair amount of traffic.
Now what I've been asked to do is write a record to a database for each of
these meters at ONE MINUTE intervals. At first I simply set up a one minute
timer and pointed my software to the directory where the XML files live and
said "write a record for every file in there." But, alas, occasionally one
(or several) of the meters' files were being overwritten at that particular
time and thus weren't available.
So, I changed things so that each time a meter speaks, I check the database
to see if it has been a minute or more since the last database update for
that meter, and if it has I write a record out. Now this works fine, but it
uses an enormous amount of CPU time: at times nearly 50% of a dual 2 gig
Pentium 3 system is hijacked.
So, my question is: is my SQL (which calls the DATEDIFF() and MAX()
functions) the likely culprit for this inefficiency? My colleague believes
that it is, since (he says) calling MAX() forces a search through all the
records contained in the table (potentially a boatload). Here's the query:
SELECT DATEDIFF(second, MAX([LastUpdateTime]), '" + [CurrentTime] + "') from
" + MeterName
Thanks in advance for any advice on improving ANY of this!
..org
BCM,
Do you have an index on LastUpdateTime? If not, that should help quite a
bit since the query engine can seek to the MAX value rather than scanning
for it.
Alternatively, you could keep a LastMeterUpdateTime table which holds only
an ID for each meter and the last time you updated it. But then it is up to
you to make sure that this table is updated.
Russell Fields
"BCM" <BCM@.none.com> wrote in message news:4104f321$1@.news.unc.edu...
> I've written some software that listens to meters on a specified port and
> writes XML files when any of those meters "speak." There are some 200 of
> them, and each is programmed to "speak" at 10 second intervals, so there's
a
> fair amount of traffic.
> Now what I've been asked to do is write a record to a database for each of
> these meters at ONE MINUTE intervals. At first I simply set up a one
minute
> timer and pointed my software to the directory where the XML files live
and
> said "write a record for every file in there." But, alas, occasionally one
> (or several) of the meters' files were being overwritten at that
particular
> time and thus weren't available.
> So, I changed things so that each time a meter speaks, I check the
database
> to see if it has been a minute or more since the last database update for
> that meter, and if it has I write a record out. Now this works fine, but
it
> uses an enormous amount of CPU time: at times nearly 50% of a dual 2 gig
> Pentium 3 system is hijacked.
> So, my question is: is my SQL (which calls the DATEDIFF() and MAX()
> functions) the likely culprit for this inefficiency? My colleague believes
> that it is, since (he says) calling MAX() forces a search through all the
> records contained in the table (potentially a boatload). Here's the query:
> SELECT DATEDIFF(second, MAX([LastUpdateTime]), '" + [CurrentTime] + "')
from
> " + MeterName
> Thanks in advance for any advice on improving ANY of this!
> .org
>