Optimization Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Optimization Help

Author

Topic

miggie_ra
Starting Member

8 Posts

Posted - 2007-06-19 : 10:26:25

Hi all,

I have this query that I am trying to optimize. Maybe somebody could help me design or redesign my existing query. From the execution plan below it is showing many clustered index scan. I am wondering if somebody can help me replace clustered index scan with clustered index seek.

Thanks in advance.

I have the following tables below and its order of links

SENSections
->SENTargets
-->SENSubTargets
--->SENPhrases

SENIEPPhrases

I need to

--tables
CREATE TABLE [dbo].[SENPhrases](
[SectionID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TargetID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SubTargetID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PhraseID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PhraseDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastUpdatedBy] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastUpdated] [datetime] NOT NULL,
CONSTRAINT [PK_SENPhrases] PRIMARY KEY CLUSTERED
(
[SectionID] ASC,
[TargetID] ASC,
[SubTargetID] ASC,
[PhraseID] ASC
)
)

-------------
CREATE TABLE [dbo].[SENTargets](
[SectionID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TargetID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastUpdatedBy] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastUpdated] [datetime] NOT NULL,
CONSTRAINT [PK_SENTargets] PRIMARY KEY CLUSTERED
(
[SectionID] ASC,
[TargetID] ASC
)
)
-----------------------
CREATE TABLE [dbo].[SENSubTargets](
[SectionID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TargetID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SubTargetID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastUpdatedBy] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastUpdated] [datetime] NOT NULL,
CONSTRAINT [PK_SENSubTargets] PRIMARY KEY CLUSTERED
(
[SectionID] ASC,
[TargetID] ASC,
[SubTargetID] ASC
)
)

------------------
CREATE TABLE [dbo].[SENSections](
[SectionID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastUpdatedBy] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastUpdated] [datetime] NOT NULL,
[UseDefaultPhrases] [bit] NOT NULL CONSTRAINT [DEF_UseDefaultPhrases] DEFAULT ((1)),
CONSTRAINT [PK_SENSections] PRIMARY KEY CLUSTERED
(
[SectionID] ASC
)
)

--------------------
CREATE TABLE [dbo].[SENIEPSection](
[SchoolID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PupilID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IEPID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LearningDifficultyID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IEPSectionID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IEPTargetID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IEPSubTargetID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IEPPhraseTitleID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PhraseDescription] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastUpdatedBy] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastUpdated] [datetime] NOT NULL,
CONSTRAINT [PK_SENIEPSection] PRIMARY KEY CLUSTERED
(
[SchoolID] ASC,
[PupilID] ASC,
[IEPID] ASC,
[LearningDifficultyID] ASC,
[IEPSectionID] ASC,
[IEPTargetID] ASC,
[IEPSubTargetID] ASC,
[IEPPhraseTitleID] ASC
)
)

--sp
DECLARE
@pupilID VARCHAR(10)
,@IEPID VARCHAR(20)
,@SENPhraseTitle INT

SET @pupilID = '1740'
SET @IEPID = ''
SET @SENPhraseTitle = 6000

