How to Improve Oracle Data Read Performance 1

[kvang]

I'm am using VS.NET 2003 to build a web application that displays data from Oracle 8. In my data access layer project, I have a function that returns a data set:

Function getDATA() As DataSet

        Dim objCommand As New OracleCommand(strSQL, objConnection)
        Dim objDataAdapter As New OracleDataAdapter(objCommand)
        Dim objDataSet As New DataSet

        'Generate SQL statement
        strSQL = "SELECT * FROM TABLE1"

        objCommand.CommandText = strSQL
        objDataAdapter.Fill(objDataSet)
        Return objDataSet

    End Function

The actual query takes 2-3 seconds to run. I want to use something similar to a stored procedure. This is easily done if I was using SQL Server back-end since I can write stored procedure to return the result. I have notice doing so also improves my application performance. Can I do something similar in Oracle? I have yet to find a way.

 

[RiverGuy]

How many records/how wide is the resultset you are returning? If there is a lot of data, you're going to be constricted by having that much data travel over a network, so there won't be a whole lot you can do to increase the performance. You can make your application appear snappier if you don't have the UI freeze for 2-3 seconds by using a separate thread.

Otherwise, you can use stored procedures in Oracle. I don't know how. Many of the posters in this forum work with Microsoft databases. You might ask the question on how to create an Oracle stored procedure in the Oracle forum. However I highly doubt you will see a performance increase on a SELECT * FROM Table1 by encapsulating it in a stored procedure.

 

[woogoo]

Hi,

you might want to name the columns in your query rather than use * as using named columns is faster. In addition to this you should also ensure that your data is properly indexed, and you are using the appropriate mode of locking, i.e. Read Only when you're not going to be doing updates.

Using a Stored Procedure in this instance won't really serve any great benefit as the only advantage (since your query is unparameterised) is that the SQL used is preprocessed and has an execution plan.

The length of time your query takes to deliver the results is constrained by many variables. Network congestion, size of result set, configuration of and any concurrency issues on your Oracle Server, Disk IO latency and your own machine's configuration of course.

If you're returning a large result set but your PC has limited memory then there will no doubt be a hit on the disk paging file, resulting in delay.


woogoo

 

[kvang]

Thanks for all the tips. It will help when I am writing my application. Here's a look at the actual query I am running:

SELECT DEPARTMENT, DEPTINITIAL, 
SUM(CASE WHEN CATG = 'BACKLOG' AND CREATETIME < TRUNC(SYSDATE-7) THEN 1 ELSE 0 END) AS DAYS_7_OR_OLDER, 
SUM(CASE WHEN CATG = 'BACKLOG' THEN 1 ELSE 0 END) AS WOB_BACKLOG, 
SUM(CASE WHEN CATG = 'BACKLOG' AND EXISTS (SELECT * FROM REPORTLS.RETAIL_PROCESSLOG C WHERE C.WOBNUM = F_WOBNUM AND C.STEPNAME = 'Complete') THEN 1 ELSE 0 END) AS WOB_BACKLOG_COMPLETE, 
SUM(CASE WHEN CATG = 'NEW' THEN 1 ELSE 0 END) AS WOB_NEW, 
SUM(CASE WHEN CATG = 'NEW' AND EXISTS (SELECT * FROM REPORTLS.RETAIL_PROCESSLOG C WHERE C.WOBNUM = F_WOBNUM AND C.STEPNAME = 'Complete') THEN 1 ELSE 0 END) AS WOB_NEW_COMPLETE 
FROM ( 
SELECT WOBNUM AS F_WOBNUM, DEPARTMENT, DEPTINITIAL, 'BACKLOG' AS CATG, CREATETIME 
FROM LS_WOBBACKLOG A, REPORTLS.WORKTYPES B 
WHERE A.WORKTYPE = B.WORKTYPE 
AND ENQUEUETIME < TRUNC(SYSDATE-1)+17/24 
UNION ALL 
SELECT WOBNUM AS F_WOBNUM, DEPARTMENT, DEPTINITIAL, 'NEW' AS CATG, STARTTIME 
FROM REPORTLS.RETAIL_PROCESSLOG A, REPORTLS.WORKTYPES B 
WHERE A.WORKTYPE = B.WORKTYPE  
AND ENQUEUETIME >= TRUNC(SYSDATE-1)+17/24 AND ENQUEUETIME < TRUNC(SYSDATE)+17/24 
AND DEPARTMENT NOT LIKE 'Direct Retail Lending%' 
AND USERID NOT IN (SELECT P8USERID FROM REPORTLS.BGPERFORMERS) 
AND STEPNAME IN (SELECT STEPNAME FROM REPORTLS.LSAPP_STEPNAME) 
) 
GROUP BY DEPARTMENT, DEPTINITIAL