SET ROWCOUNT set by ODBC... 
Author Message
 SET ROWCOUNT set by ODBC...

Friends,

My earlier post about "sp_cursorprepexec" seems to me now to be more about
SET ROWCOUNT.  My basic query uses a great plan unless preceeded by SET
ROWCOUNT <some big number>.

SET ROWCOUNT 1987651

SELECT ItemBillingID
FROM ITEMBILLING
WHERE ItemActivityID = 26240

A "SET ROWCOUNT 0" yields a great plan using "seek", with no "index scan".
The sad part is that MFC or ODBC must be initiating the SET ROWCOUNT; I
don't do it.  God only knows where it arrives at <some big number>, since it
isn't even close to the row count of the table, or any other meaningful
value.  THe number of rows to be returned is between 0-3 or so, and the
index on "ItemActivityID" is very very selective.

So "sp_cursorprepexec" in and of itself doesn't ruin the query plan, but the
SET ROWCOUNT seems to.

My latest questions, if you have a minute:
1) Why does "set rowcount" affect the plan?
2) More importantly, who is sending such stuff, (ODBC?) why, and how might I
prevent it, alter it, or in some way get around this.

I've been frustrated becaues our SQL is good, indexes are well thought out
and tested, stats healthy, and hardware capable, but still some really bad
plans, scans, and unneccesary IO.

As always, thanks for taking time to consider my troubles. I remain
appreciative and hope to return the help some day.

James



Wed, 06 Apr 2005 05:12:51 GMT
 SET ROWCOUNT set by ODBC...

James,

Are you actually sending the select statement to the back end as an adhoc
query or are you calling it thru a stored procedure?  If it is adhoc then
try making a sp with an ItemActivityID parameter and calling it that way to
see if that helps.

--
Andrew J. Kelly,  SQL Server MVP
TargitInteractive



Quote:
> Friends,

> My earlier post about "sp_cursorprepexec" seems to me now to be more about
> SET ROWCOUNT.  My basic query uses a great plan unless preceeded by SET
> ROWCOUNT <some big number>.

> SET ROWCOUNT 1987651

> SELECT ItemBillingID
> FROM ITEMBILLING
> WHERE ItemActivityID = 26240

> A "SET ROWCOUNT 0" yields a great plan using "seek", with no "index scan".
> The sad part is that MFC or ODBC must be initiating the SET ROWCOUNT; I
> don't do it.  God only knows where it arrives at <some big number>, since
it
> isn't even close to the row count of the table, or any other meaningful
> value.  THe number of rows to be returned is between 0-3 or so, and the
> index on "ItemActivityID" is very very selective.

> So "sp_cursorprepexec" in and of itself doesn't ruin the query plan, but
the
> SET ROWCOUNT seems to.

> My latest questions, if you have a minute:
> 1) Why does "set rowcount" affect the plan?
> 2) More importantly, who is sending such stuff, (ODBC?) why, and how might
I
> prevent it, alter it, or in some way get around this.

> I've been frustrated becaues our SQL is good, indexes are well thought out
> and tested, stats healthy, and hardware capable, but still some really bad
> plans, scans, and unneccesary IO.

> As always, thanks for taking time to consider my troubles. I remain
> appreciative and hope to return the help some day.

> James



Wed, 06 Apr 2005 15:53:26 GMT
 SET ROWCOUNT set by ODBC...
Yeah, it's an ad-hoc query passed to CRecordset::Open(), the results of
which are then fetched and displayed in our UI.  We have hundreds of
"lookups" like this one, and I'd hate to think we would need an SP for each
of them.  But, I'll certainly try it out.

Thanks very much for the suggestion Andrew.

James



Wed, 06 Apr 2005 17:01:42 GMT
 SET ROWCOUNT set by ODBC...
Yes I would definitely recommend using sp's vs adhoc queries.  I would
rather have 100's of sp's in one place than sql code in the front end.

--
Andrew J. Kelly,  SQL Server MVP
TargitInteractive



Quote:
> Yeah, it's an ad-hoc query passed to CRecordset::Open(), the results of
> which are then fetched and displayed in our UI.  We have hundreds of
> "lookups" like this one, and I'd hate to think we would need an SP for
each
> of them.  But, I'll certainly try it out.

> Thanks very much for the suggestion Andrew.

> James



Wed, 06 Apr 2005 18:51:23 GMT
 SET ROWCOUNT set by ODBC...
The reason SET ROWCOUNT impacts the plan is that the rowcount in effect at
the time a query is compiled is the same as if you added the "FAST n" hint
to the query.  So, if you do SET ROWCOUNT 1987651 then the optimizer is
forced to assume you are retrieving a large number of rows, which perfectly
fits with scanning the clustered index.

ODBC sends the SET ROWCOUNT because someone told it to limit the number of
rows returned.  For example, they called SQLSetStmtOption to set
SQL_MAX_ROWS.  If you aren't playing around with SQL_MAX_ROWS then perhaps
something in MFC is.  But given the odd number being picked, I'd suggest
that there also could be a memory overwrite bug in your application.

--
Hal Berenson
True Mountain Consulting



Quote:
> Friends,

> My earlier post about "sp_cursorprepexec" seems to me now to be more about
> SET ROWCOUNT.  My basic query uses a great plan unless preceeded by SET
> ROWCOUNT <some big number>.

> SET ROWCOUNT 1987651

> SELECT ItemBillingID
> FROM ITEMBILLING
> WHERE ItemActivityID = 26240

> A "SET ROWCOUNT 0" yields a great plan using "seek", with no "index scan".
> The sad part is that MFC or ODBC must be initiating the SET ROWCOUNT; I
> don't do it.  God only knows where it arrives at <some big number>, since
it
> isn't even close to the row count of the table, or any other meaningful
> value.  THe number of rows to be returned is between 0-3 or so, and the
> index on "ItemActivityID" is very very selective.

