www.webdeveloper.com
Results 1 to 2 of 2

Thread: Problem in displaying of data from select statement its consume lots of time

  1. #1
    Join Date
    Oct 2011
    Posts
    349

    Problem in displaying of data from select statement its consume lots of time

    Hi,

    I have query to get all the employees which has no in and out it means they are absent


    here is my code:
    Code:
     
    SELECT emp.employeeid, emp.lastname, emp.firstname, emp.middlename, emp.department 
         FROM employees AS emp
         LEFT OUTER
         JOIN attendance_log AS att
         ON att.emp_id = emp.employeeid
         AND DATE(att.log_time) = '2013-11-05'
         WHERE emp.sub LIKE '%REG%'
         AND emp.department IN ('Assembly', 'Fabrication', 'Compounding', 'Finishing', 'PET', 'Production', 'Squash', 'QA', 'ENGINEERING')
         AND att.emp_id IS NULL;
    this code works but the problem is too long to query. it consumes 5-15mins before display the data.


    I hope somebody can help me to change my query for faster displaying of data.


    Thank you
    Last edited by newphpcoder; 11-07-2013 at 12:47 AM.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,921
    Code:
    emp.sub LIKE '%REG%'
    As it has to read every eligible record to check that condition, it *might* help to make that the last part of the WHERE clause (though I'm not sure if the DBMS is smart enough to optimize it that way anyway). It would be better, though, if you could optimize the data design so you don't have to use a LIKE, or at least only a LIKE where the wildcard is only at the end of the string, so that it can use the index on that column. (You do have indexes on all columns being used in WHERE and ON clauses, right?)

    Code:
    DATE(att.log_time) = '2013-11-05'
    This may not be very efficient, either, since it has to do that conversion before the comparison, again likely negating the effect of the index on that column. It might be better to do:
    Code:
    att.log_time BETWEEN '2013-11-05 00:00:00' AND '2013-11-05 23:59:59.999999'
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles