schlepuetz
asked on
Faster alternative to a cross join.
I would like to be able to show all CatID and ItemID from vwCatItemList for each ID in vwTest1 even if there is no checked value for the CatID and the ItemID in vwTest1.
I am currently using two views and a cross join to acheive my results but the query is too slow. I would like to be able to get the same results but without the cross join.
This is the results of vwCatItemList
exp1 CatID ItemID
Cat1Item1 1 1
Cat1Item2 1 2
Cat1Item3 1 3
Cat1Item4 1 4
Cat1Item5 1 5
Cat1Item6 1 6
Cat2Item8 2 8
Cat2Item9 2 9
Cat2Item10 2 10
Cat2Item11 2 11
Cat2Item12 2 12
Cat2Item13 2 13
Cat2Item14 2 14
Cat2Item15 2 15
Cat3Item16 3 16
Cat3Item17 3 17
Cat3Item18 3 18
Cat3Item19 3 19
Cat3Item20 3 20
Cat3Item21 3 21
Cat3Item22 3 22
Cat4Item23 4 23
Cat4Item24 4 24
Cat4Item25 4 25
Cat5Item26 5 26
Cat5Item27 5 27
Cat5Item28 5 28
Cat5Item29 5 29
Cat5Item30 5 30
Cat6Item31 6 31
Cat6Item32 6 32
Cat6Item33 6 33
This is the results of vwTest1
ID CatID ItemID Checked
5000033 4 25 -1
5000035 4 24 -1
5000041 2 8 -1
5000041 3 20 -1
5000041 5 30 -1
5000042 2 8 -1
5000042 2 13 -1
Desired output
ID CatID ItemID Checked
5000041 1 1 0
5000041 1 2 0
5000041 1 3 0
5000041 1 4 0
5000041 1 5 0
5000041 1 6 0
5000041 2 1 0
5000041 2 2 0
5000041 2 3 0
5000041 2 4 0
5000041 2 5 0
5000041 2 6 0
5000041 2 7 0
5000041 2 8 -1
5000041 2 9 0
5000041 2 10 0
5000041 2 11 0
5000041 2 12 0
5000041 2 13 0
5000041 2 14 0
5000041 2 15 0
more records here
5000041 3 20 -1
more records here
5000041 5 30 -1
I am currently using two views and a cross join to acheive my results but the query is too slow. I would like to be able to get the same results but without the cross join.
This is the results of vwCatItemList
exp1 CatID ItemID
Cat1Item1 1 1
Cat1Item2 1 2
Cat1Item3 1 3
Cat1Item4 1 4
Cat1Item5 1 5
Cat1Item6 1 6
Cat2Item8 2 8
Cat2Item9 2 9
Cat2Item10 2 10
Cat2Item11 2 11
Cat2Item12 2 12
Cat2Item13 2 13
Cat2Item14 2 14
Cat2Item15 2 15
Cat3Item16 3 16
Cat3Item17 3 17
Cat3Item18 3 18
Cat3Item19 3 19
Cat3Item20 3 20
Cat3Item21 3 21
Cat3Item22 3 22
Cat4Item23 4 23
Cat4Item24 4 24
Cat4Item25 4 25
Cat5Item26 5 26
Cat5Item27 5 27
Cat5Item28 5 28
Cat5Item29 5 29
Cat5Item30 5 30
Cat6Item31 6 31
Cat6Item32 6 32
Cat6Item33 6 33
This is the results of vwTest1
ID CatID ItemID Checked
5000033 4 25 -1
5000035 4 24 -1
5000041 2 8 -1
5000041 3 20 -1
5000041 5 30 -1
5000042 2 8 -1
5000042 2 13 -1
Desired output
ID CatID ItemID Checked
5000041 1 1 0
5000041 1 2 0
5000041 1 3 0
5000041 1 4 0
5000041 1 5 0
5000041 1 6 0
5000041 2 1 0
5000041 2 2 0
5000041 2 3 0
5000041 2 4 0
5000041 2 5 0
5000041 2 6 0
5000041 2 7 0
5000041 2 8 -1
5000041 2 9 0
5000041 2 10 0
5000041 2 11 0
5000041 2 12 0
5000041 2 13 0
5000041 2 14 0
5000041 2 15 0
more records here
5000041 3 20 -1
more records here
5000041 5 30 -1
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
By having indexes that contain all the data needed by your query and composing the query so that it uses the indexes. Start with an explain plan to see what you already get. I don't mean to trivialize the optimization--it's art + knowledege + luck + persistence.
What indexes do you have?
What do your views look like?
What indexes do you have?
What do your views look like?
ASKER
Here is the two views and the tables that the views are built off of.
CREATE VIEW dbo.vwStopCatItemList
AS
SELECT 'Cat' + CAST(dbo.tblStopCategory.C ategoryID AS nvarchar(2)) + 'Item' + CAST(dbo.tblStopItems.Item ID AS nvarchar(2)) AS Expr1,
dbo.tblStopCategory.Catego ryID, dbo.tblStopItems.ItemID
FROM dbo.tblStopCategory INNER JOIN
dbo.tblStopItems ON dbo.tblStopCategory.Catego ryID = dbo.tblStopItems.CategoryI D
CREATE VIEW dbo.vwJMSTStopListingCross Join
AS
SELECT TOP 100 PERCENT dbo.tblJMSTStopObservation .StopID, dbo.vwStopCatItemList.Expr 1
FROM dbo.tblJMSTStopObservation CROSS JOIN
dbo.vwStopCatItemList
CREATE TABLE [dbo].[tblJMSTStopObservat ion] (
[StopID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[PlantID] [int] NOT NULL ,
[StopDate] [datetime] NOT NULL ,
[Shift] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL ,
[ObserverID] [int] NOT NULL ,
[AreaID] [int] NOT NULL ,
[StopAllSafe] [bit] NOT NULL ,
[CreatedBy] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[CreatedDate] [datetime] NULL ,
[ModifiedBy] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[ModifiedDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblJMSTStopObservat ion] WITH NOCHECK ADD
CONSTRAINT [PK_tblJMSTStopObservation ] PRIMARY KEY CLUSTERED
(
[StopID]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblStopItems] (
[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
[ItemDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL ,
[CategoryID] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblStopItems] WITH NOCHECK ADD
CONSTRAINT [PK_tblStopItems] PRIMARY KEY CLUSTERED
(
[ItemID]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblStopCategory] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblStopCategory] WITH NOCHECK ADD
CONSTRAINT [PK_tblStopCategory] PRIMARY KEY CLUSTERED
(
[CategoryID]
) ON [PRIMARY]
GO
exec sp_addextendedproperty N'MS_Description', N'List of main headings from Stop card', N'user', N'dbo', N'table', N'tblStopCategory'
GO
CREATE VIEW dbo.vwStopCatItemList
AS
SELECT 'Cat' + CAST(dbo.tblStopCategory.C
dbo.tblStopCategory.Catego
FROM dbo.tblStopCategory INNER JOIN
dbo.tblStopItems ON dbo.tblStopCategory.Catego
CREATE VIEW dbo.vwJMSTStopListingCross
AS
SELECT TOP 100 PERCENT dbo.tblJMSTStopObservation
FROM dbo.tblJMSTStopObservation
dbo.vwStopCatItemList
CREATE TABLE [dbo].[tblJMSTStopObservat
[StopID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[PlantID] [int] NOT NULL ,
[StopDate] [datetime] NOT NULL ,
[Shift] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_
[ObserverID] [int] NOT NULL ,
[AreaID] [int] NOT NULL ,
[StopAllSafe] [bit] NOT NULL ,
[CreatedBy] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_
[CreatedDate] [datetime] NULL ,
[ModifiedBy] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_
[ModifiedDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblJMSTStopObservat
CONSTRAINT [PK_tblJMSTStopObservation
(
[StopID]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblStopItems] (
[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
[ItemDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[CategoryID] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblStopItems] WITH NOCHECK ADD
CONSTRAINT [PK_tblStopItems] PRIMARY KEY CLUSTERED
(
[ItemID]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblStopCategory] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblStopCategory] WITH NOCHECK ADD
CONSTRAINT [PK_tblStopCategory] PRIMARY KEY CLUSTERED
(
[CategoryID]
) ON [PRIMARY]
GO
exec sp_addextendedproperty N'MS_Description', N'List of main headings from Stop card', N'user', N'dbo', N'table', N'tblStopCategory'
GO
Don't have much time now, but to start, let's just simplify. For the moment, forget the views and see how this performs:
SELECT O.StopID, I.CategoryID, I.ItemID
FROM dbo.tblJMSTStopObservation O CROSS JOIN dbo.tblStopItems I
Then, add a unique index to tblStopItems with the columns CategoryID and ItemID and try again.
SELECT O.StopID, I.CategoryID, I.ItemID
FROM dbo.tblJMSTStopObservation
Then, add a unique index to tblStopItems with the columns CategoryID and ItemID and try again.
ASKER
CatID and ItmeID are from two seperate tables. There is a view that is created to combine the Catid and the ItemID it is called vwStopCatItemList. Then that view is cross joined with tblStopItems.
ASKER
Ok so I have realized that doing a cross join with any large number of records is an exercise in futility. 38,000 rows cross joined with 30 rows = 1,140,000 (too many rows). I have changed the front end of the application that was using the cross joins to be able to work without the view. Thanks for the help, the suggestions and more importantly the nudge/push in a different direction.
ASKER