MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » December 2013

Re: SQL created table slows reporting tool



fixed

the CR is not the problem. The ERP is a very old one, written in the early
90's and heavily modified. What we are trying to do by starting this
Datawarehouse table is to take the most used data from all the many PF and
put into one and have this summarized eventually.

The users like the CR they are not going to purchase Sequel just now. I
have sent the info about Sequel to my boss a few months back.


On Thu, Dec 26, 2013 at 6:34 PM, Paul Nelson <nelsonp@xxxxxxxxxxxxx> wrote:

Considering the amount of effort you're putting into fighting with Crystal
Reports, You should think about generating (with RPG programs) a work file
each night that contains the data you want. Then, you can create queries
over that file to provide the users with what they want.

It doesn't sound like the data needs to have real-time availability. It
sounds like more of a data warehouse scenario.

Paul Nelson
Cell 708-670-6978
Office 409-267-4027
nelsonp@xxxxxxxxxxxxx


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Hoteltravelfundotcom
Sent: Thursday, December 26, 2013 5:04 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL created table slows reporting tool

I am not sure what to ask this vendor.So far he has just said yeah make
some logicals.

What I am to do is create some reports that are typically used by the
users. I am not sure do I make logical files now
For example, they run alot of open orders reports by month. and then some
according to product category.


On Thu, Dec 26, 2013 at 12:14 PM, Paul Nelson <nelsonp@xxxxxxxxxxxxx>
wrote:

Heh. If you know that the input file contains strictly new records, why
bother with that logic at all?

One of my clients wanted to create a couple of new companies and
duplicate
the records of what I'll call his master company for the two new
companies.

His software provider quoted him 40 hours for writing code to accomplish
this task. I did it in 4 hours just by knowing a few back door tricks I
learned back in the late 70's. He now has a SEQUEL script that prompts
him
for the original company number and his new company number. The script
takes
care of the rest.

It uses zero lines of code. One of the programmers at the software
provider
asked me to show him how I did it. He starts a new job in January at a
company that already has SEQUEL, and wanted to get a head start. :-))

Paul Nelson
Cell 708-670-6978
Office 409-267-4027
nelsonp@xxxxxxxxxxxxx


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, December 26, 2013 10:56 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL created table slows reporting tool

I hear you Paul. I have a tough time explaining to the OVER 40 crowd why
their updates take so long.
They read this huge input file. Instead of summarizing it by the
subtotals and only writing at subtotal time they do a
read
if no record found,
write
else
update
endif
Thus having over a million extra I/O's.
It's like they've never heard of control breaks, total time, etc.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "Paul Nelson" <nelsonp@xxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
Date: 12/26/2013 11:37 AM
Subject: RE: SQL created table slows reporting tool
Sent by: midrange-l-bounces@xxxxxxxxxxxx



<< For example, if you have 200 indexes on one physical file then every
time
you add a row to that table it has to update all 200 indexes.>>

Not to mention that if the physical is keyed, the index area for the file
itself needs to be updated. It seems I spend a third of my time
explaining
this to the under 40 programming crowd that whines about performance when
they're loading bazillions of records to a keyed PF that has many dozens
of
logicals, indexes, and views over it.

Me: "Did you sort the input data first?"
Them: "No, why?"
Me: "Do you understand the concept of an index area?"
Them: "No"
Me: "Does your process remove the logical file members before loading the
data?"
Them: "No, why?"
Me: "What is the DBMS doing if you don't take these steps?"
Them: "Ummm"

And so on, and so on. This happens every time the outsourcer hires a new
batch of bodies to replace the previous bodies that jumped ship for an
extra
14 cents per hour.

Paul Nelson
Cell 708-670-6978
Office 409-267-4027
nelsonp@xxxxxxxxxxxxx


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, December 26, 2013 10:22 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL created table slows reporting tool

The caution about indexes willy-nilly applies to both SQL indexes and DDS
logical files. The basic caution is that indexes put maintenance on the
system. For example, if you have 200 indexes on one physical file then
every time you add a row to that table it has to update all 200 indexes.
This used to be pounded in our heads. I'd mention the IBMer, he deserves
the credit, but too many would drift off into a geezerfest. This concern
is supposed to be somewhat alleviated by newer versions of the OS. Many
names, long respected in the industry, have said so. However there are
those who will reply that they do not care what those people say - when
they reorg a large table with a lot of indexes it runs much longer than
deleting all the indexes first.

Keeping that concern in mind, I think you would be SAFER to use the sql
indexes recommended by iNav over the DDS logical. The temptation to put
stuff in the logical file that really shouldn't be there would be
eliminated. That, and SQL tends to use more modern defaults, such as
page

size and whatnot. While DDS tends to carry over defaults best left
behind

when we all migrated off of AS/400's.

It's up to you to communicate with your change management vendor to see
how they could capture such a creation, and if not, how to best put this
into the process.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact