sql to ado.recordset very slow

Posted on 2011-04-20
Last Modified: 2012-08-14
I'm using ado object in VB6 with an sql recordset. My code is:

Set RSord = New ADODB.Recordset
RSord.Source = "select * from Orders where IDOrder = " & strIDord

Opening the recordset it is very fast.
But after the following instruction:

Set frm.AdOrd.Recordset = RSord

this is very very slow.

Any ideas?

Thanks in advance for any suggestion.
Question by:DONADIE
    LVL 75

    Expert Comment

    by:Anthony Perkins
    How big is the resultset?
    LVL 13

    Expert Comment

    Can you try specifying other parameters in the connection string? e.g. batch optimistic, etc. This would avoid locking and its especially important if it is a huge dataset.
    Also, what are you doing after setting the recordset? If it is going to be open for a longer duration, yo are better off getting the data and disconnecting it and again connecting when needed.

    Author Comment

    The resultset is around 30 fields.
    the ADODC object it used to populate textboxes on a form.

    I'll try to use other parameters (batch optimistic etc.) but I'm using the same object, same code, same server with SQL server 2005 installed, on other application with no problems.

    Could be a firewall or other restriction on the network?
    Many thanks.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>The resultset is around 30 fields.<<
    I mean how many rows.

    Author Comment

    Only one rows.
    LVL 42

    Accepted Solution

    what is your sql server version edition?
    do you have latest vb6 sp installed?
    make sure to install fresh MDAC..
    You may need to try to use another provider and\or connection  string.

    Author Comment

    I will check all suggested issues.

    Thanks a lot.


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now