VB6 Database connection

underpants

Whats the best/fastest way to connect to a database using VB6?

I'm currently using the connection string
Provider=MSDASQL.1;Persist Security Info=False;Data Source=DSN

which works fine. But at times, it can be slowing retrieving info from the database. Is there another connection method I could use that would be faster or can anyone suggest a way to spead up retrieval from the database?


Also, as a side question, how is data retrived via ODBC? is it brought back in one lump or streamed back, etc.

Cheers

DublinWriter

underpants wrote:

Also, as a side question, how is data retrived via ODBC? is it brought back in one lump or streamed back, etc.

It really all depends on how the underlying ODBC driver and DBMS was designed. Most ODBC drivers will have proprietry settings that you can change for things like fetch-sizes, etc.

With DBMSes like Oracle and Sybase, it's usually best-pracise to try and get a native driver that will allow you bypass the ODBC API.

What DBMS are you using?

underpants

DublinWriter wrote:

What DBMS are you using?

It's an MS SQL Server

bonkey

Your connection string is (most) irrelevant. How you run your queries is generally where the major differences are.

have a read of this : http://www.microsoft.com/technet/prodtechnol/sscomm/reskit/mdacapp.mspx

Might help.

amen

you could try and run your sql in the database and see if it is slow there and if so then its not the driver but more your SQL/Database design

bonkey

underpants wrote:

Also, as a side question, how is data retrived via ODBC? is it brought back in one lump or streamed back, etc.

If you can avoid it, don't via ODBC in the first place. use the native OLE DB Provider instead.

Either which way, the answer will be "it depends"

Have a look at this article for some good notes on teh subject : http://www.sql-server-performance.com/visual_basic_performance.asp

DublinWriter

bonkey wrote:

If you can avoid it, don't via ODBC in the first place. use the native OLE DB Provider instead.

Usually the best option for MS SQL Server.

You should also look at your use of indices and how complex your joins are.

underpants

Good stuff.

thanks guys.