Multiple Table Joins Makes Query Go To Sleep

[Ed_No_Spam_Please_Weber]

Hello All & Thanks in advance for your help!

Background:

1) tblT_Documents is the primary parent transaction table that has 10
fields and about 250,000 rows
2) There are 9 child tables with each having 3 fields each, their own
PK; the FK back to the parent table; and the unique data for that
table. There is a one to many relation between the parent and each of
the 9 child rows. Each child table has between 100,000 and 300,000
rows.
3) There are indexes on every field of the child tables (though I
don't believe that they are helping in this situation)
4) The client needs to be presented a view that has 5 of the main
fields from the parent table, along with any and all corresponding
data from the child tables.
5) The client will select this view by doing some pattern-matching
search on one of the child records' detail (e.g. field-name LIKE
%search-item% - so much for the indexes...)

Problem:

When I do the simple join of just the parent with one of the children,
the search works *fairly* well and returns the five parent fields and
the corresponding matching child field.

However, as soon as I add any one of the other child records to simply
display it's unique data along with the previously obtained results,
the resulting query hangs.

Is the overall structure of the tables not conducive to this kind of
query? Is this a situation where de-normalization will be required to
obtain the desired results? Or, more hopefully, am I just an idiot
and there is some simpler solution to this problem?!

Thanks again for your assistance!

- Ed

[Lee Tudor]

Lets have the full table schema (including indexes and a few sample records)
of all your tables and a copy of the query(s) you are running.
http://www.aspfaq.com/etiquette.asp?id=5006

We'll take a look and see whats going on.

Mr Tea

<Ed_No_Spam_Ple ase_Weber@Weber _Spam_Not_Enter prises.Org> wrote in message
news:5j******** *************** *********@4ax.c om...

Hello All & Thanks in advance for your help!

Background:

1) tblT_Documents is the primary parent transaction table that has 10
fields and about 250,000 rows
2) There are 9 child tables with each having 3 fields each, their own
PK; the FK back to the parent table; and the unique data for that
table. There is a one to many relation between the parent and each of
the 9 child rows. Each child table has between 100,000 and 300,000
rows.
3) There are indexes on every field of the child tables (though I
don't believe that they are helping in this situation)
4) The client needs to be presented a view that has 5 of the main
fields from the parent table, along with any and all corresponding
data from the child tables.
5) The client will select this view by doing some pattern-matching
search on one of the child records' detail (e.g. field-name LIKE
%search-item% - so much for the indexes...)

Problem:

When I do the simple join of just the parent with one of the children,
the search works *fairly* well and returns the five parent fields and
the corresponding matching child field.

However, as soon as I add any one of the other child records to simply
display it's unique data along with the previously obtained results,
the resulting query hangs.

Is the overall structure of the tables not conducive to this kind of
query? Is this a situation where de-normalization will be required to
obtain the desired results? Or, more hopefully, am I just an idiot
and there is some simpler solution to this problem?!

Thanks again for your assistance!

- Ed

[Erland Sommarskog]

(Ed_No_Spam_Ple ase_Weber@Weber _Spam_Not_Enter prises.Org) writes:

When I do the simple join of just the parent with one of the children,
the search works *fairly* well and returns the five parent fields and
the corresponding matching child field.

However, as soon as I add any one of the other child records to simply
display it's unique data along with the previously obtained results,
the resulting query hangs.

Is the overall structure of the tables not conducive to this kind of
query? Is this a situation where de-normalization will be required to
obtain the desired results? Or, more hopefully, am I just an idiot
and there is some simpler solution to this problem?!

I can only echo what Lee said. Please include CREATE TABLE and CREATE INDEX
statements for the involved tables, and the query you are having problem
with.

My guess from your description is that you simply have an error in
you query...
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ed_No_Spam_Please_Weber]

Hello All!

Per request, the following is the generate script for 4 tables; the
query that works 'OK' and the query that doesn't work.

