SQL Server Vs Oracle Performance

Hi Guys, first time posting here. I wrote some code to test the performance of VB when accessing an oracle database or a SQL server database. For some reason, my test seem to indicate that VB can access our SQL server database running on a server with dual Pentium processors (10K server) a lot faster than it can acces our Oracler database sitting on a SUN serve with Quad Processors (170K server). The test involves opening identical tables in both SQL server and Oracle then looping through 30,000 records. On average, VB was able to loop through 30K records in 2 seconds using SQL server while Oracle took 11 seconds (using the oracle in process object) and 8 seconds using OLEDB/ADODB. Anyway, here's the code for all 3 tests. Can anyone tell me how to improve VB's performance when accessing oracle?


Private Sub Test_Oracle_InProcServer()
On Error Resume Next
Dim strtest As String
Dim dattTest As Date
Dim strSQL As String
Dim strps As Variant
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim OraRecordset As OraDynaset
Dim i As Long
strSQL = "Select * From hopprod.slam_call_history"
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("Kermit", "system/manager", 0)
Set OraRecordset = OraDatabase.CreateDynaset(strSQL, ORADYN_NOCACHE)
i = 0
Label1.Caption = Now
DoEvents
Do While Not OraRecordset.EOF And i <= 30000
strtest = OraRecordset!Q_RESPONSIBLE_PEOPLE
strtest = OraRecordset!Q_UID
strtest = OraRecordset!Q_CUST_DATABASENUMBER
strtest = OraRecordset!Q_CUST_ACCOUNTNUMBER
strtest = OraRecordset!Q_CUST_FULLACCOUNTNUMBER
strtest = OraRecordset!Q_RESPONSIBLE_PEOPLE
strtest = OraRecordset!Q_RESPONSIBLE_DEPARTMENTS
strtest = OraRecordset!Q_STATUSES
strtest = OraRecordset!Q_TASKS
strtest = OraRecordset!Q_SYSTEM
dattTest = OraRecordset!Q_TIMESTAMP
dattTest = OraRecordset!Q_CREATIONDATE
dattTest = OraRecordset!Q_CREATIONDATETIME
strtest = OraRecordset!Q_COMMENT_HISTORY
strtest = OraRecordset!Q_REMINDERS
dattTest = OraRecordset!CREATED_DATETIME
strtest = OraRecordset!Q_CUST_NAME
strtest = OraRecordset!Q_DOCUMENT_TYPE
strtest = OraRecordset!CREATED_BY
dattTest = OraRecordset!LAST_MODIFIED
strtest = OraRecordset!Q_STATUS
strtest = OraRecordset!Q_TANKS
strtest = OraRecordset!Q_SVCLOCS
dattTest = OraRecordset!MODIFIED_DATETIME
strtest = OraRecordset!P_UID
i = i + 1
OraRecordset.MoveNext
Loop
Label2.Caption = Now
Set OraRecordset = Nothing
Set OraDatabase = Nothing
Set OraSession = Nothing
End Sub

Private Sub Test_SQL_Server()
On Error Resume Next
Label1.Caption = ""
Label2.Caption = ""
Dim strtest As String
Dim dattTest As Date
Dim strSQL As String
Dim strps As Variant
Dim OraSession As New ADODB.Connection
Dim OraRecordset As New ADODB.Recordset
OraSession.Open "Dsn=HOP_Datawarehouse"
Dim i As Long
strSQL = "Select * From slam_call_history"
Set OraRecordset = OraSession.Execute(strSQL, , adOpenForwardOnly)
i = 0
Label1.Caption = Now
DoEvents
Do While Not OraRecordset.EOF And i <= 30000
strtest = OraRecordset!Q_RESPONSIBLE_PEOPLE
strtest = OraRecordset!Q_UID
strtest = OraRecordset!Q_CUST_DATABASENUMBER
strtest = OraRecordset!Q_CUST_ACCOUNTNUMBER
strtest = OraRecordset!Q_CUST_FULLACCOUNTNUMBER
strtest = OraRecordset!Q_RESPONSIBLE_PEOPLE
strtest = OraRecordset!Q_RESPONSIBLE_DEPARTMENTS
strtest = OraRecordset!Q_STATUSES
strtest = OraRecordset!Q_TASKS
strtest = OraRecordset!Q_SYSTEM
dattTest = OraRecordset!Q_TIMESTAMP
dattTest = OraRecordset!Q_CREATIONDATE
dattTest = OraRecordset!Q_CREATIONDATETIME
strtest = OraRecordset!Q_COMMENT_HISTORY
strtest = OraRecordset!Q_REMINDERS
dattTest = OraRecordset!CREATED_DATETIME
strtest = OraRecordset!Q_CUST_NAME
strtest = OraRecordset!Q_DOCUMENT_TYPE
strtest = OraRecordset!CREATED_BY
dattTest = OraRecordset!LAST_MODIFIED
strtest = OraRecordset!Q_STATUS
strtest = OraRecordset!Q_TANKS
strtest = OraRecordset!Q_SVCLOCS
dattTest = OraRecordset!MODIFIED_DATETIME
strtest = OraRecordset!P_UID
i = i + 1
OraRecordset.MoveNext
Loop
Label2.Caption = Now
End Sub

