sp_cursor fetch, FETCH API CURSOR - How to handle with this?

You filter for "Item No." and you have an index starting with "Item No." (i guess).
So, there is not so much space for optimizations.

If you have thousands or millions of Item Ledger Entries for one single item than 5000 reads isn't much.

Most times it isn't very usefull, to just look in trace file with Profiler and pick a single statement.
What you want to know is, wich statements have in sum produced most Reads, Writes, Duration etc.

There are some free SQL trace analyze tools like ClearTrace or Qure Analyzer that can parse trace files and show you aggregates.


Tobias

Great to have another example how FINDSET could improve things by avoiding these "cursors"

Some other consideration:
T32 "Item Ledger Entry" has - as many other LE tables - a general problem. By default the "Clustered Index" is set to the "Primary Key", which is "Entry No.". Hence, the records are physically sorted by "Entry No.".
But this means, that records belonging to the same "Item No." may be spread over wide areas of that table! Thus, if you query filtering on "Item No." SQL Server might need to read the data over wide areas. The more records there are and the more they are "spread out", the higher is the probabilty that SQL Server starts scanning the table, causing huge I/O etc..
In some cases it is feasible to change this "Clustered Index", e.g. by adding a new Key: Item No., Entry No. - no SQLIndex property, mark as "Clustered".
In this case the records will be sorted by "Item No." first, hence, records belonging to the same Item are physically next to each other! So the range to read those records is dramatically smaller, reducing I/O, avoiding "scan" issues ...

Have in mind that changing the CI is a huge workload on the system (all records have to be re-arranged)! And of course: this needs to be tested thoroughly first!

You're absolutely right! It's all a matter of balance and "If this then that or maybe not" - that's why I said "test thoroughly". But have in mind that your concerns basically affect 99% of all indexes in NAV, thus changing the CI on T32 is not such an unsusal thing.

ndbcs wrote:

I don't think that it's a problem to have Entry No. as the CI, but I think it's in general the best CI you can have (unique, narrow, static, ever-increasing) (if they change it to auto-increment it would be much better, but that's another story).

Indeed it IS a problem which affects most of the LE tables! Item Ledger Entry, G/L Entry, Customer Ledger Entry, etc. they are all affected by the same problem - and from a physical perspective it is a problem, which is responsible for many index scans (or other stupid QEP). "Entry No." is actually the worst CI you could have - true, it has the highest selectivity - but data is allmost never queried on that field. The primary criteria for querying data is - I daresay in 99% of all cases - something with "Item No.", thus arranging the data on basis of this "primary query criteria" is a smart idea! And besides solving some I/O issues this has also tremendous impact on some blocking situation, as in SERIALIZED transactions (LOCKTABLE) usually range-locks are established. The wider that range, the more blocking potential ...
Generally: as you said, having the CI on the PK is mostly a smart idea, but in some cases other considerations may be done ... as so often: it depends ...

ndbcs wrote:

1. If you change to Item No., Entry No. both fields have to be duplicated in every secondary index (CI is part of every sec. index). So every sec. index will use more disc/ram space and produces more I/O when reading/writing that index.

True. But actually we are talking about "peanuts" here ... In NAV there are far worse indexes ...

ndbcs wrote:

2. Page splits in CI will occur. With just Entry No. as CI we have an ever increasing field as CI. So we will don't see page splits and we don't need to reindx the CI (Item Leder Entries will rarely be deleted). With Item No., Entry No. there will be many page splits and we have to reindex a lot. Item Ledger Entry is often one of the biggest tables in our DBs and reindex takes a lot of time. And if you don't have a SQL Server Enterprise Edition you have to reindex offline.

Again, also "Page Splitting" is daily business. Actually that's just a matter of adjusting the approriate Index-Fillfactor (if you have tools that could calculate this optimizerd FF 8) ) and the method you use to defragment/reindex (IMHO: the standard features suck, that's why I use optimized utilities).
While a "Entry No." CI needs a FF of 100%, with changing you need something below, hence, the index will grow. But then again: size does not matter. Technically larger indexes require more effort in updating and maintaining them, but we're talking about microseconds here - something which might be acceptable compared to the potential benefit regarding read-performance and blocking.
So if indeed the write-performance/"experience" is affected strongly depends on the underlying hardware resources.

ndbcs wrote:

3. I don't see the scanning problem. We have secondary indexes that can be seeked for Item No..

This also depends. The problem actually occurs with Items that are daramatically more often posted than others, thus if the ratio of a certain "Item No." is remarkably greater than others, as this actually decreases the "selectivity" of that field ...
If you have a problematic query on "Item No." indeed you'll mostly see the QEP performing "Index Seeks" (rarely a "Scan") but causing thousands of "Reads" as the "Key Lookup" operation is causing most of the effert ...

ndbcs wrote:

We only avoid bookmark lookups, when filtering for Item No.
If we filter for Item No. plus some other fileds chances are good that SQL Server will use a secondary index plus bookmark lookups.

How do you avoid "Bookmark Lookups"? If NAV is firing a SELECT * you'll always have a key lookup, except if your index is covering - means in this case: it INCLUDES all (!) other fields ... Creating such an index on T32 is something a absolutely would NOT recommend ...

ndbcs wrote:

There maybe situations where all those overhead is justified, but those are very rare.

Not so rare in my experience - and I deal with this every day.

To sum up:
Changing a Clustered Index is something which needs to be discussed within context of the current problem. There are advantages and disadvantages which have to be compared (I guess Tobias and I gave an example for such a discussion) - what's a solution for one system might be a problem for another; with NAV/SQL performance there's hardly something like "IF THIS HAPPENS THEN DO THAT", it's mostly "IT DEPENDS" ...
Thus, having all this in mind: just test to find out if this could help you!

Ah, now I understand! Yep, I agree ...