<-------------- Table Defs Begin -------------------->

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Amounts_FK 00]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblT_Amounts] DROP CONSTRAINT tblT_Amounts_FK 00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_AssocBP_FK 00]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblT_AssocBP] DROP CONSTRAINT tblT_AssocBP_FK 00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_BlockBld_F K00]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblT_BlockBld] DROP CONSTRAINT tblT_BlockBld_F K00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Grantees_F K00]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblT_Grantees] DROP CONSTRAINT tblT_Grantees_F K00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Grantors_F K00]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblT_Grantors] DROP CONSTRAINT tblT_Grantors_F K00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_LotUnit_FK 00]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblT_LotUnit] DROP CONSTRAINT tblT_LotUnit_FK 00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Subdivisio n_FK00]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblT_Subdivisio n] DROP CONSTRAINT
tblT_Subdivisio n_FK00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_TaxIDs_FK0 0]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblT_TaxIDs] DROP CONSTRAINT tblT_TaxIDs_FK0 0
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Documents]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[tblT_Documents]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Grantees]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[tblT_Grantees]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Grantors]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[tblT_Grantors]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Subdivisio n]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[tblT_Subdivisio n]
GO

CREATE TABLE [dbo].[tblT_Documents] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FileNo] [nvarchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[DocType] [nvarchar] (5) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NOT NULL ,
[YorDNo] [nvarchar] (8) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[DocDt] [datetime] NULL ,
[Book] [nvarchar] (5) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[Page] [int] NULL ,
[ARB] [nvarchar] (7) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[Comments] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[AudCreateWho] [nvarchar] (10) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[AudCreateWhen] [datetime] NULL ,
[AudUpdateWho] [nvarchar] (10) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[AudUpdateWhen] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblT_Grantees] (
[GranteeID] [int] IDENTITY (1, 1) NOT NULL ,
[MainID] [int] NULL ,
[GRANTEE] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblT_Grantors] (
[GrantorID] [int] IDENTITY (1, 1) NOT NULL ,
[MainID] [int] NULL ,
[GRANTOR] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblT_Subdivisio n] (
[SubdivID] [int] IDENTITY (1, 1) NOT NULL ,
[MainID] [int] NULL ,
[Subdivision] [nvarchar] (50) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Documents] WITH NOCHECK ADD
CONSTRAINT [PK_tblT_Documen ts] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Grantees] WITH NOCHECK ADD
CONSTRAINT [PK_tblT_Grantee s] PRIMARY KEY CLUSTERED
(
[GranteeID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Grantors] WITH NOCHECK ADD
CONSTRAINT [PK_tblT_Grantor s] PRIMARY KEY CLUSTERED
(
[GrantorID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Documents] ADD
CONSTRAINT [DF__tblT_Docu__ DocDt__1367E606] DEFAULT
(convert(dateti me,convert(varc har,getdate(),1 ),1)) FOR [DocDt],
CONSTRAINT [DF__tblT_Docum_ _Page__145C0A3F] DEFAULT (0) FOR
[Page],
CONSTRAINT [DF__tblT_Docu__ AudCr__15502E78] DEFAULT
(getdate()) FOR [AudCreateWhen],
CONSTRAINT [DF__tblT_Docu__ AudUp__164452B1] DEFAULT
(getdate()) FOR [AudUpdateWhen],
CONSTRAINT [CK tblT_Documents DocDt] CHECK ([DocDt] >=
'1/1/1837' and [DocDt] <=
convert(datetim e,convert(varch ar,getdate(),1) ,1))
GO

CREATE INDEX [IX_FileNo] ON [dbo].[tblT_Documents]([FileNo]) ON
[PRIMARY]
GO

CREATE INDEX [IX_DocType] ON [dbo].[tblT_Documents]([DocType]) ON
[PRIMARY]
GO

CREATE INDEX [IX_DocDt] ON [dbo].[tblT_Documents]([DocDt]) ON
[PRIMARY]
GO

CREATE INDEX [IX_Book] ON [dbo].[tblT_Documents]([Book]) ON
[PRIMARY]
GO

CREATE INDEX [IX_Page] ON [dbo].[tblT_Documents]([Page]) ON
[PRIMARY]
GO

CREATE UNIQUE INDEX [ID] ON [dbo].[tblT_Documents]([ID]) ON
[PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Grantees] ADD
CONSTRAINT [DF__tblT_Gran__ MainI__0EA330E9] DEFAULT (0) FOR
[MainID]
GO

CREATE INDEX [IX_MainID] ON [dbo].[tblT_Grantees]([MainID]) ON
[PRIMARY]
GO

CREATE INDEX [IX_Grantee] ON [dbo].[tblT_Grantees]([GRANTEE]) ON
[PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Grantors] ADD
CONSTRAINT [DF__tblT_Gran__ MainI__09DE7BCC] DEFAULT (0) FOR
[MainID]
GO

CREATE INDEX [IX_MainID] ON [dbo].[tblT_Grantors]([MainID]) ON
[PRIMARY]
GO

CREATE INDEX [IX_Grantor] ON [dbo].[tblT_Grantors]([GRANTOR]) ON
[PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Subdivisio n] ADD
CONSTRAINT [DF__tblT_Subd__ MainI__7B905C75] DEFAULT (0) FOR
[MainID],
CONSTRAINT [aaaaatblT_Subdi vision_PK] PRIMARY KEY
NONCLUSTERED
(
[SubdivID]
) ON [PRIMARY]
GO

CREATE INDEX [{C3638ECB-1FC5-4012-BE3A-855E252E07B2}] ON
[dbo].[tblT_Subdivisio n]([MainID]) ON [PRIMARY]
GO

CREATE INDEX [GrantorID] ON [dbo].[tblT_Subdivisio n]([SubdivID]) ON
[PRIMARY]
GO

CREATE INDEX [MainID] ON [dbo].[tblT_Subdivisio n]([MainID]) ON
[PRIMARY]
GO

CREATE INDEX [Subdivision] ON
[dbo].[tblT_Subdivisio n]([Subdivision]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Documents] ADD
CONSTRAINT [tblT_Documents_ FK00] FOREIGN KEY
(
[DocType]
) REFERENCES [dbo].[tblM_DocTypes] (
[DocType]
)
GO

ALTER TABLE [dbo].[tblT_Grantees] ADD
CONSTRAINT [tblT_Grantees_F K00] FOREIGN KEY
(
[MainID]
) REFERENCES [dbo].[tblT_Documents] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[tblT_Grantors] ADD
CONSTRAINT [tblT_Grantors_F K00] FOREIGN KEY
(
[MainID]
) REFERENCES [dbo].[tblT_Documents] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[tblT_Subdivisio n] ADD
CONSTRAINT [tblT_Subdivisio n_FK00] FOREIGN KEY
(
[MainID]
) REFERENCES [dbo].[tblT_Documents] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO

<-------------- Table Defs End -------------------->

<-------------- Good Query Begins -------------------->

SELECT DISTINCT d.ID, d.DocType, d.DocDt, d.Book, d.Page, gro.GRANTOR
FROM tblT_Documents d LEFT OUTER JOIN
tblT_Grantors gro ON d.ID = gro.MainID
WHERE (gro.GRANTOR LIKE N'%pracht%')

<-------------- Good Query Ends -------------------->

<-------------- Failing Query Begins -------------------->

SELECT DISTINCT d.ID, d.DocType, d.DocDt, d.Book, d.Page, gro.GRANTOR,
gre.GRANTEE, s.Subdivision
FROM tblT_Documents d LEFT OUTER JOIN
tblT_Subdivisio n s ON d.ID = s.MainID LEFT OUTER
JOIN
tblT_Grantees gre ON d.ID = gre.MainID LEFT
OUTER JOIN
tblT_Grantors gro ON d.ID = gro.MainID
WHERE (gro.GRANTOR LIKE N'%pracht%')

<-------------- Failing Query Ends -------------------->

[Ed_No_Spam_Please_Weber]

Hello All!

Per request, the following is the generate script for 4 tables; the
query that works 'OK' and the query that doesn't work.

<-------------- Table Defs Begin -------------------->

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Amounts_FK 00]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblT_Amounts] DROP CONSTRAINT tblT_Amounts_FK 00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_AssocBP_FK 00]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblT_AssocBP] DROP CONSTRAINT tblT_AssocBP_FK 00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_BlockBld_F K00]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblT_BlockBld] DROP CONSTRAINT tblT_BlockBld_F K00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Grantees_F K00]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblT_Grantees] DROP CONSTRAINT tblT_Grantees_F K00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Grantors_F K00]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblT_Grantors] DROP CONSTRAINT tblT_Grantors_F K00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_LotUnit_FK 00]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblT_LotUnit] DROP CONSTRAINT tblT_LotUnit_FK 00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Subdivisio n_FK00]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblT_Subdivisio n] DROP CONSTRAINT
tblT_Subdivisio n_FK00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_TaxIDs_FK0 0]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblT_TaxIDs] DROP CONSTRAINT tblT_TaxIDs_FK0 0
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Documents]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[tblT_Documents]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Grantees]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[tblT_Grantees]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Grantors]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[tblT_Grantors]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Subdivisio n]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[tblT_Subdivisio n]
GO

