Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ilimax
Posting Yak Master
164 Posts |
 Posted - 2009-08-14 : 10:35:02
|
| If I need to insert record from my VB.net application I have used sql statement and sql client. I have noticed some people rather use stored procedure. They add 10 paramenters to sql client then execute stored procedure. Which way is more efficint,better and faster? I use stored procedure just for complex things like, I have to first read some info from server then insert or update my record by that value. |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-08-14 : 10:50:57
|
quote:
Originally posted by ilimaxWhich way is more efficint,better and faster?
1) Better is such an ambiguous term. If you are prepared for a heated discussion you will probably find that *most* people on this forum prefer stored procs.2) faster / efficient. If you are using recent versions of SQL server then all ad hoc queries also get cached. This reduces the difference in speed / efficiency mostly to 0. (As always though it depends on the exact nature of the SQL you are issuing).There is another feature of stored procs that you didn't consider. Security - You can give a stored proc access to modify objects that the caller doesn't have access to. That means that if you trust a stored proc won't do anything stupid you can reduce the access of the end user (and hence increase security). I think that is the primary virtue of stored procs.Also -- it is generally easier for a SQL guy to debug a stored procedure in the database than it is for him or her to dig through application code to find in-line SQL.My thoughts anyway. I like stored procs.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2009-08-14 : 12:47:46
|
| Not only that but your DBA will like you much better if you use stored procedures rather than ad hoc queries. You'll get better support from the DBA and perhaps even enhancements to make your queries run faster.I'm dealing with an application now from a vendor that uses no stored procedures(and no clustered indexes) and I'd really like to have a nice talk with the development team. Preferably with a blowtorch and some rope.An infinite universe is the ultimate cartesian product. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-08-17 : 04:50:23
|
| Care to name and shame Cat_Jesus? No proper tables at all? just heaps? BlurghCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
Insert records via stored procedure