I am using an MS Access database with ASP and most of the queries are very quick. However, I have this one query which essentially joins three tables using RIGHT OUTER JOINS. In MS Access the query is fast but when displaying the results on the web it takes upwards of 20 seconds...argh. Is there a better/faster way to join these tables using SQL???
SQL Statement: SELECT tblAttachments.AttID, tblAttachments.Att_Description, tblAttachments.Att_Scheduled_Date, tblAttachments.Att_Scheduled_Time_Start, tblAttachments.Att_Scheduled_Length, tblAttachments.Att_Staff, tblAttachments.Att_Supervisor, tblAttachments.Att_Comm_Disp, tblAttachments.Att_Builder_Disp, tblAttachments.Att_WSO, tblAttachments.DateLastUpdated, tblHome.Home_Owner_Fname, tblHome.Home_Owner_Lname, tblHome.Home_Spouse_Fname, tblHome.Home_Spouse_Lname, tblHome.[Lot/Block], tblHome.Home_address1, tblHome.Home_City, tblHome.Home_ST, tblHome.Home_Zip, tblHome.[Home_Owner_Home_Tele#], tblHome.[Home_Owner_Work_Tele#], tblHome.[Home_Owner_Cell_Tele#], tblHome.[Home_Spouse_Home_Tele#], tblHome.[Home_Spouse_Work_Tele#], tblHome.[Home_Spouse_Cell_Tele#], tblHome.Home_Warranty_Expire, tblServiceOrder.SERVICE_REQUESTED, tblServiceOrder.ACTION_TAKEN FROM tblServiceOrder RIGHT JOIN (tblHome RIGHT JOIN tblAttachments ON tblHome.Home_address1 = tblAttachments.Att_Owner_Address) ON tblServiceOrder.WSO = tblAttachments.Att_WSO WHERE tblAttachments.Att_Staff = 'test' and tblAttachments.Att_Scheduled_Date = #3/30/2004# ORDER BY tblAttachments.Att_Scheduled_Time_Start
The tblAttachments table is the main table and I want all the records from that table based on the criteria and then additionally the fields from the other tables if they exist.
Hope that makes sense.....THANK YOU ALL IN ADVANCE!!
SQL Statement: SELECT tblAttachments.AttID, tblAttachments.Att_Description, tblAttachments.Att_Scheduled_Date, tblAttachments.Att_Scheduled_Time_Start, tblAttachments.Att_Scheduled_Length, tblAttachments.Att_Staff, tblAttachments.Att_Supervisor, tblAttachments.Att_Comm_Disp, tblAttachments.Att_Builder_Disp, tblAttachments.Att_WSO, tblAttachments.DateLastUpdated, tblHome.Home_Owner_Fname, tblHome.Home_Owner_Lname, tblHome.Home_Spouse_Fname, tblHome.Home_Spouse_Lname, tblHome.[Lot/Block], tblHome.Home_address1, tblHome.Home_City, tblHome.Home_ST, tblHome.Home_Zip, tblHome.[Home_Owner_Home_Tele#], tblHome.[Home_Owner_Work_Tele#], tblHome.[Home_Owner_Cell_Tele#], tblHome.[Home_Spouse_Home_Tele#], tblHome.[Home_Spouse_Work_Tele#], tblHome.[Home_Spouse_Cell_Tele#], tblHome.Home_Warranty_Expire, tblServiceOrder.SERVICE_REQUESTED, tblServiceOrder.ACTION_TAKEN FROM tblServiceOrder RIGHT JOIN (tblHome RIGHT JOIN tblAttachments ON tblHome.Home_address1 = tblAttachments.Att_Owner_Address) ON tblServiceOrder.WSO = tblAttachments.Att_WSO WHERE tblAttachments.Att_Staff = 'test' and tblAttachments.Att_Scheduled_Date = #3/30/2004# ORDER BY tblAttachments.Att_Scheduled_Time_Start
The tblAttachments table is the main table and I want all the records from that table based on the criteria and then additionally the fields from the other tables if they exist.
Hope that makes sense.....THANK YOU ALL IN ADVANCE!!
