10 Seconds for MoveNext() on rowset?

[RogerGarrett]

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. 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

[Eli Gassert]

First of all, 250,000 is hardly a huge database. When yo uget to 25 million we'll consider it kind of big :)

Can you please post your code and give as much background as yo ucan? How are you measuring move next? Are you setting a timer, outputting data, and then stopping the timer and checking between intervals? What environment are you working in?

Sample code and a bit more background will be quite useful. Thanks.

Edit: don't forget to mention what kind of database you're using and what kind of connection. The actual connection string (minus the password, of course ;)) would be useful as well.

[RogerGarrett]

I'm using SQL Server 2000. I'm accessing it from a Visual Studio C++ MFC application.

I have the folowing two classes to accommodate the returning of the rowset.


class CRowsetAccessorTheData
{
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(CRowsetAccessorTheData)
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 CRowsetTheData : public CCommand<CAccessor<CRowsetAccessorTheData> >
{
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<CRowsetAccessorTheData> >::Open(Session, NULL, &propset);
};

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<CRowsetAccessorTheData> >::Open(Session, (LPCSTR)strQuery, &propset);
};
};



In my code I open the database with:


CDBPropSet dbPropertySet(DBPROPSET_DBINIT);
BOOL bOKSoFar;

HRESULT hResult;

hResult = m_Database.OpenFromInitializationString(
OLESTR("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=RogersPlaceOLEDB;Data Source=THUNDERBIRD;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=THUNDERBIRD;Use Encryption for Data=False;Tag with column collation when possible=False"), true);

if (SUCCEEDED(hResult))
{
hResult = m_Session.Open(m_Database);



I then construct the query and then do the following to perform the query and return with the recordset:



CRowsetTheData RowsetTheData;
hResult = RowsetTheData.Open(m_Session, strQuery);

if (SUCCEEDED(hResult))
{
hResult = RowsetTheData.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 = RowsetTheData.m_strMemberGuid;
// URL
CString strUrl = RowsetTheData.m_strUrl;
// IS ONLINE
BOOL bIsOnline = FALSE;
bIsOnline = (RowsetTheData.m_IsOnline == 1);
// IS SERVABLE
BOOL bIsServable = FALSE;
bIsServable = (RowsetTheData.m_IsServable == 1);
// CONNECTION TYPE
CConnectionTypes::CONNECTION_TYPE ConnectionType = CConnectionTypes::CONNECTION_TYPE_INVALID;
ConnectionType = (CConnectionTypes::CONNECTION_TYPE)RowsetTheData.m_ConnectionType;

so some operations with the data....

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


At the point where I do the MoveFirst() and the MoveNext() I single-step over the code and it takes from 2 to 12 seconds to complete.

[Eli Gassert]

Can you try two things for me (you might have already done this; if so tell me)
1) run your code in release mode vs debug mode
2) check out www.connectionstrings.com and dumb-down your connection string to be extremely simple -- only include the required parameters.

That's all I can think of at this point :/ if neither of those have a positive effect then I'll take another look at your code.

[RogerGarrett]

Running in release mode and "dumbing down" the connection string had no effect. It still takes up to 16 seconds to do a MoveFirst() or a MoveNext().

Please note, the query itself is running perfecetly fine and screaming fast.

It's only after the query has run and it returns the data within the recordset that the speed issues arise. It's the attempts to get to the very first record in the recordset (via MoveFirst()) and the attempts to move to subsequent records in the recordset (via MoveNext()) where the speed issue arises. It's simply incredibly slow.

I've followed the Microsoft examples on how to construct the recordset using the CCommand and CAccessor templates, and how to run the query against the database using the Open() method of the CCommand-derived class to obtain the recordset, and then to use its MoveFirst and MoveNext methods to traverse and retrieve the records of the recordset. It is the MoveFirst and MoveNext that are taking so long.

I would expect that the rcordset should be somewhat like a CArray object in which accessing the elements of it are extremely fast. But it's not. And it seems to be related to how big my database is. When my database is small (a few thousand records) the MoveFirst and MoveNext on the resultant database are fast. When my databse is somewhat large (250,000 records) then MoveFirst and MoveNext take many, many seconds to run.

It's as if the recordset itself does not actually contain the information, but rather has some kind of "reference" back to the database, so that when I doa MoveNext() on the recordset it actually has to sequence through the database to find the related record and retrieve the dat. Can that possibly be true?


- Roger Garrett

[Eli Gassert]

in some cases, it does only have a reference back to the data... but usually there is a cache size of records to fetch. The only thing I can think of is that this cache size is set to like 1 or something, but even then it shouldn't take that long to get back since the recordset should be active on the server as long as your connection is established :/

The last thing I can think of that would be slowing you down is the location of the cursor. If the cursor is kept on the server, then you are querying the server for the data. If it's kept on the client (more like the CArray method yo uwere talking about) then you're querying your local data. But I'm not familiar enough with the MFC functions to know for sure what kind of setup you have (or more importantly, to know if you can change these settings).

Sorry :( I think that's about all the advice I can offer on this one.

[RogerGarrett]

Thank you for taking a look at this.

I think I may have figured out the problem. In my Open method for my CCommand-derived class I add properties to it as follows:

propset.AddProperty(DBPROP_IRowsetChange, true);
propset.AddProperty(DBPROP_UPDATABILITY,
BPROPVAL_UP_CHANGE |
DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);

I took those verbatum from the Microsoft examples. I'll just bet that having those properties set means that my resultant recordset is inherently linked back to the database, rather than being just the data extracted from the recordset, since I'm telling it (I think) to allow me to use the recordset to perform updates and inserts and deletes.

I think I need to change those properties so that it's just a "read" from the database.

[Eli Gassert]

sounds reasonable, yes... which could be the same thing that I was saying about server-side vs client-side cursors... If you figure it out, please post up a reply, I'd be interested in knowing what the problem attribute was :)