setcurrentkey & SQL

Belias

maybe it's only a wrong knowledge I have, but...
i have a form, and i open it from a codeunit, setting filters on fields a,b and c.
i have the necessary key for this set of filters; in onopenform trigger i have a setcurrentkey on a different key (with ASCENDING(FALSE)), but it should not be a problem as sql should choose the best key form my query.
The problem is that from client monitor i see a 3 seconds elapsed time with setcurrentkey instruction, and a 0,6 seconds without setcurrentkey instruction.

i'm using 4sp3 with sql 2005...can anyone explain this please?i can't figure it out

Fisherman

Belias,

As I understand it, the only way that SetCurrentKey makes a difference in the actual execution of the SQL Server Query Optimizer is if the SQLIndex Property is set on the table key. However, what I've read on MVP sites has indicated that this is not a good idea, because of the default behavior of SQL Server to find the best index for query execution. If you are not incredibly careful in how you structure the indices, then you could actually degrade performance if SQL attempts to execute the query with the index specified through the SQL Index property of the key. It was recommended in what I was reading that you leave this property as the default state (blank), and instead use the SQL Server Performance Tuning wizard and SQL Server Profiler in order to capture, replay, and create indices based on actual transactions coming from NAV.

If the SQLIndex property is not set on the key, then my understanding is that C/SIDE organizes the data according to that key when the data is delivered from SQL Server to the 4GL engine. Basically, table keys in C/SIDE do not correspond to SQL Server Indices unless you tell them to. This means that C/SIDE is the only link in the chain that cares about the value of the "SETCURRENTKEY" method if the SQLIndex property is blank.

You may be noticing a performance increase due to how C/SIDE is optimizing the data once it's delivered from SQL Server, but unless SQLIndex is set to a good, well-defined and well-covered SQL Server index for the key in question, the performance increase that you're seeing is likely not from SQL.

Belias

Thanks fisherman, but unfortunately it's not SQLIndex property, so, let's wait for someone to confirm your second guess (which is one of my thoughts, too) :-k

Fisherman

OK.

If you don't have the SQLIndex property set, then it makes sense.

Assuming that the SQLIndex property WERE set, and DID line up with a well-formed, well-covered SQL Index, then NAV would set the current key (SETCURRENTKEY), and would expect SQL to hand the data back to it presorted and preoptimized for that key.

That means that if SQLIndex is NOT set, then SQL Server is handing the data back to C/SIDE in the default order for that index, which often boils down to the table's primary key. C/SIDE would then have to reorder/resort the data for what you need. If you also have not specified the C/SIDE key through SETCURRENTKEY, but want to do sequential work on the resulting data, then I could understand why it would take longer, because the data is likely not be in the optimized order.

DenSter

Almost, but not quite. Whether SQLIndex has a value or not does not determine whether a key in the NAV table definition has a corresponding index on SQL Server. This is not managed by the SQLIndex property but by the MaintainSQLIndex property. This checkbox basically means "I want this key to be an index on SQL Server". When it is turned on, NAV will create an index on SQL Server with the same fields, and makes sure that all primary key fields are also included in this index, to make all index values unique.

The SQLIndex property is there so that if you decide that this index is not the right one (for whatever reason), you can change the index on SQL Server from the NAV table designer, and you don't have to modify any of that on SQL Server directly. Another benefit of this property is that this way all NAV code still works (SETCURRENTKEY looks at the key, not the SQLIndex), and there is a way to "optimize" the index on SQL Server. I think this is one of those properties that they had high hopes for to solve all performance problems, but that actually did not make quite the impact they thought it would.

