CRITICAL
Rule Definition
Having complex queries in Programs can cause performance problems.
Remediation
The best solution is to review the query to reduce the number of WHERE clauses.
Violation Code Sample
SELECT e.entertainerid,
e.firstname,
e.lastname,
b.customerid,
b.eventdate,
bd.category,
bd.duration,
s.specialitydescription,
es.entertainerspecialitycost
FROM Entertainer e,
BookingDetail bd,
Booking b,
EntertainerSpeciality es,
Speciality s
WHERE e.entertainerid = bd.entertainerid
AND b.bookingid = bd.bookingid
AND e.entertainerid = es.entertainerid
AND s.specialityid = es.specialityid
AND e.entertainerid = 1
Fixed Code Sample
The best solution is to review the query to reduce the number of WHERE clauses or use join:
SELECT e.entertainerid,
e.firstname,
e.lastname,
b.customerid,
b.eventdate,
bd.category,
bd.duration,
s.specialitydescription,
es.entertainerspecialitycost
FROM ENTERTAINER e
JOIN BOOKINGDETAIL bd ON bd.entertainerid = e.entertainerid
JOIN BOOKING b ON b.bookingid = bd.bookingid
JOIN ENTERTAINERSPECIALITY es ON es.entertainerid = e.entertainerid
JOIN SPECIALITY s ON s.specialityid = es.specialityid
WHERE e.entertainerid = 1
Related Technologies
Technical Criterion
Efficiency - SQL and Data Handling Performance
About CAST Appmarq
CAST Appmarq is by far the biggest repository of data about real IT systems. It's built on thousands of analyzed applications, made of 35 different technologies, by over 300 business organizations across major verticals. It provides IT Leaders with factual key analytics to let them know if their applications are on track.