SQL server with VB 6.0

HI,

I have 20,000 over of data in my SQL database under a table. When i load the data in VB 6.0 programming, I faced slow loading speed in moving the recordset to one specific position. Does anyone knows how can I program it in the way that I can speed up the loading time?

Thanks
dawn

Without any code showing how you programmed it in a slow way, it's hard to say.

One issue may be indexing of your tables in the database. SQL Serve has many tools to help you optimize your database. I have a remote hosted sql server with 1,000,000+ record tables and opening any recordsets are quite quick.

RSHeader.Open cSQL4RSHeader, GlobalSQLConn, adOpenKeyset, adLockOptimistic, adCmdText
ReadTableRecordCounter
RSHeader.MoveFirst
RSHeader.Find "DOCREF='" & gHelpSelection1 & "'"
Screen.MousePointer = vbDefault
If Not RSHeader.EOF Then
' MsgBox gHelpSelectionCompleted & gHelpSelection1 & " - " & cSQL4RSHeader
TxtDataInfo(0).Text = gHelpSelection1
ReadRow
End If

After I click on the record which I want view, I need to move the recordset to the first record and start the recordset to search for that record. But the time for the SQL server to search for the record is slow. How should I code to improve the search? i'm searching in one table only.

Thanks
dawn

I'd recommend you rething your approach and use the database to locate record(s) with the WHERE clause in your SQL. If your recordset SQL (which you didn't show) selects all records from the table, and you then use the find method of the recordset, the find will need to search through every record in the recordset which may involve many round-trips between your recordset object and the database server.

If you restrict the records in the SQL string, your recordset will only be populated with the necessary records and will be much quicker.

Hi,

Thanks alot... You are right as i have selected all the records from the table because I have to view all the details in it.

If selecting all the records is not the ideal way, I will use SQL to return smaller recordset from the server. Thanks for your advise.

Thanks,
Dawn