Slow connection

[shaminda]

I have a program in VB that users a SQL Server 7.0 database and it take about 2 - 4 minutes to make the connection. I can't figure out why its taking so long. Here is my code:


Set rsPartLbl = New ADODB.Recordset

With rsPartLbl
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
End With

rsPartLbl.Open "TRWPARTLBL", conn

its the last line that takes time.

[Jean-Guy2000]

Is this desktop SQL install or FUll? if it's the desktop SQL I found by turning down my memory in server properties helped speed up the server. Since this was only a development enviorement. otherwise your HDD takes a beating with Visual studio and SQL gobbling up ram/swap drive.

[Cakkie]

This can be a lot of things, i'll sum some up and see what you can do to change that (if needed)

Network:
First of all, in most cases this is caused by a slow network connection. If your network is slow, the opening of the recordset will be slow. This can be due to bandwidth, for some things, a connection of 10mb or less is sufficient, for others it isn't. Another thing is the amount of data that is being transferred over the network. You may have a 100mb network, wich is good, but if 100 users are sending data over the network (like when the move or copy a file) the networkspeed can go down a lot.

The amount of data you are retrieving:
If you are opening a table wich has 50000 records, obviously you will need the time to send those records over the network. In this case you must make sure you nly get the data you need. If you need customer orders of a specific customer, you must only retrieve the records for that customer. Or if you only need the name, don't bother getting the rest (like address and phone numbers).

The server:
One thing is that the server (hardware) cannot handle the job. This can be because of a lack of memory, or because several other server runing on it (like mail or webservers)

Cursor location:
The use of client side cursors have advantadges, but also some drawbacks. A client side cursor will get all the data at once. When using serverside cursors, data will only be sent when needed. The data is sent in pages. The cursor is like a double edged knife (or how do they say that). When using client side, you have longer opening times, but you can go faster through the recordset, using server cursors, you will have faster opening times, but slower when going through the recordset.

Cursor type:
The type of cursor is very important. if you don't need to alter any data, use a static cursor. If you only need to read the recordset once, use a forward only cursor. Using these low-effort types comes with some disadvantages, like not being available of some properties (like the recordcount etc) and not seeing changes (via the underlyingvalue property)

As with so many question regarding 'what should i use' or 'what approach' the only suitable answer is 'it depends'

Tom Cannaerts
[email protected]

The best way to escape a problem, is to solve it.

[shaminda]

Thank you it worked. I changed my cursur location from client to server. With server side curser it connected fast. What this recordset only does is, it reads 50 records and prints those records to a printer. What other draw backs should I consider when using a server side cursor?

[Cimperiali]

Serverside cursors are in the hands of servers: some may not let you be able to use some of pretty functions of clients one. Do not be worried, I never used them and my job is still on...

Special thanks to Lothar "the Great" Haensler. Come back soon, you Guru.

[Cimperiali]

Great answer. Here is another 10 for you.

Special thanks to Lothar "the Great" Haensler. Come back soon, you Guru.

[Cakkie]

Some other things you must take in mind that you don't know what type it is. Sure you can say it must be forward only, of whatever, but the server can change that (for some reasons this is the case, like when you open a view wich is build upon several tables, wich makes it not updateble) without letting the user know. This can be aufully painfull to debug.

Another thing is that not all providers support serverside cursors. Since you are working with ADO and SQL server, you shouldn't worry about that.

One final drawback could be that the server can't handle it, serverside cursors are extra work for the server.

Tom Cannaerts
[email protected]

The best way to escape a problem, is to solve it.