Now as for when SQL Server decides to use what index..... SETCURRENTKEY eventually turns into the ORDER BY clause. It used to be that SQL Server would pretty much ignore the ORDER BY clause when deciding which index to use (or so I've been told), but every time that I am involved in any performance issue, I see that this does have an effect after all. Many times you see SQL Server select the index from the ORDER BY statement, so your best bet is to make sure that the fields in the indexes are in a selective order.

Fisherman

Good to know. Thanks, Den.

I guess it makes sense that the SETCURRENTKEY would translate to an order by.

The WHERE clause is going to have the heaviest impact on index performance, depending on whether the index is completely covered by the columns specified in the WHERE clause, and whether the Index is sufficient to prevent a table scan depending on which columns are specified.

GROUP BYs also have a pretty big impact. ORDER BYs normally aren't too bad, because they're delayed. They occur after the query results are determined and before they're delivered. It's strange that they would have that much of an impact on query execution. Maybe it's coincidental?

Belias

My thought was that setcurrentkey doesn't affect performance in a sql database...but maybe it's not alway true :-k , i've done a test with and without the sqlindex property (with setcurrentkey instruction, also), but after i reinserted the flag on maintainsql i didn't noticed performance issues (as i've had before)...maybe because the index have been rebuilt?

DenSter

Fisherman wrote:

ORDER BYs normally aren't too bad, because they're delayed. They occur after the query results are determined and before they're delivered. It's strange that they would have that much of an impact on query execution.

No I don't think you can say that. The ORDER BY is definately a parameter for the query optimizer, it is definately not ignored until after the query has been executed. I've seen SQL Server select a different index due to a different ORDER BY clause, same query, same WHERE clause.

Fisherman

Den -

I've been looking for an online copy of something I read in a "Inside SQL Server" book. This is the only one that I've found, but it illustrates the point:

http://www.sql.co.il/books/insidetsql20 ... Poster.pdf

If you think about it, ORDER BYs can only be executed after the result set is known. If you begin ordering by DURING retrieval of the resultset, then you will be constantly reorganizing the data, or reshuffling pointers in a hash table. The only way to prevent this kind of negative impact on performance is to retrieve the data, and reshuffle it one time according to the ORDER BY.

I don't know how it might effect the query engine if you didn't completely cover an index in an ORDER BY statement, but I think it's odd that SQL Server would completely swap out indices in creating the execution plan since ordering normally takes place after the fact, and often in tempdb:

http://msdn.microsoft.com/en-us/library/ms345368.aspx
http://blogs.msdn.com/sqlserverstoragee ... ation.aspx

From the second article:

"...I had indicated in my earlier blogs, the SQL Server uses TempDB to store intermediate results as part of executing a query, for example to create a hash table or to sort as a result of order by..."

Fisherman

ACTUALLY - I just remembered something.

IF SQL Server, during the evaluation of the statement while generating an execution plan, find an existing INDEX on which the data is already sorted and which will satisfy the ORDER BY clause, then it will use that index as opposed to retrieving and reorganizing the data after the fact.

If it does not, then it does pull the data, create a temporary store in tempdb, reorganize it, and delivers it.

kine

Take all what is there written, add dynamic cursors, mix it, drink it... :shock:

With the dynamics cursors it is much more complicated.

Regarding the time in the client monitor - sometime I think that the elapsed time is only difference between one command and next command which the client monitor can catch. And if there is longer processing without using the DB, it can be affected by this. But I am not sure with that. E.g. few days ago I was solving some problems where report took to long to generate, SQL Profiler shows me only commands with duration under 200ms, but the client monitor shows me on the command over 30 seconds. But the execution plan of the query was "Index Scan" on exact index which was same with the ORDER BY... but same command executed on SQL gives me "Index Seek" on another index. I expect that it was because NAV was trying to open dynamic cursor... Redesigning the indexes solved that but... :-k

Belias

whoa!it's getting more and more complicated...anyway, tomorrow here in italy is holiday, see you on monday and thanks a lot, for now!

Fisherman

"Take all what is there written, add dynamic cursors, mix it, drink it

Yeah. I know. Optimizing indices on the NAV database has turned out to be a joy with SQL profiler because of that. :? It took me a while to figure out which events to include in the trace.

From what I understand, that's one of the primary motivations behind the new "OPTIMIZE FOR UNKNOWN" feature in SQL Server...

DenSter

We can theorize about whether it should or shouldn't, or if it makes sense or not, and we can read a million articles talking about it. The fact remains that the ORDER BY clause DOES affect the execution plan.

Fisherman

Den -

I agree. That's why I posted the follow-up to my previous post.

If you specify an index hint that SQL Server determines is not optimal given that the ORDER BY can be resolved on another index without degrading performance during data retrieval, and can return the data in the requested order, then it will disregard your index hint and pick the other index. That would make sense, for example, if you SETCURRENTKEY, and then set filters/ranges within that key (which would translate to a WHERE clause), and then asked for the data to be sorted ASC. SQL Server may look at the requested index, determine that there's already an index on the table that sorts it as requested while providing coverage for the fields in the WHERE clause, and use that index. If you have specified the SQLIndex property on the KEY in NAV, which results in an INDEX hint statement, it may completely ignore your hint in favor of the index that is optimized.

If it doesn't find an optimal index to satisfy both the WHERE and the ORDER BY (and other clauses, JOINS, EXISTS, etc...), it will use the most optimized index it can (or scan), and move the intial, unsorted resultset to tempdb, and resort it. That will also effect the total execution plan, because it's an extra step, but this scenario wouldn't affect the index that it uses to retrieve the data from source, unless the requested index was suboptimal to begin with.

David_Singleton

Belias wrote:

i'm using 4sp3 with sql 2005...can anyone explain this please?i can't figure it out

You also need to know that it depends exactly what version you are on. You really want to be on at least Build 26565.

Check here to find your current NAV Build No. Then look at Waldo's Blog for more info about the build no.

Belias

my build no. is 26954, i should have told it before, sorry.
i have some other questions, if anyone can kindly answer me

what is dynamic cursors?
what is index seek and index scan?Does they refer to table scan and table seek?
(refer to kine's post)
if there's a documentation about this, please suggest it to me.

Thanks in advance

Belias

In these days, i'm getting more and more interested in NAV/sql performance (it's something where i'm not yet experienced in), so, i started browsing sqlperform section of mibuso and i found my answer(s):
http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2009/02/20/cursor-types.aspx