1 Reply Latest reply on Feb 15, 2018 11:42 PM by Stefan van der Merwe

    How many fields should be in customs SQL query in ETL for smooth data extraction.

    Shiv PANDIT

      Hello experts,

       

      I am facing an issue  in custom query in which I have taking 50 columns from 7 tables in ETL. It is taking too much time and then not coming the data from it. So how many

      fields should i remove from the custom query.

      Actually i have applied Or condition in the query.

       

      SELECT

        `appointments`.`id` AS `id`,

        `appointments`.`parent_id` AS `parent_id`,

        `appointments`.`is_new` AS `is_new`,

        `appointments`.`department_id` AS `department_id`,

        `appointments`.`ug_id` AS `ug_id`,

        `appointments`.`pg_id` AS `pg_id`,

        `appointments`.`staff_id` AS `staff_id`,

        `appointments`.`insprovider_id` AS `insprovider_id`,

        `appointments`.`ugcasesheet_id` AS `ugcasesheet_id`,

        `appointments`.`pgcasesheet_id` AS `pgcasesheet_id`,

        `appointments`.`date_waiting` AS `date_waiting`,

        `patients`.`id` AS `id (patients)`,

        `patients`.`pat_number` AS `pat_number`,

        `patients`.`first_name` AS `first_name`,

        `patients`.`last_name` AS `last_name`,

        `patients`.`***` AS `***`,

        `patients`.`no_of_visit` AS `no_of_visit`,

        `patients`.`status` AS `status (patients)`,

        `patients`.`reporting` AS `reporting`,

        `departments`.`id` AS `id (departments)`,

        `departments`.`name` AS `name (departments)`,

        `departments`.`department_type` AS `department_type`,

        `staffs`.`id` AS `id (staffs)`,

        `staffs`.`salutation` AS `salutation (staffs)`,

        `staffs`.`first_name` AS `first_name (staffs)`,

        `staffs`.`last_name` AS `last_name (staffs)`,

        `staffs`.`***` AS `*** (staffs)`,

        `casesheets`.`id` AS `id (casesheets)`,

        `casesheets`.`case_number` AS `case_number`,

        `casesheets`.`status` AS `status (casesheets)`,

        `casesheets`.`casesheet_type` AS `casesheet_type`,

        `casesheets`.`treatment_type` AS `treatment_type`,

        `worksteps`.`id` AS `id (worksteps)`,

        `worksteps`.`name` AS `name (worksteps)`,

        `worksteps`.`parent_type` AS `parent_type (worksteps)`,

        `worksteps`.`patient_id` AS `patient_id`,

        `worksteps`.`department_id` AS `department_id (worksteps)`,

        `worksteps`.`treatment_id` AS `treatment_id`,

        `worksteps`.`status` AS `status (worksteps)`,

        `worksteps`.`staff_id` AS `staff_id (worksteps)`,

        `worksteps`.`seq_no` as `Worksteps Seq no`,

        `treatments`.`id` AS `id (treatments)`,

        `treatments`.`patient_id` AS `patient_id (treatments)`,

        `treatments`.`department_id` AS `department_id (treatments)`,

        `treatments`.`tooth_no` AS `tooth_no`,

        `treatments`.`treatment_startdate` AS `treatment_startdate`,

        `treatments`.`treatment_enddate` AS `treatment_enddate`,

        `treatments`.`units` AS `units`,

        `treatments`.`status` AS `status (treatments)`,

        `treatments`.`treatment_valid_days` AS `treatment_valid_days`

      FROM `appointments`

        LEFT JOIN `patients` ON (`appointments`.`parent_id` = `patients`.`id`)

        LEFT JOIN `departments` ON (`appointments`.`department_id` = `departments`.`id`)

        LEFT JOIN `staffs` ON (`appointments`.`staff_id` = `staffs`.`id`)

        LEFT JOIN `casesheets` ON ((`appointments`.`pgcasesheet_id` = `casesheets`.`id`) OR (`appointments`.`ugcasesheet_id` = `casesheets`.`id`))

        LEFT JOIN `worksteps` ON (`casesheets`.`id` = `worksteps`.`parent_id`)

        LEFT JOIN `treatments` ON (`worksteps`.`treatment_id` = `treatments`.`id`)

       

      Please suggest guys..

      Thank you.

      Regards

      Shiv