2.10 Apply basic performance optimization in F&O apps

Question 1

Cached data?

Answer 1

Information that is retrieved from an outside source that is used on your computer

Question 2

Why Cache data?

Answer 2

Decreases loading times by reducing the number of calls that are made to the database

Question 3

Property name on a table for cache settings?

Answer 3

CacheLookup

Question 4

Types of caching tables?

Answer 4

Set-based caching
Single-record caching

Question 5

Set-based caching?

Answer 5

Caches groups of records all at once
Set “CacheLookup” property to “Entire Table”
Avoid for large tables

Question 6

Single-Record caching?

Answer 6

Caches a single record
Must meet 2 conditions to use single-record caching

Question 7

For single-record caching, what should “cacheLookup” property be set to?

Answer 7

NotInTTS
Found
FoundAndEmpty

Question 8

Types of temporary tables?

Answer 8

InMemory
TempDB

Question 9

InMemory?

Answer 9

Uses an indexed sequential access method (ISAM) file that exists on the client tier or the AOS tier.
The data is stored in memory until it reaches 128kb

Question 10

When is InMemory table instantiated?

Answer 10

When the first record is inserted, the table exists while a record buffer exists

Question 11

When to use InMemory?

Answer 11

When you need to store and retrieve data without writing data to the database, like a container but you can use indexes

Question 12

TempDB?

Answer 12

Uses SQL Server TempDB database.
Causes for data to be removed when it is no longer used by the current method or when the system is restarted

Question 13

Capabilities of TempDB?

Answer 13

Joining to regular tables
Using FK
Being per company or global
Having indexes
Having methods, but override inability
Instantiating from the client or server tier
Being used as query

Question 14

Limitations of TempDB?

Answer 14

Inability to manage date-effective data
No delete actions
RLS does not apply
Can’t use them in views

Question 15

When to use set-based statements?

Answer 15

Update, insert, or delete multiple records of data from a table

Question 16

When to use row-based statements?

Answer 16

Update, insert, or delete a single record from a table

Question 17

Why use set-based over row-based?

Answer 17

Performance wise set-based is better as it doesn’t call the server every time a different record is modified

Question 18

Set-based methods?

Answer 18

update_recordset
delete_from
insert_recordset

Question 19

Best practices to apply to queries?

Answer 19

All queries should pull only the required data needed
Avoid nested queries

Question 20

4 types of joins?

Answer 20

Join
Outer Join
Exists Join
Notexists Join

Question 21

Join?

Answer 21

Pulls records that match on both tables, like inner join

Question 22

Outer join?

Answer 22

Pulls records regardless of whether those records match on both tables

Question 23

Exists join?

Answer 23

Pulls all the records from the first table that match the records in the second table, no records from the second table are returned

Question 24

Notexists join?

Answer 24

Pulls all the records from the first table that do not match the records in the second table. No Records from the second table are returned

Question 25

What concurrency models does F&O apps use?

Answer 25

PCC
OCC

Question 26

PCC?

Answer 26

Pessimistic Concurrency Control
Locks records as soon as they are retrieved from the DB

Question 27

OCC?

Answer 27

Optimistic Concurrency Control
Locks records when they are being updated

Question 28

When to use PCC?

Answer 28

When there is a serialization logic that requires locks on the record, or when update conflicts are likely

Question 29

When to use OCC?

Answer 29

Used on tables where it improves throughput in contrast to PCC
Preferred if the table is modified from a form and not from code

Question 30

Advantages of OCC for increasing performance?

Answer 30

Fewer resources are used to lock records during updates
Records are locked for a shorter length of time by using OCC instead of PCC
Records remain available for other processes while they are selected from the database

Question 31

Disadvantage of OCC?

Answer 31

When 2 processes try to update the same record at the same time

Question 32

Different types of parallel processing?

Answer 32

Individual task modeling
Batch bundling
Top picking

Question 33

Individual task modeling?

Answer 33

Creates a separate batch task for each work item
Works well with a small number of work items, best at creating dependencies between work items

Question 34

Batch bundling?

Answer 34

Uses limited number of batch tasks that have a bundle size value.
Works well for a simple, even workload where processing times are similar

Question 35

Top Picking?

Answer 35

Creates a limited number of batch tasks where each batch task picks and processes the first free work item.
Requires a staging table to store work items for batch tasks to process from

Question 36

Why use async processes?

Answer 36

Long-running processes can time out in F&O, async allows users continue working while the process runs in the background

Question 37

Method to use async?

Answer 37

runAsync

Question 38

Why use Windows PS?

Answer 38

Use Power Shell to develop scripts that interact with F&O apps and consume management functions

Question 39

Windows PowerShell capabilities?

Answer 39

Manage computers from the CLI
Lets you access data stores, such as registry and certificate store

Question 40

Scenarios to use Microsoft PS?

Answer 40

Database Sync
Restart Services
Reset the data mart
Deploy and install models