In a catalog db, where each product has a classID, and each class has a parent ClassID, up numerous levels, and one must generate a list of all parent ClassIDs for a specific product in the quickest, most efficient manner, what would that manner be? I have tried cursors (not fast), while loops, etc. but these take longer that 2 minutes to run (the list is long and a little complicated). I have run through the index tuner with a load file, but the indexes seem to be OK. Consider the tables as the following:
CREATE TABLE [dbo].[Class] (
[ClassID] [int] IDENTITY (1, 1) NOT NULL ,
[ClassDescrp] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrntClassID] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Product] (
[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductDescrp] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClassID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Class] WITH NOCHECK ADD
CONSTRAINT [PK_Class] PRIMARY KEY CLUSTERED
(
[ClassID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Product] WITH NOCHECK ADD
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Class] ADD
CONSTRAINT [FK_Class_Class] FOREIGN KEY
(
[PrntClassID]
) REFERENCES [dbo].[Class] (
[ClassID]
)
GO
ALTER TABLE [dbo].[Product] ADD
CONSTRAINT [FK_Product_Class] FOREIGN KEY
(
[ClassID]
) REFERENCES [dbo].[Class] (
[ClassID]
)
GO
I would like a simple select statement to do the task, but am not sure how to put that together.
All help is greatly appreciated!!
Thank You
Rollin Burr
Politicians and diapers have one thing in common. They should both be changed regularly, and for the same reason.