I'm doing a query on a database that has 250,000 records. The query is
returning a rowset with just 12 records in it. When I do the MoverFirst() on
the rowset it takes 10 seconds to accomplish. And when I do subsequent calls
to MoveNext() on the rowset it takes anywhere from 2 to 12 seconds to
accomplish the move.

This doesn't seem reasonable and I'm at a loss as to what I might be doing
wrong. When I was using a database with just a few thousand records the calls
to MoveFirst() and MoveNext() on the returned recordsets went incredibly
fast. But now that I'm using a huge database they'e slowed down to a crawl.
But why should there be any relationship between the size of the database and
the speed with which a returned recordset can be traversed?

Does anyone have any insight into this?

Roger Garrett

I'm guessing, but are you talking about returning the rowset to the
application via ADO? The following article describes the use of
CursorLocation (adUseServer, adUseClient), CursorType (adOpenForwardOnly,
adOpenKeyset, adOpenDynamic, adOpenStatic), and LockType (adLockReadOnly,
adLockPessimistic, adLockOptimistic, adLockBatchOptimistic), and how it
impacts performance depending on the situation.
http://www.sqlteam.com/item.asp?ItemID=11842
If you are basically just needing to pass the rowset to something like
Crystal Reports or Excel for reporting purposes, then what you want is a
adOpenForwardOnly / adLockReadOnly or "firehose" cursor.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprocursortype.asp
http://www.microsoft.com/resources/documentation/Windows/2000/server/reskit/en-us/Default.asp?url=/resources/documentation/Windows/2000/server/reskit/en-us/iisbook/c07_recordsets_and_cursors.asp
With 250,000 rows, you may want an adUseServer cursor with a reasonable
sized CacheSize setting.
http://msdn.microsoft.com/library/en-us/ado270/htm/mdprocachesize.asp


Show quoteHide quote

I'm not sure whether this is ADO or not. I'm a beginner at this and been
using some examples that I found on the Internet and in some books. I have
the following two classes:

class CRowsetAccessorMembersGuidUrlOnlineServabilityConnection
{
public:
    char m_strMemberGuid[CGuidMember::GUID_LENGTH_STRING + 1];
    char m_strUrl[15 + 1];
    short m_IsOnline;
    short m_IsServable;
    short m_ConnectionType;

    BEGIN_COLUMN_MAP(CRowsetAccessorMembersGuidUrlOnlineServabilityConnection)
        COLUMN_ENTRY(1, m_strMemberGuid)
        COLUMN_ENTRY(2, m_strUrl)
        COLUMN_ENTRY(3, m_IsOnline)
        COLUMN_ENTRY(4, m_IsServable)
        COLUMN_ENTRY(5, m_ConnectionType)
    END_COLUMN_MAP()

    void ClearRecord()
    {
        memset(this, 0, sizeof(*this));
    };
};

class CRowsetMembersGuidUrlOnlineServabilityConnection : public
CCommand<CAccessor<CRowsetAccessorMembersGuidUrlOnlineServabilityConnection> >
{
public:

    HRESULT Open(CSession& Session) // using the default Query
    {
        CDBPropSet propset(DBPROPSET_ROWSET);
        propset.AddProperty(DBPROP_IRowsetChange, true);
        propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE |
            DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);
        return
CCommand<CAccessor<CRowsetAccessorMembersGuidUrlOnlineServabilityConnection>
    };

    HRESULT Open(CSession& Session, CString strQuery) // using a specified Query
    {
        CDBPropSet propset(DBPROPSET_ROWSET);
        propset.AddProperty(DBPROP_IRowsetChange, true);
        propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE |
            DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);
        return
CCommand<CAccessor<CRowsetAccessorMembersGuidUrlOnlineServabilityConnection>
    };
};


and in my code, after I've set up the query string, I do the following to
get the rowset and then handle the data in the rowset:

CRowsetMembersGuidUrlOnlineServabilityConnection
RowsetMemberGuidUrlOnlineServabilityConnection;
hResult = RowsetMemberGuidUrlOnlineServabilityConnection.Open(m_Session,
strQuery);

