ADO / SQL Question.

[Steve Thomas]

I wrote a VB6 application that connects to a SQL server (7.0) using an ADO data control. I placed two text boxes on the form and bound them to the ADO control. I have two table on the SQL server One called tMaster that has 10 records and an identical table called tMaster2 with 33,000 records. I start the VB application and first connect to the table with 10 records. It is very fast. I then change the recordsource to tMaster2 and start the application. I have to do a control+Alt+Delete because it hangs. It is doing something because I can see my harddrive light moving. It takes 5 minutes just to pull back 5,000 records using Query Analyzer.

My network Admin just came back from SQL training and is telling me that I should not try to pull back that many records.

Question:
#1.Does the ADO data control have to pull all the data accross the line? I would think that when you want to move from one record to the next you would be doing it at the server and not at the client??

#2. I can take the same tMaster2 table with 33,000 records and put them in an Access database on a novell server and change the ADO control in the same VB application to point to the access table. It is very fast.

#3. Does this sound normal. I just can't believe you would have the option in the ADO control to select a table if you couldn't pull it back????

[Paul282]

I have never had this problem...

But then, I never touch data-bound controls. It's like throwing control and fexibility into the recycle bin and hitting flush

Doing everything youself takes a bit longer, but not as long as having to recode everything from scratch later because a data-bound method won't cope

[Paul282]

I think I have seen this actually.

Try this, Instead of loading to a control which is slow because you have to call it each record update, create a use type of the columns and make an array of the type.

using ado or rdo, loop straight into the array. and check the time without viewing the data.

sometimes concaternating strings (esp in a textbox) is VERY slow as the text size gets higher.

if this works then dump your array into the textbox incrementally:
if you don't know this trick, learn it. looping string appends sux the big one, 30,000 strings and your talking 1000% speed increase

For i = 1 to Ubound(UDTArray)
    TempStr1 = TempStr1 & UDTArray(i).Col1 & CDTArray(i).Col2 & vbCrLf
    If i mod 500 = 0 then
        TempStr2 = TempStr2 & TempStr1
        TempStr1 = ""
        ' You made need to go as far as tempstr3 or 4 if you have too many values
    End if
Next

    Text1.Text = TempStr2 & TempStr1