CODEWITHSUNDEEP
vb.netperformanceoracle-databasedatatabledecode
Nick
Nick

Reputation: 135

Why is the load method of a datatable sometimes so slow?

The project is a web app in ASP/VB.net. The issue is that some pages are mind-boggingly slow. After trying to track down the bottleneck, it was discovered to be the load method when filling a datatable with query results.

We are using an Oracle database and queries are executed in stored procedures. As an example, we have a relatively simple select statement within a procedure which returns 2 columns with 6 rows which was determined to take about 0.015 seconds to execute. However it takes on average 7 seconds to load the OracleDataReader into a datatable - a ridiculous amount of time for such a small record set. After messing around with the query, I found that a simple decode statement appeared to be causing the issue. The decode statement is used similar to as follows:

WHERE DECODE(iBln, 1, column1, column2) BETWEEN iDate1 and iDate2

The iBln variable is simply a number being passed in to act as a boolean variable for determining which column should be between two dates. If I comment this decode statement out and make it simply "column1 BETWEEN iDate1 and iDate2" then the load method takes no time at all as it should, signifying that it is indeed the decode statement causing issues.

So I'm just hoping to hear from anyone that could have an idea as to what's causing this or how to fix it. It's a simple decode, how does it even affect the load method anyway?

Upvotes: 4

Views: 909

Answers (2)

Dave Costa
Dave Costa

Reputation: 48111

I would verify that indexes exist for column1 and column2. If so, the likely problem is that the DECODE is preventing the use of the indexes. Try rewriting as:

WHERE ( ( iBin = 1 AND column1 BETWEEN iDate1 AND iDate2)
        OR
        ( (iBin IS NULL OR iBin <> 1) AND column2 BETWEEN iDate1 AND iDate2)
      )

Upvotes: 4

Justin Cave
Justin Cave

Reputation: 231661

If your stored procedure is returning a REF CURSOR, opening the cursor in the stored procedure will be very fast regardless of the query you're executing. Opening a cursor doesn't require that Oracle do any of the work of actually running the query, it just requires that Oracle determine the query plan which should be more or less instantaneous.

How long does it take to fetch the data from the REF CURSOR in something like SQL*Plus? If it takes something close to 7 seconds (as I suspect it will), you can eliminate the OracleDataReader class as the source of the problem. In that case, the problem would almost certainly be that the query plan is inefficient.

Based on your description, my guess is that column1 is indexed. column2 may also be indexed, it's not clear. But a regular index on either column1 or column2 could not be used to evaluate the predicate that involves the call to the DECODE function. If there are no other predicates on indexed columns, that may force Oracle to do a table scan on the underlying table (posting the full query, the table definition, and the query plan would be helpful).

Upvotes: 3

Related Questions