MDF and LDF file locations

Is it possible to relocate the data and/or the log files after they have bee
n
assigned a location? Thanks everyone for your help."coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
There are several ways of doing this.
One method is to do a backup and restore.
Look at the MOVE TO option in RESTORE in the BOL.
Rick Sawtell
MCT, MCSD, MCDBA|||yes,
1. detach and attach
2. backup and restore
Aleksandar Grbic
MCDBA, Senior Database Administrator
"coenzyme" wrote:

> Is it possible to relocate the data and/or the log files after they have b
een
> assigned a location? Thanks everyone for your help.|||You could run sp_detach_db, move the files to the new location, and then
run sp_attach_db. Another method is to backup and then restore.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.

MDF and LDF file locations

Is it possible to relocate the data and/or the log files after they have been
assigned a location? Thanks everyone for your help.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
There are several ways of doing this.
One method is to do a backup and restore.
Look at the MOVE TO option in RESTORE in the BOL.
Rick Sawtell
MCT, MCSD, MCDBA
|||yes,
1. detach and attach
2. backup and restore
Aleksandar Grbic
MCDBA, Senior Database Administrator
"coenzyme" wrote:

> Is it possible to relocate the data and/or the log files after they have been
> assigned a location? Thanks everyone for your help.
|||You could run sp_detach_db, move the files to the new location, and then
run sp_attach_db. Another method is to backup and then restore.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.

MDF and LDF file locations

Is it possible to relocate the data and/or the log files after they have been
assigned a location? Thanks everyone for your help."coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
There are several ways of doing this.
One method is to do a backup and restore.
Look at the MOVE TO option in RESTORE in the BOL.
Rick Sawtell
MCT, MCSD, MCDBA|||yes,
1. detach and attach
2. backup and restore
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"coenzyme" wrote:
> Is it possible to relocate the data and/or the log files after they have been
> assigned a location? Thanks everyone for your help.|||You could run sp_detach_db, move the files to the new location, and then
run sp_attach_db. Another method is to backup and then restore.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.sql

MDF / LDF

Hello,

I recently deleted the directory
C:\Program Files\Microsoft SQL Server\MSSQL\Data

On my production database server.
I managed using undelete software to get back most of the LDF/MDF files
and reattached them.

But there are a few that simply do not reattach.
Ive tried using

sp_attach_db
sp_attach_single_db

But to no avail.

I realise that the databases were not unattached properly.

Is there a way to get the databases back into Enterprise Manager?
Without starting again or using an expensive product like
officerecovery.com SQL to recover?

I appreciate anybodies help on this.
GurdipYou didn't mention backups. Does that mean you don't have any backups?

What does "do not reattach" mean? Do you get an error message? It might
be possible to advise you better if we knew that and the exact
sp_attach statement you used.

Before you do anything at all please, please take a backup!

--
David Portas
SQL Server MVP
--|||(gurdipv@.gmail.com) writes:
> I recently deleted the directory
> C:\Program Files\Microsoft SQL Server\MSSQL\Data
> On my production database server.
> I managed using undelete software to get back most of the LDF/MDF files
> and reattached them.
> But there are a few that simply do not reattach.
> Ive tried using
> sp_attach_db
> sp_attach_single_db
> But to no avail.
> I realise that the databases were not unattached properly.
> Is there a way to get the databases back into Enterprise Manager?
> Without starting again or using an expensive product like
> officerecovery.com SQL to recover?

If the error message are what I fear, the answer is: yes, you may be able
to get the databases back, but those ways are dangerous and can lead to
severe corruption, and requires complete understanding of what you are
up to. If those databases are dear to you, you should open a case with
Microsoft support.

But start with posting the error message, so we can verify that the problem
is not a more trivial one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

MDAC Roadmap and .mdb files

I know this isn't SQL server related but this seemed to be the only appropriate forum.

I would like to work with Access databases (.mdb files) via (non-managed) C++. At the moment I'm using the ODBC provider for OleDB. However I see from the MDAC "Data Access Technologies Road Map" that this provider is deprecated. The OleDB/ADO alternatives seem to be focussing on SQL server only; I just want a 'local', non-networked desktop system. What technology should I use that isn't deprecated?

Yours,

Peter

