Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team ,
I am trying to join 3 table i have extract data from my sql
Its taking so much time not getting the result .
Please find the code below.
Please help how to reduce less time
code:
select
a.Month as DateField,
a.Engagement_Code,
a.Project_Name,
c.resource_display_name,
a.Project_StartDate,
a.Project_EndDate,
a.Project_Code,
a.Contract_Type,
a.Project_Manager,
a.Project_Work_Location_State,
a.Payment_Terms,
a.Project_Stage,
a.System_Revenue_Recognized,
a.Consolidated_All_Resource_Direct_Cost,
a.Consolidated_Other_Direct_Cost,
a.System_Revenue,
a.Plan_Utilization_Basis_Hours,
a.Actual_Utilized_Hours,
a.Actual_Projected,
b.Engagement_Name,
b.Engagement_Account_Executive,
b.Client_Name
From mis_automation_db.project_details_ppm a
Inner join mis_automation_db.engagement_project_summary_ppm b
on a.Engagement_Code = b.Engagement_Code
Inner join mis_automation_db.project_resource_details_ppm c
on a.Project_Code=c.Project_Code;
I have use that code in Mysql db thats working fine
I don't mean to be a Negative Nancy, but this is kind of out of the scope of QlikSense. I'm not a DBA and I could never be mistake for one, but you would have to look at the query plan for the SQL Statement and go from there.
Generally, Joins can be problematic, especially if there is no indexing on the joined columns (I think).
BUT, you may be able to get around it by doing the heavy lifting in Qlik. Sometimes, when my DB isn't acting right, or when it's clear that my DB isn't the correct tool for carrying out the task I want, I'll do it in Qlik instead. Qlik excels at making JOINS, so it wouldn't hurt to try something like this.
TableA:
LOAD Engagement_Code AS '%Engagement_Code',
Project_Code AS '%Project_Code',
*
;
SQL Select
a.Month as DateField,
a.Engagement_Code,
a.Project_Name,
a.Project_StartDate,
a.Project_EndDate,
a.Project_StartDate,
a.Project_EndDate,
a.Project_Code,
a.Contract_Type,
a.Project_Manager,
a.Project_Work_Location_State,
a.Payment_Terms,
a.Project_Stage,
a.System_Revenue_Recognized,
a.Consolidated_All_Resource_Direct_Cost,
a.Consolidated_Other_Direct_Cost,
a.System_Revenue,
a.Plan_Utilization_Basis_Hours,
a.Actual_Utilized_Hours,
a.Actual_Projected
FROM
mis_automation_db.project_details_ppm a
;
TableB:
LOAD Engagement_Code AS '%Engagement_Code',
*
;
SQL Select
b.Engagement_Code,
b.Engagement_Name,
b.Engagement_Account_Executive,
b.Client_Name
FROM
mis_automation_db.engagement_project_summary_ppm b
;
DROP FIELD Engagement_Code FROM TableC
;
TableC:
LOAD Project_Code AS '%Project_Code',
*
;
SQL Select
c.Project_Code
c.resource_display_name
FROM
mis_automation_db.project_resource_details_ppm c
;
DROP FIELD Project_Code FROM TableC
;
INNER JOIN(TableA)
LOAD * Resident TableB
;
DROP TABLE TableB
;
INNER JOIN(TableA)
LOAD * Resident TableC
;
DROP TABLE TableC
;
Thanks for reply . But above code also taking so much time
Okay, I've been thinking about this incorrectly.
What is "too much time".
How many rows exist in the Tables in question?
What are the machine specs for where you are running this query?
Hi It's taking loading data above then 16 min .
System configuration 16gb Ram & 1 tb hardisk
What are the processor specs? Joining is going to eat up processor capacity. And what about the db? Is it a remote database or is it on the same machine as the Qlik VM.
Also, what are the row counts of the tables?
NoOfRows(table_name)
If it's 16 minutes for 100 Million rows that's one thing, but if it's 10,000 you might have a problem.
Can you run the raw query on the sql server itself?
Hi Can i have your Skype Id/Mobile no . So that will discuss offline