Clever Geek Handbook

Multiple Inner Joins in SQL 2000

I got into a bad situation developing an application using SQL Server 2005 in a development environment, but SQL Server 2000 on a production server. I have a fairly complex SELECT query that works fine on a dev / test server but crashes in a 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 even any way to simplify the query anyway?

+2


source to share


3 answers


Okay, this is a little awkward - it turns out (as Robin said) that nothing happened to the SQL itself; the request is being made in an ASPX application and it turns out that the connection string in the web.config file was incorrect.



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

0


source


This is not an answer to your question, but an important thing to think about. You have a rather complex query and it will consume a lot of resources. The SQL Server 2000 optimizer will not be able to create an optimized query due to the complexity and will likely use table scans. It is not recommended to use more than 4 connections on SQL Server 2000.

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

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

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


This is an interesting article about Triangular Joints

Hello

Hokan Winter

+2


source


I don't see anything wrong with SQL. However, if you have errors that you are getting, this will help.

Are the schemas / table structure the same on both servers? For example, you are using NVARCHAR (MAX) on a SQL 2005 machine, but NTEXT on SQL 2000? This will stop your GROUP BY from working.

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

+1


source






More articles:


All Articles