|
Techniques to Improve Performance while Retrieving Data
Performance tuning is a great subject in Oracle. Volumes of books would not be
enough to cover every aspect of performance tuning in Oracle. However, in this
section, we will only discuss the fundamental performance techniques while
working with ODP.NET.
Some of the frequently used techniques to achieve greater performance with ODP.
NET are as follows:
-
Connection pooling
-
Choosing a proper retrieval methodology for every data retrieval task
-
Choosing a proper CommandType (when using an OracleCommand object)
-
Controlling the amount of data returned to the client (or middle tier)
-
SQL statement caching
-
Developing object pooling components (like COM+ etc.)
We have already mentioned Connection Pooling earlier in this chapter. Working
with a physical database connection for every SQL statement could be very
expensive in terms of performance. Try to figure out the best strategy to
implement connection pooling in your applications based on factors like heavy
data consumption, server resources utilization, frequent access to database,
continuous (or long) operations on data, mission-critical scenarios, etc.
|
Book Excerpt: Retrieving Data from Oracle
Using ODP.NET
Chapter Contents
This excerpt from
ODP.NET Developer's Guide: Oracle Database 10g Development with Visual Studio
2005 and the Oracle Data Provider for .NET by Jagadish Chatarji
Pulakhandam, Sunitha Paruchuri, is printed with permission from
Packt Publishing, Copyright 2007.
|
|
|
As discussed previously, the only way to retrieve data from Oracle in ODP.NET
is by using the core OracleCommand, OracleDataReader, or OracleDataAdapter. An
application would be made with several simple to complex tasks. Be wise and
select the best option between those three, based on every respective task and
its complexity. Do not try to take a decision on using only one of them
throughout the application, which really kills performance in several
scenarios. For example, to retrieve a single value from the database, it is
always the best to use ExecuteScalar (of the OracleCommand object) directly,
rather than using the other two.
Never retrieve a whole table unnecessarily. Never use "SELECT *"; always fully
qualify an SQL statement. Using "SELECT *" would not only slow down your
application performance but also can be a bit dangerous. Imagine a few more new
columns are added to the table. All those columns would also be retrieved
automatically in the .NET application (whether required or not).
Try to be selective when choosing CommandType. It is suggested to use the
StoredProcedure command type (if you implement stored procedures) or Text
rather than TableDirect. Working with PL/SQL stored procedures is covered in
Chapter 5.
|
Another very common mistake is retrieving too many rows unnecessarily. Imagine
a table exists with one million rows and you are trying to retrieve all of them
for the user. Any user would never want to view million rows in his or her life
time. Not only that, pulling one million of rows from the server really
consumes huge memory resources and also makes the network too busy.
In any case, ODP.NET by default fetches only 64K at a time. So, even though you
try to execute a SELECT statement that retrieves all rows in a table, it
retrieves only chunks of 64K based on demand. You can customize this fetch size
by issuing the following statement:
cmd.FetchSize = cmd.RowSize * 25
The above makes sure that it retrieves a maximum of 25 rows per round-trip to
the server. You can observe that the FetchSize is completely based on RowSize
and not simply on the number of rows. Apart from modifying the FetchSize, try
to provide filters in your user interface to minimize the data fetching from
server.
If you are working continuously with a similar set of SQL statements (like
INSERT in a loop etc.) in a routine, it is always suggested to take advantage
of statement caching. A cache is nothing but some high-performance memory at
server. If you cache the frequently used SQL statements, a copy of such SQL
statements gets stored at that high-performance memory and gets executed (with
different values) every time you issue the same SQL statement. This removes the
burden at the server of parsing and preparing an execution plan for every SQL
statement and improves the performance tremendously. Generally, when you use
the concept of bind variables together with OracleParameter, the statement
caching automatically takes place.
Finally, when developing business logic, it is suggested to design scalable
business components, which can take advantage of features like automatic object
pooling, loosely coupled behavior, caching, persistence, accessibility
permissions (security), transactions etc. Designing and implementing business
components (like COM+, MSMQ, Windows Services, Web Services, .NET Remoting,
etc.) are very common in enterprise applications. Selecting a proper approach
for implementing a business component is the main backbone at the middle tier
(if you are developing multi-tier applications).
Page 1
| Page 2
|
page 3 |
page 4 |
page 5 |
page 6 |
page 7 |
page 8
|