RE: Performance of ODBC vs. other access methods

Agree 100% that "it depends".  We were doing some reporting from
EnterpriseOne using Crystal Reports via ODBC.  Horrible performance.
3.5-4 minutes with one user and if there were >5 concurrent users
results wouldn't be returned before the client's timed out (>10
minutes).  The SQL statements were rather complex (about 4 pages printed
with no whitespace) and had to de-normalize portions of the normalized
database.  They also had lots of TRIMs and other things that forced the
query optimizer to not be as efficient as it could be.

We worked with Rochester to tune the SQL and tune the database.  A lot
of analysis and about 5 LFs later the SQL statement coming from Crystal
was drastically simpler (<1 page) and user response times dropped to
under 4 seconds.  We're still not sure how well it'll scale, but it
appears scalability issues will likely exist on the Crystal side before
I get too worried about the iSeries.

BTW, the iSeries is a 570 with 1.5 CPUs (~4800 CPWs), 16GB RAM, and 36
15K RPM disks in the LPAR and does have the DB2/SMP LPP.  The Crystal
Enterprise server is on Windows on a dual Xeon with 4GB RAM.  Right now
they're WAN connected but the pipe is nowhere near capacity and will be
upped to near Gb anyway.

John A. Jones, CISSP
Americas Information Security Officer
Jones Lang LaSalle, Inc.
V: +1-630-455-2787 F: +1-312-601-1782
john.jones@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
richard@xxxxxxxxxxx
Sent: Thursday, August 31, 2006 9:40 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Performance of ODBC vs. other access methods

This is one of those loaded questions that I have to answer:  "It
depends on what you want to do and how well your database is indexed". 

If you are doing lots of queries where there are existing indexes, the
performance can be be sub-second. 

If you are doing lots of queries where there are no indexes it will be
slow as molasses. 

Tell us more about what you're trying to do and also start doing some
real-world experimentation. 

No better way to learn than to get your feet wet. 

Regards,
Richard Schoen
RJS Software Systems Inc. 
"Providing Your....iNFORMATION NOW!"
Email: richard@xxxxxxxxxxxxxxx
Web Site: http://www.rjssoftware.com
Tel: (952) 898-3038
Fax: (952) 898-1781
Toll Free: (888) RJSSOFT

------------------------------------------------------------------------
-----------------------
What's the prevailing wisdom backed up by real world experience when
using ODBC from whatever tool or programming language to access DB2/400
or ORACLE versus using some other remote or distributed access method
such as DRDA, calls to stored procedures or API calls?   I've been told
that ODBC is a good performer but have my doubts.

What's your experience show vs Ivory Tower tests?

Thanks in advance,
--
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 email is for the use of the intended recipient(s) only.  If you have 
received this email in error, please notify the sender immediately and then 
delete it.  If you are not the intended recipient, you must not keep, use, 
disclose, copy or distribute this email without the author's prior permission.  
We have taken precautions to minimize the risk of transmitting software 
viruses, but we advise you to carry out your own virus checks on any attachment 
to this message.  We cannot accept liability for any loss or damage caused by 
software viruses.  The information contained in this communication may be 
confidential and may be subject to the attorney-client privilege. If you are 
the intended recipient and you do not wish to receive similar electronic 
messages from us in future then please respond to the sender to this effect.