RE: Performance of ODBC vs. other access methods

I don't see a problem.

Lots of sites have a software package designed to support multiple
companies/locations/ect but only have one currently.

IMHO, everything you add to such a system should respect the multi
company nature.  That means all your queries would should probably make
use of the company ID field.

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx 
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Holden Tommy
Sent: Wednesday, September 06, 2006 10:54 AM
To: Midrange Systems Technical Discussion
Subject: RE: Performance of ODBC vs. other access methods

Due to the fact that there are no indexes or access paths that do NOT
use that particular field as the primary key, an access path is built
every stinking time...building an access path takes much more cycles,
etc than using an existing access path especially when 
millions of rows
are involved.  For example, I can run a query minus the (useless)
primary key field over the audit file the query takes 15 mins to
complete (with 4 million rows) I can add the semi-useless 
primary field
to the query & it runs under a minute...(IMO that's an exponential
impact!!!) 


Thanks,
Tommy Holden


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Wednesday, September 06, 2006 9:38 AM
To: Midrange Systems Technical Discussion
Subject: RE: Performance of ODBC vs. other access methods

Interesting.  So, what makes you think that this has an exponential
impact 
on performance?

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Holden Tommy" <Tommy.Holden@xxxxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
09/06/2006 10:30 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc

Subject
RE: Performance of ODBC vs. other access methods






Nope...for example in the software there is a "Company ID" field.  The
software doesn't support multiple companies so the value is 
ALWAYS 1.  I
figure this was something that was planned but never implemented (or
it's the way it's implemented here... 


Thanks,
Tommy Holden


-----Original Message-----
From: midrange-l-bounces+tommy.holden=hcahealthcare.com@xxxxxxxxxxxx
[mailto:midrange-l-bounces+tommy.holden=hcahealthcare.com@midr
ange.com]
On Behalf Of rob@xxxxxxxxx
Sent: Wednesday, September 06, 2006 9:21 AM
To: Midrange Systems Technical Discussion
Subject: RE: Performance of ODBC vs. other access methods

Don't forget, key order is also important in WHERE clauses.  For
example, 
you may have a file with 90+% of the records have an "active 
record code

field" with a value of "A" and a few records with a value of "D".  A 
simple example is an item master.  While the item number should be a 
unique key, you may also want a key with active record code 
and then the

item number.  This way you can sort by the item number for all active 
records.  Maybe this is what you are seeing?

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Holden Tommy" <Tommy.Holden@xxxxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
09/06/2006 10:13 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc

Subject
RE: Performance of ODBC vs. other access methods






 <snip>
Use existing keys in your queries and you should 
be able to get sub-second response times. 
</snip>

That is an approach I have been trying desparately to put in 
place here.
Some of the files (read most...) have key fields that make 
absolutely no
sense (considering that the value is ALWAYS the same...)  the 
folks here
don't use the field in order by clauses etc.  Using the field since it
is the first key increases performance by exponential factors, I can
only assume that the developers here are afraid of typing an 
additional
6 characters.....


Thanks,
Tommy Holden
-- 
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.