Multiple inner joins in SQL 2000

Question

I'm in the unfortunate situation of developing an app using SQL Server 2005 in the development environment but SQL Server 2000 on the production server. I have a fairly complex SELECT query which works fine on the development/test server but falls over in the production environment:

SELECT tbl_questions.Question, tbl_questions.QuestionCode 
FROM tbl_questions INNER JOIN (
    SELECT sg.questioncode, sg.gradeB, sg.gradeA, t2.wt 
    FROM tbl_scoregrade AS sg INNER JOIN (
     SELECT t1.QuestionCode, AVG(1.0 * aw.Weight) AS wt 
     FROM tbl_AnswerWeight AS aw INNER JOIN (
      SELECT assa.QuestionCode, assa.Answer 
      FROM tbl_AllStaffSurveyAnswers AS assa INNER JOIN 
      tbl_AllStaffSurvey AS ass ON assa.Questionguid = ass.Questionguid 
      WHERE (ass.Trust = 'RD7') AND (ass.Specialty = '97'))
     AS t1 ON aw.questioncode = t1.QuestionCode AND aw.Response = t1.Answer 
     GROUP BY t1.QuestionCode )
    AS t2 ON sg.questioncode = t2.QuestionCode AND sg.gradeA > t2.wt)
AS t3 ON tbl_questions.QuestionCode = t3.questioncode

Can you see anything that should make a difference when run on different versions of SQL server, or indeed any ways of simplifying the query in any case?

Answer 1

I cant see anything immediately wrong with the SQL. However, if you have the errors you are receiving that will help.

Are the schemas/table structure identical on both servers? for example, are you using NVARCHAR(MAX) on SQL 2005 machine yet NTEXT on SQL 2000? This would stop your GROUP BY from working.

Finally, you can change the compatibility level of the database on SQL 2005. In management studio, right click the database, choose, properties. Go to the Options page and select Compatibility level as SQL Server 2000 (80).

Answer 2

This is not the answer to your question, but an important thing to think of. You have a fairly complex query and it will consume a lot of resources. The Optimizer in SQL server 2000 will not be able to create an optimized queryplan due to the complexity and will probably use table scans. It is not recommended to use more than 4 joins in SQL server 2000.

I suggest you try to divide the statement and use temporary tables

Another thing to think of is the use of a "triangular join"

ON sg.questioncode = t2.QuestionCode AND sg.gradeA > t2.wt

This is an interesting article about Triangular joins

Regards

Håkan Winther

Answer 3

Ok, this is a bit embarrassing - it turns out (as Robin said) that there's nothing wrong with the SQL itself; the query is run within an ASPX application and it turns out the connection string in the web.config file was wrong.

But thanks for some very good answers - Robin's tip on changing the compatability level and Håkan's pointer to triangular joins are definitely things I'll be looking into.