CREATE TABLE [dbo].[tblT_Documents] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FileNo] [nvarchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[DocType] [nvarchar] (5) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NOT NULL ,
[YorDNo] [nvarchar] (8) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[DocDt] [datetime] NULL ,
[Book] [nvarchar] (5) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[Page] [int] NULL ,
[ARB] [nvarchar] (7) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[Comments] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[AudCreateWho] [nvarchar] (10) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[AudCreateWhen] [datetime] NULL ,
[AudUpdateWho] [nvarchar] (10) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[AudUpdateWhen] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblT_Grantees] (
[GranteeID] [int] IDENTITY (1, 1) NOT NULL ,
[MainID] [int] NULL ,
[GRANTEE] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblT_Grantors] (
[GrantorID] [int] IDENTITY (1, 1) NOT NULL ,
[MainID] [int] NULL ,
[GRANTOR] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblT_Subdivisio n] (
[SubdivID] [int] IDENTITY (1, 1) NOT NULL ,
[MainID] [int] NULL ,
[Subdivision] [nvarchar] (50) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Documents] WITH NOCHECK ADD
CONSTRAINT [PK_tblT_Documen ts] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Grantees] WITH NOCHECK ADD
CONSTRAINT [PK_tblT_Grantee s] PRIMARY KEY CLUSTERED
(
[GranteeID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Grantors] WITH NOCHECK ADD
CONSTRAINT [PK_tblT_Grantor s] PRIMARY KEY CLUSTERED
(
[GrantorID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Documents] ADD
CONSTRAINT [DF__tblT_Docu__ DocDt__1367E606] DEFAULT
(convert(dateti me,convert(varc har,getdate(),1 ),1)) FOR [DocDt],
CONSTRAINT [DF__tblT_Docum_ _Page__145C0A3F] DEFAULT (0) FOR
[Page],
CONSTRAINT [DF__tblT_Docu__ AudCr__15502E78] DEFAULT
(getdate()) FOR [AudCreateWhen],
CONSTRAINT [DF__tblT_Docu__ AudUp__164452B1] DEFAULT
(getdate()) FOR [AudUpdateWhen],
CONSTRAINT [CK tblT_Documents DocDt] CHECK ([DocDt] >=
'1/1/1837' and [DocDt] <=
convert(datetim e,convert(varch ar,getdate(),1) ,1))
GO

CREATE INDEX [IX_FileNo] ON [dbo].[tblT_Documents]([FileNo]) ON
[PRIMARY]
GO

CREATE INDEX [IX_DocType] ON [dbo].[tblT_Documents]([DocType]) ON
[PRIMARY]
GO

CREATE INDEX [IX_DocDt] ON [dbo].[tblT_Documents]([DocDt]) ON
[PRIMARY]
GO

CREATE INDEX [IX_Book] ON [dbo].[tblT_Documents]([Book]) ON
[PRIMARY]
GO

CREATE INDEX [IX_Page] ON [dbo].[tblT_Documents]([Page]) ON
[PRIMARY]
GO

CREATE UNIQUE INDEX [ID] ON [dbo].[tblT_Documents]([ID]) ON
[PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Grantees] ADD
CONSTRAINT [DF__tblT_Gran__ MainI__0EA330E9] DEFAULT (0) FOR
[MainID]
GO

CREATE INDEX [IX_MainID] ON [dbo].[tblT_Grantees]([MainID]) ON
[PRIMARY]
GO

CREATE INDEX [IX_Grantee] ON [dbo].[tblT_Grantees]([GRANTEE]) ON
[PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Grantors] ADD
CONSTRAINT [DF__tblT_Gran__ MainI__09DE7BCC] DEFAULT (0) FOR
[MainID]
GO

CREATE INDEX [IX_MainID] ON [dbo].[tblT_Grantors]([MainID]) ON
[PRIMARY]
GO

CREATE INDEX [IX_Grantor] ON [dbo].[tblT_Grantors]([GRANTOR]) ON
[PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Subdivisio n] ADD
CONSTRAINT [DF__tblT_Subd__ MainI__7B905C75] DEFAULT (0) FOR
[MainID],
CONSTRAINT [aaaaatblT_Subdi vision_PK] PRIMARY KEY
NONCLUSTERED
(
[SubdivID]
) ON [PRIMARY]
GO

CREATE INDEX [{C3638ECB-1FC5-4012-BE3A-855E252E07B2}] ON
[dbo].[tblT_Subdivisio n]([MainID]) ON [PRIMARY]
GO

CREATE INDEX [GrantorID] ON [dbo].[tblT_Subdivisio n]([SubdivID]) ON
[PRIMARY]
GO

CREATE INDEX [MainID] ON [dbo].[tblT_Subdivisio n]([MainID]) ON
[PRIMARY]
GO

CREATE INDEX [Subdivision] ON
[dbo].[tblT_Subdivisio n]([Subdivision]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Documents] ADD
CONSTRAINT [tblT_Documents_ FK00] FOREIGN KEY
(
[DocType]
) REFERENCES [dbo].[tblM_DocTypes] (
[DocType]
)
GO

ALTER TABLE [dbo].[tblT_Grantees] ADD
CONSTRAINT [tblT_Grantees_F K00] FOREIGN KEY
(
[MainID]
) REFERENCES [dbo].[tblT_Documents] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[tblT_Grantors] ADD
CONSTRAINT [tblT_Grantors_F K00] FOREIGN KEY
(
[MainID]
) REFERENCES [dbo].[tblT_Documents] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[tblT_Subdivisio n] ADD
CONSTRAINT [tblT_Subdivisio n_FK00] FOREIGN KEY
(
[MainID]
) REFERENCES [dbo].[tblT_Documents] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO

<-------------- Table Defs End -------------------->

<-------------- Good Query Begins -------------------->

SELECT DISTINCT d.ID, d.DocType, d.DocDt, d.Book, d.Page, gro.GRANTOR
FROM tblT_Documents d LEFT OUTER JOIN
tblT_Grantors gro ON d.ID = gro.MainID
WHERE (gro.GRANTOR LIKE N'%pracht%')

<-------------- Good Query Ends -------------------->

<-------------- Failing Query Begins -------------------->

SELECT DISTINCT d.ID, d.DocType, d.DocDt, d.Book, d.Page, gro.GRANTOR,
gre.GRANTEE, s.Subdivision
FROM tblT_Documents d LEFT OUTER JOIN
tblT_Subdivisio n s ON d.ID = s.MainID LEFT OUTER
JOIN
tblT_Grantees gre ON d.ID = gre.MainID LEFT
OUTER JOIN
tblT_Grantors gro ON d.ID = gro.MainID
WHERE (gro.GRANTOR LIKE N'%pracht%')

<-------------- Failing Query Ends -------------------->

[Erland Sommarskog]

(Ed_No_Spam_Ple ase_Weber@Weber _Spam_Not_Enter prises.Org) writes:

Per request, the following is the generate script for 4 tables; the
query that works 'OK' and the query that doesn't work.
Thanks for the scripts!

There are a couple of things I like to address. Permit me to start
with the data model as such.
CREATE TABLE [dbo].[tblT_Grantors] (
[GrantorID] [int] IDENTITY (1, 1) NOT NULL ,
[MainID] [int] NULL ,
[GRANTOR] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
This table looks funny to me. Why would you insert a row with NULL values
in MainID and/or GRANTOR? Is possible to insert a GRANTOR without
connecting him to a document? Does it make sense to connect the same
GRANTOR twice to the same doument? What does a NULL GRANTOR mean?

The defintion I would expect is:

CREATE TABLE [dbo].[tblT_Grantors] (
[MainID] [int] NOT NULL ,
[GRANTOR] [nvarchar] (50) NOT NULL
) ON [PRIMARY]

And then (MainID, GRANTOR) would make up the primary key. But, of course,
since I don't know the business domain, I may be wrong here.
ALTER TABLE [dbo].[tblT_Grantors] WITH NOCHECK ADD
CONSTRAINT [PK_tblT_Grantor s] PRIMARY KEY CLUSTERED
(
[GrantorID]
) ON [PRIMARY]
Assuming now that you really need to keep GrantorID, the query in question
is
likely to benefit if the primary key was non-clustered, and the index on
MainID was made clustered.

This comment applies to the Grantee and Subdivisions tables as well.

As for the indexing of all columns - this is likely to be of limited
usage. For some queries composed indexes may be better. (Although that is
not the case here.
SELECT DISTINCT d.ID, d.DocType, d.DocDt, d.Book, d.Page, gro.GRANTOR
FROM tblT_Documents d LEFT OUTER JOIN
tblT_Grantors gro ON d.ID = gro.MainID
WHERE (gro.GRANTOR LIKE N'%pracht%')
This query appears to have an error in it. Or at least it is written in
an unusual way. If you mean what the query produces, the normal way
would be to say:

SELECT DISTINCT d.ID, d.DocType, d.DocDt, d.Book, d.Page, gro.GRANTOR
FROM tblT_Documents d
JOIN tblT_Grantors gro ON d.ID = gro.MainID
WHERE (gro.GRANTOR LIKE N'%pracht%')

That is, there is no need for the LEFT JOIN. Since you have a condition
on Grantors in the WHERE clause, you are filtering away all rows with
documents without grantors. Or documents that has other grantors than
%pracht%. If you want all documents, but only include grantors like
%pracht%, the query would be:

SELECT DISTINCT d.ID, d.DocType, d.DocDt, d.Book, d.Page, gro.GRANTOR
FROM tblT_Documents d
LEFT JOIN tblT_Grantors gro ON d.ID = gro.MainID
AND (gro.GRANTOR LIKE N'%pracht%')

This far I have not discussed the DISTINCT. It appears to be unnecessary,
but since your data model permits the same grantor being added more than
once to the same document, it may not be.

<-------------- Failing Query Begins -------------------->

SELECT DISTINCT d.ID, d.DocType, d.DocDt, d.Book, d.Page, gro.GRANTOR,
gre.GRANTEE, s.Subdivision
FROM tblT_Documents d
LEFT OUTER JOIN tblT_Subdivisio n s ON d.ID = s.MainID
LEFT OUTER JOIN tblT_Grantees gre ON d.ID = gre.MainID
LEFT OUTER JOIN tblT_Grantors gro ON d.ID = gro.MainID
WHERE (gro.GRANTOR LIKE N'%pracht%')

OK, so what you have here is not only a performance problem. The query is
simply plain wrong. Assume that for a document there are three grantees,
three subdivisions, and three grantors all matching %prachh%. How many
rows will you get for this document in the result set? Answer: 27. You
have a cartesian product of the subtables, and this is probably why the
query takes so long time to compute.

Unfortunately, I cannot say what the correct query is, since I don't know
the business domain. It may be that you cannot really return all in
one query, but will have to query for subdivisions and grantors
separately. To address this, I would need more information of what you
are trying to achieve.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Lee Tudor]

Erland has highlighted most of these points but allow me to recap on the
performance issues.

1) shifting the clustered index on the 3 sub tables to MainID will help this
and other queries on these tables.
2) dont use left join if a join will do as it limits the options available
to the query optimiser, you can remove it from tblT_Grantors with no
functionality change in the query.
3) dont use distinct unless you need it, the workload is generates is
non-trivial.

