vendredi 24 juin 2016

Can Someone Help me to tuning LINQ to SQL Query?

I have Linq to SQl query that a little bit complex, and this linq take 2 minutes to retrieve data. And it's really much time to take. So, Could someone help me to tuning this query? I've tried some code, but I still can't reduce the time. Here the query : from applicant in db.V_MPM_HRD_APPLICANTs join administration in from a in db.MPMHRD_APPLICANT_POSITION_ADMINISTRATIONs where a.COMPANY_ID == company_id orderby a.NUMBER descending group a by a.APPLICANT_ID into app_item select app_item.First() on applicant.APPLICANT_ID equals administration.APPLICANT_ID into JoinedApplicantAdmin from admin in JoinedApplicantAdmin.DefaultIfEmpty() join position in db.MPMHRD_POSITIONs on admin.POSITION_ID equals position.POSITION_ID into joinedposition from posisi in joinedposition.DefaultIfEmpty() where (applicant.POSITION_COMPANY_1 == company_id || applicant.POSITION_COMPANY_1 == null) select new MPMHRD_DATABASE_APPLICANT_REC ( applicant.APPLICANT_ID, applicant.APPLICANT_FIRSTNAME, applicant.APPLICANT_MIDDLENAME, applicant.APPLICANT_LASTNAME, applicant.APPLICANT_PLACEOFBIRTH, applicant.APPLICANT_DATEOFBIRTH, applicant.APPLICANT_GENDER, applicant.APPLICANT_RELIGION, applicant.APPLICANT_ETHNIC, applicant.ETHNIC_NAME, applicant.APPLICANT_MARITALSTATUS, applicant.APPLICANT_CHILD, applicant.APPLICANT_BLOODTYPE, applicant.APPLICANT_CITIZENSHIP, applicant.CITIZENSHIP, applicant.APPLICANT_TYPEOFIDENTITY, applicant.APPLICANT_IDENTITY, applicant.APPLICANT_IDENTITY_ADDRESS, applicant.APPLICANT_IDENTITY_DISTRICT, applicant.APPLICANT_IDENTITY_SUB_DISTRICT, applicant.APPLICANT_IDENTITY_RT, applicant.APPLICANT_IDENTITY_RW, applicant.APPLICANT_IDENTITY_COUNTRY, applicant.IDENTITY_COUNTRY, applicant.APPLICANT_IDENTITY_STATE, applicant.IDENTITY_STATE, applicant.APPLICANT_IDENTITY_CITY, applicant.IDENTITY_CITY, applicant.APPLICANT_IDENTITY_POSTALCODE, applicant.APPLICANT_ADDRESS, applicant.APPLICANT_DISTRICT, applicant.APPLICANT_SUB_DISTRICT, applicant.APPLICANT_RT, applicant.APPLICANT_RW, applicant.APPLICANT_COUNTRY, applicant.COUNTRY, applicant.APPLICANT_STATE, applicant.Province, applicant.APPLICANT_CITY, applicant.CITY, applicant.APPLICANT_POSTALCODE, applicant.APPLICANT_INSURANCE_ID, applicant.APPLICANT_PHONE, applicant.APPLICANT_MOBILE_1, applicant.APPLICANT_MOBILE_2, applicant.APPLICANT_EMAIL, applicant.APPLICANT_EDUCATION_LEVEL, applicant.EDUCATION_FIELD, applicant.EDUCATIONINSTITUTION_NAME, applicant.EDUCATIONINSTITUTION_ADDRESS, applicant.EDUCATIONINSTITUTION_CITY, applicant.APPLICANT_EDUCATION_GPA, applicant.APPLICANT_DATE_APPLIED, applicant.APPLICANT_DATE_AVAILABLE, applicant.APPLICANT_EXPECTED_SALARY, applicant.APPLICANT_POINT_OF_HIRE, applicant.APPLICANT_NOTE, applicant.APPLICANT_MESSAGE, applicant.APPLICANT_SOURCE, applicant.SOURCE_NAME, applicant.APPLICANT_PHOTO, applicant.APPLICANT_RESUME, applicant.POSITION_NAME_1, applicant.POSITION_NAME_EXTERNAL_1, applicant.POSITION_LEVEL_1, (posisi.POSITION_NAME != null ? posisi.POSITION_NAME : null), (posisi.POSITION_NAME_EXTERNAL != null ? posisi.POSITION_NAME_EXTERNAL : null), (posisi.POSITION_LEVEL != null ? posisi.POSITION_LEVEL : null), applicant.COMPANY_1, applicant.JOB_TITLE_1, applicant.BEGIN_EFF_1, applicant.END_EFF_1, applicant.JOB_DESC_1, applicant.REASON_1, applicant.COMPANY_2, applicant.JOB_TITLE_2, applicant.BEGIN_EFF_2, applicant.END_EFF_2, applicant.JOB_DESC_2, applicant.REASON_2, applicant.COMPANY_3, applicant.JOB_TITLE_3, applicant.BEGIN_EFF_3, applicant.END_EFF_3, applicant.JOB_DESC_3, applicant.REASON_3, (admin.NUMBER != null ? admin.NUMBER : 0), (admin.APPLIED_POSITION_ID != null ? admin.APPLIED_POSITION_ID : null), (admin.COMPANY_ID != null ? admin.COMPANY_ID : null), (admin.BRANCH_ID != null ? admin.BRANCH_ID : null), (admin.POSITION_ID != null ? admin.POSITION_ID : null), (admin.POSITION_MAPPING_DATE !=null? admin.POSITION_MAPPING_DATE : (DateTime?)null), (admin.SOURCE_NOTE != null ? admin.SOURCE_NOTE : null), (admin.ADMINISTRATION_DATE != null ? admin.ADMINISTRATION_DATE : null), (admin.ADMINISTRATION_PIC != null ? admin.ADMINISTRATION_PIC : null), (admin.ADMINISTRATION_RESULT != null ? admin.ADMINISTRATION_RESULT : null), (admin.ADMINISTRATION_STATUS != null ? admin.ADMINISTRATION_STATUS : Convert.ToInt16(null)), (admin.JOBTEST_DATE != null ? admin.JOBTEST_DATE : null), (admin.JOBTEST_PIC != null ? admin.JOBTEST_PIC : null), (admin.JOBTEST_RESULT != null ? admin.JOBTEST_RESULT : null), (admin.JOBTEST_STATUS != null ? admin.JOBTEST_STATUS : Convert.ToInt16(null)), (admin.PSYCHOTEST_DATE != null ? admin.PSYCHOTEST_DATE : null), (admin.PSYCHOTEST_PIC != null ? admin.PSYCHOTEST_PIC : null), (admin.PSYCHOTEST_NOTE != null ? admin.PSYCHOTEST_NOTE : null), (admin.PSYCHOTEST_PLACE != null ? admin.PSYCHOTEST_PLACE : null), (admin.PSYCHOTEST_IS_INTERNAL != null ? admin.PSYCHOTEST_IS_INTERNAL : Convert.ToInt16(null)), (admin.PSYCHOTEST_ATTENDANCE != null ? admin.PSYCHOTEST_ATTENDANCE : Convert.ToInt16(null)), (admin.PSYCHOTEST_RESULT != null ? admin.PSYCHOTEST_RESULT : null), (admin.PSYCHOTEST_STATUS != null ? admin.PSYCHOTEST_STATUS : Convert.ToInt16(null)), (admin.INTERVIEW_HRD_DATE != null ? admin.INTERVIEW_HRD_DATE : null), (admin.INTERVIEW_HRD_PIC != null ? admin.INTERVIEW_HRD_PIC : null), (admin.INTERVIEW_HRD_NOTE != null ? admin.INTERVIEW_HRD_NOTE : null), (admin.INTERVIEW_HRD_RESULT != null ? admin.INTERVIEW_HRD_RESULT : null), (admin.INTERVIEW_HRD_STATUS != null ? admin.INTERVIEW_HRD_STATUS : Convert.ToInt16(null)), (admin.INTERVIEW_HRD_STATUS_PIC != null ? admin.INTERVIEW_HRD_STATUS_PIC : Convert.ToInt16(null)), (admin.INTERVIEW_DATE_1 != null ? admin.INTERVIEW_DATE_1 : null), (admin.INTERVIEW_PIC_1 != null ? admin.INTERVIEW_PIC_1 : null), (admin.INTERVIEW_NOTE_1 != null ? admin.INTERVIEW_NOTE_1 : null), (admin.INTERVIEW_RESULT_1 != null ? admin.INTERVIEW_RESULT_1 : null), (admin.INTERVIEW_STATUS_1 != null ? admin.INTERVIEW_STATUS_1 : Convert.ToInt16(null)), (admin.INTERVIEW_STATUS_1_PIC != null ? admin.INTERVIEW_STATUS_1_PIC : Convert.ToInt16(null)), (admin.INTERVIEW_DATE_2 != null ? admin.INTERVIEW_DATE_2 : null), (admin.INTERVIEW_PIC_2 != null ? admin.INTERVIEW_PIC_2 : null), (admin.INTERVIEW_NOTE_2 != null ? admin.INTERVIEW_NOTE_2 : null), (admin.INTERVIEW_RESULT_2 != null ? admin.INTERVIEW_RESULT_2 : null), (admin.INTERVIEW_STATUS_2 != null ? admin.INTERVIEW_STATUS_2 : Convert.ToInt16(null)), (admin.INTERVIEW_STATUS_2_PIC != null ? admin.INTERVIEW_STATUS_2_PIC : Convert.ToInt16(null)), (admin.INTERVIEW_DATE_3 != null ? admin.INTERVIEW_DATE_3 : null), (admin.INTERVIEW_PIC_3 != null ? admin.INTERVIEW_PIC_3 : null), (admin.INTERVIEW_NOTE_3 != null ? admin.INTERVIEW_NOTE_3 : null), (admin.INTERVIEW_RESULT_3 != null ? admin.INTERVIEW_RESULT_3 : null), (admin.INTERVIEW_STATUS_3 != null ? admin.INTERVIEW_STATUS_3 : Convert.ToInt16(null)), (admin.INTERVIEW_STATUS_3_PIC != null ? admin.INTERVIEW_STATUS_3_PIC : Convert.ToInt16(null)), (admin.INTERVIEW_DATE_4 != null ? admin.INTERVIEW_DATE_4 : null), (admin.INTERVIEW_PIC_4 != null ? admin.INTERVIEW_PIC_4 : null), (admin.INTERVIEW_NOTE_4 != null ? admin.INTERVIEW_NOTE_4 : null), (admin.INTERVIEW_RESULT_4 != null ? admin.INTERVIEW_RESULT_4 : null), (admin.INTERVIEW_STATUS_4 != null ? admin.INTERVIEW_STATUS_4 : Convert.ToInt16(null)), (admin.INTERVIEW_STATUS_4_PIC != null ? admin.INTERVIEW_STATUS_4_PIC : Convert.ToInt16(null)), (admin.FINAL_INTERVIEW_DATE != null ? admin.FINAL_INTERVIEW_DATE : null), (admin.FINAL_INTERVIEW_PIC != null ? admin.FINAL_INTERVIEW_PIC : null), (admin.FINAL_INTERVIEW_NOTE != null ? admin.FINAL_INTERVIEW_NOTE : null), (admin.FINAL_INTERVIEW_RESULT != null ? admin.FINAL_INTERVIEW_RESULT : null), (admin.FINAL_INTERVIEW_STATUS != null ? admin.FINAL_INTERVIEW_STATUS : Convert.ToInt16(null)), (admin.FINAL_INTERVIEW_STATUS_PIC != null ? admin.FINAL_INTERVIEW_STATUS_PIC : Convert.ToInt16(null)), (admin.MEDICAL_TEST_DATE != null ? admin.MEDICAL_TEST_DATE : null), (admin.MEDICAL_TEST_PLACE != null ? admin.MEDICAL_TEST_PLACE : null), (admin.MEDICAL_TEST_NOTE != null ? admin.MEDICAL_TEST_NOTE : null), (admin.MEDICAL_TEST_DATE_RESULT != null ? admin.MEDICAL_TEST_DATE_RESULT : null), (admin.MEDICAL_TEST_RESULT != null ? admin.MEDICAL_TEST_RESULT : null), (admin.MEDICAL_TEST_STATUS != null ? admin.MEDICAL_TEST_STATUS : Convert.ToInt16(null)), (admin.STATUS != null ? admin.STATUS : Convert.ToInt16(null)), (admin.DESCRIPTION != null ? admin.DESCRIPTION : null), (admin.EMPLOYEE_ID != null ? admin.EMPLOYEE_ID : null) ) ); Thanks. Btw, sorry for my bad english.

Aucun commentaire:

Enregistrer un commentaire