Private Sub Test_Oracle_OLEDB()
On Error Resume Next
Label1.Caption = ""
Label2.Caption = ""
Dim strtest As String
Dim dattTest As Date
Dim strSQL As String
Dim strps As Variant
Dim OraSession As New ADODB.Connection
OraSession.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=kermit;" & _
"User ID=system;Password=manager;"
OraSession.Open
Dim OraRecordset As New ADODB.Recordset
Dim i As Long
strSQL = "Select * From hopprod.slam_call_history"
Set OraRecordset = OraSession.Execute(strSQL, , adOpenForwardOnly)
i = 0
Label1.Caption = Now
DoEvents
Do While Not OraRecordset.EOF And i <= 30000
strtest = OraRecordset!Q_RESPONSIBLE_PEOPLE
strtest = OraRecordset!Q_UID
strtest = OraRecordset!Q_CUST_DATABASENUMBER
strtest = OraRecordset!Q_CUST_ACCOUNTNUMBER
strtest = OraRecordset!Q_CUST_FULLACCOUNTNUMBER
strtest = OraRecordset!Q_RESPONSIBLE_PEOPLE
strtest = OraRecordset!Q_RESPONSIBLE_DEPARTMENTS
strtest = OraRecordset!Q_STATUSES
strtest = OraRecordset!Q_TASKS
strtest = OraRecordset!Q_SYSTEM
dattTest = OraRecordset!Q_TIMESTAMP
dattTest = OraRecordset!Q_CREATIONDATE
dattTest = OraRecordset!Q_CREATIONDATETIME
strtest = OraRecordset!Q_COMMENT_HISTORY
strtest = OraRecordset!Q_REMINDERS
dattTest = OraRecordset!CREATED_DATETIME
strtest = OraRecordset!Q_CUST_NAME
strtest = OraRecordset!Q_DOCUMENT_TYPE
strtest = OraRecordset!CREATED_BY
dattTest = OraRecordset!LAST_MODIFIED
strtest = OraRecordset!Q_STATUS
strtest = OraRecordset!Q_TANKS
strtest = OraRecordset!Q_SVCLOCS
dattTest = OraRecordset!MODIFIED_DATETIME
strtest = OraRecordset!P_UID
i = i + 1
OraRecordset.MoveNext
Loop
Label2.Caption = Now
End Sub

That's very interesting. What are the performance differences if you run queries directly on each DB (for example, executing 10,000 INSERT queries)?

I'm not sure how you'd increase the access time for Oracle db's. It's probably an issue with the underlying DBMS.

Hi,

When you're talking about the time, are you including the time to open the connection itself or is't the time needed to open the recordset+loop through it only? If it's the first, could you try to compare the time startting from the moment the connection has been opened.

Shurik.

I think Shurik12 has a point, when you say you use the same approach and both database records are handled by ADODB. Then there should be no difference at all. All depends on ADODB capabilities.

But if you neglected the time of connection, and considers only the "recordset+loop" as Shurik12 call it, then perhaps your Oracle database needs fine tuning.:)

Hi,

When you're talking about the time, are you including the time to open the connection itself or is't the time needed to open the recordset+loop through it only? If it's the first, could you try to compare the time startting from the moment the connection has been opened.

Shurik.

Hi, I am only timing the loop. If you look at my code, I assigned the current time to label1 at the start of the loop then when the loop is done, I assign the current time to label2. What's interesting is that when I run similar queries against the 2 databases using their "native" tools, SQL Plus for Oracle and Query Analyzer for SQL, Oracle blows SQL away. My boss who is trying to migrate his Lotus Notes programs into Oracle from SQL server encountered the same problem. His Lotus Notes programs are running considerably slower using Oracle but only when looping through recordsets. This is driving us nuts because if we can't figure this out, we will have to abandon our plans to migrate all of our programs to Oracle. Any ideas?

>I am only timing the loop.
Right I see it now...

I don't immediately see what might be the reason and realize that it's not completely
"correct" to make such a comparisson comparisson but still I just made a small test on my machine and looped through some 175000 records on Oracle in 2-3 sec (from a VB form).
More details: DSN-less ADO connection, using MSDAORA provider.

Shurik.
p.s Just in case it's better to use GetTick when trying timing something.