Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
FINDSET(TRUE, FALSE) with COMMIT inside the loop
Eugene
Member Posts: 309
I want to ask if the following code is ok
and in general - do the FINDSET command's parameters stay valid after COMMIT ?
IF FINDSET(TRUE, FALSE) THEN REPEAT ... COMMIT UNTIL NEXT = 0;and if it still uses the SQL optimization advantage over standard
IF FIND('-') THEN
and in general - do the FINDSET command's parameters stay valid after COMMIT ?
0
Comments
With the parameters of FINDSET you could controll if the cursor is just used for reading or if the recordset is to be changed, thus you could optimize the cursor definition.
FINDSET(TRUE, FALSE) creates a cursor which is used for UPDATE of records.
A COMMIT has no influence on the cursor, as long as the property "CLOSE CURSOR ON COMMIT" is set to FALSE (default).
You should take care that the transactions are neither too long nor too short!
Hence, basically the loop design is OK, but it also depends on what is really happening there.
A COMMIT usually implicates that data was written previously. If you change a value which was a definition criteria for the cursor, the cursor gets invalid and will be recreated - decreasing performance ...
The "old" FIND('-') also generates a cursor, but without the advanced features of FINDSET, so you could not differ between a read-only cursor or an update-cursor.
The major difference is, that FIND('-') is loading the full recordset into the cursor, while FINDSET is loading "chunks" at 500 records each.
There could be specific queries, where it could be feasible to still use the FIND('-'), e.g. if the resultset calls 1.000.000 record it might be faster to have one huge cursor instead of fetching 2.000 "chunks" at 500.
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool