473,721 Members | 2,220 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Multiple Table Joins Makes Query Go To Sleep

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
Jul 23 '05 #1
9 2763
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

Jul 23 '05 #2
(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
Jul 23 '05 #3
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 -------------------->

Jul 23 '05 #4
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 -------------------->

Jul 23 '05 #5
(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
Jul 23 '05 #6
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
Jul 23 '05 #7
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


Jul 23 '05 #8
(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
Jul 23 '05 #9
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

Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
31561
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins" slows the system down considerably. I've tried creating a temp db, but I can't figure out how to execute two select commands. (It throws the exception "The column prefix 'tempdb' does not match with a table name or alias name used in the query.")
2
3556
by: Keith B via SQLMonster.com | last post by:
Hi! I want to return a derived table along with 4 simple tables in a stored procedure as follows: Input parameter: @FtNum (==Order Number, selects one Order and all associated data) Table 1: Orders Table 2: Items
6
2640
by: Christopher Harrison | last post by:
Is there a way to store an indefinite number of keys in one field and self join them? Imagine, for example, you have a table of users with a "friends" column. Say user 1 is friends with users 9, 7, 24 and 6; then would it be possible to store this array/list/whatever-you-want-to-call-it into user 1's friends cell and extract the join in a query, some how? An easy and obvious way, that is; I can think of string processing hacks ;) Thanks...
2
15063
by: Scott Cannon | last post by:
I am trying to query 3 tables all related by Clinet_ID. The Clients table, Monthly_Expenses table and Monthly_Income table. Each client can have 0>M instances of expenses, past due expenses, and income so I am using the SUM aggregate function to get the totals for the Monthly_Epenses.Amount, Monthly_Expenses.Past_Due_Amount, and Monthly_Income.Amount. Then problem I am having and cannot seem to resolve is that the Monthly_Income is...
6
4994
by: James Radke | last post by:
Hello, I have a multithreaded windows NT service application (vb.net 2003) that I am working on (my first one), which reads a message queue and creates multiple threads to perform the processing for long running reports. When the processing is complete it uses crystal reports to load a template file, populate it, and then export it to a PDF. It works fine so far....
20
2559
by: p175 | last post by:
Hi people, I have a stored procedure that creates many Global temporary session tables. Into each of these tables go the results of various processing using relational division all keyed and based on a common ID from an ID session table. So we can have various session tables with differing results but if they contain records, they are all keyed to the common ID. My problem now however is how do I report the overall findings of the
52
6330
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible variations(combination of fields), - then act on each group in some way ...eg ProcessRs (oRs as RecordSet)... the following query will get me the distinct groups
2
3167
by: beargrease | last post by:
I'm kind of comfortable with basic joins, but a current project requires a complex query of many tables. The GROUP_CONCAT(DISTINCT ...) function has been very useful as returning my values as comma delimited values when joining multiple tables. I have one table called 'floorplans' which has two fields (floorplan_jpg & floorplan_pdf), I'd like each of these fields to return arrays of the same length (they have the same # of values in the data...
0
1441
by: hinksta | last post by:
I have a query that has two tables with a join and works fine, the tables are from a vbulletin database and the query is from an old unsupported mod. Iím trying to add a third table to the query, Iím a bit new to joins and having a bit of trouble getting it to work. This is the original query $NewestThreads = $vbdb->query(" select t.*,p.pagetext from ".TABLE_PREFIX."thread t left join ".TABLE_PREFIX."post p on(p.postid=t.firstpostid)...
0
8840
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8730
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9367
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9215
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9064
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6669
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Duprť who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5981
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4484
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3189
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.