Transcript Advanced Queries

Best Practices
OutSystems Development
www.outsystems.com
Page 1
© 2008 outsystems – all rights reserved
Table of Contents
•
Naming Conventions
•
Coding Conventions
•
Web Flows
•
Reusability & Business Actions
•
Auditing and Logging
•
Error Handling
•
Transaction Management
•
Web Screens & Usability
•
JavaScript
•
Advanced Queries
•
Performance
•
Security
•
Build to Change
•
Architecture
•
Styling
www.outsystems.com
Page 2
© 2008 outsystems – all rights reserved
Naming Conventions (1/3)
• Use only English
• Use meaningful names for all objects
– Avoid names like ProcDesc or CDgt –
use ProcessDescription and CheckDigit
instead
• Entities naming
– Entity names in capital letters, singular
and with “_” separating words (eg:
CONTACT_TYPE)
– Entity attributes in Camel Case (eg:
PhoneNumber)
– Foreign keys in format
<EntityFullName>Id or, if several exist ,
<Purpose><EntityFullName>Id (eg:
ContactTypeId or PrimaryContactTypeId)
www.outsystems.com
Page 3
© 2008 outsystems – all rights reserved
Naming Conventions (2/3)
•
•
•
•
Variable names
–
Variable names in Camel Case (Eg:
ContactNumber)
–
Entity Identifier variables in format:
<EntityFullName>Id (Eg: ContactId)
–
Record variables in format
<EntityFullName>Rec (Eg: ContactRec)
Screen names in the format:
–
<Object>_<ScreenFunctionality> (e.g.:
Contact_Search)
–
The <Object> should be an entity name (or a
high level business object).
User Actions (that implement business
rules) names in the format:
–
<Object>_<Action> (eg: Contact_Create,
ContactType_Delete)
–
The <Object> should be an entity name (or a
high level business object).
User Actions (that run in timers) names in
the format:
–
Timer_<Action> (eg: Timer_SendEmails)
www.outsystems.com
Page 4
© 2008 outsystems – all rights reserved
Naming Conventions (3/3)
•
Web Flows names in the format:
– <Purpose or Profile>Flow (eg:
AdministratorFlow or DocumentFlow)
•
TableRecords, EditRecords,
ShowRecords with the name
– Table<Listed Records Description>
– Edit<EntityFullName>
– Show<EntityFullName>
– (eg: TableContacts,
TableRelatedContacts, EditContact,
ShowContact)
•
Prefix structures and site properties
names to group them in a logical way
•
Prefix extension structures names
with the extension name, so that they
are easily identified in the structures
folder
www.outsystems.com
Page 5
© 2008 outsystems – all rights reserved
Coding Conventions (1/2)
•
Use English everywhere!
•
Give meaningful labels to all nodes
•
–
query nodes, assign nodes, if nodes, action nodes,
etc…
–
Don’t leave the default names (like Query1, etc.)
Give meaningful names to all objects
–
•
Actions, Variables, TableRecords, etc.
Fill in the description property of all objects –
the absolutely compulsory are:
–
eSpace
–
Data Model (Entities, structures and attributes)
–
Web Screens, Web Flows, screen actions and
input/local variables
–
Actions and input/output/local variables
–
Site Properties and Session Variables
–
Permission Areas
•
Place comments when the action flow is not
clear.
•
Place example strings on ALL screen
expressions
www.outsystems.com
Page 6
© 2008 outsystems – all rights reserved
Coding Conventions (2/2)
• Keep actions tidy
– Use 'Grid' and 'Snap to Grip' View options;
– Draw flows from top to bottom (keep it
readable and simple )
– Keep the same "distance" between
connected flow elements;
– Try to put 'if' connectors always in the same
way (True to the left and False to the right
for example)
• Indent SQL scripts
• Don’t use hard-coded values
– Use Site Properties or EnterpriseManager
Global Settings for that
www.outsystems.com
Page 7
© 2008 outsystems – all rights reserved
Reusability & Business Actions
(2/2)
• DO NOT place complex business
logic in Screen Actions!
– Create a User Action to implement
business rules!
• Create User Functions to
encapsulate typical formatting in
your application (and maintain it
consistent):
– Eg: Document_FormatNumber,
Price_FormatForInvoice
– You can then use these User Functions
in your screen expressions
• Create Web Blocks to encapsulate
and reuse screen patterns
www.outsystems.com
Page 8
© 2008 outsystems – all rights reserved
Javascript (1/2)
• Try to encapsulate Javascript patterns
in a parameterized Web Block
• Use standard, cross-browser javascript
• Do not use <SCRIPT></SCRIPT>. Use
<SCRIPT
TYPE="text/javascript"></SCRIPT>
instead
– The TYPE attribute is REQUIRED.
• Make your javascript web blocks
reusable by sharing them as public
• Comment your javascript!
www.outsystems.com
Page 9
© 2008 outsystems – all rights reserved
Javascript (2/2)
• Make sure your javascript blocks are
perfectly identifiable
• Make your javascript web blocks reusable
by sharing them as public
– Place them in the WidgetLibrary_Ext eSpace
• Comment your javascript!
– Every screen with complex javascript block must
have an overall comment on the preparation flow.
www.outsystems.com
Page 10
© 2008 outsystems – all rights reserved
Advanced Queries (1/3)
• When you need to use
constants in your query,
always pass them as input
parameters (null values,
codes, etc.)
• Always indent your
Advanced Queries for
readability easiness
• Always use the Service
Studio entities and entity
fields formatting:
– {ENTITY_NAME}.[FieldName]
(eg: {CONTACT}.[Name])
www.outsystems.com
Page 11
© 2008 outsystems – all rights reserved
Advanced Queries (2/3)
• If na Advance query gets to complex, comment
it!
– Place outsystems comments to explain the query
– Place inline comments to explain specific query
details
www.outsystems.com
Page 12
© 2008 outsystems – all rights reserved
Advanced Queries (3/3)
• Always try to use Simple Queries
instead of advance queries!
– Before you start an Advanced Query
think!: How could I do this with a simple
query?
• Avoid placing business logic in
complex advanced queries!
– Queries must be used to retrieve data
only! The business logic is designed in
Service Studio flows!
www.outsystems.com
Page 13
© 2008 outsystems – all rights reserved
Performance - AGENDA
• General Issues
• Entity Model Issues
• Query Design Issues
• Entity Actions Issues
• Web Screen Issues
• Timer Issues
• Excel Issues
www.outsystems.com
Page 14
© 2008 outsystems – all rights reserved
Performance - General
• Site properties should be seen as constants,
not variables.
– Consider placing configuration variable in the
database or Enterprise Manager Global Settings
– Site property updates are extremely slow
• Reduce the number of Session Variables and
the amount of data stored in them
– Session Variables consume system resources:
network bandwidth and memory.
• Avoid executing unnecessary operations in the
OnSessionStart action
• Use Service Center reports to test and tune
performance.
www.outsystems.com
Page 15
© 2008 outsystems – all rights reserved
Performance - Entity Model (1/3)
• Reduce Text Attribute length to the
minimum possible value
– Avoid text attributes with length > 2000.
– Attributes with length <= 2000 are created as
Varchar(XX)
– Attributes with length > 2000 are created as Text
• Isolate Binary Data Attributes in separate
Entities
• When applicable, set the foreign key
Attributes Delete Rule to Ignore instead of
Delete;
www.outsystems.com
Page 16
© 2008 outsystems – all rights reserved
Performance - Entity Model (2/3)
•
Carefully define and create entity indexes
–
Check which tables might grow (heavily or indefinitely)
•
–
Think which queries will be more used and see which fields are used
to filter them
•
–
If is mostly the same group of fields used to filter the query, creating an index
over the whole group is much more effective than creating an index on each field;
Don't be afraid of creating an index on one field and then another on a
group of fields that include the first one
•
–
If the expected number of creates, updates and deletes occurring on a table is
higher than the number of selects, indexes might slow dows the databse
operations;
Create composed indexes for fields that are commonly used together
in queries:
•
–
indexes should be placed in those fields;
Be careful when creating indexes in highly dynamic tables
•
–
thinking in years, not in months or weeks, and assuming the worst possible
behavior from the users;
in many situations it pays off;
Indexes in lookup tables are usually very benefic
•
www.outsystems.com
Lookup tables have not many records and the queries are fast, but are mainly
consulting tables and are used often, therefore placing indexes on those tables
usually has a good impact on performance.
Page 17
© 2008 outsystems – all rights reserved
Performance - Entity Model (3/3)
• When a table grows indefinitely,
create a timer to clean old
unnecessary records
– Forget the database script that cleans old
records (no one will remember)
• Create archive tables for historical
data
– Avoid having historical data in the operating
tables;
– Have a daily timer to copy the records older
than some time from the primary table to
the archive table
www.outsystems.com
Page 18
© 2008 outsystems – all rights reserved
Performance - Query Design
•
Whenever possible use Identifier Attributes in
where clauses
– Hub Server creates a clustered index over every
Identifier Attribute;
•
Set the Max. Records property in Simple
Queries to the minimum value possible
•
In Advanced Queries set a Top parameter,
setting to minimum value possible
– WARINING: Without this TOP all records in the
query result will be retrieved from the database
•
For queries to be displayed in table records set
the maximum value of records to
TableRecords.StartIndex +
TableRecords.LineCount +1
•
Write Advanced Queries that update as many
rows as possible in a single statement
– rather than using multiple queries to update the
same rows
www.outsystems.com
Page 19
© 2008 outsystems – all rights reserved
Performance – Entity Actions
•
If you don't need to retrieve all Entity
Attributes, consider replacing the
Get<Entity> invocation by a Simple Query
using an Identifier parameter
– Hub Server will optimize the query to
retrieve only the used attributes;
•
If you don't need to update all Entity
Attributes, consider replacing the
Update<Entity> invocation by an
Advanced Query using an UPDATE SET
statement
– setting just the required attributes.
•
If you need to delete multiple records, use
an Advanced Query with a single Delete
instead of a For Each followed with a
Delete<Entity>
•
Use Update<Entity> instead of a
Delete<Entity> followed by a
Create<Entity>
•
Use a CreateOrUpdate<Entity> instead of a
Select<Entity> followed by a
Delete<Entity> and a Create<Entity>
www.outsystems.com
Page 20
© 2008 outsystems – all rights reserved
Performance – Web Screens (1/2)
• Avoid using Record and Record List parameters
– Replace Record and Record List parameters with repeated
data retrieval from database or external system;
• Limit the number of lines in ListRecords and
TableRecords using the “Line Count” property
– When possible, limit the number of results of the query that
gets the records to display to TableRecords.StartIndex +
TableRecords.LineCount +1
• Make simple screen preparations
– huge and complex preparations can make screens slow
• WHENEVER possible use check the IsLoadingScreen()
function in screen preparations to avoid unnecessary
repeated operations
• Set the Method property in Link widgets to "Link" in
screens with no user inputs
– The Link method considerably
reduces the size ©of2008
the
page
outsystems – all rights reserved
Page 21
www.outsystems.com
Performance – Web Screens (2/2)
• AVOID large images;
• Set cache properties in screens and web blocks
whenever no changes are expected.
– When caching the entire screen is not possible, try to
cache sub-parts of the screen by using cached Webblocks;
• Use cache for images whenever possible and
appropriate
• Use the "HTTP Security" property as "SSL" or
"SSL with certificates" only in required screens.
– HTTP Security reduces network bandwidth and requires
more CPU;
www.outsystems.com
Page 22
© 2008 outsystems – all rights reserved
Performance – Timers (1/2)
• Make timers run only when they are really required
• Audit all the important steps, so you can track what
your timer is doing…
– but don’t push the limit (Limit = 3000 / request)
• Handle exceptions in timer actions
– Take into account that you might have to Wake the timer
again.
– Do you want to commit? Or do you want to rollback?
• Highly demanding timers
– Avoid timer timeouts
• Prepare a smooth timer stop before the timer times out.
• Timer should stop and start itself in half of the timeout (default
timeout is 20m)
– Consider implementing a bulk processing logic
• Many times, bulk processing might be necessary to improve
performance.
Pagecommits
23
– Consider executing partial
www.outsystems.com
© 2008 outsystems – all rights reserved
Performance – Timers (2/2) (Heavy
Timer Example)
www.outsystems.com
Page 24
© 2008 outsystems – all rights reserved
Performance – Excel
• Take into account the number of excel lines to
upload
– If the number of lines to be uploaded are not fixed, then
you should assure that your application is capable of
loading 65000 records, the maximum records excel
allows;
• Don't forget that the records are inserted in a table
and that the insert will be progressively slower
along the table growth
• Don’t’ forget the page load timeout (2m)
– If the excel takes to much time to load, saving the file in
the database and processing it in a timer
www.outsystems.com
Page 25
© 2008 outsystems – all rights reserved
Security (1/3)
• Set screen permissions
– Set only the necessary permission areas for
flows and screens
• Sensitive data exchange
– DON’T pass passwords or sensitive
information as screen parameters as they
might appear in the url
– Remember that screen variables or
preparation action outputs used in the screen
actions or destination arguments might be
exposed in the url or web screen viewstate
– When applicable use SSL for sensitive
information exchange.
© 2008 outsystems – all rights reserved
www.outsystems.com
Page 26
Security (2/3)
• DO NOT rely on the Web Screen
widgets interface to control
permissions:
– If you do not want a user to execute a [Save]
action, you should not only hide the [Save]
button, but also validate the permissions user
permissions on the server before executing
the operation.
• Whenever possible, use integrated
windows authentication and LDAP
• Use encrypted passwords in the
database
Page 27
– Check Encrypt built-in
function
www.outsystems.com
© 2008 outsystems – all rights reserved
Security (3/3)
• Encode strings that came
from user inputs before
injecting them in your
source:
– Use EncodeHTML and
EncodeJavascript for
unescaped expressions in
Web Screens;
– Use EncodeSql for inline
parameters of advanced
queries
– Use EncodeURL for strings
used when building dynamic
URLs
www.outsystems.com
Page 28
© 2008 outsystems – all rights reserved
Build to Change
•
WHENEVER possible use Simple Queries
– When possible AVOID Advanced Queries
– Advanced Queries are Database specific
– Advanced Queries are much harder to understand and maintain
•
WHENEVER possible use the Service Studio screen widgets
– When possible AVOID generating HTML/Javascript code to
unescaped expressions
– Generated HTML/Javascript is much harder to understand or maintain
•
DO NOT place business logic in the Extension Action’s code (C#
or Java)
– Extension actions must only execute the low level operations you
cannot execute in Service Studio
– Place as much logic as possible in the Service Studio Action Flows
– Extension actions are Application Server specific
– Extension actions are much harder to understand or maintain
•
MINIMIZE the usage of javascript
– Javascript makes the user interface richer, but it is hard to maintain
and definitely NOT built to change
www.outsystems.com
– Javascript
© 2008 outsystems – all rights reserved
Page
29
has several Browser
Compatibility
problems
Architecture (1/2)
• Split your application in modules
– For complex projects, don’t implement the complete
solution in a single OML. Split functional modules in
different OML’s
– In each module expose only the necessary actions,
functions, entities and web-blocks to be used by
other modules
– The advantages of creating different modules are
• Encapsulates database and business logic;
• Generic modules can be reused;
• Promotes coding parallelization – different developers
implementing different modules;
• Oml’s are kept small – more efficient to edit, merge, 1Click publish, etc.
• Avoid cyclic references between eSpaces!
www.outsystems.com
Page 30
© 2008 outsystems – all rights reserved
Architecture (2/2)
• Fully integrate your application in the
Enterprise Manager!
– Use the EM concepts when applicable
• UserMaster, PermissionMaster, Role, Group,
Location, Locale, Global Settings, etc.
– Register your application in EM
• Implement DoBootstrap and GetConfiguration
– Make your application configurable in the
Enterprise Manager using global settings
• Using the GlobalSetting_CreateOrUpdate and
GlobalSetting_Set EM public actions
www.outsystems.com
Page 31
© 2008 outsystems – all rights reserved
Styling
• Base your styling in the Style
Guide patterns AS MUCH AS
POSSIBLE
– The less you think about it, the
better!
• Centralize all styling in CSS
styles (in the Enterprise eSpace)
– AVOID using the Service Studio
built-in styling (bold, alignment,
table padding, table cell-spacing,
etc.)
• Use only standard CSS
attributes
• Golden rule: KEEP IT SIMPLE!
www.outsystems.com
Page 32
© 2008 outsystems – all rights reserved