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!
.orgTry creating a non-clustered index on LastUpdateTime. This should optimize
your MAX query.
Hope this helps.
Dan Guzman
SQL Server MVP
"BCM" <BCM@.none.com> wrote in message news:4104f30d$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] + "')[/v
bcol]
from[vbcol=seagreen]
> " + MeterName
> Thanks in advance for any advice on improving ANY of this!
> .org
>