Rules for Writing SQL Queries


Most of the standard functions used in Product name - ev itsm.png are implemented via codeless configuration. But Logo - EasyVista.png 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 where the use of SQL queries is necessary, it is essential to comply with various basic rules on composing and optimising queries.

   Open url.png  see: 

  • SQL School
  • Learn SQL The Hard Way or in a business rule;
    • a query that takes several minutes to execute MUST be optimised 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. Note: If you do not have this optional environment, please contact your Logo - EasyVista.png consultant.

Caution

  • All Product name - ev itsm.png platforms are installed with 3 environments connected to the same SQL Server: Production (database 5004 - Live data); Sandbox (database 50005 - Tests for new developments); Test database (database 40000 - Training data).
  • All the SQL queries execute on a sandbox database call the same CPU and memory as the production database. If they are non-optimised, they can affect the execution time of the production database. 

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 icon.png 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 contains incidents, events, service requests, change requests, problems and projects
  • AM_ACTION contains actions on all objects in SD_REQUEST
  • AM_ASSET contains equipment, licenses, contracts and configuration elements. 

All these elements are identified by a catalogue and a unique GUID, which can identify them whatever the environment.

Always filter field update requests in alerts

Note: 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 icon.png 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 queries: CASE clause

Notes

  • The query is optimised compared to the initial query because it only recalculates what is necessary.
  • The length of queries is not an indication of their optimisation (below, we replace 8 individual queries by an overall optimised query).
     
Best Practice icon.png Not recommanded
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 icon.png 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 icon.png Not recommanded
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

Best Practice big icon.pngBest 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. In all the other cases, use the night alerts.

Most of the time, 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;
  • certain 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 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, there are several ways of defining the condition: 

  • define restrictions in the execution conditions, 
  • put WHERE clauses in the SQL update stages
  • define conditional stages in the workflows that will execute. 

Executing a workflow is a resource-intensive process which requires numerous SQL transactions. We therefore recommend always preferring execution conditions for business rules to the two other options.
For 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 Product name - ev itsm - big.png ev|Service Manager queries as examples

By adding &Showstatement=yes to the end of the URL for any screen, once connected to Product name - ev itsm.png, it is possible to display the query sent to SQL Server and the execution time. 

  • The ReceivedQuery part gives the query sent by Product name - ev itsm.png to the data server for processing and is only useful for analysing any malfunction in Product name - ev itsm.png. This query should not be used. 
  • The part QuerySentToSGBD corresponds to the exact SQL queries sent by the application server to the SQL server. It is this second part that can be used as an example. 

Note: Product name - ev itsm.png queries are optimised and comply with the above rules. Nevertheless they can vary significantly according to the number of columns displayed in the interface: Product name - ev itsm.png 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 to be used to differentiate the objects stored in SD_REQUEST
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 to be used to differentiate the objects stored in AM_ASSET
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:
Last modified by Unknown User on 2017/10/25 21:32
Created by Administrator XWiki on 2014/02/14 17:56

Shortcuts

Recent Updates

Haven't been here in a while? Here's what changed recently:

-   Product name - ev itsm.png
-   Product name - ev sas.png

Interesting Content

How to Automate Integration
Add a Shortcut to an App
History
Quick Dashboard
Full text search - Stop Words

Powered by XWiki ©, EasyVista 2018