March 18, 2021 at 5:05 am
I have tuned most of the parts what else can be done here:
Declare @GetQuery nvarchar(max)
declare @f_snf varchar(20) = @fid
Set @GetQuery='
Select [bookingId] as id
,[bookingTitle] as [text]
, bk.[startDate] as start
,[endDate] as [end]
,[userId] as [resource]
,[roleId]
,[jobCode]
,[bookingType]
,[billableType]
,[taskId]
,[minutesDay]
,[minutesPerDay]
,[totalBucketMinutesAllocation]
,[percentAllocation]
,[includeSaturday]
,[includeSunday]
,[includeHoliday]
,[details]
,[firmid]
,[NoOFHours]
,[bookingJson]
,tm.client_id as clientId,
(case when (cm.last_corporate_name + '','' + cm.first_name +'' ''+ cm.middle_name +'',''+ Cm.generation_type)
Like ''%, ,'' then cm.last_corporate_name
When (cm.last_corporate_name + '','' + cm.first_name +'' ''+ cm.middle_name +'',''+ cm.generation_type)
Like ''%,'' then (cm.last_corporate_name + '','' + cm.first_name +'' ''+ cm.middle_name )
Else (cm.last_corporate_name + '','' + cm.first_name +'' ''+ cm.middle_name+'',''+ cm.generation_type) end)clientName,
cm.account_number as AccountNumber,
ft.task_code as tasktype,
convert(varchar,tm.period_enddate,101) as PE,
'''' as jobcode_name,
tm.task_desc,
(select cUserFullName from DBO.usm where firm_id=bk.firmid and user_id=bk.[userId]) as username
From
dbo.Booking bk
Inner join dbo.tam tm on tm.firm_id = bk.firmid and bk.taskId = tm.task_id
Inner join dbo.cam cm on cm.firm_id = tm.firm_id and cm.client_id = tm.client_id
Inner join dbo.fit ft on ft.firm_id = tm.firm_id and ft.tasktype_id = tm.tasktype_id
inner join dbo.Fispc fsc on fsc.firm_id = ft.firm_id and fsc.Category_id = ft.category_id AND fsc.is_active=1
Where firmID= '+@f_snf+' and isdeleted=''N'''
If(@startDate!='0')
Begin
Set @GetQuery= @GetQuery + ' and
(CONVERT(DATE,bk.startDate) between convert(datetime,'''+@StartDate+''') and convert(datetime,'''+@endDate+''') or
CONVERT(DATE,bk.enddate) between convert(datetime,'''+@StartDate+''') and convert(datetime,'''+@endDate+''') or
(CONVERT(DATE,bk.startDate)<convert(datetime,'''+@StartDate+''') and CONVERT(DATE,bk.endDate)>convert(datetime,'''+@endDate+''')))'
End
If(@bid!='0')
Begin
Set @GetQuery= @GetQuery + ' and bk.bookingId in ('+@bid+')'
End
If(@tid!='0')
Begin
Set @GetQuery= @GetQuery + ' and bk.taskid in ('+@tid+')'
End
If(@uid!='0')
Begin
Set @GetQuery= @GetQuery + ' and bk.userId in ('+@uid+')'
End
If(@Roleid!='0')
Begin
Set @GetQuery= @GetQuery + ' and bk.roleId in ('+@Roleid+')'
End
Declare @GetQueryNonTask nvarchar(max)
Set @GetQueryNonTask=' Union All
select [bookingId] as id
,[bookingTitle] as [text]
, bk.[startDate] as start
,[endDate] as [end]
,[userId] as [resource]
,[roleId]
,[jobCode]
,[bookingType]
,[billableType]
,[taskId]
,[minutesDay]
,[minutesPerDay]
,[totalBucketMinutesAllocation]
,[percentAllocation]
,[includeSaturday]
,[includeSunday]
,[includeHoliday]
,[details]
,[firmid]
,[NoOFHours]
,[bookingJson]
,NULL as clientId
,NULL as clientName
,NULL as AccountNumber
,NULL as tasktype
,NULL as PE
,'''' as jobcode_name
,NULL as task_desc
,(select cUserFullName from DBO.uam where firm_id=bk.firmid and user_id=bk.[userId]) as username
From
dbo.Booking bk
Where bk.taskid= 0 and bk.isdeleted=''N'' and bk.firmID= '+@f_snf+' '
If(@startDate!='0')
Begin
Set @GetQueryNonTask= @GetQueryNonTask + ' and
(CONVERT(DATE,bk.startDate) between convert(datetime,'''+@StartDate+''') and convert(datetime,'''+@endDate+''') or
CONVERT(DATE,bk.enddate) between convert(datetime,'''+@StartDate+''') and convert(datetime,'''+@endDate+''') or
(CONVERT(DATE,bk.startDate)<convert(datetime,'''+@StartDate+''') and CONVERT(DATE,bk.endDate)>convert(datetime,'''+@endDate+''')))'
End
If(@bid!='0')
Begin
Set @GetQueryNonTask= @GetQueryNonTask + ' and bk.bookingId in ('+@bid+')'
End
If(@tid!='0')
Begin
Set @GetQueryNonTask= @GetQueryNonTask + ' and bk.taskid in ('+@tid+')'
End
If(@uid!='0')
Begin
Set @GetQueryNonTask= @GetQueryNonTask + ' and bk.userId in ('+@uid+')'
End
set @GetQuery = @GetQuery + @GetQueryNonTask
Exec sp_executesql @GetQuery
March 18, 2021 at 11:58 am
Let's start with the importance of SQL Injection attacks, because you are absolutely open to them with this code. Especially the IN statements. That alone, you should rearchitect this.
Next, I suggest reading Gail Shaw's work on catch-all queries. That's certainly what you're attempting here. One query to rule them all. It never really works out well.
I'd suggest getting the WITH RECOMPILE hint worked into your existing code, assuming you're not going to follow the above advice.
Also, this:
CONVERT(DATE,bk.endDate)
Used in comparisons in the JOIN or WHERE clauses will prevent good statistics use and therefore, good index use. It's absolutely going to cause problems. If bk.endDate is not a DATE data type, make it one. Data conversions like this are calculations. A calculation in filtering criteria (ON, WHERE, HAVING) against columns hurts performance because the calculation, by it's nature, must be run against all rows, resulting in scans being the only way to retrieve data.
However, see the first two suggestions. This query shouldn't really be tuned. It should be replaced.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 18, 2021 at 12:17 pm
I came up with one version so far it works well. Will surely have a look at above suggestion
If( (@Bookingid!='0') and (@taskid!='0') and (@userid!='0') and (@Roleid!='0') and (@startDate!='0') )
begin
--print 'hi'
Select [bookingId] as id
,[bookingTitle] as [text]
, bk.[startDate] as start
,[endDate] as [end]
,[userId] as [resource]
,[roleId]
,[jobCode]
,[bookingType]
,[billableType]
,[taskId]
,[minutesDay]
,[minutesPerDay]
,[totalBucketMinutesAllocation]
,[percentAllocation]
,[includeSaturday]
,[includeSunday]
,[includeHoliday]
,[details]
,[firmid]
,[NoOFHours]
,[bookingJson]
,tm.client_id as clientId,
(case when (cm.last_corporate_name + ',' + cm.first_name +' '+ cm.middle_name +','+ Cm.generation_type)
Like '%, ,' then cm.last_corporate_name
When (cm.last_corporate_name + ',' + cm.first_name +' '+ cm.middle_name +','+ cm.generation_type)
Like '%,' then (cm.last_corporate_name + ',' + cm.first_name +' '+ cm.middle_name )
Else (cm.last_corporate_name + ',' + cm.first_name +' '+ cm.middle_name+','+ cm.generation_type) end)clientName,
cm.account_number as AccountNumber,
ft.task_code as tasktype,
convert(varchar,tm.period_enddate,101) as PE,
'' as jobcode_name,
tm.task_desc,
(select cUserFullName from DBO.user_master with(nolock) where firm_id=bk.firmid and user_id=bk.[userId]) as username
From
dbo.Booking bk with(nolock)
Inner join dbo.task_master tm with(nolock) on tm.firm_id = bk.firmid and bk.taskId = tm.task_id
Inner join dbo.client_master cm with(nolock) on cm.firm_id = tm.firm_id and cm.client_id = tm.client_id
Inner join dbo.firm_task ft with(nolock) on ft.firm_id = tm.firm_id and ft.tasktype_id = tm.tasktype_id
inner join dbo.Firm_specific_Category fsc with(nolock) on fsc.firm_id = ft.firm_id and fsc.Category_id = ft.category_id AND fsc.is_active=1
Where firmID= @firmid_snf and isdeleted='N'
and bk.bookingId in (@Bookingid)
and bk.taskid in (@taskid)
and bk.userId in (@userid)
and bk.roleId in (@Roleid)
and (CONVERT(DATE,bk.startDate) between convert(datetime,@StartDate) and convert(datetime,@endDate) or
CONVERT(DATE,bk.enddate) between convert(datetime,@StartDate) and convert(datetime,@endDate) or
(CONVERT(DATE,bk.startDate)<convert(datetime,@StartDate) and CONVERT(DATE,bk.endDate)>convert(datetime,@endDate)))
--UNION ALL
end
far
March 18, 2021 at 12:44 pm
Oh, don't do NOLOCK. Yeah, it will enhance performance because it changes how locking and blocking occurs within SQL Server. However, especially if you're getting lots of large scans, as I'm absolutely sure you are, you're going to see duplicate or missing data in the results. This is because it changes how locking & blocking occurs. During the scan, any page splits or rearrangements are ignored, therefore a given row can be read multiple times, or not read at all, depending.
Further, putting NOLOCK everywhere in your code just makes it extremely messy and much harder to clean up after the fact. If you're going to insist on NOLOCK ( and I think it's a poor choice), instead do this:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
It does the same thing, but gives you a single place to make a change when you realize just how dangerous using dirty reads can be.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply