Hello,
this is the Generated Native SQLÃ, from teh tabular model of my main query:
If somebody can tell me if it looks OK? It takes a while to get the data back.....
is it OK that I have in this SQL cople select statments??
Thank you Olha
select "Projects"."project_id", "Projects"."project_name", "Projects"."curr_sponsor_rating1", "Projects"."curr_sponsor_rating_updatedby", "Projects"."c24", "Projects"."Calculation1", "Projects"."project_state_desc", "Projects"."project_type_desc", "Projects"."project_priority_desc", "Projects"."project_group_desc", "Projects"."Calculation9", "Projects"."Calculation2", "Projects"."percent_complete", "Projects"."project_scope", "Projects"."div_code"+'-'+"Projects"."loc_code"+'-'+"Projects"."dept_code", "Projects"."plan_end_date", "Projects"."Calculation4", "Projects"."Calculation5", "Projects"."Calculation6", "Projects"."Calculation7", "Sub_Projects"."subproject_name", "Sub_Projects"."subproject_state_desc", "Sub_Projects"."subproject_id", "Sub_Projects"."curr_sponsor_rating", "Sub_Projects"."c13", "Sub_Projects"."curr_sponsor_rating_updatedby"
from (
select "d_project"."project_id" AS "project_id", "d_project"."div_code" AS "div_code", "d_project"."loc_code" AS "loc_code", "d_project"."dept_code" AS "dept_code", "d_project"."project_name" AS "project_name", "d_project"."plan_end_date" AS "plan_end_date", "d_project"."percent_complete" AS "percent_complete", "d_project_priority"."project_priority_desc" AS "project_priority_desc", "v_project"."project_scope" AS "project_scope", "d_project_state_current"."project_state_desc" AS "project_state_desc", "d_project_type"."project_type_desc" AS "project_type_desc", "d_project"."curr_sponsor_rating" AS "curr_sponsor_rating1", "d_project"."curr_sponsor_rating_updatedby" AS "curr_sponsor_rating_updatedby", "d_project"."curr_sponsor_rating_effectivedate" AS "c24", "d_project_group"."project_group_desc" AS "project_group_desc", "FOCUS_OLAP"."dbo".udf_focus_rpt_get_projectsponsor("d_project"."project_id") AS "Calculation2", "FOCUS_OLAP"."dbo".udf_focus_rpt_get_projectmanager("d_project"."project_id") AS "Calculation1", "FOCUS_OLAP"."dbo".udf_focus_rpt_get_projectbusinesslead("d_project"."project_id") AS "Calculation9", "FOCUS_OLAP"."dbo".udf_focus_rpt_project_businessrequesters("d_project"."project_id") AS "Calculation4", "FOCUS_OLAP"."dbo".udf_focus_rpt_project_financialdrivers("d_project"."project_id") AS "Calculation5", "FOCUS_OLAP"."dbo".udf_focus_rpt_project_gamewinners("d_project"."project_id") AS "Calculation6", "FOCUS_OLAP"."dbo".udf_focus_rpt_project_businessstrategies("d_project"."project_id") AS "Calculation7"
from "dbo"."d_project" "d_project", "dbo"."d_project_costcenter" "d_project_costcenter", "dbo"."d_project_priority" "d_project_priority", "dbo"."v_project" "v_project", "dbo"."d_project_state_current" "d_project_state_current", "dbo"."d_project_type" "d_project_type", "dbo"."d_project_sponsor_rating_current" "T7", "dbo"."d_project_group" "d_project_group", "dbo"."d_project_sponsor_rating_prior_1" "T9", "dbo"."d_project_sponsor_rating_prior_2" "T10", "dbo"."d_project_sponsor_rating_prior_3" "T11", "dbo"."d_project_sponsor_rating_prior_4" "T12"
where "T11"."project_sponsor_rating_code" = "d_project"."prior3_sponsor_rating" and "T10"."project_sponsor_rating_code" = "d_project"."prior2_sponsor_rating" and "T9"."project_sponsor_rating_code" = "d_project"."prior1_sponsor_rating" and "T7"."project_sponsor_rating_code" = "d_project"."curr_sponsor_rating" and "d_project_group"."project_group_id" = "d_project"."project_group_id" and "d_project_priority"."project_priority_id" = "d_project"."project_priority_id" and "d_project_type"."project_type_id" = "d_project"."project_type_id" and "d_project_state_current"."project_state_id" = "d_project"."curr_project_state_id" and "d_project_costcenter"."div_code" = "d_project"."div_code" and "d_project_costcenter"."loc_code" = "d_project"."loc_code" and "d_project_costcenter"."dept_code" = "d_project"."dept_code" and "T12"."project_sponsor_rating_code" = "d_project"."prior4_sponsor_rating" and "v_project"."project_id" = "d_project"."project_id") "Projects", (
select "v_subproject"."project_id" AS "project_id", "d_subproject"."subproject_id" AS "subproject_id", "d_subproject"."subproject_name" AS "subproject_name", "d_subproject_state"."subproject_state_desc" AS "subproject_state_desc", "d_subproject"."curr_sponsor_rating" AS "curr_sponsor_rating", "d_subproject"."curr_sponsor_rating_updatedby" AS "curr_sponsor_rating_updatedby", "d_subproject"."curr_sponsor_rating_effectivedate" AS "c13"
from "dbo"."v_subproject" "v_subproject", "dbo"."d_subproject" "d_subproject", "dbo"."d_subproject_state" "d_subproject_state"
where "d_subproject_state"."subproject_state_id" = "d_subproject"."subproject_state_id" and "d_subproject"."project_id" = "v_subproject"."project_id" and "d_subproject"."subproject_id" = "v_subproject"."subproject_id") "Sub_Projects"
where "Projects"."project_id" = "Sub_Projects"."project_id"
select "Projects"."project_id" AS "Project_Id"
from (
select "d_project"."project_id" AS "project_id"
from "dbo"."d_project" "d_project", "dbo"."d_project_costcenter" "d_project_costcenter", "dbo"."d_project_priority" "d_project_priority", "dbo"."v_project" "v_project", "dbo"."d_project_state_current" "d_project_state_current", "dbo"."d_project_type" "d_project_type", "dbo"."d_project_sponsor_rating_current" "T7", "dbo"."d_project_group" "d_project_group", "dbo"."d_project_sponsor_rating_prior_1" "T9", "dbo"."d_project_sponsor_rating_prior_2" "T10", "dbo"."d_project_sponsor_rating_prior_3" "T11", "dbo"."d_project_sponsor_rating_prior_4" "T12"
where "T11"."project_sponsor_rating_code" = "d_project"."prior3_sponsor_rating" and "T10"."project_sponsor_rating_code" = "d_project"."prior2_sponsor_rating" and "T9"."project_sponsor_rating_code" = "d_project"."prior1_sponsor_rating" and "T7"."project_sponsor_rating_code" = "d_project"."curr_sponsor_rating" and "d_project_group"."project_group_id" = "d_project"."project_group_id" and "d_project_priority"."project_priority_id" = "d_project"."project_priority_id" and "d_project_type"."project_type_id" = "d_project"."project_type_id" and "d_project_state_current"."project_state_id" = "d_project"."curr_project_state_id" and "d_project_costcenter"."div_code" = "d_project"."div_code" and "d_project_costcenter"."loc_code" = "d_project"."loc_code" and "d_project_costcenter"."dept_code" = "d_project"."dept_code" and "T12"."project_sponsor_rating_code" = "d_project"."prior4_sponsor_rating" and "v_project"."project_id" = "d_project"."project_id") "Projects", "dbo"."v_project_status" "Weekly_Status"
where "Weekly_Status"."project_id" = "Projects"."project_id"
order by 1 asc
select "T1"."project_id" AS "Project_Id"
from "dbo"."f_project_estimates" "T1"
order by 1 asc
select "Business_Strategy"."business_strategy_id1", "Business_Strategy"."project_id1"
from (
select "T1"."project_id" AS "project_id1", "T1"."business_strategy_id" AS "business_strategy_id1"
from "dbo"."d_project_business_strategy_xref" "T1", "dbo"."d_project_business_strategy" "d_project_business_strategy"
where "d_project_business_strategy"."business_strategy_id" = "T1"."business_strategy_id") "Business_Strategy"
{call "FOCUS_OLAP"."dbo".usp_focus_rpt_get_businessstrategylist(?,?)}
select "T1"."project_id" AS "Project_Id"
from "dbo"."d_project_business_requester_xref" "T1", "dbo"."d_project_business_requester" "d_project_business_requester"
where "d_project_business_requester"."business_unit_id" = "T1"."business_unit_id"
order by 1 asc
ReportNet Addict - Note:
I changed the topic name to clarify the problem
is it Oracle / SQL Server / DB2 / Excel / XML / TXT / CSV / D-ISAM / C-ISAM / FOXPRO / ACCESS / .... / ... ? As you should understand... it's hard to say.
BUT:
It's not strange that you query is running slow: because your sql code contains 5 individual SQL statements that are executed individually on the server and are joined by the ReportNet server.
I think you must take a look at your Framework package and check your joins and if that's not the case consider write the sql statement by yourself.
thank you very much for respond,
we use SQL 2000;
I don't have an acess to FM :(
If I will write my own SQL, how whould I insert insted of this one ??
I just check some SQL from my other reports that run much faster and they look similar. I don't know what to do.
Do you see any filters applied to the above sql? What happens: The Database returns ALL data and all necessary filters & joins are made by ReportNet. So if your db is eg. 700MB and your Report only 100KB then
DB -> CRN = 700MB of Data; CRN process this (700MB of data) and returns 100KB report (Unfortunately this is a real-example :S )
i couldn't get what u meant by those numbers 700 and 100 MB. Plz can u explain us once more...
Srik
I mean... Almost the complete db (700MB) is copied to CRN and processed there and returns a 100KB report
thank you very much for the wxplanations,
could you please tell me more how would I implement this?
Quoteconsider write the sql statement by yourself.
any examples?
Thank you ;)
Start SQL Query Analyzer and start typing.... :S
Or reevaluate you framework.... Maybe it can be solved in there... Otherwise I don't know...