Rules for Writing SQL Queries

Last modified on 2023/08/03 12:03
Contents

   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.

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 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.

   If you do not have this optional environment, please contact your EasyVista consultant.

   The information presented on these sites is not the responsibility of EasyVista.

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

SELECT sd_request.request_id
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
Tags:
Powered by XWiki © EasyVista 2022