SELECT d.ID, d.DocType, d.DocDt, d.Book, d.Page, gro.GRANTOR,
gre.GRANTEE, s.Subdivision
FROM tblT_Documents d
JOIN tblT_Grantors gro ON d.ID = gro.MainID
LEFT OUTER JOIN tblT_Subdivisio n s ON d.ID = s.MainID
LEFT OUTER JOIN tblT_Grantees gre ON d.ID = gre.MainID
WHERE (gro.GRANTOR LIKE N'%pracht%')

If this gives you no joy, check the execution plan and see if you are
getting the cartesian product issue (input rows scaling up by a factor after
each join operator).

Mr Tea

[Ed_No_Spam_Please_Weber]

Erland and Mr. Tea!

Thank you both for your wonderful insights.

Here are some clarifying items:

1) Yes, original data exists whereby the exact same Grantor appears
as a separate record for the exact same Document ID - this is original
source data (about 75,000 rows) that was never cleaned up ----
Removing duplicates will correct this problem but it hasn't happened
yet. Therefore, the 'distinct' clause.

Second... The objective of the original query was to eventually put
the whole thing in a stored proc and then pass in the entire 'Where'
clause as a parameter. Therefore, the access form would collect
whichever child fields they wanted to search on, and the 'Where'
clause would be built dynamically in Access, and then passed as a
parameter to the SP. The LEFT JOIN seems necessary because the
criteria won't always be 'GRANTOR LIKE...' Rather, it may end up
being 'Subdivision LIKE ... AND GRANTEE LIKE... etc. etc. By keeping
the query focused on the Document parent table with all of the child
tables as Left Joins from the parent, then a generic where clause can
be passed in without problems. The JOIN alone would drop records if
the Where clause doesn't match the table being merely joined and not
left joined. (I think I'm saying this right...)
On Mon, 21 Feb 2005 08:02:43 GMT, "Lee Tudor" <mr****@ntlworl d.com>
wrote:

Erland has highlighted most of these points but allow me to recap on the
performance issues.

1) shifting the clustered index on the 3 sub tables to MainID will help this
and other queries on these tables.
2) dont use left join if a join will do as it limits the options available
to the query optimiser, you can remove it from tblT_Grantors with no
functionalit y change in the query.
3) dont use distinct unless you need it, the workload is generates is
non-trivial.

