Dynamic cursor behaviour

kriki · 2010-02-15

I just wondered about something:
We know that NAV (the latest NAV version and we have some exceptions anyway. But I am not going into this now.) uses dynamic cursors for getting records.
And we know that SQL in this case looks more at the ORDER BY clause to decide which index to use.
And we also know that SQL looks more at the WHERE clause in case of fast forward cursors.

Now my question : wouldn't it be good if we can influence SQL server through a trace-flag to look more at the WHERE clause to decide the index to use in case of a dynamic cursor?

I did some searches (google, MS connect under SQL, SQL server books online) but I couldn't find such a flag.

If someone know such a flag, let me know.

In the other case, I will post a suggestion on connect.

NaviDeveloper_NL · 2010-02-15

A fast forward cursor copies the query result to tempdb and reads the fetches from tempdb.

The dynamic cursor reflects all changes made to the underlying tables. Each time a fetch
is called the cursor is re-evaluated.

For this reason sorting of a dynamic cursor is not practical.

stryk · 2010-02-15

Hi!

Well, actually there is a reason why SQL Server supports different types of cursors, thus, there might also be a real purpose for using "Dynamic Cursors" ...
So basically it should always be up to the process declaring the cursor to define the appropriate type. To generally overrule this in SQL Server is IMHO a bad idea.
I think the best way would be to give the NAV developers/administratirs a chance to define the required cursor type within NAV; e.g. by a flag set with FIND or FINDSET, or maybe NAV-globally using the "diagnostics" feature in table "$ndo$dbproperty" (similar to disabling "cursor preparation" or using "repeatableread").

Cheers,
Jörg

kriki · 2010-02-15

Well, there is a version in which the fast forward cursors are used for forms and dynamic cursors for the rest.
But still no way to always force fast forward cursors.

Probably it would be best to use a NAV-globally. This way we have the best of both worlds. Dynamic cursors out-of-the-box. And the possibility to change to fast forward cursors with heavily SQL-tuned systems.

pdj · 2010-02-15

I agree - I want fast forward cursors as well...

However; it is possible to force it in code like this:

COMMIT; // For some reason...
CURRENTTRANSACTIONTYPE := TRANSACTIONTYPE::Browse;

Haven't used it yet - just saw it in a Microsoft WebCast the other day.

kriki · 2010-02-15

But the problem is still that you have to change the code.
Before it was enough to play with the properties of the keys in NAV which is a lot easier and faster to do.
Searches for all places where the table is used in a certain way is a lot more work and thus costlier.

pdj · 2010-02-15

kriki wrote:

But the problem is still that you have to change the code.

Yes, and I didn't say I liked the method - it is just the only way to do it, yet

Howdy, Stranger!

Quick Links

Categories

Dynamic cursor behaviour

Comments