I have a customer suffering a particular performance problem. For example , with you drill down in the “Qty. on Purch. Order” in the Item card , sometimes you have to wait several seconds (tens) to have the Purchase Line form. With Profile I ‘ve noticed this behaviour: There are a lot of server calls like exec sp_cursoropen @P1 output, N’SELECT * FROM “DATABASE”.“dbo”.“COMPANY$Purchase Line” WHERE ((“Document Type”=@P1)) AND ((“Type”=@P2)) AND ((“No_”=@P3)) AND “Document Type”=@P4 AND “Type”=@P5 AND “No_”=@P6 AND “Variant Code”=@P7 AND “Drop Shipment”=@P8 AND “Location Code”=@P9 AND “Bin Code”=@P10 AND “Expected Receipt Date”=@P11 AND “Document No_”=@P12 AND “Line No_”>@P13 ORDER BY “Document Type”,“Type”,“No_”,“Variant Code”,“Drop Shipment”,“Location Code”,“Bin Code”,“Expected Receipt Date”,“Document No_”,“Line No_” OPTION (FAST 10)’, where every next server call has the same clause WHERE without a field. That is the next call has …AND “Document No_”>@P12 ; the next …AND “Expected Receipt Date”>@P11 and so on. And then it repeates the same calls , but with ‘<’ in the site of ‘>’. The problem is that the server call with …AND “Document No_”<@P12 , it is resolved with a “Clustered Index Seek” spending (and so waiting) tens of seconds (with thousands of reads), while the other calls are solved with Index Seek on $Purchase Line].[$7] and then a bookmark lookup. We have 3,70 version technically migrated to V4.03 and the database is on SQL2000.I’ve tested with SQL2005 , but the problem is the same. Surely it depends on the calcformula of the flow field “Qty. on Purch. Order” , but Does anyone know how can I do to eliminate such a behaviour? Thank you in advance
Sorry , But I’ve some problem with the character "less than"truncating my post…The post is: I have a customer suffering a particular performance problem. When you drill down the “Qty. on Purch. Order” in the Item card , sometimes you have to wait tens of seconds to obtain the Purchase Line form. With Profile I’ve seen that the delay is due to a server call : “exec sp_cursoropen , N’SELECT * FROM COMPANY$Purchase Line WHERE ((Document Type=@1)) AND ((Type=@2)) AND ((No_=@3)) AND Document Type=@4 AND Type=@5 AND No_=@6 AND … AND Document No_ “less than”@12 ORDER BY “Document Type”,“Type”,“No_”,…,“Document No_”,“Line No_” OPTION (FAST 10)’”, that is resolved with a Clustered Index Scan with a lot of reads. Surely the query is generated for the CalcFormula of the flow field “Qty. on Purch. Order” along with a lot of other queries of the same type. But “Document No.” is not in the CalcFormula. Why the driver generate such a query? Is there a way to workaround this generation ? Thank you in advance for any help.
Check if you have done an indexrebuild lately or at least calculated the SQL-statistics.
If this is not the case, you should ask help from Navision-on-SQL-specialists. It might be there is some tuning to be done.
I could give you some ideas to try, but it is possible they hurt more then they cure. This depends on how your DB (and specially that table) is used.
Thank you for the answer.
Asking help . it means to open a request support from PartnerSource, is not it?
Just for a complete information :
The platform is SQL2000.
Every night there is an Optimization from Maintainance Plan for the database and it goes right.
The statistics properties for the database are set ON.
The table in exam is the “Sales Line” (800.0000 rec) and randomly a query with a cursoropen on SELECT * FROM “Sales Line” WHERE ((“Document Type”=1)) AND ((“Type”=2)) AND ((“No_”=‘ZZZZZZZ’)) AND “Document Type”=1 AND “Document No_” less than ‘YYYYYYY’ ORDER BY “Document Type” DESC,“Document No_” DESC,“Line No_” DESC OPTION (FAST 10)
is sometimes solved with a clustered Index Seek (Document No.,Line No.,Document Type) rather than the $1 built on “No.”.
And it waits for 200 seconds.
With Query Analyzer it extimates the Plans with $1 , but with Profiler (launching from NAV 4.0 SP1) it chooses (not always , but randomly) the Clustered Index Seek.
I’ve tried to change the Clustered Index (copying from NAV 5.0) and also $1 , but nothing helps.
I’ve read the Index Hinting , and I like to test.
Is there anyone knowing what is to put in the KEY parameter when launching the Form Sales Orders from item card?
For kriki (visto che vedo Milano…)
Ciao e buon lavoro (a patto di non lavorare con chi ha progettato quel driver di “conversione” chiamate nav–>sql)
Hi!
At first I have to admit, that I’m not fully sure if I understood your problem, as it’s quite tough to read the query in that format …
Well, thsi statement is not caused by a FlowField, then the statement would start with a SELECT SUM(…) etc.
So, this is the problematic statement:
SELECT * FROM "DATABASE"."dbo"."COMPANY$Purchase Line" WHERE (("Document Type"=@P1)) AND (("Type"=@P2)) AND (("No_"=@P3)) AND "Document Type"=@P4 AND "Type"=@P5 AND "No_"=@P6 AND "Variant Code"=@P7 AND "Drop Shipment"=@P8 AND "Location Code"=@P9 AND "Bin Code"=@P10 AND "Expected Receipt Date"=@P11 AND "Document No_"=@P12 AND "Line No_">@P13 ORDER BY "Document Type","Type","No_","Variant Code","Drop Shipment","Location Code","Bin Code","Expected Receipt Date","Document No_","Line No_" OPTION (FAST 10)This is loaded into a cursor (sp_cursoropen) and has an OPTION (FAST …) hint which shows it’s called from a form. If this is executed in a Clustered Index Scan, this simply shows that no better index is found, but you say $1 (should be NAV Key ID 2 in standard 4.00) would be appropriate?
Changing the Clustered Index to “Document No.,Line No.,Document Type” is generally a good idea, but may not solve the problem. Also index hinting is something to handle with care … but just try:
CREATE TABLE [$ndo$dbconfig] (config VARCHAR(1024))
GRANT SELECT ON [$ndo$dbconfig] TO [public]
INSERT INTO [$ndo$dbconfig] VALUES
('IndexHint=Yes;Company="COMPANY";Table="Purchase Line";Key="Document Type","Type","No_","Variant Code","Drop Shipment","Location Code","Bin Code","Expected Receipt Date";Search Method="-+";Index=2')
Hope this helps …
Regards,
Jörg
You might try to create this index:
“No.,Document Type,Type,Document No.,Line No.”. But I don’t guarantee it works.
The professional help is not MS, but Navision-on-SQL specialists like Mark Brummel (http://www.brummelds.com) or Jörg Stryk (http://www.stryk.info/).
First of all , thank you for the answer.I have tried HindexHint, but I had some problems, due to the fact that
it wants “No.” not “No_” and also in COMPANY I have “… S.r.l.” not to be converted in " … S_r_l_".
Finally I have created a codeunit to test and I have seen in Profiler SQL 2000 the:
SELECT TOP 1 * FROM “DATABASE”.“dbo”.“COMPANY$Purchase Line”
WITH (READUNCOMMITTED, INDEX("$1"))
WHERE ((“Document Type”=@P1)) AND ((“Type”=@P2)) AND ((“No_”=@P3))
ORDER BY “Document Type”,“Type”,“No_”,“Variant Code”,“Drop Shipment”,“Location Code”,“Bin Code”,“Expected Receipt Date”,“Document No_”,“Line No_”
I suppose that INDEX("$1") means that it works, and in fact it has chooses the index $1 (the one I desire).
But in codeunit I can explicity use SETCURRENTKEY , while launching a form I 'm afraid It doesn’t understand.
My problem arises (I’ve seen sometimes there) when you drills down the flow field “Qty. on Purch. Order” in the “Item” card.
It launches the “Purchase Lines - 518” form and in opening the form , it sometimes hangs.
For using IndexHint , I have tried to set the SourceTableView property of the 518-form with :
SORTING(Document Type,Type,No.,Variant Code,Drop Shipment,Location Code,Bin Code,Expected Receipt Date)
but with Profiler I’ve seen one of the queries generated
SELECT * FROM “DATABASE”.“dbo”.“COMPANY$Purchase Line”
WHERE ((“Document Type”=@P1)) AND ((“Type”=@P2)) AND ((“No_”=@P3))
AND “Document Type”=@P4 AND “Type”=@P5 AND “No_”=@P6 AND “Variant Code”=@P7 AND
“Drop Shipment”= @P8 AND “Location Code”=@P9 AND “Bin Code”=@P10 AND “Expected Receipt Date”=@P11
AND “Document No_”=@P12 AND “Line No_”>=@P13
ORDER BY “Document Type”,“Type”,“No_”,“Variant Code”,“Drop Shipment”,“Location Code”,“Bin Code”,“Expected Receipt Date”,“Document No_”,“Line No_” OPTION (FAST 10)
solved with a clustered index seek, so I suppose It hasn’t understood the Hint.
I know that this query is correctly to be solved with a clustered index seek (Document No.,Line No,Document Type) , but there are tens (!) of queries
generated in drilling down the flow field, and I’ve seen one these queries (usually the one with … AND “Document No_” < @P12) solved with clustered index seek , and this is the error (hanging time) my customer suffers.
Sometimes It happens also in launching form-56 from the menu button “Purchases” , and sometimes for the “Sales”.
I hope that forcing that index can solve , I’m not sure , but I can do something.
Do you have any idea how forcing index launching forms?
tank you to have read the problem
We found out the following:
If you open a form and put a filter on it, SQL is opening a cursor with the SELECT statement inside, it seems to use the correct index, while the CPU and READ values on that query are quite low, after some scrolling up and down the system stalls and after a while presents the (correct) data. If I ltake a ook at the profiler i see MUCH higher CPU and READ values. If I use the query analyser with the same query it tells me it’s going to use the correct index (and it does). If i run the query and force SQL to use the clustered index (autonumber field, not in the filter) i get about the same CPU and RED values as i have seen earlier while scrolling.
my assumption is that SQL forgets to use the correct index after a while.
It seem that Microsoft says it’s a SQL bug…
Any comment ??
Hi
What NAV version do you use?
There could be point when SQL uses old execution plan instead of new…
Resolution is to install latest NAV 4.0 SP3 U6 and use “OPTION (RECOMPILE)”.
The OPTION(RECOMPILE) is only available with SQL Server 2005; here we have 2000 …