SELECT
--seniepsection info
SENSections.SchoolID
, SENSections.PupilID
, SENSections.IEPID
--section info
, SENSections.SectionID
, SENSections.Description AS SectionDescription
, SENSections.SectionSelected
--target info
, SENTargets.TargetID
, SENTargets.Description AS TargetDescription
, SENTargets.TargetSelected
--subtarget info
, SENSubTargets.SubTargetID
, SENSubTargets.Description AS SubTargetDescription
, SENSubTargets.SubTargetSelected
--phrases info
, SENPhrases.PhraseID, SENPhrases.PhraseDescription
FROM
(
SELECT
A.SectionID
, A.Description
, SchoolID = ISNULL(IEP.SchoolID, '')
, PupilID = ISNULL(IEP.PupilID, '')
, IEPID = ISNULL(IEP.IEPID, '')
, SectionSelected = CASE WHEN IEP.IEPSectionID IS NULL THEN 0 ELSE 1 END
FROM SENSections A
LEFT JOIN SENIEPSection IEP
ON IEP.IEPSectionID = A.SectionID
AND
IEP.PupilID = @pupilID
AND
IEP.IEPID = @IEPID
)
SENSections
--
INNER JOIN (
SELECT
A.SectionID
, A.TargetID
, A.Description
, TargetSelected = CASE WHEN IEP.IEPTargetID IS NULL THEN 0 ELSE 1 END
FROM SENTargets A
LEFT JOIN SENIEPSection IEP
ON IEP.IEPSectionID = A.SectionID
AND
IEP.IEPTargetID = A.TargetID
AND
IEP.PupilID = @pupilID
AND
IEP.IEPID = @IEPID
)
SENTargets
ON SENSections.SectionID = SENTargets.SectionID
INNER JOIN (
SELECT
A.SectionID
, A.TargetID
, A.SubTargetID
, A.Description
, SubTargetSelected = CASE WHEN IEP.IEPSubTargetID IS NULL THEN 0 ELSE 1 END
FROM SENSubTargets A
LEFT JOIN SENIEPSection IEP
ON IEP.IEPSectionID = A.SectionID AND
IEP.IEPTargetID = A.TargetID AND
IEP.IEPSubTargetID = A.SubTargetID
WHERE
IEP.PupilID = @pupilID AND
IEP.IEPID = @IEPID
)

SENSubTargets
ON SENTargets.SectionID = SENSubTargets.SectionID AND
SENTargets.TargetID = SENSubTargets.TargetID
INNER JOIN SENPhrases
ON SENSubTargets.SectionID = SENPhrases.SectionID AND
SENSubTargets.TargetID = SENPhrases.TargetID AND
SENSubTargets.SubTargetID = SENPhrases.SubTargetID

---SHOWPLAN TEXT

