suggest a alternate query to improve the performance?

Question

SELECT
B.tblBooking_id,
F.M_Merged_CustomerId,
B.depart_date,
B.cancellation,
B.booking_num,
B.end_date,
B.date_booked,
B.booking_price,
B.tblBooking_Id as SOURCE_RES_ID ,
B.agentid,
B.is_insert_date,
B.is_update_date is_update_date,
A.Agent_Email_Address as agent_email,
A.Agent_Email_Address as agent_email_system
FROM tblBooking B 
JOIN tblAgent A 
ON B.agentid=A.agentid
LEFT JOIN  tblFactCustomers F
ON B.booking_num = F.raw_booking_num
WHERE B.agentid !=0 or B.agentid IS NOT  NULL
GROUP BY B.tblBooking_id;

Table used : tblBooking , tblAgent , tblFactCustomers Index are present in booking num and raw booking num

Does this even compile? You are having a group by on the id and selecting so many other fields that are not in the group by nor aggregative. — Gilad Green, Jun 29 '16 at 15:01
If you don't want to get records with id of 0 or null then you should have an "and" operator between them — Gilad Green, Jun 29 '16 at 15:04
`B.agentid !=0 or B.agentid IS NOT NULL` is very likely to be wrong. It will _include_ `B.agentid = 0` because that is `NOT NULL`; I suspect you did not mean that. — Rick James, Jul 02 '16 at 02:35
Please provide `SHOW CREATE TABLE` for each table. We need to see the indexes, primary keys, engine, datatypes, etc. — Rick James, Jul 02 '16 at 02:36

score 0 · Answer 1 · answered Aug 30 '16 at 07:10

I doubt that script will execute successfully as there's group by clause and no aggregate function used. However for performance measures please check the field value B.agentID and if there's no value in negative then replace WHERE B.agentid !=0 or B.agentid IS NOT NULL with WHERE B.agentid >0 and create a non clustered index on agentID.

suggest a alternate query to improve the performance?

1 Answers1