Infralution Support Forum Index Infralution Support
Support groups for Infralution products
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Grabbing a VRecord from a VRecordset is very slow

 
Post new topic   Reply to topic    Infralution Support Forum Index -> Virtual Data Objects Support
View previous topic :: View next topic  
Author Message
mos



Joined: 19 Nov 2006
Posts: 4
PostPosted: Thu Feb 08, 2007 11:57 pm    Post subject: Grabbing a VRecord from a VRecordset is very slow Reply with quote
Hi Infralution,

I'm again in trouble with my database. I simply like to get one vrecord out of round about 3000 vrecords. My Code:

Code:
VirtualConnection vc = new VirtualConnection("dsn=mydsn", "login", "pwd", 0, 2);
vc.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
vc.CursorType = ADODB.CursorTypeEnum.adOpenKeyset;
VirtualRecordset vrs = new VirtualRecordset(vc, "clients", "id", "SELECT * FROM clients ORDER BY id");
VirtualRecord vr = vrs[2];
MessageBox.Show(vr["id"].ToString());
vc.Close();


After 57 secs i get the desired information. Why does it take so long? The same problem when i try to get the amount of records in the recordset.

It seems to me, that the complete recordset is copied into the client memory.

Is there a way to fasten up this process?

Thank you,
Marc
Back to top
View user's profile Send private message
Infralution



Joined: 28 Feb 2005
Posts: 5027
PostPosted: Fri Feb 09, 2007 6:24 am    Post subject: Reply with quote
That definitely is very slow. You are using a client side cursor which does mean that ADO will copy the data into memory - however I have used clientside cursors on much larger databases than yours without seeing this sort of performance problem. Generally you will get better performance out of server side cursors - however some databases don't fully support bidirectional server side cursors which means you have to use a client side cursor.

You should perhaps try some raw ADODB code and check the performance of your database with that. For instance try the following:

Code:
ADODB.Connection cn = new ADODB.ConnectionClass();
cn .CursorLocation = ADODB.CursorLocationEnum.adUseClient;
cn.Open("dsn=mydsn", "login", "pwd", 0);
Recordset rs = new ADODB.RecordsetClass();
rs.Open("SELECT * FROM clients ORDER BY id", cn, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockOptimistic, -1);
int count = rs.RecordCount;
rs.AbsolutePosition = 2;
object id = rs.Fields["id"].Value;


If this code takes a long time to execute then you could try changing the CursorLocation to a server side cursor or the CursorType - but I would probably talk to your database provider about why their ADO bindings are so slow. Unfortunately if the underlying ADO provider performance is terrible for some reason then this will affect Virtual Data Object performance.
_________________
Infralution Support
Back to top
View user's profile Send private message Visit poster's website
mos



Joined: 19 Nov 2006
Posts: 4
PostPosted: Mon Feb 19, 2007 2:36 am    Post subject: Reply with quote
Thank you for your quick answer.

Yes, you are right. It seems to be a problem with the database driver, i have to use. It takes the same time when I try the query directly with ADODB. And I get better response times when using other databases.

Nevertheless, I found some strange reaction in der Virtualconnection: I am unable to use a server-side cursor. When try a server-side cursor directly with ADODB it works fine, but when I try it using a Virtualconnection, the program throws a COMException (multiple-steps...). It seems, that my driver can't count the records when using a server-side cursor. And it seems, that you use this ADODB function in your code, so it produces an error. Am i right? Is there a work around?

Thank you,
Marc
Back to top
View user's profile Send private message
Infralution



Joined: 28 Feb 2005
Posts: 5027
PostPosted: Mon Feb 19, 2007 3:22 am    Post subject: Reply with quote
Quote:
It seems, that my driver can't count the records when using a server-side cursor. And it seems, that you use this ADODB function in your code, so it produces an error. Am i right? Is there a work around?


You are right, Virtual Data Objects requires the database provider to implement the RecordCount property. Some providers do not do this for server side cursors. Client side cursors are implemented by ADODB (and not the provider) and so can be relied on to implement the RecordCount property. So the workaround is to use a Client side cursor.

Did you test the raw ADODB with both client and server side cursors? If so was the performance any different?
_________________
Infralution Support
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    Infralution Support Forum Index -> Virtual Data Objects Support All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group