[Closed] Problems with Generated Native SQL

[otsizh]

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

[sir_jeroen]

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.

[otsizh]

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.

[sir_jeroen]

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 )

[BIsrik]

i couldn't get what u meant by those numbers 700 and 100 MB. Plz can u explain us once more...

Srik

[sir_jeroen]

I mean... Almost the complete db (700MB) is copied to CRN and processed there and returns a 100KB report

[otsizh]

thank you very much for the wxplanations,

could you please tell me more how would I implement this?

consider write the sql statement by yourself.

any examples?

Thank you ;)

[sir_jeroen]

Start SQL Query Analyzer and start typing.... :S
Or reevaluate you framework.... Maybe it can be solved in there... Otherwise I don't know...