ADO internet to SQL server (performace) 1

[Trowser]

Ok I would like to know how best to increase and speed up the performace of my program fetching data over the internet using ADO

Con.Connectionstring "Driver={SQL Server};" & _
"Server=xxx.xxx.xxx.xxx;" & _
"Address=xxx.xxx.xxx.xxx,1433;" & _
"Network=DBMSSOCN;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
con.open

Set Rec = New ADODB.Recordset
With Rec
.CursorLocation = adUseClient
.LockType = adLockReadOnly
.Open "SELECT * FROM " & Tbl & " WHERE(((" & Tbl & ".level)=" & Lvl & &quot);", Con
.ActiveConnection = Con
.MoveFirst
.CacheSize = 10
End With

ok any ideas where I could improve performance?

Sometimes its a quick as a whip sometimes it can take upto 30seconds to get data, dunno if thats just connection issues at times or anything else just wanna check with you Gurus if I have done it the best possiable way

 

[sguslan]

Well, my 2 cents:

1 - Create a stored procedure(SP) that returns your query row set. SP improve performance a lot .
2 - Investigate using server side cursors instead of client side..

 

[Trowser]

Ok I would like to know how best to increase and speed up the performace of my program fetching data over the internet using ADO

Con.Connectionstring "Driver={SQL Server};" & _
"Server=xxx.xxx.xxx.xxx;" & _
"Address=xxx.xxx.xxx.xxx,1433;" & _
"Network=DBMSSOCN;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
con.open

Set MSGrec = New ADODB.Recordset
With MSGrec
.CursorLocation = adUseServer
.LockType = adLockReadOnly
.Open "SELECT Ore.ID, Ore.[Name],Ore.[level], Ore.[Mineral-Location], " & _
"Ore.[Mineral-Cost] From Ore WHERE (((Ore.[Name])='" & OreNam & "')) " & _
"OR (((Ore.[Refines-to])='" & OreNam & "'));", Con, adOpenDynamic
.CacheSize = 100
end with


Now with using the serverside as suggested it now opens the DB very quickly but browsing through the rows is very slow?? now I use client on other sections and it works as quick as a bullet but this one section is as slow as a snail

Now Sguslan had a great idea with using a Stored procedure but dunno how to create one or how to then call that SP in VB (anyone able to help)