update query so slow

I wrote 7 update queries for updating the field startofweek and endofweek depending on the reportday, it takes more than 2 hrs to run all of them for 250000 records, do anyone knows how to run these 7 queries faster?

for all records with reportday is on SUNDAY
UPDATE mastertable SET mastertable.Startofweek = [REPORTDAY], mastertable.Endofweek = [REPORTDAY]+6
WHERE (((DatePart("w",[REPORTDAY]))=1));

for all records with reportday is on MONDAY
UPDATE mastertable SET mastertable.Startofweek = [REPORTDAY]-1, mastertable.Endofweek = [REPORTDAY]+5
WHERE (((DatePart("w",[REPORTDAY]))=2));
:
:
:
for all records with reportday is on Saturday
UPDATE mastertable SET mastertable.Startofweek = [REPORTDAY]-6, mastertable.Endofweek = [REPORTDAY]
WHERE (((DatePart("w",[REPORTDAY]))=7));

You might try (after testing it first to make sure it does what you want)
doing it in one query whose SQL looks something like this:

UPDATE
mastertable
SET
mastertable.Startofweek = [REPORTDAY]-(Weekday([REPORTDAY])-1),
mastertable.Endofweek = [REPORTDAY]+(7-Weekday([REPORTDAY])

"VC" wrote in message
...
I wrote 7 update queries for updating the field startofweek and endofweek
depending on the reportday, it takes more than 2 hrs to run all of them for
250000 records, do anyone knows how to run these 7 queries faster?

for all records with reportday is on SUNDAY
UPDATE mastertable SET mastertable.Startofweek = [REPORTDAY],
mastertable.Endofweek = [REPORTDAY]+6
WHERE (((DatePart("w",[REPORTDAY]))=1));

for all records with reportday is on MONDAY
UPDATE mastertable SET mastertable.Startofweek = [REPORTDAY]-1,
mastertable.Endofweek = [REPORTDAY]+5
WHERE (((DatePart("w",[REPORTDAY]))=2));
:
:
:
for all records with reportday is on Saturday
UPDATE mastertable SET mastertable.Startofweek = [REPORTDAY]-6,
mastertable.Endofweek = [REPORTDAY]
WHERE (((DatePart("w",[REPORTDAY]))=7));

On Mon, 7 Jun 2004 10:56:10 -0700, "VC"
wrote:

I wrote 7 update queries for updating the field startofweek and endofweek depending on the reportday, it takes more than 2 hrs to run all of them for 250000 records, do anyone knows how to run these 7 queries faster?

for all records with reportday is on SUNDAY
UPDATE mastertable SET mastertable.Startofweek = [REPORTDAY], mastertable.Endofweek = [REPORTDAY]+6
WHERE (((DatePart("w",[REPORTDAY]))=1));

for all records with reportday is on MONDAY
UPDATE mastertable SET mastertable.Startofweek = [REPORTDAY]-1, mastertable.Endofweek = [REPORTDAY]+5
WHERE (((DatePart("w",[REPORTDAY]))=2));
:
:
:
for all records with reportday is on Saturday
UPDATE mastertable SET mastertable.Startofweek = [REPORTDAY]-6, mastertable.Endofweek = [REPORTDAY]
WHERE (((DatePart("w",[REPORTDAY]))=7));

Ummm... why seven queries? It's slow because you're having to do seven
table scans, since your criterion is a function which cannot be
indexed. This should work in one go with no criteria at all:

UPDATE MasterTable
SET mastertable.StartOfWeek = DateAdd("d", 1-DatePart("w",
[ReportDay]), [ReportDay]),
mastertable.EndOfWeek = DateAdd("d", 7-DatePart("w", [ReportDay]),
[ReportDay]);

Or - better yet - get rid of the redundant calculated fields
StartOfWeek and EndOfWeek altogether and calculate them as needed in a
Query using the same expressions.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Hi VC,

Brian might see some error in my thinking,
but another option might be to use the Max
and Min [REPORTDAY] where [Startofweek]
is null or [Endofweek] is null....

run a "prequery"
to fill a table of all the sequential dates between
Min and Max, and compute the Endofweek and
Startofweek within this "prequery."

Surely that will be way less than 250,000 records.

Then run your update query linking to this prequery
table....no DatePart/Weekday/addition for each record,
just join on REPORTDAY.

Does that make sense?

Gary Walter

"VC" wrote
I wrote 7 update queries for updating the field startofweek and endofweek depending
on the reportday, it takes more than 2 hrs to run all of them for 250000 records, do
anyone knows how to run these 7 queries faster?

for all records with reportday is on SUNDAY
UPDATE mastertable SET mastertable.Startofweek = [REPORTDAY], mastertable.Endofweek
= [REPORTDAY]+6
WHERE (((DatePart("w",[REPORTDAY]))=1));

for all records with reportday is on MONDAY
UPDATE mastertable SET mastertable.Startofweek = [REPORTDAY]-1,
mastertable.Endofweek = [REPORTDAY]+5
WHERE (((DatePart("w",[REPORTDAY]))=2));
:
:
:
for all records with reportday is on Saturday
UPDATE mastertable SET mastertable.Startofweek = [REPORTDAY]-6,
mastertable.Endofweek = [REPORTDAY]
WHERE (((DatePart("w",[REPORTDAY]))=7));

Hi VC,

For example, how long does this take,
and how many records (hopefully REPORTDAY
is indexed):

SELECT DISTINCT
REPORTDAY As EnumDay,
IIF(True,Null,#1/1/1900#) As StartWeek,
IIF(True,Null,#1/1/1900#) As EndWeek
INTO tblEnum
FROM mastertable
WHERE
Endofweek IS NULL
OR
Startofweek IS NULL;

This would be one-time-only make table.

Once tblEnum was created, the "process" would be
from then on

1)delete all records from tblEnum

DELETE * FROM tblEnum;

2) append records

INSERT INTO tblEnum (EnumDay)
SELECT DISTINCT REPORTDAY
FROM mastertable
WHERE
Endofweek IS NULL
OR
Startofweek IS NULL;

3) update fields in tblEnum

