Performance and Store Procedure

[Versys]

Hi everyone

I was checking on this forum about PERFORMANCE and did not find any about this topic.

My question is, where do Store Procedures RUN, let's say I have a SQL-SERVER running in one computer and programs in another separate, when I call or execute the STORE PROCEDURE via Query Analizer, or Visual Basicm where does this STORE PROCEDURE actually run in the SERVER, or from the computer where I started the process.

Want to know you comments....


Thanks for you support.....


Regards



Vinnie.....

 

[dbomrrsm]

vinnie

Not an expert on this but I think the SP will run on the server where it is held not on the local pc.

DBomrrsm

 

[SQLSister]

The stored procedure runs on the SQL server as do any T-SQl commands sent from another computer. Stored procedures are more efficient than creating the code in the user interface and sending it because they reduce network traffic. THey also will reuse the execution plan saving some rpocessing time there as well.

Further they are better for security because you can grant rights only to the SP and the users never have direct access to the tables.

Plus as a dba, it is a big plus for me to have all the code that accesss my database in the the database making it much simpler to see what will be affected by a proposed change to database structure. Also even though I don;t write all the stored procedures for my database, I do review them and make adjustments to improve performance. If this code were in the user interface, likely I wouldn't see it.

Questions about posting. See faq183-874