Same function running at different speeds

Mauddib

Truely baffled here. Nav5execs on 3.60 code base. SQL 2005.

I have one function in a codeunit 5000 thats run in two companies.

In it is the following code:

PurchaseLine.SETCURRENTKEY("Document Type",Type,"No.","Variant Code","Drop Shipment","Location Code","Bin Code","Expected Receipt Date");
PurchaseLine.SETRANGE("Document Type", PurchaseLine."Document Type"::Order);
PurchaseLine.SETRANGE(Type, PurchaseLine.Type::Item);
PurchaseLine.SETRANGE("No.", CalcItemNo);
PurchaseLine.SETFILTER("Outstanding Qty. (Base)", '>0');
IF CalcLocationCode <> '' THEN
  PurchaseLine.SETRANGE("Location Code", CalcLocationCode)
IF PurchaseLine.FINDSET THEN REPEAT

The following is happening that I cant explain:

1) Firstly its running slower in the company with the smaller Purchase Line table. 50000 records compared to 160000 records.

2) The SQL code generated is different when viewed in SQL profiler. In the larger company I get:

WHERE (("Document Type"=1)) AND (("Type"=2)) AND (("No_"='C113790')) AND (("Location Code"='T-1')) AND (("Outstanding Qty_ (Base)">0.0))

while in the smaller

WHERE (("Document Type"=1)) AND (("Outstanding Qty_ (Base)">0.0)) AND (("Location Code"='F-1')) AND (("Type"=2)) AND (("No_"='376457'))

3) There is no ISEMPTY in my code but in both companies I seem to get many:

SELECT TOP 1 NULL FROM "TC1$Purchase Line" WITH (READUNCOMMITTED)

before the where statements above.

4) Removing the set current key only slows down both companies. It causes no other changes in the other issues mentioned here. No other key I have currently, even ones that would appear to be better, make and difference to the speed.

ara3n

I've never seen this, only the people who have written the code that translates CA/L code to T-SQL would answer that.

I thought that CA/L that was translated would be always the same, no mater if you had 1 record or 1 million.


Althrough I have seen different behaviors on how fast navision would create a cursor on sql.

kine

Are you sure, that both SQL queries are for same code in NAV? May be that some setting is different and thus another code is executed? Or what about using RESET before using the variable? May be different filters remains from another process which is different in each company? Did you tried to debug the code and watch the filters which are set in both companies?

Mauddib

Yes I tried to debug the code and i have confirmed the code is not for anything else also.

Each time before running my codeunit I had the SQL profiler only show calls from my username and I cleared the profiler before each test completly.

The code looks mostly the same. The only different really is that in the smaller company the code will slightly more often set the location code filter to blank. This happens in both companies but due to the nature of the smaller one is slightly more common.

The TOP 1 NULL is the strangest part of the whole thing for me. I thought this only occoured with an ISEMPTY call, which is definetly not there. Ive run both client monitor and code coverage, gone into the CAL code, done a SHOW ALL and then filtered the code on words like *ISEMPTY*, *FIND*, *COUNT* etc

Also I do have a RESET. The function itself in the codeunit only uses local variables also.

Im truely baffeled with this one :oops:

Mauddib

I should add that the SQLIndex for the key i use, the key that runs the fastest is:

No.,Type,Document Type

pdj

Do you have a SQL table called $ndo$dbconfig? If you do: What does it contain?

Mauddib

Nope. Ive played with settings in that table in the past on our test server when i was tuning our SQL performance, but we do not have this table anymore in test and never had it in the live system.

Mauddib

OK, I have now isolated the little bit of code that I posted before into a report on its own.

Report has an INTEGER datatype where Number=Const(1)

On predataitem is this:

CalcItemNo := '372644';

On AfterGetRecord is this:

CLEAR(PurchaseLine);
PurchaseLine.RESET;
PurchaseLine.SETCURRENTKEY("Document Type",Type,"No.","Variant Code","Drop Shipment","Location Code","Bin Code","Expected Receipt Date");
PurchaseLine.SETRANGE("Document Type", PurchaseLine."Document Type"::Order);
PurchaseLine.SETRANGE(Type, PurchaseLine.Type::Item);
PurchaseLine.SETRANGE("No.", CalcItemNo);
PurchaseLine.SETFILTER("Outstanding Qty. (Base)", '>0');
PurchaseLine.SETFILTER("Location Code", '%1', '');
IF PurchaseLine.FINDSET THEN REPEAT

UNTIL PurchaseLine.NEXT = 0;

I have set the SQL profiler in 2005 Management studio to show me anything that happens that is greater than 100 reads.

I have chosen an item that is not on a PO in either company so no records will be found in either by this FINDSET.

In the company with the large PO Line table no query shows up in the profiler, hence its less than 100 reads.

In the company with the smaller PO Line table the query shows with 139796 reads!!!! This table has exactly 56874 records so this is 2.5 times more reads than records in the table.

The query resulting from the findset, even after a DBCC FREEPROCCACHE is (and is the same whether the FINDSET gets records or not):

SELECT TOP 1 NULL FROM "navtestsql"."dbo"."BC1$Purchase Line" WITH (READUNCOMMITTED)   WHERE (("Document Type"=@P1)) AND (("Type"=@P2)) AND (("No_"=@P3)) AND (("Location Code"=@P4)) AND (("Outstanding Qty_ (Base)">@P5))

The ONLY other difference I can think of between the two companies is that most Item Numbers in the company where things are running well start with an alpha character, where they do not in the company with the slower system (numberic here). Does this affect SQL in any way?

Both companies are in the same database

kriki

I think it has something to do with SQL selecting an index to use.
I think that SQL decides to use a clustered index scan for the smaller company because he thinks it is faster (Sometimes SQL DOES make a mistake in selecting the best key).
In case the order is not so important, you can put MaintainSQLIndex to No in this key and create a new key. Something like
"Item No.,Document Type,Type,Location Code,Completely Received"

And this is the code to be used:

PurchaseLine.RESET;
PurchaseLine.SETCURRENTKEY("Item No.,Document Type,Type,Location Code,Completely Received");
PurchaseLine.SETRANGE("No.", CalcItemNo);
PurchaseLine.SETRANGE("Document Type", PurchaseLine."Document Type"::Order);
PurchaseLine.SETRANGE(Type, PurchaseLine.Type::Item);
IF CalcLocationCode <> '' THEN
  PurchaseLine.SETRANGE("Location Code", CalcLocationCode) 
PurchaseLine.SETRANGE("Completely Received",FALSE);

Some remarks:
1) I put Location code more to the end of the key, because you don't have always a filter on it.
2) "Completely Received" is a boolean and smaller than a decimal. I also put it in the index. In this way I create a "Covering Index" containing all fields in the WHERE and in the ORDER BY.

Mauddib

The order isnt important in this case, it just seems to work faster in both companies with that key.

As the key is used else where I dont want to change it any more.

To get around it I have only filtered on the first three filters which are whats in the index that SQL maintains. I populate the results into a temporary table (Navision Native) and filter that using the full set of filters.

This has solved the problem, but not the mystery which still remains to be seen.

I gather from my tests that the SELECT TOP 1 NULL is a natural part of FINDSET? It will always do this first before checking whether to do a SELECT TOP 501? I HAD thought the Top 1 Null was only seen with ISEMPTY.