|--Compute Scalar(DEFINE:([Expr1004]=isnull([HdnDevelopment].[dbo].[SENIEPSection].[SchoolID] as [IEP].[SchoolID],''), [Expr1005]=isnull([HdnDevelopment].[dbo].[SENIEPSection].[PupilID] as [IEP].[PupilID],''), [Expr1006]=isnull([HdnDevelopment].[dbo].[SENIEPSection].[IEPID] as [IEP].[IEPID],''), [Expr1007]=CASE WHEN [HdnDevelopment].[dbo].[SENIEPSection].[IEPSectionID] as [IEP].[IEPSectionID] IS NULL THEN (0) ELSE (1) END))
|--Nested Loops(Left Outer Join, WHERE:([HdnDevelopment].[dbo].[SENIEPSection].[IEPSectionID] as [IEP].[IEPSectionID]=[HdnDevelopment].[dbo].[SENSections].[SectionID] as [A].[SectionID]))
|--Compute Scalar(DEFINE:([Expr1017]=CASE WHEN [HdnDevelopment].[dbo].[SENIEPSection].[IEPSubTargetID] as [IEP].[IEPSubTargetID] IS NULL THEN (0) ELSE (1) END))
| |--Nested Loops(Left Outer Join, WHERE:([HdnDevelopment].[dbo].[SENIEPSection].[IEPSectionID] as [IEP].[IEPSectionID]=[HdnDevelopment].[dbo].[SENSubTargets].[SectionID] as [A].[SectionID] AND [HdnDevelopment].[dbo].[SENIEPSection].[IEPTargetID] as [IEP].[IEPTargetID]=[HdnDevelopment].[dbo].[SENSubTargets].[TargetID] as [A].[TargetID] AND [HdnDevelopment].[dbo].[SENIEPSection].[IEPSubTargetID] as [IEP].[IEPSubTargetID]=[HdnDevelopment].[dbo].[SENSubTargets].[SubTargetID] as [A].[SubTargetID]))
| |--Compute Scalar(DEFINE:([Expr1012]=CASE WHEN [HdnDevelopment].[dbo].[SENIEPSection].[IEPTargetID] as [IEP].[IEPTargetID] IS NULL THEN (0) ELSE (1) END))
| | |--Nested Loops(Left Outer Join, WHERE:([HdnDevelopment].[dbo].[SENIEPSection].[IEPSectionID] as [IEP].[IEPSectionID]=[HdnDevelopment].[dbo].[SENTargets].[SectionID] as [A].[SectionID] AND [HdnDevelopment].[dbo].[SENIEPSection].[IEPTargetID] as [IEP].[IEPTargetID]=[HdnDevelopment].[dbo].[SENTargets].[TargetID] as [A].[TargetID]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([HdnDevelopment].[dbo].[SENPhrases].[SectionID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([HdnDevelopment].[dbo].[SENPhrases].[SectionID], [HdnDevelopment].[dbo].[SENPhrases].[TargetID]))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([A].[SectionID], [A].[TargetID], [A].[SubTargetID]))
| | | | | |--Clustered Index Scan(OBJECT:([HdnDevelopment].[dbo].[SENSubTargets].[PK_SENSubTargets] AS [A]))
| | | | | |--Clustered Index Seek(OBJECT:([HdnDevelopment].[dbo].[SENPhrases].[PK_SENPhrases]), SEEK:([HdnDevelopment].[dbo].[SENPhrases].[SectionID]=[HdnDevelopment].[dbo].[SENSubTargets].[SectionID] as [A].[SectionID] AND [HdnDevelopment].[dbo].[SENPhrases].[TargetID]=[HdnDevelopment].[dbo].[SENSubTargets].[TargetID] as [A].[TargetID] AND [HdnDevelopment].[dbo].[SENPhrases].[SubTargetID]=[HdnDevelopment].[dbo].[SENSubTargets].[SubTargetID] as [A].[SubTargetID]) ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT:([HdnDevelopment].[dbo].[SENTargets].[PK_SENTargets] AS [A]), SEEK:([A].[SectionID]=[HdnDevelopment].[dbo].[SENPhrases].[SectionID] AND [A].[TargetID]=[HdnDevelopment].[dbo].[SENPhrases].[TargetID]) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([HdnDevelopment].[dbo].[SENSections].[PK_SENSections] AS [A]), SEEK:([A].[SectionID]=[HdnDevelopment].[dbo].[SENPhrases].[SectionID]) ORDERED FORWARD)
| | |--Clustered Index Scan(OBJECT:([HdnDevelopment].[dbo].[SENIEPSection].[PK_SENIEPSection] AS [IEP]), WHERE:([HdnDevelopment].[dbo].[SENIEPSection].[PupilID] as [IEP].[PupilID]=[@pupilID] AND [HdnDevelopment].[dbo].[SENIEPSection].[IEPID] as [IEP].[IEPID]=[@IEPID]))
| |--Clustered Index Scan(OBJECT:([HdnDevelopment].[dbo].[SENIEPSection].[PK_SENIEPSection] AS [IEP]), WHERE:([HdnDevelopment].[dbo].[SENIEPSection].[PupilID] as [IEP].[PupilID]=[@pupilID] AND [HdnDevelopment].[dbo].[SENIEPSection].[IEPID] as [IEP].[IEPID]=[@IEPID]))
|--Clustered Index Scan(OBJECT:([HdnDevelopment].[dbo].[SENIEPSection].[PK_SENIEPSection] AS [IEP]), WHERE:([HdnDevelopment].[dbo].[SENIEPSection].[PupilID] as [IEP].[PupilID]=[@pupilID] AND [HdnDevelopment].[dbo].[SENIEPSection].[IEPID] as [IEP].[IEPID]=[@IEPID]))

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 10:49:49

Do you really need all those columns in your PK on SENIEPSection? Just choose key(s) that are unique, don't add extra columns beyond that, and choose key(s) that will, 99.999% of the time, NOT change.

LEFT JOIN SENIEPSection AS IEP
ON IEP.IEPSectionID = A.SectionID
AND IEP.PupilID = @pupilID
AND IEP.IEPID = @IEPID

You PK for SENIEPSection is:

n [SchoolID] ASC,
Y [PupilID] ASC,
Y [IEPID] ASC,
n [LearningDifficultyID] ASC,
Y [IEPSectionID] ASC,
n [IEPTargetID] ASC,
n [IEPSubTargetID] ASC,
n [IEPPhraseTitleID] ASC

The "Y" are columns included in your Join, "n" is not provided.

All the "significant" columns need to be provided for the index to be used. As you have not provided SchoolID the index [usually] cannot be used.

You can add an Index that has the columns relevant to your Query. Put the most commonly used columns first, and for those that are as-often used then put the most "selective" columns first (Columns which are "more" Selective have more distinct values. So a column that has very few distinct values is Not very selective.

In case it helps anyone else, here is the formatted original:


--tables
CREATE TABLE [dbo].[SENPhrases](
	[SectionID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[TargetID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SubTargetID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PhraseID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PhraseDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LastUpdatedBy] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LastUpdated] [datetime] NOT NULL,
 CONSTRAINT [PK_SENPhrases] PRIMARY KEY CLUSTERED 
(
	[SectionID] ASC,
	[TargetID] ASC,
	[SubTargetID] ASC,
	[PhraseID] ASC
)
) 

-------------
CREATE TABLE [dbo].[SENTargets](
	[SectionID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[TargetID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Description] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LastUpdatedBy] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LastUpdated] [datetime] NOT NULL,
 CONSTRAINT [PK_SENTargets] PRIMARY KEY CLUSTERED 
(
	[SectionID] ASC,
	[TargetID] ASC
)
) 
-----------------------
CREATE TABLE [dbo].[SENSubTargets](
	[SectionID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[TargetID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SubTargetID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Description] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LastUpdatedBy] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LastUpdated] [datetime] NOT NULL,
 CONSTRAINT [PK_SENSubTargets] PRIMARY KEY CLUSTERED 
(
	[SectionID] ASC,
	[TargetID] ASC,
	[SubTargetID] ASC
)
) 

------------------
CREATE TABLE [dbo].[SENSections](
	[SectionID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Description] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LastUpdatedBy] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LastUpdated] [datetime] NOT NULL,
	[UseDefaultPhrases] [bit] NOT NULL CONSTRAINT [DEF_UseDefaultPhrases]  DEFAULT ((1)),
 CONSTRAINT [PK_SENSections] PRIMARY KEY CLUSTERED 
