Performance

we have application and customized it by using VBA
In VBA Code i run some queries witch summrize some information
we plan set the query in stored procedure then we execute it from VBA
insted of run query from VBA
so how this will effect in performance
Thank you for your help> so how this will effect in performance
From the SQL Server 2000 Books Online:
<Excerpt href="http://links.10026.com/?link=optimsql.chm::/odp_tun_1a_6x45.htm">
All well-designed Microsoft® SQL ServerT 2000 applications should use stored
procedures. This is true whether or not the business logic of the
application is written into stored procedures. Even standard Transact-SQL
statements with no business logic component gain a performance advantage
when packaged as stored procedures with parameters. Transact-SQL statements
compiled into stored procedures can save a significant amount of processing
at execution time. For more information, see Stored Procedures.
Another advantage of stored procedures is that client execution requests use
the network more efficiently than equivalent Transact-SQL statements sent to
the server. For example, suppose an application needs to insert a large
binary value into an image data column. To send the data in an INSERT
statement, the application must convert the binary value to a character
string (doubling its size), and then send it to the server. The server then
converts the value back into a binary format for storage in the image
column. In contrast, the application can create a stored procedure of the
form:
CREATE PROCEDURE P(@.p1 image) AS INSERT T VALUES (@.p1)
When the client application requests an execution of procedure P, the image
parameter value will stay in binary format all the way to the server,
thereby saving processing time and network traffic.
SQL Server stored procedures can provide even greater performance gains when
they include business services logic because it moves the processing to the
data, rather than moving the data to the processing.
</Excerpt>
There are also security benefits over direct table access.
Stored procedures are a Best Practice but whether or not your application
will experience faster performance simply by switching from embedded queries
to procs depends on other factors. It is doubtful you will see any
noticeable improvement if you currently have performance problems due to
lack of tuning or poor application design.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Serri" <Serrihakmi@.yahoo.com> wrote in message
news:1141037915.122884.185280@.i39g2000cwa.googlegroups.com...
> we have application and customized it by using VBA
> In VBA Code i run some queries witch summrize some information
> we plan set the query in stored procedure then we execute it from VBA
> insted of run query from VBA
> so how this will effect in performance
>
> Thank you for your help
>