April 12, 2012 at 8:24 am
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
(SELECT dbo.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.
April 12, 2012 at 8:35 am
Please provide DDL and sample data.
Also, it would be helpful to attach a query plan so we can see what's going on.
April 12, 2012 at 8:36 am
Please post table definitions, index definitions and execution plan, as per http://qa.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
April 12, 2012 at 8:39 am
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).
April 12, 2012 at 8:46 am
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.
April 12, 2012 at 9:05 am
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
April 12, 2012 at 9:09 am
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
April 12, 2012 at 9:56 am
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
(SELECT dbo.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
(SELECT dbo.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 <> ' ')
April 12, 2012 at 9:58 am
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
(SELECT dbo.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
(SELECT dbo.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 <> ' ')
April 12, 2012 at 10:01 am
Thank you Lynn.
April 12, 2012 at 10:39 am
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 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]
April 12, 2012 at 11:07 am
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.
April 12, 2012 at 11:46 am
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply