Link to home
Start Free TrialLog in
Avatar of Bruce Gust
Bruce GustFlag for United States of America

asked on

This query times out. I've incorporated an EXPLAIN, but I don't know how to make use of it's feedback.

Here's the query:

INSERT into tasks_master_tracker_idb (`TASK_ID`,   
        `CUSTID`,  
        `INTEGRATION_ID`,  
        `INTEGRATION_NAME`,    
        `INTEGRATION_DESCRIPTION`, 
        `INTEGRATION_TYPE`,    
        `DIRECTION`,   
        `CRITICAL_CLIENTS`,    
        `CRITICAL_OPERATIONS`, 
        `COMPLEXITY`,  
        `COUNTRY_REGION`,  
        `VENDOR`,  
        `INTEGRATION_DOMAIN`,  
        `INTEGRATION_STATUS`,  
        `SCHEDULED_MANUAL`,    
        `SCHEDULE_FREQUENCY`,  
        `MONDAY`,  
        `TUESDAY`, 
        `WEDNESDAY`,   
        `THURSDAY`,    
        `FRIDAY`,  
        `SATURDAY`,    
        `SUNDAY`,  
        `SCHEDULE_NOTES`,  
        `INTEGRATION_RUN_TIME`,    
        `TIME_ZONE`,   
        `TRANSPORT_METHOD`,    
        `PROD_INBOUND_TRANSPORT_ADDR`, 
        `PROD_INBOUND_TRANSPORT_DIR`,  
        `PROD_INBOUND_USER_ID`,    
        `PROD_INBOUND_DELETE_AFTER_RETRIEVAL`,
        `PROD_OUTBOUND_TRANSPORT_ADDR`,    
        `PROD_OUTBOUND_TRANSPORT_DIR`, 
        `PROD_OUTBOUND_USER_ID`,
        `IMPL_INBOUND_TRANSPORT_ADDR`, 
        `IMPL_INBOUND_TRANSPORT_DIR`,  
        `IMPL_INBOUND_USER_ID`,    
        `IMPL_INBOUND_DELETE_AFTER_RETRIEVAL`,
        `IMPL_OUTBOUND_TRANSPORT_ADDR`,    
        `IMPL_OUTBOUND_TRANSPORT_DIR`, 
        `IMPL_OUTBOUND_USER_ID`,
        `FULLFILE_DELTAFILE`,  
        `PROD_INBOUND_FILE_NAME`,  
        `PROD_OUTBOUND_FILE_NAME`, 
        `IMPL_INBOUND_FILE_NAME`,  
        `IMPL_OUTBOUND_FILE_NAME`, 
        `PROD_INBOUND_FILE_DOCUMENT_RETENTION_POLICY`, 
        `IMPL_INBOUND_FILE_DOCUMENT_RETENTION_POLICY`,
        `CLIENT_CONTACT`,  
        `VENDOR_CONTACT`,
        `CREATED_BY`,  
        `CREATED_DATE`,    
        `MODIFIED_BY`, 
        `MODIFIED_DATE`)
      SELECT 
                t.ID,
                t.CUSTID, 
                t.INTID,  
                t.NAME, 
                t.DESCRIPTION,
                r.MAIN_6,
                t.DIRECTION,
                t.PRIORITY,
                tsk.CRITICAL_TO_OPERATION, 
                t.COMPLEXITY,  
                t.COUNTRY,
                r.MAIN_1,
                r.MAIN_7,
                r.REQ_STATUS,
                r.TECH_21,
                r.TECH_5,
                tsk.MONDAY, 
                tsk.TUESDAY, 
                tsk.WEDNESDAY, 
                tsk.THURSDAY, 
                tsk.FRIDAY, 
                tsk.SATURDAY, 
                tsk.SUNDAY,
                r.TECH_29,
                r.TECH_20,
                r.TECH_20,
                rt.TRANSPORT_3,
                rt.TRANSPORT_4,
                rt.TRANSPORT_21,
                rt.TRANSPORT_5,
                rt.TRANSPORT_26,
                rt.TRANSPORT_4,
                rt.TRANSPORT_21,
                rt.TRANSPORT_5,
                rt.TRANSPORT_9,
                rt.TRANSPORT_22,
                rt.TRANSPORT_10,
                rt.TRANSPORT_26,
                rt.TRANSPORT_9,
                rt.TRANSPORT_22,
                rt.TRANSPORT_10,
                r.DATA_6,
                rt.TRANSPORT_18,                
            rt.TRANSPORT_18, 
                rt.TRANSPORT_19, 
                rt.TRANSPORT_19, 
                rt.TRANSPORT_24, 
                rt.TRANSPORT_24, 
                 (SELECT c.USER_ID FROM contacts c where c.RID=t.ID and c.ROLE='PM' and RTYPE='TASK' LIMIT 1), 
                t.VENDOR,
                t.CREATED_BY,
               case
               when 
                  t.CREATE_DT IS NULL
               then
                  CURDATE()
               else
                  t.CREATE_DT
            end,
            t.UPDATED_BY,
            case
               when 
                  t.LAST_MODIFIED_DT IS NULL
               then
                  CURDATE()
               else
                  t.LAST_MODIFIED_DT
            end   
                FROM tasks t 
                LEFT JOIN tasks_ams tsk ON t.ID = tsk.RID   
            LEFT JOIN requirements r on t.ID = r.INTID_ and r.ID = (select MAX(ID) from requirements where INTID_ = t.ID)
            LEFT JOIN requirements_transport rt ON t.ID = rt.INTID__ and rt.ID = (select MAX(ID) from requirements_transport where INTID__ = t.ID)
                WHERE  (t.INTID is not null and t.CUSTID is not null ) 
            AND t.ID not in (select TASK_ID from tasks_master_tracker_idb)

Open in new window

When I place "EXPLAIN" before the INSERT, I get this:

1INSERTtasks_master_tracker_idb
ALL






1PRIMARYt
ALL



7689890.00Using where; Using temporary
1PRIMARYtsk
ALL



3100.00Using where; Using join buffer (hash join)
1PRIMARYr
eq_refPRIMARY,ID_UNIQUE,INTID__UNIQUEPRIMARY4func1100.00Using where
1PRIMARYrt
eq_refPRIMARYPRIMARY4func1100.00Using where
5SUBQUERYtasks_master_tracker_idb
indexTASK_ID_UNIQUE,TASK_IDTASK_ID_UNIQUE5
1100.00Using index
4DEPENDENT SUBQUERYrequirements_transport
ALL



2424710.00Using where
3DEPENDENT SUBQUERYrequirements
refINTID__UNIQUEINTID__UNIQUE4idb_dev.t.ID1100.00Using index
2DEPENDENT SUBQUERYc
refUNIQUE,RIDUNIQUE66const,idb_dev.t.ID310.00Using where; Using index
What does this mean and what can I do to streamline the query so I'm not taxing the database to the point where it times out?
SOLUTION
Avatar of Bembi
Bembi
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bruce Gust

ASKER

The SELECT is sound. When I run it, it will give me the standard 100 rows without much delay at all.

When I first ran it in context, the table that I was attempting to INSERT rows into had over 100K records. I thought that might be a problem given the subquery : AND t.ID not in (select TASK_ID from tasks_master_tracker_idb) , but that didn't seem to have any impact.

I tried this in an attempt to figure out how may records I was attempting to INSERT...

select count(*) from (SELECT 
      t.ID,
      t.CUSTID, 
      t.INTID,  
      t.NAME, 
      t.DESCRIPTION,
      r.MAIN_6,
      t.DIRECTION,
      t.PRIORITY,
      tsk.CRITICAL_TO_OPERATION, 
      t.COMPLEXITY,  
      t.COUNTRY,
      r.MAIN_1,
      r.MAIN_7,
      r.REQ_STATUS,
      r.TECH_21,
      r.TECH_5,
      tsk.MONDAY, 
      tsk.TUESDAY, 
      tsk.WEDNESDAY, 
      tsk.THURSDAY, 
      tsk.FRIDAY, 
      tsk.SATURDAY, 
      tsk.SUNDAY,
      r.TECH_29,
      r.TECH_20,
      r.TECH_20,
      rt.TRANSPORT_3,
      rt.TRANSPORT_4,
      rt.TRANSPORT_21,
      rt.TRANSPORT_5,
      rt.TRANSPORT_26,
      rt.TRANSPORT_4,
      rt.TRANSPORT_21,
      rt.TRANSPORT_5,
      rt.TRANSPORT_9,
      rt.TRANSPORT_22,
      rt.TRANSPORT_10,
      rt.TRANSPORT_26,
      rt.TRANSPORT_9,
      rt.TRANSPORT_22,
      rt.TRANSPORT_10,
      r.DATA_6,
      rt.TRANSPORT_18,                
      rt.TRANSPORT_18, 
      rt.TRANSPORT_19, 
      rt.TRANSPORT_19, 
      rt.TRANSPORT_24, 
      rt.TRANSPORT_24, 
      (SELECT c.USER_ID FROM contacts c where c.RID=t.ID and c.ROLE='PM' and RTYPE='TASK' LIMIT 1), 
      t.VENDOR,
      t.CREATED_BY,
      t.CREATE_DT,
      t.UPDATED_BY,
       t.LAST_MODIFIED_DT
      FROM tasks t 
      LEFT JOIN tasks_ams tsk ON t.ID = tsk.RID   
      LEFT JOIN requirements r on t.ID = r.INTID_ and r.ID = (select MAX(ID) from requirements where INTID_ = t.ID)
      LEFT JOIN requirements_transport rt ON t.ID = rt.INTID__ and rt.ID = (select MAX(ID) from requirements_transport where INTID__ = t.ID)
      WHERE  (t.INTID is not null and t.CUSTID is not null ) 
      AND t.ID not in (select TASK_ID from tasks_master_tracker_idb))

Open in new window

...and I got an error that said, "Every derived table must have it's own alias," which doesn't make sense since the SELECT that I'm counting from can be run independently.

What do you think?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial