Hi All

I am writing a MSSQL statement, and I wanted to know if there is any way that the statement could be optimized for speed, etc…

Ok! Here is the scenario…

I have 11 tables, and need to extract a SUM from one row and then need I do a GROUP BY Countries etc.

I have done it… and seem to work fine… but as I said it is not the best work I have done. If anybody could suggest some good ways for me to optimize my statement .

Thanks

use EXPLAIN, make sure it's using indexes

? Can you show us your code? It's hard to say how to optimize when you're guessing about how it's written now.

Sorry Guys...

Did not mean to...

Here is the View..

(Report View)
SELECT SUM(dbo.1_Link_StudentGradeRaceGender.NumberOfStud ents) AS NumberOfStudents, dbo.1_Race.RaceID, dbo.1_Gender.GenderID,
dbo.1_Grades.GradeLevelID, dbo.1_Schools.SchoolID, dbo.1_Schools.SchoolDeliveryCityID, dbo.1_StudentAges.StudentAgeID,
dbo.1_Provinces.CountryID, dbo.1_Schools.ProvinceID
FROM dbo.1_LinkGradeToAgeToSchool 1_LinkGradeToAgeToSchool INNER JOIN
dbo.1_Schools ON 1_LinkGradeToAgeToSchool.SchoolID = dbo.1_Schools.SchoolID INNER JOIN
dbo.1_LinkGradeAge ON 1_LinkGradeToAgeToSchool.LinkGradeAgeID = dbo.1_LinkGradeAge.LinkGradeAgeID INNER JOIN
dbo.1_StudentAges ON dbo.1_LinkGradeAge.StudentAgeID = dbo.1_StudentAges.StudentAgeID INNER JOIN
dbo.1_Grades INNER JOIN
dbo.1_Link_StudentGradeRaceGender INNER JOIN
dbo.1_Gender ON dbo.1_Link_StudentGradeRaceGender.GenderID = dbo.1_Gender.GenderID ON
dbo.1_Grades.GradeLevelID = dbo.1_Link_StudentGradeRaceGender.GradeLevelID INNER JOIN
dbo.1_Race ON dbo.1_Link_StudentGradeRaceGender.RaceID = dbo.1_Race.RaceID ON
dbo.1_LinkGradeAge.GradeLevelID = dbo.1_Grades.GradeLevelID AND
dbo.1_LinkGradeAge.GenderID = dbo.1_Gender.GenderID LEFT OUTER JOIN
dbo.1_Countries INNER JOIN
dbo.1_Provinces ON dbo.1_Countries.CountryID = dbo.1_Provinces.CountryID ON
dbo.1_Schools.ProvinceID = dbo.1_Provinces.ProvinceID LEFT OUTER JOIN
dbo.1_Cities ON dbo.1_Schools.SchoolDeliveryCityID = dbo.1_Cities.CityID AND
dbo.1_Schools.SchoolDeliveryCityID = dbo.1_Cities.CityID
GROUP BY dbo.1_Race.RaceID, dbo.1_Gender.GenderID, dbo.1_Grades.GradeLevelID, dbo.1_Schools.SchoolID, dbo.1_StudentAges.StudentAge,
dbo.1_Schools.SchoolDeliveryCityID, dbo.1_StudentAges.StudentAgeID, dbo.1_Provinces.CountryID, dbo.1_Schools.ProvinceID


(One of the Group By views)
The other 2 views are the same just group by Provinces and Country

SELECT SUM(NumberOfStudents) AS NumberOfStudents, RaceID, GenderID, GradeLevelID, SchoolDeliveryCityID, StudentAgeID
FROM dbo.v_1_report2
GROUP BY RaceID, GenderID, GradeLevelID, StudentAgeID, SchoolDeliveryCityID

Using asp.Net to retrive display the result...

here's your query, reformatted for readability:there seem to be several errors in the joins

i don't expect this query would even run, you should be getting syntax errors

also, why LEFT OUTER for some of them?

finally, your GROUP BY does not agree with your SELECT list

It seems to run fine on my side.. could maybe point some of the problems. i donot come up with any syntax errors and group by seems to be working...

can u please elaborate!!

well, i'll give you an example

the way i understand the SQL Server syntax, it should be impossible to write two ON conditions in a row like that