| Author |
Message |
Bryant Likes
Guest
|
 Posted:
Thu Sep 23, 2004 5:19 am Post subject:
HIS 2004 Performance |
|
|
Hello,
Well I was able to get the HIS 2004 stuff working on our Development AS/400
DB2 server. The next step was to run some tests for the AS/400 guys so that I
could demonstrate why we would benefit from using HIS 2004.
The first batch of tests I ran compared performance of the HIS 2004 managed
provider, the IBM DB2 ODBC provider, and the IBM DB2 OLEDB provider. My tests
showed a performance boost of about 25% over ODBC and 50% over OLEDB. This
gave the AS/400 guys some motivation to put HIS 2004 packages on the
production server.
However, the second batch of tests had me scratching my head a little. The
second batch tested the performance of these same drivers using SQL Server
DTS. Here instead of small result sets (< 100,000 rows) I was using some
medium size result sets (~ 5,000,000 rows). The result was that the HIS
connection got beat by the ODBC connection by about 50%.
My question is, are there any guides to using HIS 2004 with SQL Server DTS?
How should I tune this? Will I gain anything if I install the HIS Service on
the SQL Server machine?
Any hints would be great.
Thanks for the help!
--
Bryant |
|
| Back to top |
|
 |
Aaron Grady [MSFT]
Guest
|
| Posted:
Thu Sep 23, 2004 11:38 pm Post subject:
Re: HIS 2004 Performance |
|
|
Bryant-
When using DTS, I assume you are not using the Managed Provider for DB2. Are
you using the HIS Ole Db Provider or ODBC driver? We generally recommend
that you use the Ole Db provider with SQL Server. Are you
Inserting/updating/selecting? What data types are bieng used? Are you
connecting over SNA or directly over TCP/IP? If you take a look at what's
going on can you see lots of connections being made to the AS/400, or one
connection with lots of work being done? If lots of connections are being
made than making sure connection pooling is turned on and working could make
a huge performance improvement. With performance issues, connection pooling
is the only "generic" answer that I can really provide. Other than that,
there are several factors here and you would basically need to isolate which
piece is the bottleneck. One thing I might recommend is taking some DB2
Network Library traces (using snatrace.exe) and examining them to see if
they can provide any clues, or installing Network Monitor and observing the
interaction between the Windows server and the AS/400 to see where more time
is being spent. You can also take these traces with the IBM providers and
compare to see if there might be something set differently in the two
drivers causing the discrepency.
If you post back more details I can try and help, or you may want to
consider opening a support case so that one of our support engineers can
actually examine your traces and help identify any possible performance
tuning that can be done.
HTH,
--
Aaron Grady
MCSE + Internet, MCDBA, MCSD
SNA Server/Host Integration Server 2000
Microsoft
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of any included samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bryant Likes" <BryantLikes@discussions.microsoft.com> wrote in message
news:41EAF953-3802-406C-8E98-59D403A07374@microsoft.com...
| Quote: | Hello,
Well I was able to get the HIS 2004 stuff working on our Development
AS/400
DB2 server. The next step was to run some tests for the AS/400 guys so
that I
could demonstrate why we would benefit from using HIS 2004.
The first batch of tests I ran compared performance of the HIS 2004
managed
provider, the IBM DB2 ODBC provider, and the IBM DB2 OLEDB provider. My
tests
showed a performance boost of about 25% over ODBC and 50% over OLEDB. This
gave the AS/400 guys some motivation to put HIS 2004 packages on the
production server.
However, the second batch of tests had me scratching my head a little. The
second batch tested the performance of these same drivers using SQL Server
DTS. Here instead of small result sets (< 100,000 rows) I was using some
medium size result sets (~ 5,000,000 rows). The result was that the HIS
connection got beat by the ODBC connection by about 50%.
My question is, are there any guides to using HIS 2004 with SQL Server
DTS?
How should I tune this? Will I gain anything if I install the HIS Service
on
the SQL Server machine?
Any hints would be great.
Thanks for the help!
--
Bryant |
|
|
| Back to top |
|
|
Bryant Likes
Guest
|
| Posted:
Fri Sep 24, 2004 1:03 am Post subject:
Re: HIS 2004 Performance |
|
|
"Aaron Grady [MSFT]" wrote:
[snip]
| Quote: | When using DTS, I assume you are not using the Managed Provider for DB2. Are
you using the HIS Ole Db Provider or ODBC driver?
|
I'm using the HIS Ole DB Provider.
[snip]
| Quote: | Are you
Inserting/updating/selecting?
|
I'm doing a data transfer of just over 5 million rows from an AS/400 table
to a table on a SQL Server.
| Quote: | What data types are bieng used?
|
char, varchar, decimal, int, and numeric
| Quote: | Are you
connecting over SNA or directly over TCP/IP?
|
Directly over TCP/IP.
| Quote: | If you take a look at what's
going on can you see lots of connections being made to the AS/400, or one
connection with lots of work being done?
|
How would I go about this? I'm assuming it is one connection with lots of
work being done (since I don't see how it could do it any other way).
[snip]
| Quote: | With performance issues, connection pooling
is the only "generic" answer that I can really provide. Other than that,
there are several factors here and you would basically need to isolate which
piece is the bottleneck.
|
Thanks. It seems that the HIS OLE DB provider is just slower than the ODBC
provider. When I went to an HIS 2004 session at TechEd they talked about how
the HIS provider was just as fast if not faster than the IBM driver. My tests
showed otherwise and so I was curious as to why this is.
I know you probably can't give me exact numbers, but what do you guys see
when you run your own tests? Is the HIS provider generally on par with IBM's
providers?
The answer to that would solve my question. If you guys are seeing numbers
that are on par with IBM, then I'm probably not doing something correctly. If
you show the same type of numbers that I'm seeing, then I would be wasting my
time trying to tune it.
Thanks..
--
Bryant |
|
| Back to top |
|
|
Aaron Grady [MSFT]
Guest
|
| Posted:
Tue Sep 28, 2004 5:55 am Post subject:
Re: HIS 2004 Performance |
|
|
Bryant-
Yes, sounds most likely that this is simply one select which is happening
over one connection so connection pooling probably isn't a factor. One other
thing to check is the DBMS platform setting on the HIs provider. Making sure
this is set to DB2/AS400 should make sure that an extra conversion process
is not occurring which can also slow things down.
As for comparison numbers I can only provide limited info, but here are some
things to understand:
1. We mainly run comparison numbers against IBM DB2 Connect. IBM also makes
Client Access specifically for the AS/400 which uses a different server
program on the AS/400. IBM DB2 Connect can connect to all flavors of DB2
(not just AS/400) as can HIS and this is what we mainly compare ourselves
against.
2. We do not have any published performance comparison numbers. For HIS 2000
we did internally run a number of performance comparisons against DB2
Connect and we did find in our results that we performed on par or better
than DB2 connect in almost every task. However, as we did not publish this
information, I cannot quantify it and give you the details. What would be
best is if a third party group were to run some performance benchmarks and
publish this info, but I have not seen anything of that sort.
3. For HIS 2004 we have run performance tests which show improvements
against HIS 2000.
Performance is a tricky area and it can vary widely for each customer
environment. I would still encourage you to start a support case with our
support team so they can actually examine some traces from your system to
see if there are any bottlenecks in our provider or elsewhere that can be
eliminated.
HTH,
--
Aaron Grady
MCSE + Internet, MCDBA, MCSD
SNA Server/Host Integration Server 2000
Microsoft
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of any included samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bryant Likes" <BryantLikes@discussions.microsoft.com> wrote in message
news:29E66B76-936F-4372-8C4E-8F7A11A26031@microsoft.com...
| Quote: | "Aaron Grady [MSFT]" wrote:
[snip]
When using DTS, I assume you are not using the Managed Provider for DB2.
Are
you using the HIS Ole Db Provider or ODBC driver?
I'm using the HIS Ole DB Provider.
[snip]
Are you
Inserting/updating/selecting?
I'm doing a data transfer of just over 5 million rows from an AS/400 table
to a table on a SQL Server.
What data types are bieng used?
char, varchar, decimal, int, and numeric
Are you
connecting over SNA or directly over TCP/IP?
Directly over TCP/IP.
If you take a look at what's
going on can you see lots of connections being made to the AS/400, or
one
connection with lots of work being done?
How would I go about this? I'm assuming it is one connection with lots of
work being done (since I don't see how it could do it any other way).
[snip]
With performance issues, connection pooling
is the only "generic" answer that I can really provide. Other than that,
there are several factors here and you would basically need to isolate
which
piece is the bottleneck.
Thanks. It seems that the HIS OLE DB provider is just slower than the ODBC
provider. When I went to an HIS 2004 session at TechEd they talked about
how
the HIS provider was just as fast if not faster than the IBM driver. My
tests
showed otherwise and so I was curious as to why this is.
I know you probably can't give me exact numbers, but what do you guys see
when you run your own tests? Is the HIS provider generally on par with
IBM's
providers?
The answer to that would solve my question. If you guys are seeing numbers
that are on par with IBM, then I'm probably not doing something correctly.
If
you show the same type of numbers that I'm seeing, then I would be wasting
my
time trying to tune it.
Thanks..
--
Bryant |
|
|
| Back to top |
|
|
|
|
|
|
FAQ
Memberlist
Register
Profile
Log in to check your private messages
Log in