If you are using C++ and either OLE DB or ODBC then you might want to consider SQL Server 2005 Express Edition (http://msdn.microsoft.com/sql/express/).

I'd also recommend looking at C++/CLI (there's an Express edition for Visual Studio 2005 also - http://msdn.microsoft.com/vstudio/express/visualc/default.aspx). It's now very straightforward to mix native and .Net code so you can get the best of both worlds - reuse your code and add new features/UI with .Net at your own pace http://msdn.microsoft.com/msdnmag/issues/04/05/VisualC2005/ and http://msdn.microsoft.com/msdnmag/issues/05/01/COptimizations/

|||

Thanks for your answer. That has given me confidence that I can stick with my current system for the time being, but shift to SQL Server in the future, as required. My database needs are pretty simple and I think I could do it without actually changing any code apart from the connection string.

Pete

MDAC Component Checker

I am on a win 2003 server and have run the component
checker and found 2 errors. The files obdcbcp.dll and
sqlsrv32.dll have mismatch errors. There errors say
expecting version 2000.85.1022.0; but the problem is that
I have 2000.85.1025.0
How can I fix this?
Thanks in advance,
Stewart
It sounds like you have installed MS04-003 to thsi system. The sqlsrv32.dll
and odbcbcp.dll from that patch are version 2000.85.1025.0.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Stewart,
I have exactly same problem, what did you do to fix it?
Thanks
Regards
Kasba

MDAC Component Checker

I am on a win 2003 server and have run the component
checker and found 2 errors. The files obdcbcp.dll and
sqlsrv32.dll have mismatch errors. There errors say
expecting version 2000.85.1022.0; but the problem is that
I have 2000.85.1025.0
How can I fix this?
Thanks in advance,
StewartIt sounds like you have installed MS04-003 to thsi system. The sqlsrv32.dll
and odbcbcp.dll from that patch are version 2000.85.1025.0.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Stewart,
I have exactly same problem, what did you do to fix it?
Thanks
Regards
Kasba
vkasba
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message744871.html

Maxing out log files when using SIMPLE

We have our databases set to SIMPLE model, but we continue to blow up during DataWarehouse loads because the log file fills. Our understanding is that SIMPLE should have no logging yet it appers to. I have 2 requests.

1) Is there a command to tell the database prior to the DataWarehouse load to not log any transactions?

2) Do we need to do more then set the DB to SIMPLE to get a general "no log" effect?

Thanks,
Todd1) No. Logging is never entirely turned off. The transaction log is simply not saved when you are in SIMPLE recovery mode.

2) Probably. The culprit is ikely to be your code. I would guess there is either:
a) A begin transaction statement somewhere way at the top of the process
or
b) the BCP/DTS load command is not batching the rows it is inserting.|||I know the culprit is the code. There is a join of massive tables. We had gone the batching route but it still was blowing up. We have ben increasing the max size of the log but were hoping we could stop going that direction.

Thanks|||If this is a complete refresh of the table (meaning you can drop it before running the insert), you can try to write the insert as SELECT INTO.|||With SIMPLE recovery, the log is not needed for backup, but SQL Server still needs it for recovery. Therefore every transaction is still logged, causing the log file to expand if there is no free space.

A section of a log file is marked for reuse as soon as all transactions are committed or rolled back and they are not needed for recovery, meaning the data has been written to disk during a checkpoint.|||Thanks for all the input

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!
Is there an index on the LastUpdateTime column? - since that might make a
significant difference.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"BCM" <BCM@.none.com> wrote in message news:4104f338$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!
|||Hi,
Just a thought but the way I'd approach it is to have a 'feeder' table that
whenever one of your meters 'speaks' you update the table with the info
irrrespective of whether there is already a record in there. Don't have any
indexes on this table so writing is very quick. Also dont close / re-open
the connection for each write, keep it open and re-use. Then have a 'job' on
your database server run every minute that takes the latest reading for each
meter and updates the main table deleting the records in the feeder table
when it is finished. If you wrap this job in a transaction no writes can be
made whilst the update is progressing. This is nice as far as anatomic
transactions are concerned but could cause a block on your updater.
Therefore if your table has an AutoIncrementing field (Identity in TSQL)
then when the server job starts it reads the Max value and processes any
with the Autonumber <= to that value, thereby ignoring any new ones entered
whilst the job is running, finally deleting all records where the
Incrementing field is <= to the value read at the start
If you need any more info or explanations of my ramblings just shout.
Graham
"BCM" <BCM@.none.com> wrote in message news:4104f338$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!
>

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!
Where there are a few things wrong with this query as far as efficiency is
concerned. First there is no WHERE clause so SQL Server has no choice but
to scan the entire table unless there is a proper index it can use on
LastUpdateTime. What you should do is use EXISTS to test for the existence
of a qualifying row. EXISTS will stop looking when it finds the first row
that matches the expression and if you have a proper index and WHERE clause
this should be a simple seek. Create a stored procedure that you can call
and have it return a 1 or 0. Make sure you have an index on LastUpdateTime
CREATE PROCEDURE YourProc
AS
SET NOCOUNT ON
DECLARE @.DateTime DATETIME, @.Return INT
SET @.DateTime = DATEADD(ss,-60,GEDTATE())
IF EXISTS (SELECT * FROM MeterName WHERE LastUpdateTime > @.DateTime)
SET @.Return = 0
ELSE
SET @.Return = 1
RETURN @.Return
Andrew J. Kelly SQL MVP
"BCM" <BCM@.none.com> wrote in message news:4104f360$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!
>

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
>

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
Try 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] + "')
from
> " + MeterName
> Thanks in advance for any advice on improving ANY of this!
> .org
>

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
>

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
PLEASE DON'T MULTI-POST!!!
http://www.aspfaq.com/
(Reverse address to reply.)
"BCM" <BCM@.none.com> wrote in message news:4104f2c3$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
>