(
	[SectionID] ASC
)
) 


--------------------
CREATE TABLE [dbo].[SENIEPSection](
	[SchoolID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PupilID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[IEPID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LearningDifficultyID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[IEPSectionID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[IEPTargetID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[IEPSubTargetID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[IEPPhraseTitleID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PhraseDescription] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LastUpdatedBy] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LastUpdated] [datetime] NOT NULL,
 CONSTRAINT [PK_SENIEPSection] PRIMARY KEY CLUSTERED 
(
	[SchoolID] ASC,
	[PupilID] ASC,
	[IEPID] ASC,
	[LearningDifficultyID] ASC,
	[IEPSectionID] ASC,
	[IEPTargetID] ASC,
	[IEPSubTargetID] ASC,
	[IEPPhraseTitleID] ASC
)
) 


ON IEP.IEPSectionID = A.SectionID	
AND IEP.PupilID = @pupilID	
AND IEP.IEPID = @IEPID

--sp
DECLARE	
	 @pupilID VARCHAR(10)
	,@IEPID VARCHAR(20)
	,@SENPhraseTitle INT


SET @pupilID = '1740'
SET @IEPID = ''
SET @SENPhraseTitle = 6000

SELECT    
	--seniepsection info
		SENSections.SchoolID
	,	SENSections.PupilID
	,	SENSections.IEPID
	--section info 
	,	SENSections.SectionID 
	,	SENSections.Description AS SectionDescription
	,	SENSections.SectionSelected
	--target info
	,	SENTargets.TargetID
	,	SENTargets.Description AS TargetDescription
	,	SENTargets.TargetSelected
	--subtarget info			
	,	SENSubTargets.SubTargetID
	,	SENSubTargets.Description AS SubTargetDescription
	,	SENSubTargets.SubTargetSelected
	--phrases info
	,	SENPhrases.PhraseID, SENPhrases.PhraseDescription
FROM         
	(
	SELECT 
			A.SectionID
		,	A.Description
		,	SchoolID = ISNULL(IEP.SchoolID, '')
		,	PupilID = ISNULL(IEP.PupilID, '')
		,	IEPID = ISNULL(IEP.IEPID, '')
		,	SectionSelected = CASE WHEN IEP.IEPSectionID IS NULL THEN 0 ELSE 1 END
	FROM	SENSections A
		LEFT JOIN	SENIEPSection IEP
		ON	IEP.IEPSectionID = A.SectionID	
			AND
			IEP.PupilID = @pupilID	
			AND
			IEP.IEPID = @IEPID
	) 
	SENSections 						
	--
	INNER JOIN	(
			SELECT 
					A.SectionID
				,	A.TargetID
				,	A.Description
				,	TargetSelected = CASE WHEN IEP.IEPTargetID IS NULL THEN 0 
								ELSE 1 END
			FROM	SENTargets A
				LEFT JOIN	SENIEPSection IEP
				ON	IEP.IEPSectionID = A.SectionID	
					AND
					IEP.IEPTargetID = A.TargetID	
					AND
					IEP.PupilID = @pupilID	
					AND
					IEP.IEPID = @IEPID
			) 
		SENTargets 						
		ON		SENSections.SectionID = SENTargets.SectionID 
	INNER JOIN	(
			SELECT 
					A.SectionID
				,	A.TargetID
				,	A.SubTargetID
				,	A.Description
				,	SubTargetSelected = CASE WHEN IEP.IEPSubTargetID IS NULL THEN 0 
								ELSE 1 END
			FROM	SENSubTargets A
				LEFT JOIN	SENIEPSection IEP
				ON	IEP.IEPSectionID = A.SectionID	AND
					IEP.IEPTargetID = A.TargetID	AND
					IEP.IEPSubTargetID = A.SubTargetID
			WHERE
				IEP.PupilID = @pupilID	AND
				IEP.IEPID = @IEPID
			)

		SENSubTargets 
		ON	SENTargets.SectionID = SENSubTargets.SectionID AND 
			SENTargets.TargetID = SENSubTargets.TargetID				
	INNER JOIN	SENPhrases 
		ON	SENSubTargets.SectionID = SENPhrases.SectionID AND 
			SENSubTargets.TargetID = SENPhrases.TargetID AND 
			SENSubTargets.SubTargetID = SENPhrases.SubTargetID

Kristen

miggie_ra
Starting Member

8 Posts

Posted - 2007-06-19 : 11:53:12

Hi Kristen,

Thanks for the reply and formatting...

[SchoolID] ASC,
[PupilID] ASC,
[IEPID] ASC,
[LearningDifficultyID] ASC,
[IEPSectionID] ASC,
[IEPTargetID] ASC,
[IEPSubTargetID] ASC,
[IEPPhraseTitleID] ASC

combination of this columns actually makes the record unique,
I need to link that with SENSections, SENTargets, SENSubTargets...
but you are right I dont need the other columns to be there.

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 12:28:06

"actually makes the record unique"

So, just a test to confirm:

Is it possible to have TWO records with the same [SchoolID], [PupilID], [IEPID] and DIFFERENT values for [LearningDifficultyID]? (just as an example, but I'm sure you get the idea).

I'm obviously guessing somewhat because I don't know your subject/data!

Kristen

miggie_ra
Starting Member

8 Posts

Posted - 2007-06-20 : 09:04:26

Hi Kristen,
Yes the values for the combination of these fields will be the same
[SchoolID]
[PupilID]
[IEPID]
[LearningDifficultyID]
[IEPSectionID]
[IEPTargetID]
[IEPSubTargetID]
except for the [IEPPhraseTitleID] which will be different on each row.

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 10:06:25

So for a given value of

[SchoolID]
[PupilID]
[IEPID]
[LearningDifficultyID]
[IEPSectionID]
[IEPTargetID]
[IEPSubTargetID]

there can be multiple rows, each with a different value of [IEPPhraseTitleID].

OK, I'm happy with that.

But for a SINGLE set of values for

[SchoolID]
[PupilID]
[IEPID]

is it possible to have more multiple rows with different values for

[LearningDifficultyID]

It strikes me that for a given Pupil, at a Given School, that that pupil will have only one LearningDifficultyID. Or if they have multiple values for that then they will not have multiple values, per LearningDifficultyID, for IEPSectionID.

As I said I don't know your data, so I could be wrong, but I'm just trying to check that you do actually need all these columns in the PK.

Kristen

miggie_ra
Starting Member

8 Posts

Posted - 2007-06-20 : 11:07:56

Hi Kristen,

I actually just made changes to the SENIEPSection. I have taken out [LearningDifficultyID]. This in not actually required here in this table an is not actually being used. (this tables are all existing tables from the existing application).

[SchoolID]
[PupilID]
[IEPID]
[IEPSectionID]
[IEPTargetID]
[IEPSubTargetID]
[IEPPhraseTitleID]

SENSections
->SENTargets
-->SENSubTargets
--->SENPhrases
In the application these tables shows their data in the form of treeview control. So as you would imagine the relation, SENSections is the root that links with child SENTargets. and SENTargets links with its child SENSubtargets with their common fields and SENPhrases (list of SENPhrases values which are displayed in the the gridview for selection) links with its parent SENSubtargets.

Now for each row SENPhrases selected is saved in SENIEPSection only the IEPPhraseTitleID can be unique.

FIELDS CAN HAVE MULTIPLE ROWS
[SchoolID] Y
[PupilID] Y
[IEPID] Y
[IEPSectionID] Y
[IEPTargetID] Y
[IEPSubTargetID] Y
[IEPPhraseTitleID] N

I know I should have normalized table that have minimum column like [IEPPhraseTitleID] plus rest of the columns that are not IDs but as I've said these are already existing query that I am trying to optimize. I hope my explanation makes it clear for what I am trying to do.

Thanks very much.

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 11:50:14

OK, so far so good!

To improve performance my point earlier (06/19/2007 : 10:49:49) about the fact that the other columns in the PK are not being used in the JOINs in your query (such as SchoolID) needs addressing

Kristen

miggie_ra
Starting Member

8 Posts

Posted - 2007-06-20 : 12:05:42

Sorry, I have not mention that the tables are also accepting rows of different SchoolIDs because the application is setup mutliple schools and the data are centralized. In that case if I remove SchoolID it may affect the data.

miggie_ra
Starting Member

8 Posts

Posted - 2007-06-20 : 12:09:01

Additionaly SchoolID and PupilID in this table are also included in the WHERE clause in other stored procedure that uses this table.

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 12:51:13

I obviously did not describe this in a way that you could understand in my earlier post (06/19/2007 : 10:49:49)

You are JOINing to SENIEPSection and only providing values for

PupilID
IEPID
IEPSectionID

but your PK on SENIEPSection

is

[SchoolID]
[PupilID]
[IEPID]
[LearningDifficultyID]
[IEPSectionID]
[IEPTargetID]
[IEPSubTargetID]
[IEPPhraseTitleID]

Because your JOIN does not provide [SchoolID] the PK index cannot be used.

Either provide [SchoolID] in the JOIN, or if that is not appropriate create an Index on just PupilID, IEPID, IEPSectionID. Or if it is safe to do so change the order of the Keys in the PK so that PupilID, IEPID, IEPSectionID (or at least PupilID & IEPID) are first.

Kristen

miggie_ra
Starting Member

8 Posts

Posted - 2007-06-26 : 10:19:35

Hi Kristen,

Your suggestions made a difference in my query...thanks very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources