Same Question. More Info. SQL Server.

[Steve Thomas]

I am running VB6 and SQL 7.0


Information:
#1. Speeds up around 4:00 in the evening.(Less Traffic on Network)
#2. Identical Access database opens in 7 to 10 seconds on Novell Server and 8 Minutes on NT server.
#3. Switched to same segment on network but did not notice any change
#4. I can run a query using SQL Query Analyzer and it is very fast. Example: I can search 500,000 records and count all records that have a social security number like 52* and it takes 2 - 3 seconds to give me the answer.
#5.It seems that the SQL server is doing it's thing very quickly by crunching the data (2 - 3 seconds). However, If I have 1,000 records as part of my recordset, it takes a few minutes to return the records to the ADO recordset on my VB app.


Questions:


- Is this a limitation of NT or SQL ? The reason I ask is because I have the same application already built in an Access Database that is currently in production. I have no problems with response times. It sits on a Novell Server.

- Should I be able to retrieve 1,000 records in less than a few minutes?

- Does anyone have a VB app connecting to a SQL server and are pulling over 500 - 1000 records back to the VB app that is not having this problem?

[Iain17]

Yep. A while ago i had to write a program to edit a SQL table. it only had 3 columns, but i tested it with 2,000 rows. Even with that many records the response time was still very quick.

[Steve Thomas]

So you created a recordset and could move forward from record 1 to 2000 on your VB app using the MoveNext or MoveLast function or a data Control and it was very fast?? And you are also saying it did not take minutes to open the form and retrieve the recordset but seconds????


I need this information because I have to prove to my Telecom Guys that it may be a network or NT server issue and not SQL.

[Iain17]

My recordset was bound to a datagrid.

I have just tested the program again and it takes about 4 seconds to load up, with 2000 records.

I had a search algorithim that searches through all of the records in the recordset. It takes 5 seconds for my search to find the very last record,(starting from the first).

For your information i am using SQL 6.5, VB6 (enterprise) SP3, NT4 Server SP6a.

[Edited by Iain17 on 05-03-2000 at 05:19 PM]

[Steve Thomas]

Thanks for the info!!!!!!!