SQL Server Slow. Yes or No question?

[Steve Thomas]

I am running VB6. I have a SQL server 7.0.

I have loaded MDAC 2.5 and I have connected using ADO data Controls. I have tried using ODBC drivers, Microsft SQL Server drivers but it takes 1.5 to 2 seconds sometimes to move from one record to the next. Since I am converting from Access97 to SQL server, I still have the data in an Access97 database. I can connect to the access97 database using VB6 and it is very fast.

My conclusion is that either the network protocol used to communicate with SQL server is slower than IPX/SPX or the SQL server is slow.

Server memory 96 meg RAM.

QUESTION:

When i go into SQL Enterprise Manager, it takes a few seconds to connect. It then takes about 2 seconds to get to the database directory. I can then move to the Views area and look at the exact same view that I am using in the VB6 data control. When I open the view and ask to see all records, it takes about 5 seconds before I get to the view screen. I can then watch the data roll down the screen and it takes a good 30 seconds to finish. Is this Normal for enterprise manager?

SQL 7 can smoke almost anything Access can do with regards to speed and scalability, show your code, I use both and each has their own place, if you've bound a control unbind it and dynamically attach a recordset to the control. Show what code you're using.

[Steve Thomas]

Thanks for your help. I would prefer to use an ADO data control rather than set them using code. However, if this is my only solution, I guess I will rewrite. I just can't believe Microsft would put a control out that doesn't work.

What I have done is use an ADO Data Control. I have set the following fields.

ConnectionString= "DSN=TestSQL"
CursorLocation = adUseServer (If I set this to adUseClient) it freezes)
CursorType=adOpenDynamic
RecordSource=vMaster(This is a view in SQL)

I have tried everything I can think of and it takes forever to load and then forever to scroll through accounts.

*****************************************************
If I use the following code it takes forever, but I can get the absoluteposition and recordcount. If I take out the cursor location it is very fast but then i can not obtain the absoluteposition.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "provider=SQLOLEDB.1;" & _
"Password=;" & _
"Persist Security Info=False;" & _
"User Id=sa;" & _
"Initial Catalog=TPO;" & _
"Data Source=SLC-CUSTSQL"

rs.CursorLocation = adUseClient
rs.Open "Select * From table", cn
**********************************************************

********************************************************
I can use the following code and it is fast.(I can not determine absolutePositionn unless I set the cursorlocation. When I do that it again slows to a crawl.


Dim rc as recordset
set rc = new recordset
rc.open "Select * from Table","DSN=MYDSN"
**********************************************************