SELECT d.ID, d.DocType, d.DocDt, d.Book, d.Page, gro.GRANTOR,
gre.GRANTEE, s.Subdivision
FROM tblT_Documents d
JOIN tblT_Grantors gro ON d.ID = gro.MainID
LEFT OUTER JOIN tblT_Subdivisio n s ON d.ID = s.MainID
LEFT OUTER JOIN tblT_Grantees gre ON d.ID = gre.MainID
WHERE (gro.GRANTOR LIKE N'%pracht%')

If this gives you no joy, check the execution plan and see if you are
getting the cartesian product issue (input rows scaling up by a factor after
each join operator).

Mr Tea

[Erland Sommarskog]

(Ed_No_Spam_Ple ase_Weber@Weber _Spam_Not_Enter prises.Org) writes:

Second... The objective of the original query was to eventually put
the whole thing in a stored proc and then pass in the entire 'Where'
clause as a parameter. Therefore, the access form would collect
whichever child fields they wanted to search on, and the 'Where'
clause would be built dynamically in Access, and then passed as a
parameter to the SP. The LEFT JOIN seems necessary because the
criteria won't always be 'GRANTOR LIKE...' Rather, it may end up
being 'Subdivision LIKE ... AND GRANTEE LIKE... etc. etc. By keeping
the query focused on the Document parent table with all of the child
tables as Left Joins from the parent, then a generic where clause can
be passed in without problems. The JOIN alone would drop records if
the Where clause doesn't match the table being merely joined and not
left joined. (I think I'm saying this right...)

But you still have the problem of the cartesian product. Before you go
any further you need to specify how you should handle multiple child
records for a document.

If you only were to present data from the document, it would be an
easy thing:

SELECT ...
FROM documents d
WHERE EXISTS (SELECT *
FROM grantors g
WHERE g.MainID = d.id
AND g.gratnor LIKE '%pracht%')
AND EXISTS (...)

If you only want to present one gratnor, grantee, subdivision etc per
document it's still an easy thing. Take the above into a temp table
and then:

UPDATE #temp
SET grantor = (SELECT MAX(grantor)
FROM grantors g
WHERE g.MainID = d.id)
FROM #temp d

If you want to present all grantors etc, you do indeed have a
presentational problem, that I cannot solve for you.

As for passing WHERE clauses from Access, please keep in mind that
the SQL syntax is very different in Access and SQL Server. In any
case, if your idea about writing stored procedures is that you pass
the WHERE clause as a parameter, stop writing store procedures. You
are just increasing the complexity without gaining anything at all.

Since you appear to be in to dynamic search conditions, you may be
interested in this article on my web site:
http://www.sommarskog.se/dyn-search.html. That is, if you really
want to do it in T-SQL and use stored procedures. If you are
more comfortable of building the SQL Statement in VB code in Access
to that. But in such case, build the entire SQL statement there.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Lee Tudor]

