September 10, 2015 at 2:07 pm
Good day,
I come from the darkest corners of the internets with a question that I just haven't manage to sort out.
I'll try to describe the scenario as good as I can...
Table1 will be:
==============================================================================
| EntryID | PersonID | EntryDate | WeekNo | EntryNo | FullAmount | WeekCount |
==============================================================================
| 1 | 10 | 2015-09-07| 36 | 200 | 40.00 | 4 |
| 2 | 20 | 2015-09-07| 36 | 200 | 40.00 | 4 |
| 3 | 30 | 2015-09-07| 36 | 200 | 60.00 | 6 |
------------------------------------------------------------------------------
So... This table holds a record of weekly payments for a list of people.
Table Key:
PersonID: Is the FK of a table holding the personal info of each user.
EntryDate: Is the first monday on the month being paid. For example this month's first monday is the 7th.
WeekNo: Is the EntryDate's week number, literaly the 7th of September is week number 36 (counting the weeks of the year starting from the first monday of the year)
EntryNo: Is FK of another table that holds info about this particular batch, not related to the end problem, but worth mentioning.
FullAmount: Is a fixed yearly amount that also comes from another table, it only changes the 1st of January and for this example I set the value to 10.
WeekCount: holds the amount of weeks each person is paying, so since each week is $10.00 we get 4 for EntryID 1 and 2 and 6 for EntryID 3.
Then, once a month this data will update another table that holds 52 records per each person, one record per week of the year. This table will end up looking like this:
==============================================================================
| RecID | PersonID | PaidDate | WeekNo | EntryNo | PaidAmount | WeekStart |
==============================================================================
[...]
| 1 | 10 | 2015-09-10| 36 | 200 | 10.00 | 2015-09-07|
| 2 | 10 | 2015-09-10| 37 | 200 | 10.00 | 2015-09-14|
| 3 | 10 | 2015-09-10| 38 | 200 | 10.00 | 2015-09-21|
| 4 | 10 | 2015-09-10| 39 | 200 | 10.00 | 2015-09-28|
[...]
| 5 | 20 | 2015-09-10| 36 | 200 | 10.00 | 2015-09-07|
| 6 | 20 | 2015-09-10| 37 | 200 | 10.00 | 2015-09-14|
| 7 | 20 | 2015-09-10| 38 | 200 | 10.00 | 2015-09-21|
| 8 | 20 | 2015-09-10| 39 | 200 | 10.00 | 2015-09-28|
[...]
| 9 | 30 | 2015-09-10| 36 | 200 | 10.00 | 2015-09-07|
| 10 | 30 | 2015-09-10| 37 | 200 | 10.00 | 2015-09-14|
| 11 | 30 | 2015-09-10| 38 | 200 | 10.00 | 2015-09-21|
| 12 | 30 | 2015-09-10| 39 | 200 | 10.00 | 2015-09-28|
| 13 | 30 | 2015-09-10| 40 | 200 | 10.00 | 2015-10-05|
[...]
------------------------------------------------------------------------------
So basically my question is how can I UPDATE table 2 using the info on table one... I think It would be rather simple if all of the users where paying a fixed amount of weeks but since some pay more and some less I do not know hot to do it...
My method is taking about 4 minutes for doing this with 585 records (on table1)
Loop 1 Table1.$RecCount [LoopPos]
ExecSQL UPDATE Table2
SETPaidAmount = Table1.FullAmount/Table1.WeekCount,
PaidDate = GETDATE(),
WHERE PersonID = $CurrentPersonID
AND Year = $CurrentYear
AND WeekNo BETWEEN Table1.WeekNo AND Table1.WeekNo + Table1.WeekCount-1
Next Table1
end loop
I know code looks weird, I'm using some very dodgy RAD.. but basically I just loop through Table1 and UPDATE table2 with it... It works.. it just takes too long...
Any ideas?
September 11, 2015 at 6:00 am
At first glance, I would say that your performance issues are probably related to the using a loop in the first place, which results in records being updated one by one instead of as a set (also known as RBAR: Row By Agonizing Row; refer to the writing of Jeff Moden for more information).
You could also take a look at this article by Luis Cavares for more guidance: Introducing the Set-based Loop[/url]
September 11, 2015 at 8:10 am
Thank you for the reference on the article, but this problem doesn't seem to need a loop. A single update can do the job.
CREATE TABLE Table1(
EntryID int,
PersonID int,
EntryDate datetime,
WeekNo int,
EntryNo int,
FullAmount decimal(10,4),
WeekCount int
);
INSERT INTO Table1
VALUES
( 1, 10, '20150907', 36, 200, 40.00, 4),
( 2, 20, '20150907', 36, 200, 40.00, 4),
( 3, 30, '20150907', 36, 200, 60.00, 6);
CREATE TABLE Table2(
RecID int,
PersonID int,
PaidDate date,
Year int,
WeekNo int,
EntryNo int,
PaidAmount decimal(10,4),
WeekStart date,
);
INSERT INTO Table2
VALUES
( 1 , 10, '20150910', 2015, 36, 200, 10.00, '2015-09-07'),
( 2 , 10, '20150910', 2015, 37, 200, 10.00, '2015-09-14'),
( 3 , 10, '20150910', 2015, 38, 200, 10.00, '2015-09-21'),
( 4 , 10, '20150910', 2015, 39, 200, 10.00, '2015-09-28'),
( 5 , 20, '20150910', 2015, 36, 200, 10.00, '2015-09-07'),
( 6 , 20, '20150910', 2015, 37, 200, 10.00, '2015-09-14'),
( 7 , 20, '20150910', 2015, 38, 200, 10.00, '2015-09-21'),
( 8 , 20, '20150910', 2015, 39, 200, 10.00, '2015-09-28'),
( 9 , 30, '20150910', 2015, 36, 200, 10.00, '2015-09-07'),
( 10, 30, '20150910', 2015, 37, 200, 10.00, '2015-09-14'),
( 11, 30, '20150910', 2015, 38, 200, 10.00, '2015-09-21'),
( 12, 30, '20150910', 2015, 39, 200, 10.00, '2015-09-28'),
( 13, 30, '20150910', 2015, 40, 200, 10.00, '2015-10-05');
SELECT * FROM Table1;
SELECT * FROM Table2;
UPDATE t2
SET PaidAmount = t1.FullAmount/t1.WeekCount,
PaidDate = GETDATE()
FROM Table2 t2
JOIN Table1 t1 ON t2.PersonID = t1.PersonID
AND t2.Year = YEAR(GETDATE())
AND t2.WeekNo BETWEEN t1.WeekNo AND t1.WeekNo + t1.WeekCount-1;
SELECT * FROM Table2;
GO
DROP TABLE Table1;
DROP TABLE Table2;
Do you need to insert rows as well? If so, a merge should fit but I would need more information regarding how do you define the rows.
And please, post further sample data this way to avoid the need to create the scripts ourselves.
September 11, 2015 at 8:59 am
xynergy (9/11/2015)
Dude, I just went from 4 minutes to a few seconds on that task!Its pure awesomeness... Thanks both for your help... I'll carry on working on my frontend...
Cheers! :hehe:
A few seconds seems too slow for 585 rows. It should complete in under a second with the correct indexes.
September 12, 2015 at 1:22 pm
Luis Cazares (9/11/2015)
xynergy (9/11/2015)
Dude, I just went from 4 minutes to a few seconds on that task!Its pure awesomeness... Thanks both for your help... I'll carry on working on my frontend...
Cheers! :hehe:
A few seconds seems too slow for 585 rows. It should complete in under a second with the correct indexes.
Hi,
Yeah It was my bad...After the first run its working almost instantly...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply