Query performance

  • Hello,

    I was given the task to try and increase performance on a query that was written years ago. From what I was told, this query executed in less than 2 minutes, prior to migrating comments within the mag_comment_base table. However, I tested this query several times within my development environment by removing these comments, but the query still took 2 hours to execute. Below is the query:

    SELECT h.CommentTypeName,

    a.Comment,

    a.CommentDate AS DateCreated,

    CASE a.IsFollowupRequired

    WHEN 0 THEN 'No' ELSE 'Yes'

    END AS FollowupRequired,

    a.FollowupDate AS FollowupReqDate,

    a.FollowupClosedDate,

    a.CreatedByUser,

    a.EntityType,

    a.EntityuniqueID,

    CASE a.entitytype

    WHEN 'Inventory'THEN b.unitnumber

    WHEN 'Lease'THEN c.UnitNumber

    WHEN 'Plates'THEN d .unitnumber

    WHEN 'Inventory Sale' THEN e.unitnumber

    END AS UnitNumber,

    CASE a.entitytype

    WHEN 'Customer'THEN f.AccountNumber

    WHEN 'Inventory'THEN b.AccountNumber

    WHEN 'Lease'THEN c.AccountNumber

    WHEN 'Inventory Sale'THEN e.AccountNumber

    WHEN 'Insurance Policy' THEN g.AccountNumber

    WHEN 'Plates'THEN d .AccountNumber

    END AS CustomerNumber,

    CASE a.entitytype

    WHEN 'Customer'THEN f.CustomerName

    WHEN 'Inventory'THEN b.CustomerName

    WHEN 'Lease'THEN c.CustomerName

    WHEN 'Plates'THEN d .CustomerName

    WHEN 'Insurance Policy'THEN g.CustomerName

    WHEN 'Inventory Sale'THEN e.customername

    END AS CustomerName,

    CASE a.entitytype

    WHEN 'Plates'THEN d .PlateState

    WHEN 'Inventory'THEN b.platestate

    WHEN 'Lease'THEN c.PlateState

    WHEN 'Inventory Sale'THEN e.platestate

    END AS PlateState,

    CASE a.entitytype

    WHEN 'Plates'THEN d .PlateNumber

    WHEN 'Inventory'THEN b.PlateNumber

    WHEN 'Lease'THEN c.PlateNumber

    WHEN 'Inventory Sale'THEN e.PlateNumber

    END AS PlateNumber

    FROM dbo.mag_comment_base AS a LEFT JOIN

    (SELECTdbo.AssetView.InventoryID,

    dbo.AssetView.AccountNumber,

    dbo.AssetView.UnitNumber,

    dbo.CustomerView.CustomerName,

    dbo.PlateView.PlateNumber,

    dbo.PlateView.PlateState

    FROM dbo.AssetView

    LEFT JOIN dbo.CustomerView ON dbo.AssetView.AccountNumber = dbo.CustomerView.AccountNumber

    LEFT JOIN dbo.PlateView ON dbo.AssetView.InventoryID = dbo.PlateView.InventoryID)

    AS b ON a.InventoryID = b.InventoryID

    AND a.EntityType = 'Inventory'

    LEFT JOIN

    (SELECT dbo.LeaseView.LeaseID,

    dbo.LeaseView.UnitNumber,

    dbo.LeaseView.AccountNumber,

    CustomerView_3.CustomerName,

    PlateView_3.PlateNumber,

    PlateView_3.PlateState

    FROM dbo.LeaseView

    LEFT JOIN dbo.CustomerView AS CustomerView_3 ON dbo.LeaseView.AccountNumber = CustomerView_3.AccountNumber

    LEFT JOIN dbo.PlateView AS PlateView_3 ON dbo.LeaseView.InventoryID = PlateView_3.InventoryID)

    AS c ON a.Leaseid = c.LeaseID

    AND a.EntityType = 'Lease'

    LEFT JOIN

    (SELECT PlateView_2.PlateId,

    PlateView_2.UnitNumber,

    AssetView_2.AccountNumber,

    CustomerView_2.CustomerName,

    PlateView_2.PlateNumber,

    PlateView_2.PlateState

    FROM dbo.PlateView AS PlateView_2

    LEFT JOIN dbo.AssetView AS AssetView_2 ON PlateView_2.UnitNumber = AssetView_2.UnitNumber

    LEFT JOIN dbo.CustomerView AS CustomerView_2 ON AssetView_2.AccountNumber = CustomerView_2.AccountNumber)

    AS d ON a.PlateID = d.PlateId

    AND a.EntityType = 'Plates'

    LEFT JOIN

    (SELECT AssetView_1.InventoryID,

    AssetView_1.AccountNumber,

    AssetView_1.UnitNumber,

    CustomerView_1.CustomerName,

    PlateView_1.PlateNumber,

    PlateView_1.PlateState

    FROM dbo.AssetView AS AssetView_1

    LEFT JOIN dbo.CustomerView AS CustomerView_1 ON AssetView_1.AccountNumber = CustomerView_1.AccountNumber

    LEFT JOIN dbo.PlateView AS PlateView_1 ON AssetView_1.InventoryID = PlateView_1.InventoryID)

    AS e ON a.IniventorySaleID = e.InventoryID

    AND a.EntityType = 'Inventory Sale'

    LEFT JOIN dbo.CustomerView AS f ON a.Custnmbr = f.CustomerID

    AND a.EntityType = 'Customer'

    LEFT JOIN

    (SELECT dbo.InsuranceView.InsurancePolicyID,

    dbo.InsuranceView.AccountNumber,

    dbo.CustomerView.CustomerName

    FROM dbo.InsuranceView

    LEFT JOIN dbo.CustomerView ON dbo.InsuranceView.CustomerID = dbo.CustomerView.CustomerID)

    AS g ON a.Insurance_policyId = g.InsurancePolicyID

    AND a.EntityType = 'Insurance Policy'

    LEFT JOIN dbo.Comment_Type AS h ON a.CommentTypeID = h.CommentTypeID

    WHERE (a.Comment <> ' ')

    After running an optimization plan, its spending most of the time in the joins.

    Any information would be much appreciated.

  • Please provide DDL and sample data.

    Also, it would be helpful to attach a query plan so we can see what's going on.

    Converting oxygen into carbon dioxide, since 1955.
  • Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • You probably won't get much help with what you have posted so far. We are also going to need the DDL for the table(s) including any indexes defined on the table(s). The actual execution plan will also help. You can save it as a .sqlplan file, zip it and upload it to this thread as well.

    One last thing, I find the code difficult to read as formatted. You may want to consider reformatting it and posting it inside the IFCode tags [ code="sql" ] and [ /code ] (no spaces between the brackets).

  • Sorry about not having this information up front. This particular query is using views, not tables. Below is the underlying table for the mag_comment_base view with constraints

    USE [LW_Merchants]

    GO

    /****** Object: Table [dbo].[Comment_EntityEntry] Script Date: 04/12/2012 10:42:18 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Comment_EntityEntry](

    [CommentID] [bigint] IDENTITY(1,1) NOT NULL,

    [EntityTypeID] [tinyint] NOT NULL,

    [EntityID] [bigint] NOT NULL,

    [CommentTypeID] [int] NOT NULL,

    [CommentResponseTypeID] [smallint] NOT NULL,

    [Comment] [varchar](6000) NOT NULL,

    [CommentDate] [datetime] NOT NULL,

    [IsFollowupRequired] [tinyint] NOT NULL,

    [FollowupDate] [datetime] NULL,

    [IsFollowupClosed] [tinyint] NOT NULL,

    [FollowupClosedDate] [datetime] NULL,

    [CreatedByUserID] [int] NOT NULL,

    [LastModifiedUserID] [int] NOT NULL,

    [LastModifiedDate] [datetime] NOT NULL,

    [ContactRelationID] [bigint] NULL,

    [AssociatedNumber] [nvarchar](40) NULL,

    CONSTRAINT [PK_Comment_EntityEntry] PRIMARY KEY CLUSTERED

    (

    [CommentID] 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

    SET ANSI_PADDING OFF

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Comment Id, primary key, system generated' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment_EntityEntry', @level2type=N'COLUMN',@level2name=N'CommentID'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'FK to Template_EntityType_Cnfg' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment_EntityEntry', @level2type=N'COLUMN',@level2name=N'EntityTypeID'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Depending on the Entity Type selected, users must have the ability to select the associated Entity ID.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment_EntityEntry', @level2type=N'COLUMN',@level2name=N'EntityID'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'FK to Comment_Type' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment_EntityEntry', @level2type=N'COLUMN',@level2name=N'CommentTypeID'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'FK to Comment_ResponseType _Cnfg' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment_EntityEntry', @level2type=N'COLUMN',@level2name=N'CommentResponseTypeID'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User input comment text.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment_EntityEntry', @level2type=N'COLUMN',@level2name=N'Comment'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The date on which the comment was posted. ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment_EntityEntry', @level2type=N'COLUMN',@level2name=N'CommentDate'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Y/N flag that signifies whether a comment record needs to be followed up on. ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment_EntityEntry', @level2type=N'COLUMN',@level2name=N'IsFollowupRequired'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User input date on which a follow up should be acted upon. ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment_EntityEntry', @level2type=N'COLUMN',@level2name=N'FollowupDate'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Y/N flag to indicate if the comment record was followed up on. ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment_EntityEntry', @level2type=N'COLUMN',@level2name=N'IsFollowupClosed'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User input date on which Follow Up has been closed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment_EntityEntry', @level2type=N'COLUMN',@level2name=N'FollowupClosedDate'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'FK to Security_UserProfile; ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment_EntityEntry', @level2type=N'COLUMN',@level2name=N'CreatedByUserID'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Id of the last modified user' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment_EntityEntry', @level2type=N'COLUMN',@level2name=N'LastModifiedUserID'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time when this record was last modified ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment_EntityEntry', @level2type=N'COLUMN',@level2name=N'LastModifiedDate'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'FK to Contact_EntityPersonRelation' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment_EntityEntry', @level2type=N'COLUMN',@level2name=N'ContactRelationID'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the associated number of the comment' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment_EntityEntry', @level2type=N'COLUMN',@level2name=N'AssociatedNumber'

    GO

    ALTER TABLE [dbo].[Comment_EntityEntry] WITH NOCHECK ADD CONSTRAINT [FK_Comment_EntityEntry_Comment_ResponseType_Cnfg] FOREIGN KEY([CommentResponseTypeID])

    REFERENCES [dbo].[Comment_ResponseType_Cnfg] ([CommentResponseTypeID])

    GO

    ALTER TABLE [dbo].[Comment_EntityEntry] CHECK CONSTRAINT [FK_Comment_EntityEntry_Comment_ResponseType_Cnfg]

    GO

    ALTER TABLE [dbo].[Comment_EntityEntry] WITH NOCHECK ADD CONSTRAINT [FK_Comment_EntityEntry_Comment_Type] FOREIGN KEY([CommentTypeID])

    REFERENCES [dbo].[Comment_Type] ([CommentTypeID])

    GO

    ALTER TABLE [dbo].[Comment_EntityEntry] CHECK CONSTRAINT [FK_Comment_EntityEntry_Comment_Type]

    GO

    ALTER TABLE [dbo].[Comment_EntityEntry] WITH NOCHECK ADD CONSTRAINT [FK_Comment_EntityEntry_Template_EntityType_Cnfg] FOREIGN KEY([EntityTypeID])

    REFERENCES [dbo].[Template_EntityType_Cnfg] ([EntityTypeID])

    GO

    ALTER TABLE [dbo].[Comment_EntityEntry] CHECK CONSTRAINT [FK_Comment_EntityEntry_Template_EntityType_Cnfg]

    GO

    ALTER TABLE [dbo].[Comment_EntityEntry] WITH NOCHECK ADD CONSTRAINT [FK_TCommentEntity_ContactPerson] FOREIGN KEY([ContactRelationID])

    REFERENCES [dbo].[Contact_EntityPersonRelation] ([ContactRelationID])

    GO

    ALTER TABLE [dbo].[Comment_EntityEntry] CHECK CONSTRAINT [FK_TCommentEntity_ContactPerson]

    GO

    ALTER TABLE [dbo].[Comment_EntityEntry] WITH NOCHECK ADD CONSTRAINT [CK_Comment_EntityEntry_IsFollowupClosed] CHECK (([IsFollowupClosed]=(0) OR [IsFollowupClosed]=(1)))

    GO

    ALTER TABLE [dbo].[Comment_EntityEntry] CHECK CONSTRAINT [CK_Comment_EntityEntry_IsFollowupClosed]

    GO

    ALTER TABLE [dbo].[Comment_EntityEntry] WITH NOCHECK ADD CONSTRAINT [CK_Comment_EntityEntry_IsFollowupRequired] CHECK (([IsFollowupRequired]=(0) OR [IsFollowupRequired]=(1)))

    GO

    ALTER TABLE [dbo].[Comment_EntityEntry] CHECK CONSTRAINT [CK_Comment_EntityEntry_IsFollowupRequired]

    GO

    ALTER TABLE [dbo].[Comment_EntityEntry] WITH NOCHECK ADD CONSTRAINT [CK_Comment_EntityEntry_LastModifiedUserID] CHECK (([LastModifiedUserID]>(0)))

    GO

    ALTER TABLE [dbo].[Comment_EntityEntry] CHECK CONSTRAINT [CK_Comment_EntityEntry_LastModifiedUserID]

    GO

    USE [LW_Merchants]

    GO

    /****** Object: Index [IX_Comment_EntityEntry] Script Date: 04/12/2012 10:50:33 ******/

    CREATE NONCLUSTERED INDEX [IX_Comment_EntityEntry] ON [dbo].[Comment_EntityEntry]

    (

    [EntityID] ASC,

    [EntityTypeID] 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

    Security_UserProfile table:

    USE [LW_Merchants]

    GO

    /****** Object: Table [dbo].[Security_UserProfile] Script Date: 04/12/2012 10:43:17 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[Security_UserProfile](

    [UserID] [int] IDENTITY(1,1) NOT NULL,

    [SiteID] [tinyint] NOT NULL,

    [UserCode] [nvarchar](50) NOT NULL,

    [UserAlias] [nvarchar](50) NULL,

    [Title] [nvarchar](50) NULL,

    [FirstName] [nvarchar](100) NOT NULL,

    [MiddleName] [nvarchar](100) NULL,

    [LastName] [nvarchar](100) NOT NULL,

    [Suffix] [nvarchar](20) NULL,

    [Phone] [nvarchar](100) NULL,

    [Extension] [nvarchar](100) NULL,

    [Fax] [nvarchar](100) NULL,

    [EMail] [nvarchar](100) NOT NULL,

    [HireDate] [datetime] NOT NULL,

    [UserSourceID] [bigint] NOT NULL,

    [LoginName] [nvarchar](40) NOT NULL,

    [LoginEffectiveFromDate] [datetime] NOT NULL,

    [LoginExpiryDate] [datetime] NULL,

    [Password] [nvarchar](60) NOT NULL,

    [PasswordEffectiveFromDate] [datetime] NOT NULL,

    [PasswordExpiryDate] [datetime] NOT NULL,

    [IsLoginBlocked] [tinyint] NOT NULL,

    [IsPasswordToBeReset] [tinyint] NOT NULL,

    [IsPreset] [tinyint] NOT NULL,

    [IsWindowsAuthenticationRequired] [tinyint] NOT NULL,

    [IsNotifyByEmailAllowed] [tinyint] NOT NULL,

    [IsActive] [tinyint] NOT NULL,

    [ActivationDate] [datetime] NOT NULL,

    [DeactivationDate] [datetime] NULL,

    [LastModifiedUserID] [int] NOT NULL,

    [LastModifiedDate] [datetime] NOT NULL,

    [IsAllowedToExtendLock] [tinyint] NOT NULL,

    [DefaultAppSubsystemID] [tinyint] NOT NULL,

    [Prefix] [nvarchar](20) NULL,

    CONSTRAINT [PK_Security_UserProfile] PRIMARY KEY CLUSTERED

    (

    [UserID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

    CONSTRAINT [UKC_UserProfile_UserCode] UNIQUE NONCLUSTERED

    (

    [UserCode] 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

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User Id, primary key, system generated' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'UserID'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'FK to Security_Site_Cnfg' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'SiteID'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the code of the user' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'UserCode'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the alias of the user' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'UserAlias'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the title of the user' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'Title'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the first name of the user' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'FirstName'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the middle name of the user' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'MiddleName'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the last name of the user' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'LastName'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the Suffix of the user' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'Suffix'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the phone number of the user' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'Phone'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the extension' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'Extension'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the fax number of the user' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'Fax'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the email of the user' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'EMail'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the hire date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'HireDate'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the user Source Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'UserSourceID'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the Login name of the user' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'LoginName'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date when the login is effective from' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'LoginEffectiveFromDate'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the login expiry date ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'LoginExpiryDate'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the password' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'Password'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date when the password is effective from' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'PasswordEffectiveFromDate'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date when the password expires' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'PasswordExpiryDate'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Y/N flag indicating if the login is blocked' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'IsLoginBlocked'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Y/N flag indicating if the password is to be reset' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'IsPasswordToBeReset'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Y/N flag indicating if the user ir preset or not' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'IsPreset'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Y/N flag indicating if the windows authetication is required' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'IsWindowsAuthenticationRequired'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Y/N flag indicating if the notification by email is allowed' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'IsNotifyByEmailAllowed'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Y/N flag, Indicates if the record is active or not.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'IsActive'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date when the user is activated' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'ActivationDate'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date when the user is deactivated' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'DeactivationDate'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User that last modified this record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'LastModifiedUserID'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time when this record was last modified ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'LastModifiedDate'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Y/N flag indicating if extend lock is allowed' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'IsAllowedToExtendLock'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'FK to Security_ApplicationSubsystem_Cnfg' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'DefaultAppSubsystemID'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the prefix of the user' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Security_UserProfile', @level2type=N'COLUMN',@level2name=N'Prefix'

    GO

    ALTER TABLE [dbo].[Security_UserProfile] WITH NOCHECK ADD CONSTRAINT [FK_Security_userprofile_Security_ApplicationSubsystem_Cnfg] FOREIGN KEY([DefaultAppSubsystemID])

    REFERENCES [dbo].[Security_ApplicationSubsystem_Cnfg] ([ApplicationSubsystemID])

    GO

    ALTER TABLE [dbo].[Security_UserProfile] CHECK CONSTRAINT [FK_Security_userprofile_Security_ApplicationSubsystem_Cnfg]

    GO

    ALTER TABLE [dbo].[Security_UserProfile] WITH NOCHECK ADD CONSTRAINT [FK_Security_UserProfile_Security_Site_Cnfg] FOREIGN KEY([SiteID])

    REFERENCES [dbo].[Security_Site_Cnfg] ([SiteID])

    GO

    ALTER TABLE [dbo].[Security_UserProfile] CHECK CONSTRAINT [FK_Security_UserProfile_Security_Site_Cnfg]

    GO

    ALTER TABLE [dbo].[Security_UserProfile] WITH NOCHECK ADD CONSTRAINT [CK_Security_UserProfile_LastModifiedUserID] CHECK (([LastModifiedUserID]>(0)))

    GO

    ALTER TABLE [dbo].[Security_UserProfile] CHECK CONSTRAINT [CK_Security_UserProfile_LastModifiedUserID]

    GO

    Attached is the execution plan.

    Thanks for your help.

  • Err..... <stares at 16 000 line long execution plan>

    I suggest a 'divide and conquer' approach.

    Take each of the subqueries in that query, insert their results into temp tables then join the temp tables. We can then try to tune each of the smaller inserts and index the temp tables if necessary.

    That query is insanely huge, tuning it as it is is going to be somewhere on the near-impossible side of the continuum.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (4/12/2012)


    Err..... <stares at 16 000 line long execution plan>

    I suggest a 'divide and conquer' approach.

    Take each of the subqueries in that query, insert their results into temp tables then join the temp tables. We can then try to tune each of the smaller inserts and index the temp tables if necessary.

    That query is insanely huge, tuning it as it is is going to be somewhere on the near-impossible side of the continuum.

    Thank you Gila! I totally agree that the query is enormous. I'll try to insert the results into temp tables, than join on them.

    On another note, would you be able to provide an example on how I can do this with temp tables? I've constructed a query but not sure it will work

  • Based on Gail's recommendations, I built temp tables for each subquery. For some reason I'm unable to get this to work, but this is what I have so far:

    SELECT h.CommentTypeName,

    a.Comment,

    a.CommentDate AS DateCreated,

    CASE a.IsFollowupRequired

    WHEN 0 THEN 'No' ELSE 'Yes'

    END AS FollowupRequired,

    a.FollowupDate AS FollowupReqDate,

    a.FollowupClosedDate,

    a.CreatedByUser,

    a.EntityType,

    a.EntityuniqueID,

    CASE a.entitytype

    WHEN 'Inventory'THEN b.unitnumber

    WHEN 'Lease'THEN c.UnitNumber

    WHEN 'Plates'THEN d .unitnumber

    WHEN 'Inventory Sale' THEN e.unitnumber

    END AS UnitNumber,

    CASE a.entitytype

    WHEN 'Customer'THEN f.AccountNumber

    WHEN 'Inventory'THEN b.AccountNumber

    WHEN 'Lease'THEN c.AccountNumber

    WHEN 'Inventory Sale'THEN e.AccountNumber

    WHEN 'Insurance Policy' THEN g.AccountNumber

    WHEN 'Plates'THEN d .AccountNumber

    END AS CustomerNumber,

    CASE a.entitytype

    WHEN 'Customer'THEN f.CustomerName

    WHEN 'Inventory'THEN b.CustomerName

    WHEN 'Lease'THEN c.CustomerName

    WHEN 'Plates'THEN d .CustomerName

    WHEN 'Insurance Policy'THEN g.CustomerName

    WHEN 'Inventory Sale'THEN e.customername

    END AS CustomerName,

    CASE a.entitytype

    WHEN 'Plates'THEN d .PlateState

    WHEN 'Inventory'THEN b.platestate

    WHEN 'Lease'THEN c.PlateState

    WHEN 'Inventory Sale'THEN e.platestate

    END AS PlateState,

    CASE a.entitytype

    WHEN 'Plates'THEN d .PlateNumber

    WHEN 'Inventory'THEN b.PlateNumber

    WHEN 'Lease'THEN c.PlateNumber

    WHEN 'Inventory Sale'THEN e.PlateNumber

    END AS PlateNumber

    FROM dbo.mag_comment_base AS a LEFT JOIN

    (SELECTdbo.AssetView.InventoryID,

    dbo.AssetView.AccountNumber,

    dbo.AssetView.UnitNumber,

    dbo.CustomerView.CustomerName,

    dbo.PlateView.PlateNumber,

    dbo.PlateView.PlateState

    INTO #Inventory

    FROM dbo.AssetView

    LEFT JOIN dbo.CustomerView ON dbo.AssetView.AccountNumber = dbo.CustomerView.AccountNumber

    LEFT JOIN dbo.PlateView ON dbo.AssetView.InventoryID = dbo.PlateView.InventoryID)

    AS b ON a.InventoryID = b.InventoryID

    AND a.EntityType = 'Inventory'

    -- Begin PMF Code

    INNER JOIN

    (SELECTdbo.AssetView.InventoryID,

    dbo.AssetView.AccountNumber,

    dbo.AssetView.UnitNumber,

    dbo.CustomerView.CustomerName,

    dbo.PlateView.PlateNumber,

    dbo.PlateView.PlateState

    FROM #Inventory AS Inv

    WHERE Inv.InventoryID = PlateView.InventoryID)

    -- End PMF Code

    LEFT JOIN

    (SELECT dbo.LeaseView.LeaseID,

    dbo.LeaseView.UnitNumber,

    dbo.LeaseView.AccountNumber,

    CustomerView_3.CustomerName,

    PlateView_3.PlateNumber,

    PlateView_3.PlateState

    INTO #Lease

    FROM dbo.LeaseView

    LEFT JOIN dbo.CustomerView AS CustomerView_3 ON dbo.LeaseView.AccountNumber = CustomerView_3.AccountNumber

    LEFT JOIN dbo.PlateView AS PlateView_3 ON dbo.LeaseView.InventoryID = PlateView_3.InventoryID)

    AS c ON a.Leaseid = c.LeaseID

    AND a.EntityType = 'Lease'

    -- Begin PMF Code

    INNER JOIN

    (SELECT dbo.LeaseView.LeaseID,

    dbo.LeaseView.UnitNumber,

    dbo.LeaseView.AccountNumber,

    CustomerView_3.CustomerName,

    PlateView_3.PlateNumber,

    PlateView_3.PlateState

    FROM #Lease Les

    WHERE Les.InventoryID = PlateView_3.InventoryID

    -- End PMF Code

    LEFT JOIN

    (SELECT PlateView_2.PlateId,

    PlateView_2.UnitNumber,

    AssetView_2.AccountNumber,

    CustomerView_2.CustomerName,

    PlateView_2.PlateNumber,

    PlateView_2.PlateState

    INTO #Plates

    FROM dbo.PlateView AS PlateView_2

    LEFT JOIN dbo.AssetView AS AssetView_2 ON PlateView_2.UnitNumber = AssetView_2.UnitNumber

    LEFT JOIN dbo.CustomerView AS CustomerView_2 ON AssetView_2.AccountNumber = CustomerView_2.AccountNumber)

    AS d ON a.PlateID = d.PlateId

    AND a.EntityType = 'Plates'

    -- Begin PMF Code

    INNER JOIN

    (SELECT PlateView_2.PlateId,

    PlateView_2.UnitNumber,

    AssetView_2.AccountNumber,

    CustomerView_2.CustomerName,

    PlateView_2.PlateNumber,

    PlateView_2.PlateState

    FROM #Plates Pla

    WHERE Pla.UnitNumber = AssetView_2.UnitNumber

    -- End PMF Code

    LEFT JOIN

    (SELECT AssetView_1.InventoryID,

    AssetView_1.AccountNumber,

    AssetView_1.UnitNumber,

    CustomerView_1.CustomerName,

    PlateView_1.PlateNumber,

    PlateView_1.PlateState

    INTO #Customer

    FROM dbo.AssetView AS AssetView_1

    LEFT JOIN dbo.CustomerView AS CustomerView_1 ON AssetView_1.AccountNumber = CustomerView_1.AccountNumber

    LEFT JOIN dbo.PlateView AS PlateView_1 ON AssetView_1.InventoryID = PlateView_1.InventoryID)

    AS e ON a.IniventorySaleID = e.InventoryID

    AND a.EntityType = 'Inventory Sale'

    LEFT JOIN dbo.CustomerView AS f ON a.Custnmbr = f.CustomerID

    AND a.EntityType = 'Customer'

    -- Begin PMF Code

    INNER JOIN

    (SELECT AssetView_1.InventoryID,

    AssetView_1.AccountNumber,

    AssetView_1.UnitNumber,

    CustomerView_1.CustomerName,

    PlateView_1.PlateNumber,

    PlateView_1.PlateState

    FROM #Customer Cus

    WHERE Cus.AccountNumber = CustomerView_1.AccountNumber

    -- End PMF Code

    LEFT JOIN

    (SELECT dbo.InsuranceView.InsurancePolicyID,

    dbo.InsuranceView.AccountNumber,

    dbo.CustomerView.CustomerName

    INTO #InsurancePolicy InsP

    FROM dbo.InsuranceView

    LEFT JOIN dbo.CustomerView ON dbo.InsuranceView.CustomerID = dbo.CustomerView.CustomerID)

    AS g ON a.Insurance_policyId = g.InsurancePolicyID

    AND a.EntityType = 'Insurance Policy'

    LEFT JOIN dbo.Comment_Type AS h ON a.CommentTypeID = h.CommentTypeID

    -- Begin PMF Code

    INNER JOIN

    (SELECT dbo.InsuranceView.InsurancePolicyID,

    dbo.InsuranceView.AccountNumber,

    dbo.CustomerView.CustomerName

    FROM #InsurancePolicy InsP

    WHERE InsP.CustomerID = CustomerView.CustomerID

    -- End PMF Code

    WHERE (a.Comment <> ' ')

  • PFlorenzano-641896 (4/12/2012)


    Based on Gail's recommendations, I built temp tables for each subquery. For some reason I'm unable to get this to work, but this is what I have so far:

    SELECT h.CommentTypeName,

    a.Comment,

    a.CommentDate AS DateCreated,

    CASE a.IsFollowupRequired

    WHEN 0 THEN 'No' ELSE 'Yes'

    END AS FollowupRequired,

    a.FollowupDate AS FollowupReqDate,

    a.FollowupClosedDate,

    a.CreatedByUser,

    a.EntityType,

    a.EntityuniqueID,

    CASE a.entitytype

    WHEN 'Inventory'THEN b.unitnumber

    WHEN 'Lease'THEN c.UnitNumber

    WHEN 'Plates'THEN d .unitnumber

    WHEN 'Inventory Sale' THEN e.unitnumber

    END AS UnitNumber,

    CASE a.entitytype

    WHEN 'Customer'THEN f.AccountNumber

    WHEN 'Inventory'THEN b.AccountNumber

    WHEN 'Lease'THEN c.AccountNumber

    WHEN 'Inventory Sale'THEN e.AccountNumber

    WHEN 'Insurance Policy' THEN g.AccountNumber

    WHEN 'Plates'THEN d .AccountNumber

    END AS CustomerNumber,

    CASE a.entitytype

    WHEN 'Customer'THEN f.CustomerName

    WHEN 'Inventory'THEN b.CustomerName

    WHEN 'Lease'THEN c.CustomerName

    WHEN 'Plates'THEN d .CustomerName

    WHEN 'Insurance Policy'THEN g.CustomerName

    WHEN 'Inventory Sale'THEN e.customername

    END AS CustomerName,

    CASE a.entitytype

    WHEN 'Plates'THEN d .PlateState

    WHEN 'Inventory'THEN b.platestate

    WHEN 'Lease'THEN c.PlateState

    WHEN 'Inventory Sale'THEN e.platestate

    END AS PlateState,

    CASE a.entitytype

    WHEN 'Plates'THEN d .PlateNumber

    WHEN 'Inventory'THEN b.PlateNumber

    WHEN 'Lease'THEN c.PlateNumber

    WHEN 'Inventory Sale'THEN e.PlateNumber

    END AS PlateNumber

    FROM dbo.mag_comment_base AS a LEFT JOIN

    (SELECTdbo.AssetView.InventoryID,

    dbo.AssetView.AccountNumber,

    dbo.AssetView.UnitNumber,

    dbo.CustomerView.CustomerName,

    dbo.PlateView.PlateNumber,

    dbo.PlateView.PlateState

    INTO #Inventory

    FROM dbo.AssetView

    LEFT JOIN dbo.CustomerView ON dbo.AssetView.AccountNumber = dbo.CustomerView.AccountNumber

    LEFT JOIN dbo.PlateView ON dbo.AssetView.InventoryID = dbo.PlateView.InventoryID)

    AS b ON a.InventoryID = b.InventoryID

    AND a.EntityType = 'Inventory'

    -- Begin PMF Code

    INNER JOIN

    (SELECTdbo.AssetView.InventoryID,

    dbo.AssetView.AccountNumber,

    dbo.AssetView.UnitNumber,

    dbo.CustomerView.CustomerName,

    dbo.PlateView.PlateNumber,

    dbo.PlateView.PlateState

    FROM #Inventory AS Inv

    WHERE Inv.InventoryID = PlateView.InventoryID)

    -- End PMF Code

    LEFT JOIN

    (SELECT dbo.LeaseView.LeaseID,

    dbo.LeaseView.UnitNumber,

    dbo.LeaseView.AccountNumber,

    CustomerView_3.CustomerName,

    PlateView_3.PlateNumber,

    PlateView_3.PlateState

    INTO #Lease

    FROM dbo.LeaseView

    LEFT JOIN dbo.CustomerView AS CustomerView_3 ON dbo.LeaseView.AccountNumber = CustomerView_3.AccountNumber

    LEFT JOIN dbo.PlateView AS PlateView_3 ON dbo.LeaseView.InventoryID = PlateView_3.InventoryID)

    AS c ON a.Leaseid = c.LeaseID

    AND a.EntityType = 'Lease'

    -- Begin PMF Code

    INNER JOIN

    (SELECT dbo.LeaseView.LeaseID,

    dbo.LeaseView.UnitNumber,

    dbo.LeaseView.AccountNumber,

    CustomerView_3.CustomerName,

    PlateView_3.PlateNumber,

    PlateView_3.PlateState

    FROM #Lease Les

    WHERE Les.InventoryID = PlateView_3.InventoryID

    -- End PMF Code

    LEFT JOIN

    (SELECT PlateView_2.PlateId,

    PlateView_2.UnitNumber,

    AssetView_2.AccountNumber,

    CustomerView_2.CustomerName,

    PlateView_2.PlateNumber,

    PlateView_2.PlateState

    INTO #Plates

    FROM dbo.PlateView AS PlateView_2

    LEFT JOIN dbo.AssetView AS AssetView_2 ON PlateView_2.UnitNumber = AssetView_2.UnitNumber

    LEFT JOIN dbo.CustomerView AS CustomerView_2 ON AssetView_2.AccountNumber = CustomerView_2.AccountNumber)

    AS d ON a.PlateID = d.PlateId

    AND a.EntityType = 'Plates'

    -- Begin PMF Code

    INNER JOIN

    (SELECT PlateView_2.PlateId,

    PlateView_2.UnitNumber,

    AssetView_2.AccountNumber,

    CustomerView_2.CustomerName,

    PlateView_2.PlateNumber,

    PlateView_2.PlateState

    FROM #Plates Pla

    WHERE Pla.UnitNumber = AssetView_2.UnitNumber

    -- End PMF Code

    LEFT JOIN

    (SELECT AssetView_1.InventoryID,

    AssetView_1.AccountNumber,

    AssetView_1.UnitNumber,

    CustomerView_1.CustomerName,

    PlateView_1.PlateNumber,

    PlateView_1.PlateState

    INTO #Customer

    FROM dbo.AssetView AS AssetView_1

    LEFT JOIN dbo.CustomerView AS CustomerView_1 ON AssetView_1.AccountNumber = CustomerView_1.AccountNumber

    LEFT JOIN dbo.PlateView AS PlateView_1 ON AssetView_1.InventoryID = PlateView_1.InventoryID)

    AS e ON a.IniventorySaleID = e.InventoryID

    AND a.EntityType = 'Inventory Sale'

    LEFT JOIN dbo.CustomerView AS f ON a.Custnmbr = f.CustomerID

    AND a.EntityType = 'Customer'

    -- Begin PMF Code

    INNER JOIN

    (SELECT AssetView_1.InventoryID,

    AssetView_1.AccountNumber,

    AssetView_1.UnitNumber,

    CustomerView_1.CustomerName,

    PlateView_1.PlateNumber,

    PlateView_1.PlateState

    FROM #Customer Cus

    WHERE Cus.AccountNumber = CustomerView_1.AccountNumber

    -- End PMF Code

    LEFT JOIN

    (SELECT dbo.InsuranceView.InsurancePolicyID,

    dbo.InsuranceView.AccountNumber,

    dbo.CustomerView.CustomerName

    INTO #InsurancePolicy InsP

    FROM dbo.InsuranceView

    LEFT JOIN dbo.CustomerView ON dbo.InsuranceView.CustomerID = dbo.CustomerView.CustomerID)

    AS g ON a.Insurance_policyId = g.InsurancePolicyID

    AND a.EntityType = 'Insurance Policy'

    LEFT JOIN dbo.Comment_Type AS h ON a.CommentTypeID = h.CommentTypeID

    -- Begin PMF Code

    INNER JOIN

    (SELECT dbo.InsuranceView.InsurancePolicyID,

    dbo.InsuranceView.AccountNumber,

    dbo.CustomerView.CustomerName

    FROM #InsurancePolicy InsP

    WHERE InsP.CustomerID = CustomerView.CustomerID

    -- End PMF Code

    WHERE (a.Comment <> ' ')

  • Thank you Lynn.

  • is it just me or does any one else notice on his query in the OP and the so far, he never closes any of the parentheses for the derived tables (the selects in the from statement)??


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • I fixed the parenthesis issue; I'm unsuccessful with getting the temp tables to work when executing the query as a whole. It works when I run them individually.

  • Because you can't select into in subqueries. A select into can only be in the outer select and can only appear once in a query.

    Create an explicit temp table for each subquery

    Pull each subquery out and insert the results of the queries into temp tables.

    Use the temp tables in the main query instead of the subqueries.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply