| 10-15 seconds on a 200 record DataSet [message #402139] |
Thu, 07 May 2009 17:06  |
rboggess
Messages: 15
Registered: May 2009
Location: Pittsburgh
|
Junior Member |
|
|
I'm a process controls engineer developing an HMI in vb.net for a company that sells furnaces and software to efficiently control their furnaces. We don't actually operate any of those furnaces here.
As a part of the custom solutions we provide, we often times are asked to integrate our furnace automation software with an existing order tracking system. Most often, it's enough to make our data available in any database format, or not at all. In which case, we use SQL Server Express because it's free and it integrates well with vb.net, which is what all the HMIs are (well, nearly so).
This particular customer (and several following) are using Oracle, and usually those licenses (and the boxes they're installed on) are brand new, purchased by us, and delivered as part of the solution. In this case, however, it's a VMS box, and it's currently in use (which is often the case when it's VMS). So, we have a VMS box here with a copy of Oracle 9.1 running, just for development purposes.
I've seen several posts here, and more than I can count on the world wide web, about slow performance with DataSets, but none of them with a mere 200 records or so. And I had thought that it was because the database has never been tuned and it's running on a very old VMS box. It has several abandoned databases with tables, but no stored procedures and is not otherwise in use. And I don't (can't) tune it, and we don't have a DBA. We don't really care how slow it runs on our system, since we don't actually use it for anything but testing our software.
But as I'm learning more about how to use reference cursors (which is how it retrieves the dataset), I've learned how to create a ref cursor in a bind variable, allowing me to run the equivalent of my DataSet stored procedure in SQL Plus. The performance difference is staggering, and it has shaken my beliefe that this is just a tuning and equipment issue.
It takes roughly 1-2 (1.5 on average) seconds to establish the connection with oracle, so I can subtract that from the 10-15 seconds it takes to retrieve and fill the dataset. But when I run its equivalent from SQL Plus, it completes almost immediately. Does this mean that it's taking 8-12 seconds just to fill the DataSet? Or was my original guess that it's likely tuning and equipment that's causing the problem, and the DBA at the customer's site will be able to address this issue?
|
|
|
|
| Re: 10-15 seconds on a 200 record DataSet [message #402140 is a reply to message #402139] |
Thu, 07 May 2009 17:21  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>But when I run its equivalent from SQL Plus, it completes almost immediately.
This means the Oracle RDBMS is not the source of the delay/slow response.
> Does this mean that it's taking 8-12 seconds just to fill the DataSet?
I see the question but I am not sure what you mean by "To fill the Data Set".
It might help if you explain in as much detail as possible all the various parts & components between the DB & final destination for the data.
HMI? Human/Machine Interface?
[Updated on: Thu, 07 May 2009 17:26] Report message to a moderator |
|
|
|
| Re: 10-15 seconds on a 200 record DataSet [message #402267 is a reply to message #402140] |
Fri, 08 May 2009 05:05 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
No, something else is going wrong.
Either it is using a different execution plan, or when you run it in SQL*Plus you are just calculating the time to retrieve the FIRST row, not the full set.
I don't think there was a v9.1. Release 1 of 9i was 9.0 and release 2 was 9.2. You might have 9.0.1. If so, check the following documentation link for instructions on tracing a session. http://download.oracle.com/docs/cd/A91202_01/901_doc/server.901/a87503/sqltrace.htm#1018
Trace both the fast and slow examples (separately), produce the TKPROF output and compare them. They should tell you the difference. Post the results here if you are unsure what they mean.
Ross Leishman
|
|
|
|
| Re: 10-15 seconds on a 200 record DataSet [message #402289 is a reply to message #402267] |
Fri, 08 May 2009 08:03 |
rboggess
Messages: 15
Registered: May 2009
Location: Pittsburgh
|
Junior Member |
|
|
My bad. Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production.
HMI = Human Machine Interface, yes.
Details:
I'm told that VB.Net's OracleDataAdapter uses an OracleDataReader in the background to fill a DataSet. All I'm doing is assigning the stored procedure to the select command of the DataAdapter. The magic of dot net does the rest. Whatever that means.
Assuming it is the same execution plan -- keep in mind that we're only talking about 200 records -- it ends almost as quickly as it starts in SQL Plus. Maybe two seconds. Maybe. If it is the same execution plan, and it's taking roughly 12 seconds to return results through the data set, that would mean it's spending that extra time doing the dot net magic. And I wouldn't be able to fix that without resorting to a Data Reader and managing the retrieval manually.
As for the trace, I'll have to get back to you. Thank you for the reference. Today is a half-day, and I'm already fretting over many other issues on this one. I'll get back to you with the results. I can almost garuantee you that I won't understand the results, but even if I do, my experience with this aspect of Oracle is extremely limited, and I'd value your insight.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
