Upsizing - ODBC issues

[Parax]

I am at present looking into upgrading an Acc97 back end to SQL Server, I have done a few tests and am finding that there are some wierd performace issues.

I was looking at improving sections of the database and leaving other sections alone and linking (via odbc) the remaining tables so that the functionallity remains the same (only linked to SQLserver not another mdb) I have masive improvements from my search Procs, however I am getting really bad performance through ODBC - sometimes 2 to 3 times slower than when it was using the MDB.

Can anyone shed any light on this? why is the SQL odbc slower is this normal behavior? or is there a better way that I am missing?

 

[Parax]

I have included my test code below, it gives the time taken to access each record in the table. I used the same table of 26000 records, the Access Backend Database is located in a share on the SQL server machine, to keep network useage the same.

I have:
1 a linked access table
2 an odbc sqlserver table (imported from the acces table)
3 an odbc sqlserver view of the table

the accessing times are:

1 0.17000 seconds. (first connection time 0.66100 seconds.)
2 0.54100 seconds.
3 0.53100 seconds.


Function as below:

Public Function Testit(Optional intType As Integer)
Dim RS As Recordset
Dim strData As String
Dim x As Double
Dim y As Double

Select Case intType
Case 1
strData = "tblContacts"
Case 2
strData = "dbo_tblContacts"
Case 3
strData = "dbo_ContactsView"
Case 4
strData = "qrypt-tblcontacts"
Case Else
Debug.Print "Select 1:Access 2:SQL-Table 3:SQL-View 4assThrough(Read Only)"
Exit Function
End Select

x = Timer

Set RS = CurrentDb.OpenRecordset(strData)
Do Until RS.EOF
RS.MoveNext
Loop

Do Until RS.BOF
RS.MovePrevious
Loop

y = Timer

Debug.Print RS.RecordCount
Debug.Print "in " & Format(y - x, "0.00000&quot & " seconds."

Set RS = Nothing

End Function