Re: Access/Oracle Stored procedures?

In article <31E3F3AA.1957_at_mail.utexas.edu>, "Raul P. Murguia" <murguia_at_mail.utexas.edu> says:
>
>I am designing a form in Access that allows the user to query
>a remote Oracle table. I build an SQL query from the text boxes
>that the user fills out. Unfortunately, the query is too slow
>right now.

Is the Oracle table indexed according to your query's needs? ie. the columns in the 'Where' clause should be indexed inside the Oracle database. If you are selecting records from only *one* table, and the table is indexed appropriately, the query *should* run in a reasonable time.

Is this 'slow' query based on *attached* Oracle tables, or is it a SQL Passthru query? A passthru query is always faster, assuming you have the appropriate indicies on the Oracle table.

If you have already made it a passthru query, using an Oracle stored procedure will usually *not* result in a noticeable speed difference. Stored procedures can really help if you're selecting hundreds/thousands of rows and performing some kind of loop - ie. for each row selected, you do something with it.

>
>I am vaguely familiar with the concept of stored procedures. Could
>they help speed up the app?

I doubt it...not for a query selecting rows from one table.

>Are there any good books on how to call a procedure from Access

inside a SQL Passthru query, set the SQL to:

Begin

Execute PackageName.StoredProcedureName (Paramater1,...);

/* or, Execute SchemaName.StoredProcedureName (Paramater1,...); */

End;

>& how to code it on the Oracle db?

You really need to consult the Oracle development manual for this...

>Any help would be appreciated.
>
>Thanks.

Regards,

Bill. Received on Thu Jul 11 1996 - 00:00:00 CEST