Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: IIS and Oracle performance problems

Re: IIS and Oracle performance problems

From: Marty <mphee_at_jump-technologies.com>
Date: 11 Jan 2002 07:34:29 -0800
Message-ID: <5ff7c081.0201110734.4150068a@posting.google.com>


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 

end Function

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)

End Function

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

End Function

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US