May 17, 2000

Create explicit Field objects when looping on large Recordsets

Whenever you access a database field through the Recordset object you incur in a slight overhead. Take for example the following code snippet: Dim rs As New ADODB.Recordsetrs.Open “SELECT au_lname, au_fname FROM authors”, “DSN=pubs”, , , adCmdTextDo Until rs.EOF List1.AddItem rs(“au_lname”) & “, ” & rs(“au_fname”) rs.MoveNextLooprs.Close The references to

Disaster Recovery of Exchange Services

Question: To make a long story short, we have a copy of the private and public EDB files. Is there a way to get the data out of at least pub.edb? (We have fresh installs of NT 4 SP6 and Exchange 5.5 SP3.) Any help is much appreciated! Answer: If

Advantages of EXISTS over IN Clause in a Query

If you wish to retrieve from the department table all the department numbers that have at least one employee assigned to them, write the query as: select deptno, deptname from dept where deptno in ( select deptno from emp) ; This query will run a full table scan on both

Performance Analysis of Application by Code Profiling

Oracle8i provides a standard package, DBMS_PROFILER, which facilitates performance and code coverage analysis of your PL/SQL application. This package provides functions for gathering statistics, such as total number of times each line has executed, time spent executing each line, and the minimum and maximum amounts of time spent executing a

Caching Packages on Database Startup

Oracle8i has introduced database-level and system-level triggers. They can track events in the database such as logon/logoff from a schema, startup and shutdown of a database, and response to a server error. We will use these triggers to cache the frequently used packages in the memory during database startup. Before

Performance Enhancements Using Temporary Tables

A temporary table has a definition or structure like that of a regular table, but the data it contains exists for only the duration of a transaction or session. Oracle8i allows you to create temporary tables, which can be bound to a session or to a transaction. You can use

Wrapping Application Code

Oracle comes with a PL/SQL wrapper, which converts the PL/SQL source code into an intermediate form of object code. By hiding application internals, the wrapper prevents the misuse of your code by other developers and also prevents the exposure of your algorithms to your business competitors. The wrapped code is

Faster Data Access Using Index-Organized Tables

If you have a table whose columns always are queried together, maybe you should store them as an index-organized table (IOT). An IOT is indexed on one or more columns and all the table data is maintained as a B*tree index. Any manipulation of table data updates the index. The

Avoid Multiple Exit Points in a Function

If you have a database function that has multiple return statements, try to minimize the exit points. I will explain with the following example: create or replace function getStatus(p_status_code in varchar2) return varchar2 is begin if p_status_code =