Hi, I have to convert an DAO 3.5 access vb program to a ADO 2.5 SQL Server 2000 program. I have grids that displays 2000 records... What is the fastest SQL Server access method in ADO to return rows is it Store Proc Or Views ??? (My Data doesn't need to be
change only viewed) How do we call a view in ADO? Also is my connection string good or is there a faster connection that exist I know that ODBC Is slow because of multiple layers so I use that one. (BTW I have a lot of vb experience but no experience in SQL
server) Is it a good thing to open a connection only when the program starts and close it when the program close??? StrSqlSrv = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _ "Persist Security Info=False;Initial Catalog=testdb;" & _ "Data Source=" & servername
I would suggest a Stored Procedure over a view. The database should be able to generate a more efficient execution plan that way. You can tell for sure by using SQL Query Analyzer for each way and comparing the execution plans. You can call a view just like
you would a table. The connection string is good. It's best to open a connection just before you want to get the data and close it immediately after. Opening and closing connections is much more efficient than in the old days so there's little overhead associated
with opening and closing. Keeping connections open for the minimum amount of time allows the connection pool manager to operate more efficiently. But if a procedure needs to make more than one database call, then it's ok to open before the first and close
after the last it done.
Francois1975
Member
270 Points
54 Posts
View VS Store Procedure in ADO 2.5
Aug 06, 2003 08:53 PM|LINK
McMurdoStati...
Contributor
3015 Points
601 Posts
Re: View VS Store Procedure in ADO 2.5
Aug 06, 2003 09:25 PM|LINK