2.10 Apply basic performance optimization in F&O apps Flashcards
Cached data?
Information that is retrieved from an outside source that is used on your computer
Why Cache data?
Decreases loading times by reducing the number of calls that are made to the database
Property name on a table for cache settings?
CacheLookup
Types of caching tables?
Set-based caching
Single-record caching
Set-based caching?
Caches groups of records all at once
Set “CacheLookup” property to “Entire Table”
Avoid for large tables
Single-Record caching?
Caches a single record
Must meet 2 conditions to use single-record caching
For single-record caching, what should “cacheLookup” property be set to?
NotInTTS
Found
FoundAndEmpty
Types of temporary tables?
InMemory
TempDB
InMemory?
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
When is InMemory table instantiated?
When the first record is inserted, the table exists while a record buffer exists
When to use InMemory?
When you need to store and retrieve data without writing data to the database, like a container but you can use indexes
TempDB?
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
Capabilities of TempDB?
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
Limitations of TempDB?
Inability to manage date-effective data
No delete actions
RLS does not apply
Can’t use them in views
When to use set-based statements?
Update, insert, or delete multiple records of data from a table
When to use row-based statements?
Update, insert, or delete a single record from a table
Why use set-based over row-based?
Performance wise set-based is better as it doesn’t call the server every time a different record is modified
Set-based methods?
update_recordset
delete_from
insert_recordset
Best practices to apply to queries?
All queries should pull only the required data needed
Avoid nested queries
4 types of joins?
Join
Outer Join
Exists Join
Notexists Join
Join?
Pulls records that match on both tables, like inner join
Outer join?
Pulls records regardless of whether those records match on both tables
Exists join?
Pulls all the records from the first table that match the records in the second table, no records from the second table are returned
Notexists join?
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
What concurrency models does F&O apps use?
PCC
OCC
PCC?
Pessimistic Concurrency Control
Locks records as soon as they are retrieved from the DB
OCC?
Optimistic Concurrency Control
Locks records when they are being updated
When to use PCC?
When there is a serialization logic that requires locks on the record, or when update conflicts are likely
When to use OCC?
Used on tables where it improves throughput in contrast to PCC
Preferred if the table is modified from a form and not from code
Advantages of OCC for increasing performance?
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
Disadvantage of OCC?
When 2 processes try to update the same record at the same time
Different types of parallel processing?
Individual task modeling
Batch bundling
Top picking
Individual task modeling?
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
Batch bundling?
Uses limited number of batch tasks that have a bundle size value.
Works well for a simple, even workload where processing times are similar
Top Picking?
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
Why use async processes?
Long-running processes can time out in F&O, async allows users continue working while the process runs in the background
Method to use async?
runAsync
Why use Windows PS?
Use Power Shell to develop scripts that interact with F&O apps and consume management functions
Windows PowerShell capabilities?
Manage computers from the CLI
Lets you access data stores, such as registry and certificate store
Scenarios to use Microsoft PS?
Database Sync
Restart Services
Reset the data mart
Deploy and install models