> So "sp_cursorprepexec" in and of itself doesn't ruin the query plan, but
the
> SET ROWCOUNT seems to.

> My latest questions, if you have a minute:
> 1) Why does "set rowcount" affect the plan?
> 2) More importantly, who is sending such stuff, (ODBC?) why, and how might
I
> prevent it, alter it, or in some way get around this.

> I've been frustrated becaues our SQL is good, indexes are well thought out
> and tested, stats healthy, and hardware capable, but still some really bad
> plans, scans, and unneccesary IO.

> As always, thanks for taking time to consider my troubles. I remain
> appreciative and hope to return the help some day.

> James



Wed, 06 Apr 2005 19:13:05 GMT
 SET ROWCOUNT set by ODBC...
Very interesting.  This gives me some room to research things a bit.  We
would be pleased if SET ROWCOUNT were never called on a connection, ever.
So I'll work toward that.  If it's burried in MFC, it might be tougher to
solve, but there is a chance somebody misundestood something way back when
the client side was coded.

Thanks so much for the ideas.

James



Wed, 06 Apr 2005 21:29:35 GMT
 SET ROWCOUNT set by ODBC...
The app uses our own "application framework", which is configured via
table/field/join information in static structures.  The SQL is generated on
the fly at the client side, so SPs for many things are not really an option
right now.

We do have many hundreds of SPs for all insert/update/delete operations.
These enforce busines rules, and all that, and are a different optimization
challenge.  (We were just introduced to parameter sniffing, and that
highlight a few changes that we really needed to make.  Armed with the new
information we are honing the performance of things with great results.

Thanks for all the help.

James



Wed, 06 Apr 2005 21:33:44 GMT
 SET ROWCOUNT set by ODBC...
Hal,

Best explanation I've ever read on why SET ROWCOUNT can impact the query
plan!

Thanks for the information!

BPM


Quote:
> The reason SET ROWCOUNT impacts the plan is that the rowcount in effect at
> the time a query is compiled is the same as if you added the "FAST n" hint
> to the query.  So, if you do SET ROWCOUNT 1987651 then the optimizer is
> forced to assume you are retrieving a large number of rows, which
perfectly
> fits with scanning the clustered index.

> ODBC sends the SET ROWCOUNT because someone told it to limit the number of
> rows returned.  For example, they called SQLSetStmtOption to set
> SQL_MAX_ROWS.  If you aren't playing around with SQL_MAX_ROWS then perhaps
> something in MFC is.  But given the odd number being picked, I'd suggest
> that there also could be a memory overwrite bug in your application.

> --
> Hal Berenson
> True Mountain Consulting



> > Friends,

> > My earlier post about "sp_cursorprepexec" seems to me now to be more
about
> > SET ROWCOUNT.  My basic query uses a great plan unless preceeded by SET
> > ROWCOUNT <some big number>.

> > SET ROWCOUNT 1987651

> > SELECT ItemBillingID
> > FROM ITEMBILLING
> > WHERE ItemActivityID = 26240

> > A "SET ROWCOUNT 0" yields a great plan using "seek", with no "index
scan".
> > The sad part is that MFC or ODBC must be initiating the SET ROWCOUNT; I
> > don't do it.  God only knows where it arrives at <some big number>,
since
> it
> > isn't even close to the row count of the table, or any other meaningful
> > value.  THe number of rows to be returned is between 0-3 or so, and the
> > index on "ItemActivityID" is very very selective.

> > So "sp_cursorprepexec" in and of itself doesn't ruin the query plan, but
> the
> > SET ROWCOUNT seems to.

> > My latest questions, if you have a minute:
> > 1) Why does "set rowcount" affect the plan?
> > 2) More importantly, who is sending such stuff, (ODBC?) why, and how
might
> I
> > prevent it, alter it, or in some way get around this.

> > I've been frustrated becaues our SQL is good, indexes are well thought
out
> > and tested, stats healthy, and hardware capable, but still some really
bad
> > plans, scans, and unneccesary IO.

> > As always, thanks for taking time to consider my troubles. I remain
> > appreciative and hope to return the help some day.

> > James



Thu, 07 Apr 2005 00:50:53 GMT
 SET ROWCOUNT set by ODBC...
For what it's worth, we have victory!  Sure enough, in our code we call

SQLSetStmtAttr(SQL_ATTR_MAX_ROWS, SQLPOINTER(m_sqluiMaxRows));

The docs state that the second paraemter is a point to a value, so we used
to pass &m_sqluiMaxRows.  But further down in the documentation it clarifies
that the value is NOT a pointer for in/long/ulong.  So I removed the "&",
and we get no more bizarre SET ROWCOUNT 123245455 being sent to SQL Server.

Hal, thanks so much for the clues that led us to this fix!  And thanks to
everybody that took the time to help us through this!

James



Fri, 08 Apr 2005 17:32:14 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. setting rowcount via odbc

2. Set rowcount possible??? (VB/ODBC/SYBASE)

3. SET ROWCOUNT inside of a user-defined function

4. set rowcount 500000

5. PROBLEM with SET ROWCOUNT

6. set rowcount and cursor, which one performs better?

7. Sql Server SELECT & SET ROWCOUNT

8. Set RowCount in UDF

9. Update Top n rows Without Set RowCount

10. SET ROWCOUNT/ derived table

11. Question about Using SET ROWCOUNT on delete that fires triggers

12. Does SET ROWCOUNT affect subqueries?


 
Powered by phpBB® Forum Software