UPDATE tblEnum
SET StartWeek = ......,
EndWeek = ......;

(use either Brian's or John's "formula"
based on EnumDay)

4) update mastertable

UPDATE mastertable
INNER JOIN tblEnum
ON
mastertable.REPORTDAY=tblEnum.EnumDay
SET mastertable.Startofweek = tblEnum.StartWeek,
mastertable.Endofweek = tblEnum.EndWeek;

I imagine this being a lot faster, but I could be wrong.
Surely you have less than (250000/365) ~ 684 years
of distinct dates.

Gary Walter


"Gary Walter" wrote
Brian might see some error in my thinking,
but another option might be to use the Max
and Min [REPORTDAY] where [Startofweek]
is null or [Endofweek] is null....

run a "prequery"
to fill a table of all the sequential dates between
Min and Max, and compute the Endofweek and
Startofweek within this "prequery."

Surely that will be way less than 250,000 records.

Then run your update query linking to this prequery
table....no DatePart/Weekday/addition for each record,
just join on REPORTDAY.

Does that make sense?

Gary Walter

"VC" wrote
I wrote 7 update queries for updating the field startofweek and endofweek
depending
on the reportday, it takes more than 2 hrs to run all of them for 250000 records,
do
anyone knows how to run these 7 queries faster?

for all records with reportday is on SUNDAY
UPDATE mastertable SET mastertable.Startofweek = [REPORTDAY],
mastertable.Endofweek
= [REPORTDAY]+6
WHERE (((DatePart("w",[REPORTDAY]))=1));

for all records with reportday is on MONDAY
UPDATE mastertable SET mastertable.Startofweek = [REPORTDAY]-1,
mastertable.Endofweek = [REPORTDAY]+5
WHERE (((DatePart("w",[REPORTDAY]))=2));
:
:
:
for all records with reportday is on Saturday
UPDATE mastertable SET mastertable.Startofweek = [REPORTDAY]-6,
mastertable.Endofweek = [REPORTDAY]
WHERE (((DatePart("w",[REPORTDAY]))=7));