April 1, 2010 at 9:03 am
Hi Folks,
I have a stored procedure to pull out some data, I don't know if it is efficient enough or not, I used paging with a CTE, I have attached the actual execution plan below, I used only 2 tables (Metric, User) in my db:
CREATE TABLE [dbo].[Metric](
[id] [int] IDENTITY(1,1) NOT NULL,
[type] [varchar](128) NULL,
[startdate_milliseconds] [numeric](15, 0) NULL,
[enddate_milliseconds] [numeric](15, 0) NULL,
[startdate] [datetime] NULL,
[enddate] [datetime] NULL,
[duration] [numeric](38, 0) NULL,
[success] [int] NULL,
[originaddress] [varchar](128) NULL,
[originhostname] [varchar](128) NULL,
[metriclevel] [int] NULL,
[errorcode] [int] NULL,
[createuserid] [varchar](128) NULL,
[description] [varchar](5000) NULL,
[Status] [varchar](50) NOT NULL CONSTRAINT [DF_Metric_Status] DEFAULT ('Pending'),
[Comment] [varchar](1000) NOT NULL CONSTRAINT [DF_Metric_Comment] DEFAULT (''),
[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_Metric_CreatedOn] DEFAULT (getdate()),
[LastUpdatedOn] [datetime] NOT NULL CONSTRAINT [DF_Metric_LastUpdatedOn] DEFAULT (getdate()),
[LastUpdatedBy] [varchar](50) NOT NULL CONSTRAINT [DF_Metric_LastUpdatedBy] DEFAULT (suser_sname()),
CONSTRAINT [PK_Metric] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[User](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Login] [varchar](54) NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
[Email] [varchar](255) NOT NULL,
[RoleID] [int] NOT NULL,
[PID] [int] NOT NULL,
[Category] [varchar](50) NULL,
[Division] [varchar](50) NULL,
[Supervisor] [varchar](50) NULL,
[SupervisorID] [int] NULL,
[Notification] [bit] NOT NULL CONSTRAINT [DF_User_Notification] DEFAULT ((0)),
[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_User_CreatedOn] DEFAULT (getdate()),
[CreatedBy] [varchar](50) NOT NULL CONSTRAINT [DF_User_CreatedBy] DEFAULT (suser_sname()),
[LastModifiedOn] [datetime] NOT NULL CONSTRAINT [DF_User_LastModifiedOn] DEFAULT (getdate()),
[LastModifiedBy] [varchar](max) NOT NULL CONSTRAINT [DF_User_LastModifiedBy] DEFAULT (suser_sname()),
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Table Metric has another index IDX_Type (Non-Unique, Non-Clustered, includes type, createuserid, status)
My sp is:
USE [Cloakware]
GO
/****** Object: StoredProcedure [dbo].[spListRecordsByUserIDNew1] Script Date: 04/01/2010 10:44:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spListRecordsByUserIDNew1](@day int, @status varchar(20), @user-id int, @pgSize int=25, @pgNbr int=1)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @role varchar(20)
declare @Manager varchar(50)
select @role = [Role] from [User] u
inner join [Role] r on r.RoleID = u.RoleID where u.[login] = suser_sname()
CREATE TABLE #temp(
[Checked] bit,
[ID] varchar(10),
[Status] [varchar](50),
HostName [varchar](50),
[FCID] [varchar](50),
[PLOG] [varchar](100),
[StartDate] datetime,
[ApplicationName] [varchar](50),
[Reason] [varchar](100) ,
[CreateUserID] [varchar](50),
UserID int,
[Manager] [varchar](50)
)
CREATE INDEX IXC_LastName ON #temp(Manager) include (UserID)
--1. First of all, insert all the records in #temp table
insert into #temp
--Status = 'Pending'
SELECT distinct 0, m.ID, Status,
dbo.fnReadField(description, 'TargetServer.hostName') as HostName,
FCID = case when type = 'viewAccountPassword' then Substring(dbo.fnReadField(description, 'TargetAccount.userName'), 1, 50) else ''end,
PLOG = case when type = 'viewAccountPassword' then Substring(dbo.fnReadField(description, 'referenceCode'), 1, 50) else ''end,
StartDate,
dbo.fnReadField(description, 'TargetApplication.name') as ApplicationName,
Reason = case when Substring(dbo.fnReadField(description, 'reasonDetails'), 1, 50) <> '' then Substring(dbo.fnReadField(description, 'reasonDetails'), 1, 50) + '...'
else '' end,
CreateUserID,
u1.ID,
Manager = case when u2.firstname + ' ' + u2.lastname is not null then u2.firstname + ' ' + u2.lastname else 'Unknown' end
from Metric m
left outer join u1 on m.createuserid = SUBSTRING(u1.[login], CHARINDEX('\',u1.[login]) + 1, len(u1.[login]))
left outer join u2 on u2. firstname + ' ' + u2.lastname = u1.supervisor
where dbo.fnGetDateDiff(m.id) <= case when @day = 0 then 365 else @day end
and type = 'viewAccountPassword'
and [Status] = 'Pending'
--order by m.lastupdatedon desc, m.startdate desc
insert into #temp
--Status = 'Approved' or 'Condition'
SELECT distinct 0, m.ID, Status,
dbo.fnReadField(description, 'TargetServer.hostName') as HostName,
FCID = case when type = 'viewAccountPassword' then Substring(dbo.fnReadField(description, 'TargetAccount.userName'), 1, 50) else ''end,
PLOG = case when type = 'viewAccountPassword' then Substring(dbo.fnReadField(description, 'referenceCode'), 1, 50) else ''end,
StartDate,
dbo.fnReadField(description, 'TargetApplication.name') as ApplicationName,
Reason = case when Substring(dbo.fnReadField(description, 'reasonDetails'), 1, 50) <> '' then Substring(dbo.fnReadField(description, 'reasonDetails'), 1, 50) + '...'
else '' end,
CreateUserID,
u1.ID,
Manager = case when u3.firstname + ' ' + u3.lastname is not null then u3.firstname + ' ' + u3.lastname else 'Unknown' end
from Metric m
left outer join u1 on m.createuserid = SUBSTRING(u1.[login], CHARINDEX('\',u1.[login]) + 1, len(u1.[login]))
left outer join u2 on u2. firstname + ' ' + u2.lastname = u1.supervisor
left outer join u3 on u3.[login] = m.lastupdatedby
where dbo.fnGetDateDiff(m.id) <= case when @day = 0 then 365 else @day end
and type = 'viewAccountPassword'
and ([Status] = 'Approved' or [Status] = 'Condition' )
--2.using a CTE (Common Table Expression) to create row numbering
DECLARE @NbrPages INT
SELECT @NbrPages = CEILING(count(*)/(@pgSize*1.0)) FROM #temp
;WITH PagingCTE (Row_ID,ID,Status,HostName, FCID,PLOG,StartDate,ApplicationName,Reason,CreateUserID,UserID, Manager )
AS
(
SELECT ROW_NUMBER() OVER(Order By ID) AS [Row_ID], ID,Status,HostName, FCID,PLOG,StartDate,ApplicationName,Reason,CreateUserID, UserID, Manager
FROM #temp
)
--3. using (@UserID = 0 or UserID = @user-id) in where clause to filter out all user(Admin/Auditor view) or single user(Manager view)
SELECT Row_ID,ID,Status,HostName, FCID,PLOG,StartDate,ApplicationName,Reason,CreateUserID, Manager
FROM PagingCTE
WHERE Row_ID >= (@pgSize * @pgNbr) - (@pgSize -1) AND
Row_ID <= @pgSize * @pgNbr And
Status like case when @status <> 'All' then @status else '%%' end And
(@UserID = 0 or UserID = @user-id)
drop table #temp
END
The sp should return the specific page result, it's kind of slow than I expect, can anyone provide any suggestion on how to improve it? Thank you very much.
April 1, 2010 at 1:01 pm
Can you send the xml of the actual execution plan.
April 1, 2010 at 1:20 pm
<MissingIndex Database="[Cloakware]" Schema="[dbo]" Table="[Metric]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[type]" ColumnId="2" />
<Column Name="[Status]" ColumnId="15" />
</ColumnGroup>
<ColumnGroup Usage="INCLUDE">
<Column Name="[id]" ColumnId="1" />
<Column Name="[startdate]" ColumnId="5" />
<Column Name="[createuserid]" ColumnId="13" />
<Column Name="[description]" ColumnId="14" />
Try creating the above index.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2010 at 7:10 am
where dbo.fnGetDateDiff(m.id) <= case when @day = 0 then 365 else @day end
it will force the optimizer NOT to use seek operation.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 6, 2010 at 8:46 pm
Try your query by removing distinct
April 6, 2010 at 9:43 pm
Have any of our suggestions helped you with this query?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 8, 2010 at 8:40 am
1) the function could be a big problem here. Not sure of the code for it
2) I doubt the index on your temp table is helpful
3) why use a temp at all? can you refactor to eliminate it completely?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 8, 2010 at 8:49 am
Agree with what everyone else has said;
From a quick look, these joins do not really seem ideal for performance, perhaps consider a computed column with an index.
left outer join u1 on m.createuserid = SUBSTRING(u1.[login], CHARINDEX('\',u1.[login]) + 1, len(u1.[login]))
left outer join u2 on u2. firstname + ' ' + u2.lastname = u1.supervisor
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply