BCM wrote:

> 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
>
>