I was trying to evaluate the ODBC interface at our company compared to the native Access database. As part of this evaluation I wanted to benchmark WebFOCUS interfaces. I found some rather interesting results and some questions.
I used the same 6.8 Million record file using 96 byte records. The query was very simple, a TABLEF to a binary hold file. I tried this experiment on both our production and development server.
The Servers:
Dev - VM, Single CPU, Win 2003, Xeon 3.2 Ghz, 2 gig RAM, Raid 5 15K SCSI
Prod - Win 2000, Dual CPUR, Xeon 3.2 Ghz, 4 gig RAM, Raid 5 15K SCSI

Both machines were tested on WebFOCUS 5.3.3

The results:

  
                     CPU                         Wall            
Server     File Type Time  Recs/Sec    Recs/Min  Time  Recs/Sec  Recs/Min
Dev        WebFOCUS   22    310,864  18,651,821   55    124,345   7,460,728 
           Fixed      53    129,038   7,742,265   59    115,915   6,954,916
           Access    127     53,850   3,231,024  216     31,662   1,899,723
           ODBC      183     37,372   2,242,295  265     25,808   1,548,453
           Sybase    281     24,221   1,453,270  309     22,026   1,321,583
                                                                 
Prod       WebFOCUS   35    195,400  11,724,002  101     67,713   4,062,773 
           Fixed      48    142,479   8,548,751   48    142,479   8,548,751 
           Sybase    187    36,397    2,183,791  200     34,031   2,041,845

The statistics show both CPU and Wall clock time to control for network and Sybase server loads. We used ODBC to read a local Access database. What was surprising was that ODBC was faster than the native Sybase adapter. I also found WebFOCUS file performance was significantly faster on the development machine than the specs on the CPU suggest. Our production machine was significantly faster for Sybase access which begs the question, how can we best tune our development machine?

This message has been edited. Last edited by: Edan,

The MS Access db is on the same box.

This message has been edited. Last edited by: Edan,

Edan,

One question and one observation.

Q. If I had to guess, I'd say you benched the Sybase adapter first?

O. Win 2003 on Dev box vs. Win2k on Prod. Win 2003 is SIGNIFICANTLY optimized compared to WIN2k.

Kevin

Kevin,

Actually, on our development system, I benchmarked ODBC first. I assume you said this because of the OS cache. This would only cause the wall clock time to be close to the CPU. This only occured in the case of the Fixed file since it was the result of a prior table. Since the data sources were different the cache would not influence any other result.

Edan

Edan,

Performance tuning for different environments seems to be a common challenge. All of our reports use SQL passthru with WebFOCUS against our DB2 databases which gives us greater control of the SQL that is passed. In benchmarking, we have discovered (not really to anyone's surprise) that even though our PROD environment has WAY more horsepower than UAT or DEV, the sheer amount of data housed in PROD alters the DB Access plans that DB2 uses to retrieve the data. What works efficiently in DEV and UAT, does not necessarily work efficiently in PROD. Also, the time the DB spends creating the access plan (overhead) is different between all of our environments due to the numbers of rows difference in those environments. I could make a strong argument for stored procs, but then we would have to get our DBA's involved every time a requirement was updated. We due notice that DB2 seems to cache and reuse it's access plans if the same request is repeated within a short timeframe.

Keep us posted on your research and if you have time, maybe some more details of the test architecture you used will help. I'm very interested in this tuning topic and looking forward to reading feedback from others as well.


Thanks,

Kevin