Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    5

    Unanswered: Need to know how to optimise an SQL statement

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use EXPLAIN, make sure it's using indexes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    ? Can you show us your code? It's hard to say how to optimize when you're guessing about how it's written now.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    Aug 2004
    Posts
    5

    Sorry!! Here is the Views

    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...

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's your query, reformatted for readability:
    Code:
    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
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2004
    Posts
    5

    can u please elaborate!!

    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!!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    Syntax
    [ FROM { < table_source > } [ ,...n ] ]

    < table_source > ::=
    table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
    | view_name [ [ AS ] table_alias ] [ WITH ( < view_hint > [ ,...n ] ) ]
    | rowset_function [ [ AS ] table_alias ]
    | user_defined_function [ [ AS ] table_alias ]
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
    | < joined_table >

    < joined_table > ::=
    < table_source > < join_type > < table_source > ON < search_condition >
    | < table_source > CROSS JOIN < table_source >
    | [ ( ] < joined_table > [ ) ]

    < join_type > ::=
    [ INNER | { { LEFT | RIGHT | FULL } [ OUTER] } ]
    [ < join_hint > ]
    JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •