Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: IIS and Oracle performance problems
Well, I wish we could use ADO/OraOLEDB, but we didn't write the
website and I can't change it since it's all COM objects.
The two types of providers I've been trying. Application("GMS_DB") = "Provider=MSDAORA;Password=xxxx;User ID=xxxx;Data Source=xxxx;Persist Security Info=True" Application("GISB_DB") = "Provider=MSDAORA;Password=xxxx;User ID=xxxx;Data Source=xxxx;Persist Security Info=True"
'Application("GMS_DB") = "Provider=oraoledb.oracle;Password=xxxxx;User
ID=xxxxx;Data Source=xxxx;Persist Security Info=True"
'Application("GISB_DB") =
"Provider=oraoledb.oracle;Password=xxxxx;User ID=xxxxx;Data
Source=xxxx;Persist Security Info=True"
Here is the connect off of my test page: Function GetConnection(adoDB)
Set adoDB = server.CreateObject("ADODB.Connection") adoDB.ConnectionString = "Provider=MSDAORA.1;Password=xxxxx;User ID=xxxx;Data Source=xxxx;Persist Security Info=True" adoDB.ConnectionTimeout = 15 adoDB.CommandTimeout = 30 adoDB.CursorLocation = 3 adoDB.Open
Here is my SQL:
Function GetDetailTrans()
dim zSQL
'zSql = "Select contract, partner, volume, sum(amount), transsource, costid, lastchange from detailtrans where prodmonth=10 and prodyear=2001"
zSql = "Select contract, partner, sum(volume) vol, sum(amount) amt, transsource, costid " & _
"from some_table " & _ "where prodmonth=10 and prodyear=2001 " & _ "group by contract, partner, transsource, costid " Set GetDetailTrans=GetRecordSet(zSQL)
Here is the function to get the results: Function GetRecordset(byval zSQL)
dim rsData dim adocnDB on error resume next 'Get the connection from the database GetConnection adocnDB if Err.number <> 0 THEN ErrorTrap zSQL exit function END IF 'Create a temporary recordset in this function set rsData= adocnDB.Execute(zSQL) if Err.number <> 0 THEN response.write "After execute" ErrorTrap zSQL exit function END IF 'Remove the connection to the recordset and close the DB connection rsData.ActiveConnection=nothing adocnDB.Close set adocnDB=Nothing 'Set the return of the function to the recordset retrieved Set GetRecordset=rsData set rsData=Nothing
Start Java:
start_mills = System.currentTimeMillis();
DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver() );
System.out.println("Creating a connection."); m_cn = DriverManager.getConnection("jdbc:oracle:oci8:@xxxx", "xxxx", "xxxx" );
end_mills = System.currentTimeMillis();
diff_mills = end_mills - start_mills;
System.out.println("Done with connection:" + String.valueOf( diff_mills/1000 ));
zSql = "Select contract, partner, sum(volume) vol, sum(amount) amt, transsource, costid " +
"from some_table " + "where prodmonth=10 and prodyear=2001 " + "group by contract, partner, transsource, costid ";
Statement stmt = m_cn.createStatement ();
start_mills = System.currentTimeMillis();
ResultSet rset = stmt.executeQuery (zSql);
end_mills = System.currentTimeMillis();
diff_mills = end_mills - start_mills;
System.out.println("Done with query:" + String.valueOf( diff_mills/1000 ));
Thanks for any help,
Marty
"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3c3ec55f$0$232$ed9e5944_at_reading.news.pipex.net>...
> Can you show us your code?
>
> If you are using asp I would definitely recommend one of ADO/OraOLEDB or
> oo4o
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
> "Marty" <mphee_at_jump-technologies.com> wrote in message
> news:5ff7c081.0201100955.6ded9232_at_posting.google.com...
> > Have a lot of performance problems with IIS and Oracle.
> >
> > Setup
> > w2k sp2
> > Oracle 8.1.7
> > Webserver load is usually less than 5%
> >
> > I wrote a test ASP page to connect to the database, and then run a
> > query. The connection time was 1.515 seconds. The query time was
> > 3.422 seconds. IIS is uning MSDAORA, and I created a VBS script to do
> > the same thing. The results of the VBS script were the same.
> >
> > I then wrote a java app to run the exact same query. Connection time
> > was around the same, but the query time was .170 seconds and .223
> > seconds after two runs. The java app is using oracle oci8 JDBC.
> >
> > Any idea's?
> >
> > Thanks for the help,
> >
> > Marty
Received on Fri Jan 11 2002 - 09:34:29 CST