if (SUCCEEDED(hResult))
{
    hResult = RowsetMemberGuidUrlOnlineServabilityConnection.MoveFirst(); //
will fail if no records found
    if (SUCCEEDED(hResult) && (hResult != DB_S_ENDOFROWSET))
    {
        // We actually have some matches.
        BOOL bHaveARecord;
        bHaveARecord = TRUE;
        while (bHaveARecord)
        {
            // Extract the data from the current record
            // MEMBER GUID
            CString strMemberGuid =
RowsetMemberGuidUrlOnlineServabilityConnection.m_strMemberGuid;
            // URL
            CString strUrl = RowsetMemberGuidUrlOnlineServabilityConnection.m_strUrl;
            // IS ONLINE
            BOOL bIsOnline = FALSE;
            bIsOnline =  (RowsetMemberGuidUrlOnlineServabilityConnection.m_IsOnline
== 1);
            // IS SERVABLE
            BOOL bIsServable = FALSE;
            bIsServable =
(RowsetMemberGuidUrlOnlineServabilityConnection.m_IsServable == 1);
            // CONNECTION TYPE
            CConnectionTypes::CONNECTION_TYPE ConnectionType =
CConnectionTypes::CONNECTION_TYPE_INVALID;
            ConnectionType =
(CConnectionTypes::CONNECTION_TYPE)RowsetMemberGuidUrlOnlineServabilityConnection.m_ConnectionType;

            so some operations with the data....

    hResult = RowsetMemberGuidUrlOnlineServabilityConnection.MoveNext();
            bHaveARecord = (SUCCEEDED(hResult) && (hResult != DB_S_ENDOFROWSET));
        }
    }
}

The calls to MoveFirst and MoveNext take many seconds to complete, which
just doesn't seem right to me, especially since the actual query takes much
less than a second.

What can I do to speed it up?

Roger Garrett

As a programming language, C++ seems so stilted compared to my day to day VB
or T-SQL, but I'll give it a try. Some of the method calls resemble ADO, but
I don't recognize the object names like
CRowsetAccessorMembersGuidUrlOnlineServabilityConnection, and there was no
hit on google either. Perhaps this is a 3rd party or in-house developed data
access class or high level "wrapper" class for the ADO connection and
recordset objects. I would reccomend contacting whomever provided you with
the data access class and describe the problem with the latency when
selecting data or attempting a move to a different row; there is no telling
how they have it programmed internally.


Show quoteHide quote

JT,

The names like CRowsetAccessorMembersGuidUrlOnlineServabilityConnection are
my names for my classes. They are built on the CAccessor and CCommand
templates of Microsoft's OLE DB Consumer Templates Except for the data
members, they're basically copies of examples from Microsoft.

CRowsetMembersGuidUrlOnlineServabilityConnection defines the data members,
and CRowsetAccessorMembersGuidUrlOnlineServabilityConnection defines the
accessor to the rowset.

I declare an object of type CRowsetGuidUrlServabillityConnectionType (which
is of type CCommand) and then use the open() method on that object, passing
in the dastabase session and the query string. That performs the query and
fills the RowsetGuidUrlServabillityConnectionType object with the results. I
can then use that object's MoveFIrst() and MoveNext() to retrieve the
individual records.

CRowsetGuidUrlServabillityConnectionType
RowsetGuidUrlServabillityConnectionType;
        HRESULT hResult;
//TRACE("DB -->RowsetGuidUrlServabillityConnectionType.Open\n");
        hResult = RowsetGuidUrlServabillityConnectionType.Open(m_Session, strQuery);


I guess I need someone who understands the CAccessor and CCommand templates.

From the sounds of it this is an application code issue and not a DB issue
at all.  I am betting you can change your application code and correct the
problem fairly quickly.

First thing I would recomend is do a quick performance test by removing thr
ereferences to your classes and directly using the .Net classes that are
provided.  You have far too much going on here to readily find the source of
your problems.

Just execute your SQL Query or Procedure and return a dataset.  Loop through
that dataset and pop up a message box showing the key field for each row.
Do this with the simplest, least amount of code possible, and see how long
it takes.  I am betting you will see each row processed in milliseconds or
less.

Try this, then you can strat putting your functions into the code a small
piece at a time and retesting in the same manner.  You'll know right away
when you add logic that slows things down.


Show quoteHide quote

Jim,

You're right, in that the problem is in my code. Well sort of my code. I
took some code from a Microsoft example on setting up a CCommand/CAccessor to
retrieve the recordset. In that sample code it adds properties, as follows:

CDBPropSet propset(DBPROPSET_ROWSET);
propset.AddProperty(DBPROP_IRowsetChange, true);
propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE |
    DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);

But all I'm trying to do is read some data, not edit, add, or delete the
recordset's records, so those DBPROPVAL_ properties are not needed. In fact,
they appear to be the source of the lengthy processing times when doing the
MoveFirst and MoveNexts. When I remove those DBPROPVAL_s that provide for the
change, insert, and delete, it goes nice and fast.

Thank you to each of you who offerred advice on this issue.

- Roger Garrett

SQL Statement guru required please!
Bug with GROUPING keyword?
Normalization Question....
Comparing Two Tables Without a Cursor...HELP!!!
Split First name and last name if lastname is number