Error in code leading to SQL Time out

[Ethos]

Hi, Why do I have an issue with a report that I am attempting to run. After 5 minutes I get an sql time out message. what code be the reasons? I have inclused the free hand sql code responsible.

I have inclused the code if you wish (should you require) to view it.

A break down analysis would serve my interests well.

Kind Regards,

P.S This is run in Business Objects XI (still if you can answer it!!!)

Ethos.

 

[WayneRyan]

Ethos,

To tell you the truth, I didn't read your SQL in too much detail.

I would say that with about 20 joins, AND nested Selects, I wouldn't
expect instantaneous results.

Are all of the joined fields indexed? If they aren't, you could have trouble.

Secondly, how about starting with a smaller query and working your
way up to the final query?

Maybe that way you'll find that it bogs down when adding one of your
tables in particular.

Thirdly, have you tried it in the Query Analyzer? How's its performance
there?

Wayne

 

[pdx_man]

Just taking a quick glance at the SQL code is showing me that there is a lot of room for improvement.

Nested SELECT statements in a join can be a real killer. Don't do that. ie

LEFT JOIN(SELECT episode_id,
date_answer AS end_date
FROM episode_question_answers
WHERE question_id = 35508) AS manual2
  ON NEW_EPISODES.id = manual2.episode_id

Should be:

LEFT JOIN episode_question_answers manual2
  ON NEW_EPISODES.id = manual2.episode_id AND question_id = 35508

then just reference your alias field as the field name above in the field list

When you have some kind of complex logic in a nested join, create a table variable containing your recordset prior to your main logic. ie

LEFT OUTER JOIN (SELECT aa.ID,
 CASE
WHEN( Blind IS Not NULL
	Or partial IS Not NULL)
	And( deaf IS Not NULL
	Or hard IS Not NULL) THEN 'Dual' WHEN Blind IS Not NULL
	Or partial IS Not NULL THEN 'Visual Impairment' WHEN deaf IS Not NULL
	Or hard IS Not NULL THEN 'Hearing Impairment'END AS Impairment
FROM people AS aa
LEFT OUTER JOIN(SELECT ISNULL( dbo.REGISTER_CATEGORIES.DESCRIPTION,
'-') AS blind,
dbo.PERSON_REGISTRATIONS.START_DATE,
dbo.PERSON_REGISTRATIONS.END_DATE,
dbo.PERSON_REGISTRATIONS.PERSON_ID
FROM dbo.REGISTER_CATEGORIES
RIGHT OUTER JOIN dbo.PERSON_REGISTRATION_CATEGORIES
  ON( dbo.PERSON_REGISTRATION_CATEGORIES.CATEGORY = dbo.REGISTER_CATEGORIES.ID)
RIGHT OUTER JOIN dbo.PERSON_REGISTRATIONS
  ON( dbo.PERSON_REGISTRATIONS.ID = dbo.PERSON_REGISTRATION_CATEGORIES.REGISTRATION_ID
	And dbo.PERSON_REGISTRATIONS.REG_ID <> '1')
WHERE( ISNULL( dbo.REGISTER_CATEGORIES.DESCRIPTION,
'-') In ( 'Blind' ))) AS blind

Can be optimized by:

CREATE PROCEDURE dbo.blahblahblah AS

DECLARE @MyTable (MyID [i]TheDataType[/i],
                Impairment VARCHAR(20))

INSERT INTO @MyTable
SELECT aa.ID,
 CASE
WHEN( Blind IS Not NULL
	Or partial IS Not NULL)
	And( deaf IS Not NULL
	Or hard IS Not NULL) THEN 'Dual' WHEN Blind IS Not NULL
	Or partial IS Not NULL THEN 'Visual Impairment' WHEN deaf IS Not NULL
	Or hard IS Not NULL THEN 'Hearing Impairment' END AS Impairment
FROM people AS aa
LEFT OUTER JOIN(SELECT ISNULL( dbo.REGISTER_CATEGORIES.DESCRIPTION,
'-') AS blind,
dbo.PERSON_REGISTRATIONS.START_DATE,
dbo.PERSON_REGISTRATIONS.END_DATE,
dbo.PERSON_REGISTRATIONS.PERSON_ID
FROM dbo.REGISTER_CATEGORIES
RIGHT OUTER JOIN dbo.PERSON_REGISTRATION_CATEGORIES
  ON( dbo.PERSON_REGISTRATION_CATEGORIES.CATEGORY = dbo.REGISTER_CATEGORIES.ID)
RIGHT OUTER JOIN dbo.PERSON_REGISTRATIONS
  ON( dbo.PERSON_REGISTRATIONS.ID = dbo.PERSON_REGISTRATION_CATEGORIES.REGISTRATION_ID
	And dbo.PERSON_REGISTRATIONS.REG_ID <> '1')
WHERE( ISNULL( dbo.REGISTER_CATEGORIES.DESCRIPTION,
'-') In ( 'Blind' ))

-- The start of where your code begins

SELECT dbo.PEOPLE.ID AS Person_ID,
( dbo.PERSON_NAMES.FIRST_NAMES)+' '+( dbo.PERSON_NAMES.LAST_NAME) AS Full_Name,
Prev_EPISODE_TYPES.DESCRIPTION AS Ep1_Desc,
:
:
LEFT JOIN(SELECT episode_id,
date_answer AS end_date
FROM episode_question_answers
WHERE question_id = 35508) AS manual2
  ON NEW_EPISODES.id = manual2.episode_id
LEFT OUTER JOIN [b]@MyTable[/b] AS blind
  ON blind.person_id = aa.id
LEFT OUTER JOIN(SELECT ISNULL( dbo.REGISTER_CATEGORIES.DESCRIPTION,
'-') AS partial,
:
:

Not the mention it makes your code a whole lot easier to read and debug.

 

[Ethos]

Gratis

Hi

I'm so gratefull for the free lesson in SQL but more so for the time and effort you spent helping. You are obviously wise and caring people, thank you all very much.



Ethos