Re: foreign key references only part of composite PK - table scan issue

On Aug 9, 2:31 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
Dave (djohanns...@xxxxxxxxx) writes:
thanks for your efforts, you may have a miscommunication though.
there is only 1 join with 3 tables at play, where an index scan
(essentially a table scan) is at play.  i won't explain the table
layouts again though - so no worries.  thanks again.

Well, given the minimum of information you disclose, there are
certainly good oppurtunies to leaving things up to our imagination,
or miscommunicaiton, if you prefer.

But it's your call. The less you share with us, the less accurate
the advice.

--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

ok here you go, lots of details. i don't have access to
ABC_Staging.dbo.Stage_ABC_ContactKey until tomorrow but i can recall
it only had one index on it, its PK on NSO_IndividualID, and C.NSO_ID.

you can see the offending tables ABC_Person and ABC_Organization each
only have 1/2 of the fields of the Stage_ABC_ContactKey PK.

SELECT DISTINCT
B.ABC_PersonID,
C.ABC_OrganizationID,
GETDATE() AS ABC_UploadedDate,
NULL AS ABC_ChangeDate,
1 AS IsActive
FROM ABC_Staging.dbo.Stage_ABC_ContactKey AS A WITH (NOLOCK)
JOIN dbo.ABC_Person AS B WITH (NOLOCK)
ON A.NSO_IndividualID = B.NSO_IndividualID
JOIN dbo.ABC_Organization AS C WITH (NOLOCK)
ON C.NSO_ID = A.NSO_ID;

/****** Object: Table [dbo].[ABC_Organization] Script Date:
08/09/2009 16:09:33 ******/
CREATE TABLE [dbo].[ABC_Organization](
[ABC_OrganizationID] [int] IDENTITY(1,1) NOT NULL,
[NSO_SuppressionID] [tinyint] NOT NULL,
[DEF_TOH] [int] NULL,
[DEF_CompositeID] [int] NULL,
[DEF_OrgName] [nvarchar](256) NULL,
[NSO_ID] [int] NULL,
[NSO_Org_Updated] [datetime] NOT NULL,
[TPID] [int] NULL,
[IsActive] [bit] NOT NULL,
[SBL_AccountParentID] [nvarchar](15) NULL,
[NSS_TopParentName] [nvarchar](60) NULL,
[DepthBreadthCustomer] [nvarchar](20) NULL,
[ABC_ChangeDate] [datetime] NULL,
[ABC_DeactivationID] [int] NULL,
[HighestLP] [nvarchar](50) NULL,
CONSTRAINT [ABC_Organization_PK_ABC_OrganizationID] PRIMARY KEY
CLUSTERED
(
[ABC_OrganizationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


/****** Object: Index [Idx_ABC_Organization_NSO_ID] Script Date:
08/09/2009 16:17:05 ******/
CREATE NONCLUSTERED INDEX [Idx_ABC_Organization_NSO_ID] ON [dbo].
[ABC_Organization]
(
[NSO_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object: Table [dbo].[ABC_Person] Script Date: 08/09/2009
16:09:56 ******/
CREATE TABLE [dbo].[ABC_Person](
[NSO_Ind_Suppression] [tinyint] NOT NULL,
[ABC_PersonID] [int] IDENTITY(1,1) NOT NULL,
[NSO_IndividualID] [int] NOT NULL,
[NSO_UpdatedDate] [datetime] NOT NULL,
[IsActive] [bit] NOT NULL,
[ABC_ChangeDate] [datetime] NULL,
[ABC_DeactivationID] [int] NULL,
CONSTRAINT [ABC_Person_PK] PRIMARY KEY CLUSTERED
(
[ABC_PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** Object: Index [Idx_ABC_Person_NSO_IndividualID] Script
Date: 08/09/2009 16:17:47 ******/
CREATE NONCLUSTERED INDEX [Idx_ABC_Person_NSO_IndividualID] ON [dbo].
[ABC_Person]
(
[NSO_IndividualID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

.

Relevant Pages

  • Re: strange BLOB beahaviour
    ... ASC, ... plan A is without "Picture is not null" and plan B is with that statement. ... and and the preferred data type in SQL 2005 and later is varbinary. ...
    (microsoft.public.sqlserver.server)
  • Re: strange BLOB beahaviour
    ... ASC, ... plan A is without "Picture is not null" and plan B is with that statement. ... and and the preferred data type in SQL 2005 and later is varbinary. ...
    (microsoft.public.sqlserver.server)
  • Re: strange BLOB beahaviour
    ... After i removed "Pictures ... Even better would be to see the query plans for the two cases. ... Links for SQL Server Books Online: ... ASC, ...
    (microsoft.public.sqlserver.server)
  • Re: Data Type Mismatch - String versus Long
    ... The problem is your SQL is not correctly formed. ... "ORDER BY lngOrderID ASC;" ... I am trying to build a WHERE clause with numeric criteria and storing it ... ' Create the WHERE clause criteria. ...
    (microsoft.public.access.modulesdaovba)
  • Re: GAL replication
    ... > and want to play with it. ... you don't have to purchase the MIIS product for your ... >> requires a license for SQL, but if you have SQL somewhere else you may ... >> The products Bob talks about are good ones, ...
    (microsoft.public.exchange.connectivity)