If you are building up your where clause dynamically, then build up your
join clause dynamically.

CREATE PROCEDURE Search @whereclause varchar(500), @joingrantor bit,
@joingrantee bit, @joinsubdivisio n bit AS

these bits would indicate if you need a 'JOIN' or a 'LEFT JOIN' depending if
the table appeared in the where clause. One other step would be to attempt
to make the search clause sargable. dont automatically tack on %% if you
dont need to (mainly applies to the prefix) as this also limits options
available to the optimiser. You could decide to prefix the % in your code
only if it was necessary.

If you could get the code to generate a block similar to this containing the
above suggestions:

SELECT DISTINCT d.ID, d.DocType, d.DocDt, d.Book, d.Page, gro.GRANTOR,
gre.GRANTEE, s.Subdivision
FROM tblT_Documents d
JOIN tblT_Grantors gro ON d.ID = gro.MainID
LEFT OUTER JOIN tblT_Subdivisio n s ON d.ID = s.MainID
LEFT OUTER JOIN tblT_Grantees gre ON d.ID = gre.MainID
WHERE (gro.GRANTOR LIKE N'pracht%')

with clustered indexes on: tblT_Grantors.M ain, tblT_Subdivisio n.Main,
tblT_Grantees.M ain
and nonclustered indexes on: tblT_Grantors.G rantor,
tblT_Subdivisio n.Subdivision, tblT_Grantees.G rantee

