June 11, 2008 at 11:04 am
Second that.
From your code, the main part is:
*****************
SELECT @PNo = min(PNo) FROM #PT
WHERE PDone = 0
UPDATE #PT SET PDone = 1 WHERE PNo = @PNo
UPDATE PT SET PDone = 1 WHERE PNo = @PNo
********************
So, why not use:
UPDATE PT SET PDone = 1 WHERE PDone = 0 ???
If the stored procedure is re-written to work off of a set, there will be no need for this.
Old Method:
1. Create Temp table
2. Load temp table with working set
3. loop through temp table
a. Call SP for to work current row in working set.
b. Mark current row as 'done'
4. loop untill all rows in temp table are marked 'done'.
New Method:
1. Call SP that works off of the entire SET of data instead of 1 row only.
2. There is no step #2. All processing done to the set in step #1.
June 11, 2008 at 5:14 pm
Peso (6/11/2008)
I believe you are wrong Sergiy, again.
Happy to brighten you day!
What else could bring some joy into your life?
😀
Yes, aggregation does "SET" in your query and I missed it. It was "2am thing".
And yet again you proved you're the champion in providing bad solutions I would not consider worth testing.
Congratulations!
Your "perfect" loop is still performing worse than a cursor.
And it will always be worse than a cursor, no matter how much effort you'll put to improve it.
You gave your customer what he was asking for, instead of solving his problem.
Believe me, I'm not gonna compete with you on this field.
I don't code many loops and I don't have any intention to do it in future.
I prefer to replace loops with proper T-SQL solutions.
So, you may stay proud of yourself: your solutions are probably the most perfect bad solutions in the world!
😛
_____________
Code for TallyGenerator
June 11, 2008 at 10:10 pm
I accept your apologize. I believe you are sincere about it.
As for the original question, we don't really know what the loop/cursor is for, do we?
All we know is that OP wrote that he calls another SP that "reacts" to this @PNo value.
How can you be so sure that there is a set-based solution available at all?
Maybe the loop is for doing some BCP according to @PNo?
Maybe OP wants to send an email to each and one @PNo with some resultset?
And having a global cursor is not a good idea in terms of concurrency, is it?
We don't know if the procedure is part of a batch or it could be called simultaneously, do we?
If it can be called simultaneously, having a cursor, or a temp table, is then not a good idea because both executions can see same record as available (PDone=0) which can lead to same @PNo is treated twice or more, leading to create two duplicate files or dulplicate emails to same @PNo.
If this is the case, a while loop avoids both problems at the cost of a sligthly slower procedure.
Which is then "better"?
1) Faster code but doing wrong/duplicate things
2) Slower code doing the right thing?
If the code is run once a day, I personally don't mind the procedure taking some seconds longer if it does the right things. If the procedure is called twice a second, then performance is vital.
OP hasn't told us anything of schedule.
You make too many assumptions my friend.
Pleae feel free to continue monitor my posts and don't be afraid to share your constructive opinion, like I did when I spotted your triangular join recently.
N 56°04'39.16"
E 12°55'05.25"
June 12, 2008 at 2:28 am
Well I can tell you that the PNo is passed to an SP, and that this SP then analyses that PNo, and depending on the type etc it will copy records from 12 other tables into an Archive database, or delete them for good.
The issue with doing this in one big chunk I feel is that if it goes wrong, thats 40,000 records in one hit. Individually you can track each PNo, and its better on the transaction logs as well isn't it?
Please correct me If I'm wrong.
June 12, 2008 at 3:07 am
Thank you for your feedback.
I think this can be done with 12 insert statements (one for each source table to archive target table).
You can process all records for a table at once, checking the Pno and insert the records validating the criteria.
Then you can delete all records from that source table depending on same critera.
But then again, it depends on how the information about PNo is gathered, and how often this routine is executed.
N 56°04'39.16"
E 12°55'05.25"
June 12, 2008 at 3:27 am
It's a once a week thing. I guess if I stick a commit on it then there should be no harm done actioning each task in one hit.
June 12, 2008 at 3:43 am
Shark Energy (6/12/2008)
The issue with doing this in one big chunk I feel is that if it goes wrong, thats 40,000 records in one hit. Individually you can track each PNo, and its better on the transaction logs as well isn't it?
Do you mean you're sitting over there and watching the process???
What is the computer for then?
We all are paid to PROGRAM computers to work.
Not to watch what computers are doing.
Build a program, test it debug it, deploy and go home, get your life while computer you've programmed earns you money.
And if you're so worried about 40k rows to delete (valid point actually) then just reduce intervals between deletes. Do it not once per month but twice per week.
On my systems (they are 24/7 systems and I don't have a luxury of overnight processing) I move data to archive database every 2 hours.
Believe me, the performance impact is absolutely unnoticeable.
_____________
Code for TallyGenerator
June 12, 2008 at 3:51 am
Nightly wouldn't be out of the question. I didn't mean I sit and watch each one by the way.
Initially we are catching up on 2 years of archiving. That's one issue. But once that is out of the way we could go with overnight.
Cheers for all the help everyone.
EDIT: Sergiy - Out of interest, how do you manage the possibility of a user viewing a record that you are archiving? I wouldn' have this issue out of hours, but I wonder how you manage it with the 24/7 system.
June 12, 2008 at 5:35 am
Don't do 2 years.
Do 1 week at a time. According to your estimation 1 week takes about 400 rows - nothing really.
Run it once per 2 hours - and you're gonna clean 2 month per night, 2 years in 2 weeks.
No sweat.
As for archiving - not a big deal at all.
Records are copied to archive first, then pointers are updated, then records are deleted.
I did not invent anything here.
Find any article about how NTFS works - the file system holding all your files, including databases. How does it manage access to sectors being copied? And it does not take any breaks for overnight maintenance, right?
_____________
Code for TallyGenerator
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply