Success !! (was RE:Indexed or Seq?)
Deskin, Bob
Bob.Deskin at Cognos.COM
Wed Aug 24 07:12:38 CDT 2005
I'd say that's a significant improvement. Thanks for the feedback.
Bob
-----Original Message-----
From: powerh-l-bounces+bob.deskin=cognos.com at lists.sowder.com
[mailto:powerh-l-bounces+bob.deskin=cognos.com at lists.sowder.com]On
Behalf Of fernando.olmos at hpa.com.au
Sent: August 23, 2005 6:44 PM
To: powerh-l at lists.sowder.com
Subject: Success !! (was RE:Indexed or Seq?)
Bob, Darren, Joe and Conrad:
I have finished the project and I have made huge in-roads into the
performance issues with this particular program. In short, the program
took over 12 HOURS to run on some end of months, and I have reduced it
to under 10 minutes!! Thank God for SQL cursors!
Here are the times from the original batch run (only one of the QUIZ
programs I changed):
: quiz cc='(closeoff,vic)' auto=../mipsz110i nolist
..
..some parameter passing here
..
Producing the report please wait ...
Tue Aug 23 14:47:36 EST 2005
> GO
Records selected: 11
Records sorted: 11
Records written: 3
Tue Aug 23 14:57:21 EST 2005
Tue Aug 23 14:57:21 EST 2005
Records selected: 0
Records sorted: 0
Tue Aug 23 15:09:00 EST 2005
Tue Aug 23 15:09:00 EST 2005
> GO
Records selected: 3579
Records sorted: 3579
Records written: 3
Tue Aug 23 15:09:08 EST 2005
Tue Aug 23 15:09:08 EST 2005
> GO
Records selected: 3
Records sorted: 3
Records written: 3
Tue Aug 23 15:09:08 EST 2005
Tue Aug 23 15:09:08 EST 2005
Records selected: 3
Records written: 3
Tue Aug 23 15:09:08 EST 2005
Tue Aug 23 15:09:08 EST 2005
Records selected: 100
Records sorted: 100
Records written: 46
Tue Aug 23 15:09:43 EST 2005
Tue Aug 23 15:09:43 EST 2005
> GO
Records selected: 268
Records sorted: 268
Records written: 46
Tue Aug 23 15:09:43 EST 2005
Tue Aug 23 15:09:43 EST 2005
Records selected: 6
Records sorted: 6
Records written: 6
Tue Aug 23 15:09:43 EST 2005
Tue Aug 23 15:09:43 EST 2005
Records selected: 1215
Records sorted: 1215
Records written: 37
Tue Aug 23 15:13:46 EST 2005
Records selected: 49
Records sorted: 49
Lines printed: 183
Pages printed: 11
<end>
And here is the new version ...
: quiz auto=/kanga2/mis/mips/src/mipsz110i.qzs nolist
cc='(closeoff,vic)'
..
..again, some parms here
..
Producing the report please wait ...
Tue Aug 23 16:34:21 EST 2005
Records selected: 11
Records written: 3
Tue Aug 23 16:34:21 EST 2005
Tue Aug 23 16:34:21 EST 2005
Records selected: 0
Records sorted: 0
Tue Aug 23 16:34:22 EST 2005
Tue Aug 23 16:34:22 EST 2005
Records selected: 2399
Records sorted: 2399
Records written: 3
Tue Aug 23 16:34:26 EST 2005
Tue Aug 23 16:34:26 EST 2005
Records selected: 3
Records sorted: 3
Records written: 3
Tue Aug 23 16:34:27 EST 2005
Tue Aug 23 16:34:27 EST 2005
Records selected: 3
Records written: 3
Tue Aug 23 16:34:27 EST 2005
Tue Aug 23 16:34:27 EST 2005
Records selected: 100
Records sorted: 100
Records written: 46
Tue Aug 23 16:34:46 EST 2005
Tue Aug 23 16:34:46 EST 2005
Records selected: 268
Records sorted: 268
Records written: 46
Tue Aug 23 16:34:46 EST 2005
Tue Aug 23 16:34:46 EST 2005
Records selected: 6
Records sorted: 6
Records written: 6
Tue Aug 23 16:34:46 EST 2005
Tue Aug 23 16:34:46 EST 2005
Records selected: 1215
Records sorted: 1215
Records written: 37
Tue Aug 23 16:37:24 EST 2005
Records selected: 49
Records sorted: 49
Lines printed: 183
Pages printed: 11
<end>
As you can see, the original program took around 30mins to run, and this
was based on a month's worth of data, which is production data and based
on real time spans. I ran the original program across 12 months and it
took over 2 hours!!
Now the progam takes less than 4 minutes. And that's without any new
indexes. It could go faster but the usual project deadlines and costs
approval saga forced my hand to deliver something significantly better,
but not trully the best, which I was hoping for. I could make every pass
an SQL cursor, which is what I wanted, but considering some passes took
less than 1 second to run in their original form, I figured.... why
spend hours to save a few milliseconds? Oops.... I can see some of you
having differing views on this already! hehehe
Essentially I used one main SQL cursor as a template and declared the
WHERE clause a variable to alter during different passes. Certainly the
question of whether brackets around the WHERE conditions was attempted,
but yielded little benefit for me. What I noticed improved this a lot
was the fact that I am only selecting the columns I require and NOT the
entire table structure. The SORT is the real culprit of eating into CPU
time, and I changed many sorts to SORTED where possible.
I also overcame the problem of accessing many tables from more than two
databases using two of more SQL cursors and linking them in a single
ACCESS statement. Mind you, I which I could have declared the WHERE
clause somewhere AFTER the ACCESS statement because I would have liked
to have used some define parms to control the filtering of data.
Therefore, I left some SELECT IF... on their own and took what I could
out of them and merged it into the SQL cursor to speed things up.
If you want me to show you explicit examples of the code - minus company
"secrets", please let me know.
A special thanks to Darren and Joe for persisting with examples on how
to use variables and cursors.
Cheers
**********************************************************************
IMPORTANT
The contents of this e-mail and its attachments are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you received this e-mail in error, please notify the HPA Postmaster, postmaster at hpa.com.au, then delete the e-mail.
This footnote also confirms that this e-mail message has been swept for the presence of computer viruses by MimeSweeper. Before opening or using any attachments, check them for viruses and defects.
Our liability is limited to resupplying any affected attachments.
HPA collects personal information to provide and market our services. For more information about use, disclosure and access see our Privacy Policy at www.hpa.com.au
**********************************************************************
--
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l at lists.sowder.com
Subscribe: "subscribe" in message body to powerh-l-request at lists.sowder.com
Unsubscribe: "unsubscribe <password>" in message body to powerh-l-request at lists.sowder.com
http://lists.sowder.com/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.
This message may contain privileged and/or confidential information. If you have received this e-mail in error or are not the intended recipient, you may not use, copy, disseminate or distribute it; do not open any attachments, delete it immediately from your system and notify the sender promptly by e-mail that you have done so. Thank you.
More information about the powerh-l
mailing list