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.
Inscription à :
Publier les commentaires (Atom)
Aucun commentaire:
Enregistrer un commentaire