"Adjust Cost Item Entries" : Invalid Item Update

hedaya

Hi All,

I am doing some research on Adjust Cost Item Entries...where the C/ AL code iterates the Item Ledger Entry table and Updates the Item table based on the business logic...Well and Good...But when it comes to MODIFY statement it issues the following SQL Statement

UPDATE "BMW_INCADEA"."dbo"."BMW_REL_QA_EN_MAND2$Item" WITH (REPEATABLEREAD, ROWLOCK)  SET "SA3 Default Option"=@P1 WHERE ("No_"=@P2)

There is no need for the above UPDATE with that Column....!

Do you folks encountered this scenario...?

Thanks,
Hed

DenSter

Navision doesn't use SQL statements, at least not within the IDE for regular Navision objects. Where are you looking? The client monitor? How do you figure that there is no need for the statement?

hedaya

This is the SQL Server Version of the Navision Client....I going through the Step-by-Step mode...with SQL Profiler running at the other side to monitor what are the SQL statements that are get fired during the execution of the statement...

The Client Does modifies the Unit Cost and Profit But issues the wrong the Update Statement during the MODIFY Statement on the "UpdateUnitCost" procedure on the 5804 CodeUnit....

Any thoughts...???
Appreciated your early response...!

Hed.

DenSter

The point is that you will not be able to control those SQL statements. The SQL profiler is very interesting because you get to see the inner workings of Navision's communications with SQL Server, but I wouldn't spend too much time trying to figure out how to make that work better, since you won't be able to change it anyway.

So tell me... what should it have been then?

hedaya

Thanks Denstar

The Issue is, Eventhough the Data are being modified by the Code Block...system is not sending appropriate Update Statements (with the Column Data changes)...or in an another scenario...it sends an update statement even if there is no change to that particular ROW...because you have MODIFY Statement without any check to see whether the data got modified or not...

I am just trying to see is there any possibility to avoid that unnecesary update statement...and thats where the I came to know about this invalid statement...

My ultimate goal is to optimize the Code, to run the system better...

Thanks for you update...

Hed.

kine

Run Client monitor on your client when running the batch, you can run Code Coverage too... than you can use this data to find, where the Modify is nested... (you can use extended client monitor, may be that there is the download somewhere)...

hedaya

Folks,

I finallly Found the Culprit...the catch is...
Even you literally update some columns in the record and if you're updating the same value on the column system is smart enought to not issue the update statement on the modified column as its already holding the same value. But it does pick the last column and issues the update anyway to complete that "MODIFY" code.

I will try to use MARK boolean to make sure whether or not we need to execute the code MODIFY if there is any real value change...

thanks folks,
Appreciated!
Hed.