You should notice a performance increase by several orders of magnitude.

Mr Tea

<Ed_No_Spam_Ple ase_Weber@Weber _Spam_Not_Enter prises.Org> wrote in message
news:iu******** *************** *********@4ax.c om...

Erland and Mr. Tea!

Thank you both for your wonderful insights.

Here are some clarifying items:

1) Yes, original data exists whereby the exact same Grantor appears
as a separate record for the exact same Document ID - this is original
source data (about 75,000 rows) that was never cleaned up ----
Removing duplicates will correct this problem but it hasn't happened
yet. Therefore, the 'distinct' clause.

Second... The objective of the original query was to eventually put
the whole thing in a stored proc and then pass in the entire 'Where'
clause as a parameter. Therefore, the access form would collect
whichever child fields they wanted to search on, and the 'Where'
clause would be built dynamically in Access, and then passed as a
parameter to the SP. The LEFT JOIN seems necessary because the
criteria won't always be 'GRANTOR LIKE...' Rather, it may end up
being 'Subdivision LIKE ... AND GRANTEE LIKE... etc. etc. By keeping
the query focused on the Document parent table with all of the child
tables as Left Joins from the parent, then a generic where clause can
be passed in without problems. The JOIN alone would drop records if
the Where clause doesn't match the table being merely joined and not
left joined. (I think I'm saying this right...)
On Mon, 21 Feb 2005 08:02:43 GMT, "Lee Tudor" <mr****@ntlworl d.com>
wrote:

Erland has highlighted most of these points but allow me to recap on the
performance issues.

1) shifting the clustered index on the 3 sub tables to MainID will help
this
and other queries on these tables.
2) dont use left join if a join will do as it limits the options available
to the query optimiser, you can remove it from tblT_Grantors with no
functionali ty change in the query.
3) dont use distinct unless you need it, the workload is generates is
non-trivial.

SELECT d.ID, d.DocType, d.DocDt, d.Book, d.Page, gro.GRANTOR,
gre.GRANTEE , s.Subdivision
FROM tblT_Documents d
JOIN tblT_Grantors gro ON d.ID = gro.MainID
LEFT OUTER JOIN tblT_Subdivisio n s ON d.ID = s.MainID
LEFT OUTER JOIN tblT_Grantees gre ON d.ID = gre.MainID
WHERE (gro.GRANTOR LIKE N'%pracht%')

If this gives you no joy, check the execution plan and see if you are
getting the cartesian product issue (input rows scaling up by a factor
after
each join operator).

Mr Tea