Stored Proc versus direct SQL dilemma! 
Author Message
 Stored Proc versus direct SQL dilemma!

I believe that calling a stored procedure is quicker than an ODBC direct
call (or using an ADO connection.execute).  I would retrieve the
information using a stored procedure and just create a sql string to update
only the changed data fields.

-COB



Quote:
> Using VB6 enterprise & Oracle 7.3

> I have a table that has about 150 EDITABLE fields on it (yep! even
> after normalization)

> Nearly all these fields need to appear of a VB form - i.e using tabbed
> dialog control.

> The problem is that I can't make up my mind whether to create a stored
> proc with all these parameters in which case I always have to pass
> every one regardless if they've changed or not OR create a SQL string
> from VB and use that!

> I would have thought that using a SQL directly from VB would be more
> efficient especially as not many of the fields are likely to change in
> one edit but I've always preferred using stored procs to do this!

> Any thoughts?

> Adrian Harrison



Fri, 07 Sep 2001 03:00:00 GMT  
 Stored Proc versus direct SQL dilemma!
Have you use ADO to retrieve a recordset from an Oracle Store Procedure?

Please tell me how do you do that.
TIA

COB > escribi en mensaje

Quote:
>I believe that calling a stored procedure is quicker than an ODBC direct
>call (or using an ADO connection.execute).  I would retrieve the
>information using a stored procedure and just create a sql string to update
>only the changed data fields.

>-COB



>> Using VB6 enterprise & Oracle 7.3

>> I have a table that has about 150 EDITABLE fields on it (yep! even
>> after normalization)

>> Nearly all these fields need to appear of a VB form - i.e using tabbed
>> dialog control.

>> The problem is that I can't make up my mind whether to create a stored
>> proc with all these parameters in which case I always have to pass
>> every one regardless if they've changed or not OR create a SQL string
>> from VB and use that!

>> I would have thought that using a SQL directly from VB would be more
>> efficient especially as not many of the fields are likely to change in
>> one edit but I've always preferred using stored procs to do this!

>> Any thoughts?

>> Adrian Harrison



Sat, 08 Sep 2001 03:00:00 GMT  
 Stored Proc versus direct SQL dilemma!
Adrian,

I apologize for being RUDE, but um,
     Show me that you have a "normalized" table with 150 columns, and I'll
show you your errors of "normalization", and recommend abstractions.

Byron
Downey Enterprises
Opening Information for your Organization
P.O. Box 264
Kenosha, WI  53141-0264

(414) 654-3555
http:\\ www.wi.net/~byrond

Quote:

>Using VB6 enterprise & Oracle 7.3

>I have a table that has about 150 EDITABLE fields on it (yep! even
>after normalization)

>Nearly all these fields need to appear of a VB form - i.e using tabbed
>dialog control.

>The problem is that I can't make up my mind whether to create a stored
>proc with all these parameters in which case I always have to pass
>every one regardless if they've changed or not OR create a SQL string
>from VB and use that!

>I would have thought that using a SQL directly from VB would be more
>efficient especially as not many of the fields are likely to change in
>one edit but I've always preferred using stored procs to do this!

>Any thoughts?

>Adrian Harrison



Sat, 08 Sep 2001 03:00:00 GMT  
 Stored Proc versus direct SQL dilemma!
Microsoft has published a KB article on retrieving data from Oracle SP's.
It basically requires returning each column as a table-type, which is
extremely inefficient.

Issuing a dynamic SQL statement via ADO is much faster.

Mike

Quote:

>Have you use ADO to retrieve a recordset from an Oracle Store Procedure?

>Please tell me how do you do that.
>TIA

>COB > escribi en mensaje

>>I believe that calling a stored procedure is quicker than an ODBC direct
>>call (or using an ADO connection.execute).  I would retrieve the
>>information using a stored procedure and just create a sql string to
update
>>only the changed data fields.

>>-COB



>>> Using VB6 enterprise & Oracle 7.3

>>> I have a table that has about 150 EDITABLE fields on it (yep! even
>>> after normalization)

>>> Nearly all these fields need to appear of a VB form - i.e using tabbed
>>> dialog control.

>>> The problem is that I can't make up my mind whether to create a stored
>>> proc with all these parameters in which case I always have to pass
>>> every one regardless if they've changed or not OR create a SQL string
>>> from VB and use that!

>>> I would have thought that using a SQL directly from VB would be more
>>> efficient especially as not many of the fields are likely to change in
>>> one edit but I've always preferred using stored procs to do this!

>>> Any thoughts?

>>> Adrian Harrison



Wed, 12 Sep 2001 03:00:00 GMT  
 Stored Proc versus direct SQL dilemma!
I offer to use a stored procedures, each of them save data from single list
of tabbed dialog or any other grouping of saved fields. Then begin
transaction and subsequently call each of sp. If all of them executed OK
then commit transaction, else rollback.
But l think, that no need so large table and some grouping of fields is a
best choice.
If you can grouping fields on front-end app ( using tabbed dialog ), no
problems make it in database.

Best regards.
Boris Mikhailov

Quote:

>Using VB6 enterprise & Oracle 7.3

>I have a table that has about 150 EDITABLE fields on it (yep! even
>after normalization)

>Nearly all these fields need to appear of a VB form - i.e using tabbed
>dialog control.

>The problem is that I can't make up my mind whether to create a stored
>proc with all these parameters in which case I always have to pass
>every one regardless if they've changed or not OR create a SQL string
>from VB and use that!

>I would have thought that using a SQL directly from VB would be more
>efficient especially as not many of the fields are likely to change in
>one edit but I've always preferred using stored procs to do this!

>Any thoughts?

>Adrian Harrison



Fri, 14 Sep 2001 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Stored Proc versus direct SQL dilemma!

2. Stored Proc versus direct SQL dilemma!

3. Stored Proc versus direct SQL dilemma!

4. Returning a Value to Access from a SQL Stored proc

5. SQL Stored Proc for insert/ SQLDataAdapter insertcommand Subroutine

6. Retrieving Output Parameters from SQL Stored Proc

7. Stored Proc Return values / Output Params w ADO and SQL Server 7

8. Access Fields in SQL Stored Proc?

9. SQL Server stored proc to VB Source generator

10. Stored Proc Return values / Output Params w ADO and SQL Server 7

11. Help needed with SQL remote stored proc and vb execution of same

12. SQL Server stored proc to VB Source generator

 

 
Powered by phpBB® Forum Software