Rules for Writing SQL Queries
- Caution
- Best Practice
- Basic rules
- WHERE clause: prefer = or IN() and not LIKE%
- Target the objects to be updated (UPDATE clause) or to be displayed (SELECT clause)
- Always filter field update requests in alerts
- Reduce the number of individual querie (CASE clause)
- Prefer JOIN over WHERE clauses to link 2 tables
- Prefer INNER JOIN over any other type of join
- Other rules of best practice
- Special cases
Most of the standard functions used in Service Manager are implemented via codeless configuration. But EasyVista also lets you include SQL queries replacing standard codeless processes, in particular to improve the performance in case of large databases volumetries.
- In the filters in advanced mode;
- In the advanced conditional stages and in the SQL update stages of workflows / business rules;
- In the alerts.
In all cases, it is essential to comply SQL queries with various basic rules on composing and optimizing queries.
Caution
- You should always perform a backup of the Service Manager production database prior to modifying or optimizing SQL queries. The EasyVista team will not resolve any issues related to the update/deletion of data and its impact on your platform.
- All the SQL queries execute on a sandbox database call the same CPU and memory as the production database. If they are non-optimized, they can affect the execution time of the production database.
Best Practice
- Before putting your query in place, execute it for the first time in SQL Server Enterprise Manager. The execution time will not be any shorter in Service Manager than in SQL Enterprise Manager.
- A query that takes several seconds to execute must be optimized if it is used in a workflow / business rule.
- A query that takes several minutes to execute must be optimized if it is used in an alert executed during the day, whatever the execution frequency.
- Execute your SQL queries in a development environment independent of the production environment.
See 2 SQL tutorials sites.
Basic rules
WHERE clause: prefer = or IN() and not LIKE%
- Always prefer platform performance to administrative simplicity, even if it will be necessary to modify the query.
Best Practice | Not recommended | |
---|---|---|
WHERE ( action_id IN (24, 25, 26, 27) ) |
WHERE ( action_label_fr LIKE 'Relance %' OR action_label_fr LIKE 'Appel%' ) |
Target the objects to be updated (UPDATE clause) or to be displayed (SELECT clause)
- The tables SD_REQUEST, AM_ACTION and AM_ASSET contain several types of data.
- SD_REQUEST table: incidents, events, service requests, change requests, problems, projects.
- AM_ACTION table: actions on all objects in SD_REQUEST.
- AM_ASSET table: equipment, licenses, contracts, configuration elements (CI).
- All these elements are identified by a unique GUID, which can identify them whatever the environment.
Always filter field update requests in alerts
- Update requests executed in workflows and business rules must comply with the individual update rules described in the documentation and only rarely pose problems of filtering the scope of the update.
Best Practice | Not recommended | |
---|---|---|
UPDATE sd_request SET sd_request.available_field_1 = "my_value" WHERE sd_request.available_field_1 IS NOT NULL |
UPDATE sd_request SET sd_request.available_field_1 = my_value |
Reduce the number of individual querie (CASE clause)
- The query is optimized compared to the initial query because it only recalculates what is necessary.
- The length of queries is not an indication of their optimization.
example Below, we replace 8 individual queries by an overall optimized query.
Best Practice | Not recommended | |
---|---|---|
UPDATE sd_request SET available_field_4 = CASE WHEN time_used_to_solve_request BETWEEN 0 AND 240 THEN '4 hours' WHEN time_used_to_solve_request BETWEEN 241 AND 480 THEN '4 to 8 hours' WHEN time_used_to_solve_request BETWEEN 481 AND 1440 THEN '8 to 24 hours' WHEN time_used_to_solve_request BETWEEN 1441 AND 7200 THEN '24 hours to 5 days' WHEN time_used_to_solve_request BETWEEN 7201 AND 14400 THEN '5 to 10 days' WHEN time_used_to_solve_request BETWEEN 14401 AND 21600 THEN '10 to 15 days' WHEN time_used_to_solve_request > 21600 THEN 'More than 15 days' WHEN time_used_to_solve_request IS NULL THEN 'No SLA' END FROM sd_request WHERE ( submit_date_ut > Getutcdate () - 360 ) AND ( CASE WHEN time_used_to_solve_request BETWEEN 0 AND 240> THEN '4 hours' WHEN time_used_to_solve_request BETWEEN 241 AND >480 THEN '4 to 8 hours' WHEN time_used_to_solve_request BETWEEN 481 AND >1440 THEN '8 to 24 hours' WHEN time_used_to_solve_request BETWEEN 1441 AND >7200 THEN '24 hours to 5 days' WHEN time_used_to_solve_request BETWEEN 7201 AND >14400 THEN '5 to 10 days' WHEN time_used_to_solve_request BETWEEN 14401 AND >21600 THEN '10 to 15 days' WHEN time_used_to_solve_request > 21600 THEN 'More than 15 days' WHEN time_used_to_solve_request IS NULL THEN 'No SLA' END ) <> available_field_4 |
UPDATE sd_request SET available_field_4 = '4 hours' WHERE ( time_used_to_solve_request BETWEEN 0 AND 240 ); UPDATE sd_request SET available_field_4 = '4 to 8 hours' WHERE ( time_used_to_solve_request BETWEEN 241 AND 480 ); UPDATE sd_request SET available_field_4 = '8 to 24 hours' WHERE ( time_used_to_solve_request BETWEEN 481 AND 1440 ); UPDATE sd_request SET available_field_4 = '24h to 5 days' WHERE ( time_used_to_solve_request BETWEEN 1441 AND 7200 ); UPDATE sd_request SET available_field_4 = '5 to 10 days' WHERE ( time_used_to_solve_request BETWEEN 7201 AND 14400 ); UPDATE sd_request SET available_field_4 = '10 to 15 days' WHERE ( time_used_to_solve_request BETWEEN 14401 AND 21600 ); UPDATE sd_request SET available_field_4 = 'More than 15 days' WHERE ( time_used_to_solve_request > 21600 ); UPDATE sd_request SET available_field_4 = 'No SLA' WHERE time_used_to_solve_request IS NULL |
Prefer JOIN over WHERE clauses to link 2 tables
Best Practice | Not recommended | |
---|---|---|
INNER JOIN sd_request ON sd_request.request_id = am_action.request_id |
WHERE am_action.request_id = sd_request.request_id |
Prefer INNER JOIN over any other type of join
- Avoid the joins FULL OUTER JOIN or CROSS JOIN, which cannot ensure acceptable response times in operation.
- Always prefer an INNER JOIN clause to a LEFT OUTER JOIN clause, which is much more resource-intensive for the SQL server.
- If the key on which the join is performed is always completed, use an INNER JOIN.
- If the key cannot be completed:
Best Practice | Not recommended | |
---|---|---|
LEFT OUTER JOIN sd_request ON sd_request.request_id = am_action.request_id |
WHERE AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID OR AM_ACTION.REQUEST_ID IS NULL |
Other rules of best practice
Limit query nesting
Prefer alerts to business rules
- If a calculation needs to be done in real time, use business rules that will always perform a calculation at the time a record is created or modified. A business rule may therefore execute several times, while a daily alert will generate only a single transaction.
Note: Real-time is not necessary:
- when the calculations are used only for information or for reporting;
- when the calculations concern static objects such as equipment.
- In all the other cases, use the night alerts.
Note: Calculations concerning maximum resolution dates for incidents may be implemented via night alerts for periods greater than 8 hours. In this case, no delay can be observed on the day when the incident is entered.
Plan alerts at night or during periods when activity is low
- If you are a SaaS client of the Easyvista.com service, you have the service My EasyVista.com which gives you the activity ranges per day during the week and per hour during the day. If you need to perform a calculation several times during the day, make sure that it is scheduled during the mid-day drop in activity.
- If a calculation can only be performed once a day, plan it during the least active period, taking account of the time zones in which you operate. Even in the case of implementation 24x7, the time zones that correspond to the middle of the day in the middle of the Pacific Ocean are less active than the rest of the time. Use the graphics in My EasyVista to assist you.
Business rules: prefer execution conditions to conditional stages
- When a business rule only applies in certain cases, you can:
- define restrictions in the execution conditions;
- put WHERE clauses in the SQL update stages;
- define conditional stages in the workflows that will execute.
- Always prefer execution conditions for business rules to the two other options (executing a workflow requires numerous SQL transactions).
example If a calculation rule is put in place to perform a calculation on the AM_ACTION table which only concerns certain groups, the restriction on the groups should be put in the execution condition rather than in a conditional stage.
Using Service Manager queries as examples
- Display the query sent to SQL Server and the execution time by adding &Showstatement=yes to the end of the URL for any screen, once connected to Service Manager.
- You can use the part QuerySentToSGBD as an example, which corresponds to the SQL queries sent by the application server to the SQL server.
- Service Manager queries comply with the optimization rules.
- They can vary significantly according to the number of columns displayed in the interface: Service Manager adds the necessary joins if and only if the fields requiring these joins are displayed.
Special cases
GUID to be used on generic tables SD_REQUEST and AM_ASSET
FROM sd_request
INNER JOIN sd_catalog ON sd_request.sd_catalog_id = sd_catalog.sd_catalog_id
INNER JOIN am_reference SD_CATALOG_REF ON SD_CATALOG_REF.reference_id = sd_catalog.category_id
WHERE ( SD_CATALOG_REF.reference_guid = '{E9C336A1-F7A7-4145-9F28-FDD883BFF389}' )
GUID list of the objects in SD_REQUEST table
CATALOG_GUID | TITLE_EN |
---|---|
932E417B-2798-4E45-9918-7FB6A9CB49D5 | Incidents |
2BD6E0E2-E21F-40D9-B10F-34CA9A28B239 | Service Request |
E37589D4-16F7-44FF-A655-D17B81F4B1FF | Investment |
8B988B9E-8233-4761-A924-A658C87E6E9F | Events |
A4DF15D8-011F-46DA-A77E-422B3204DD10 | Change |
D6AF44B4-4112-461C-9A21-5100955AA8AC | Problem |
5C5E0A47-5E1E-4397-BBD7-69EFE069A3CA | Main catalog |
37C28C20-83CF-483A-B556-9A4C9202F54B | Projects |
GUID list of the objects in AM_ASSET table
CATALOG_GUID | TITLE_EN |
---|---|
B0BFA585-3F8D-490F-B1FA-1BE74388561C | Contracts |
CB9E0D41-2520-47F8-9E3A-2F916B29B7CA | Equipment |
73E026ED-A8E2-4C0D-A062-4EA116F5A628 | Licenses |
7B1D1137-E175-49D2-AD3E-F8A771C31DD5 | Installation Types |
68A3DFF0-9217-45F6-8E3D-69BE0F8E6425 | Consumable |
C03BA430-AD99-4E31-84FC-9339E6DC8BB2 | CI |