Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Faster alternative to a cross join.

Posted on 2007-03-29
8
Medium Priority
?
2,734 Views
Last Modified: 2008-01-09
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
                  
0
Comment
Question by:schlepuetz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 9

Assisted Solution

by:dduser
dduser earned 400 total points
ID: 18817901
Hi,
This is really impossible as there is no connection between vwtest1 and vwCatItemList. If you don't apply Cross Join your id would also come as null.

Select b.ID,a.CatId,a.ItemId,b.Checked from vwCatItemList Left Outer Join vwtest1 on a.CatId = b.CatId and a.ItemId = b.ItemId

Regards,

dduser
0
 
LVL 42

Accepted Solution

by:
dqmq earned 600 total points
ID: 18818032
No matter how you spin it, you want a cross join, by definition. So the question really, is can you write a faster cross join than SQL.  Maybe.  But I think your time would be better spent optimizing for the SQL cross join.

Don't know what the views are doing, but given the output shown you should try to limit I/O to index scans--deally, indexes that are ordered to your advantage: ID, CatID, ItemID  
0
 
LVL 3

Author Comment

by:schlepuetz
ID: 18818056
How can I limit the I/O to index scans?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 42

Expert Comment

by:dqmq
ID: 18818338
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?
0
 
LVL 3

Author Comment

by:schlepuetz
ID: 18818431
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.CategoryID AS nvarchar(2)) + 'Item' + CAST(dbo.tblStopItems.ItemID AS nvarchar(2)) AS Expr1,
                      dbo.tblStopCategory.CategoryID, dbo.tblStopItems.ItemID
FROM         dbo.tblStopCategory INNER JOIN
                      dbo.tblStopItems ON dbo.tblStopCategory.CategoryID = dbo.tblStopItems.CategoryID

CREATE VIEW dbo.vwJMSTStopListingCrossJoin
AS
SELECT     TOP 100 PERCENT dbo.tblJMSTStopObservation.StopID, dbo.vwStopCatItemList.Expr1
FROM         dbo.tblJMSTStopObservation CROSS JOIN
                      dbo.vwStopCatItemList

CREATE TABLE [dbo].[tblJMSTStopObservation] (
      [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].[tblJMSTStopObservation] 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
0
 
LVL 42

Expert Comment

by:dqmq
ID: 18820206
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.  


0
 
LVL 3

Author Comment

by:schlepuetz
ID: 18823587
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.
0
 
LVL 3

Author Comment

by:schlepuetz
ID: 18826100
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.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question