Optimizing a query with multiple joins and aggregates?

[obuspider]

I have the following query that I am building through and ASP page based on a user selection. The info is being pulled from a SQL server db. I am being told that there are too many joins and aggregates. I know there are, but I don’t know of any other way to pull the information I need for one report. I don’t have access to the db or the the dba to use the tools to see how the query is running and what might make it better. Any ideas?

sqlQry1="Select schools.site, schools.area, avg(total_enrollment), avg(d_free_apr), "
sqlQry1= sqlQry1 & " avg(sc_free_Meals), avg(d_Redu_apr), avg(sc_redu_meals), "
sqlQry1= sqlQry1 & " avg(sc_d_paid_meals), sc_meal_type, schcat, avg(sc_emp_meals), "
sqlQry1= sqlQry1 & " avg(d_stud_adult_meals_cash), avg(d_tot_meals), "
sqlQry1= sqlQry1 & " avg(d_stud_adult_Ala_meals), schoolName, avg(fc_free_apr), "
sqlQry1= sqlQry1 & " avg(fc_reduced_apr), avg(fc_paid_apr), "
sqlQry1= sqlQry1 & " avg(fc_alac_cash), avg(tot_meals), avg(fc_adult_cash)"
sqlQry1= sqlQry1 & " FROM (((schools inner join tbl_scanned_sales on schools.site = "
sqlQry1= sqlQry1 & " tbl_scanned_sales.sc_site) inner join enrollment on schools.site = "
sqlQry1= sqlQry1 & " enrollment.cus_site) LEFT OUTER join tbl_forecast on schools.site = "
sqlQry1= sqlQry1 & " tbl_forecast.site and sc_meal_type=meal_type) "
sqlQry1= sqlQry1 & " WHERE sc_sales_date between '" & fsale_date & "' and '" & tsale_date & "' "
If optCH = 1 then sqlQry1 = sqlQry1 & "and sc_site = '" & txtSite & "'" End if
If optCH = 3 then sqlQry1 = sqlQry1 & "and schools.area='" & txtarea & "'" End if
If optCH = 5 then sqlQry1 = sqlQry1 & "and lower(right(tbl_scanned_sales.area,1))='" & selReg & "'" End if
If optCH = 7 then sqlQry1 = sqlQry1 & "and schcat ='" & schcat & "'" End if
sqlQry1= sqlQry1 & " and fc_program = 'bfast_lunch'"
sqlQry1=sqlQry1 & " group by schools.site, schools.area, schcat, sc_meal_type, SchoolName"

 

[rac2]

Who or what is telling you that you have too many aggregates and joins?

It's not like there is an ethical standard for aggregates and joins. It is a matter of the structure of your database and the question you need to answer.

At any rate it is difficult to consider your question without knowing which tables the various columns are in.

You might try writing the query with aliases for the table names like this.

SELECT s.site, s.area, AVG(s.total_enrollment),
       AVG(tss.d_free_apr), . . .
FROM schools s
JOIN tbl_scanned_sales tss ON s.site = tss.sc_site

That way we can see which columns come from which tables.

 

[obuspider]

rac2,

The programmer that built most of the rest of the application is telling me there are too many joins etc. When I ask how he'd do it though, he won't give me a straight answer

Here is the query rewritten like you suggested.

sqlQry1="Select s.site, s.area, avg(e.total_enrollment), avg(tss.d_free_apr), "
sqlQry1= sqlQry1 & " avg(tss.sc_free_Meals), avg(tss.d_Redu_apr), avg(tss.sc_redu_meals), "
sqlQry1= sqlQry1 & " avg(tss.sc_d_paid_meals), tss.sc_meal_type, s.schcat, avg(tss.sc_emp_meals), "
sqlQry1= sqlQry1 & " avg(tss.d_stud_adult_meals_cash), avg(tss.d_tot_meals), "
sqlQry1= sqlQry1 & " avg(tss.d_stud_adult_Ala_meals), s.schoolName, avg(tf.fc_free_apr), "
sqlQry1= sqlQry1 & " avg(tf.fc_reduced_apr), avg(tf.fc_paid_apr), "
sqlQry1= sqlQry1 & " avg(tf.fc_alac_cash), avg(tf.tot_meals), avg(tf.fc_adult_cash)"
sqlQry1= sqlQry1 & " FROM (((schools s inner join tbl_scanned_sales tss on s.site = "
sqlQry1= sqlQry1 & " tss.sc_site) inner join enrollment e on s.site = "
sqlQry1= sqlQry1 & " e.cus_site) LEFT OUTER join tbl_forecast tf on s.site = "
sqlQry1= sqlQry1 & " tf.site and tss.sc_meal_type=tf.meal_type) "
sqlQry1= sqlQry1 & " WHERE tss.sc_sales_date between '" & fsale_date & "' and '" & tsale_date & "' "
If optCH = 1 then sqlQry1 = sqlQry1 & "and tss.sc_site = '" & txtSite & "'" End if
If optCH = 3 then sqlQry1 = sqlQry1 & "and s..area='" & txtarea & "'" End if
If optCH = 5 then sqlQry1 = sqlQry1 & "and lower(right(tss.area,1))='" & selReg & "'" End if
If optCH = 7 then sqlQry1 = sqlQry1 & "and s.schcat ='" & schcat & "'" End if
sqlQry1= sqlQry1 & " and tf.fc_program = 'bfast_lunch'"
sqlQry1=sqlQry1 & " group by s.site, s.area, s.schcat, tss.sc_meal_type, s.SchoolName"

 

[rac2]

OK, good.

In general you need n-1 joining conditions to link n tables. The condition to join a pair of tables is that the values of the common columns are equal. Your query involves four tables - schools, tbl_scanned_sales, enrollment, and tbl_forecast. Therefore you need three joining conditions.

Your query has three JOINs. So what is the problem? Well that last condition, tss.sc_meal_type=tf.meal_type, is actually a joining condition. Here are the joining conditions in your query.

s.site = tss.sc_site
s.site = e.cus_site
s.site = tf.site
tss.sc_meal_type = tf.meal_type

You don't need that last condition to join the four tables.

My next question is this. Is the purpose of this query to compare actual sales to forecasts? And to breakdown by school site and meal type? And to show the area, category of the school, enrollment, and name of the school in addtion to showing the site and meal type?

Yep, that was all one question. I have another question but first I await your answer.

 

[obuspider]

rac2,

yes, the query is supposed to pull a recset for reporting and comparing actual sales to forecasted sales

 

[rac2]

The next question is this. For one school.site, the area, category, enrollment, and name of the school are determined? That is, those columns describe the site?

An area has many sites?
A category has many sites?
A school name has one site?

The next question is. There is one row of forecasted data for a meal type at a site? In other words the forecast covers a fixed period and is specific to a type of meal at a school.site?

And. There are many, many scanned sales every day at every site? In other words the scanned sales are the transactions that are aggregated?


If these assumptions are correct then you can eliminate all of the AVG functions involving columns in tbl_forecast. This is because the forecast is a single value for a site and the result lists every site, so there is nothing to aggregate.

 

[obuspider]

rac2,

Thanks for the tips. They have been helpful. I finally go them to run the query through analyzer and after making the recommended changes, it runs well now.