sql in vb vs stored procedure 3

[graphix03]

hi,

when i need to get a recordset, I either
write a select sql statement directly in
vb or have it to call a stored procedure.
Which method is better, more efficient,
or faster?

any input would be appreciated. thanks much.

 

[pms18066]

It really depends on if the database is local or on a server. The stored proc will execute on the server which is usually a faster machine than a workstation. This speeds it up from an execution standpoint. However the network traffic is the tradeoff.

 

[graphix03]

Thanks much pms18066 for your input. the database is
sitting on the server, so as you stated, stored
procedure would be faster in execution.

 

[DrJavaJoe]

There are lot's of discussions on this on the web, Google these keywords and read thru some of the blogs.

Dynamic SQL vs Stored Procedures

Afterward you might also add to that search these keywords:

"SQL Injection"


List of Tsunami Relief Donations Sites

Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[CaptainD]

I recently changed one of my programs to stored procedures for the larger datasets. It sped the program up conciderably, especially at the worksites with slower connections.(these are remote work sites three or four miles from the office where the server is located)

It originally was DAO, then switching to ADO improved it, then the Stored Procedures took it to the next level. The amount of data moving across the wire was reduced conciderably.

Stored procedures sort and filter the data on the server and then send you what you asked for. Running sql statements dirrectly have to run the data back and forth to filter and sort it, that is why it's slower.

I've never heard of Sql injection so I'm going to look at that as well but I've been really happy with what I can do with stored procedures.

A few of my Stored procedures create a temp table in memmory, insert data from different tables as needed, do calculations etc from that table and then send me the new data. Once the stored procedure finishes, it drops the temp table. Very nice feature!

 

[graphix03]

Thanks much DrJavaJoe and CaptainD for valuable inputs.