Songnian qian 2012-07-02 15:58:57
How to open two rowset with same connection?
In my application (I use OleDB consumer class), I need
loop though two tables. For example:
…. open database connection
table2.m_strFilter.Format(“Index = %d”, table1.m_nIndex);
From SQL trace, table2.Open(&db) cause open new database
connection and therefore, it is very slow.
Why the second open cause opening new database connection?
How to solve the problem?
Brannon jones 2012-07-02 15:59:05
The problem is you can only have one active rowset per connection. If you
try to use a second rowset, while another is still active, the provider will
automatically open a new connection for you (the alternative is that you
would receive an error that the connection was busy, like you do in ODBC).
In this scenario you either need to use two seperate connections, or use
server-side cursors. If using two connections is acceptable, I would leave
your code as-is and let us open the second connection implicitly. That way,
if we change the limitation of one active rowset per connection, we will do
the right thing for you and you wont have to change your code.
It looks like you are opening a new rowset for each row of your first
rowset. This will never perform very well. What sort of work are you doing
for the second rowset? Can it be done in a stored procedure on the server?
Can you modify your two queries to produce a single query?
Developer – MDAC
This posting is provided “as is” with no warranties and confers no rights.
Robert sedor 2012-07-02 15:59:15
Doesn’t CCommand.Close free the rowset and accessor.
Mombu 2012-07-02 15:59:26
Thank you for your help.
The table1 and table2 are different table ralated by a
Index1 (auto index, primary key)
Index2 (auto index, primary key)
Index1 (same as Table1.Index1, foreign key)
I solved the problem by add rowset property
to table1 and table2. When I use SQL trace, no new
connection any more.
My question are:
1. BY default, the cousor is server-side, why
should I set SERVERCURSOR property?
2. For better performance, what is the rule for open
multiple rowsets? Could you recommand any article?
connection. If you
active, the provider will
alternative is that you
you do in ODBC).
connections, or use
acceptable, I would leave
implicitly. That way,
connection, we will do
of your first
work are you doing
procedure on the server?
confers no rights.