Need to know how to optimize an MSSQL statement

[mavrick]

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 .

[raptorix]

With this description its hard to give hints, but you can check the execution plan in the query analyser to see what steps takes the most time. One of the most common causes of slow queries are bad indices, or bad identifier datatypes

[mavrick]

Sorry Guys...

Did not mean to...

Here is the View..

(Report View)
SELECT SUM(dbo.1_Link_StudentGradeRaceGender.NumberOfStudents) 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...

[jgar]

I don't pretend to understand the code entirely, but what is "Race"?

[mattglet]

Race is another term for ethnicity.

"The descendants of a common ancestor; a family, tribe, people, or nation, believed or presumed to belong to the same stock; a lineage; a breed."

[duckhunter]

Statement looks pretty normal and well formed. One thing that can affect speed is the order in which you bring back the tables depending on how they are indexed. Try moving your tables around so that you are selecting smaller tables first then joining to the larger ones and visa/versa to see what's faster.

How are you presenting the data? Can you show us some sample output? It seems that with so much data, this would lend itself to multiple queries just depending on how you are presenting it.

Another thing is to run your statement in Query Analyzer with the Execution plan turned on. You will see the bottlenecks (High % Cost). The one thing to watch out for are 'Table Scans'. They are as bad as they sound. You want to see 'Index Seek' or 'Bookmarks' being used. Set up indexes, primary & foreign key relationships on your tables that help those bottlenecks and you should see big improvements.

[edit]Add the 'with (nolock)' hint behind all your tables. That will help speed too.[edit]

[mattglet]

duckhunter-

Any good sites that show how to decipher the Execution plan? I have a query that could use some help with things (a couple different 10-11% Cost), and I might be able to optimize it a little.

[duckhunter]

THIS [sql-server-performance.com] is a pretty good page on the subject.

[mattglet]

That's a great page. Thanks.

[RainMaker]

Time and Execution also depends on how you are calling this massive statement. To me it's really big because I haven't done anything I guess THAT large scale...so Kudo's to you!.....(I am sure there is much larger :-)) Anyhoo if you are not using a storedprocedure this would affect performance as well. SP's reside right on the server...and are compiled because your queries are "linked" to an execution plan which determines the fastest way to retrieve the data. Also it is known that on the client or Web Service Side that it is a decrease in performance sending complex Queries across the net using the provided protocol. So you might be using SP's but it didn't look like it was mentioned...so I couldn't resist.