March 17, 2017 at 1:10 am
Hello Everyone,
I am facing some challenges with SQL performance, the database is SQL Azure. Please let me know if anyone has some suggesations to improve this performance,
Code block,
ALTER FUNCTION [dbo].[fnGetAllAccountLookup] (
@select_id nvarchar(100),
@status nvarchar(100),
@conditionIndex int,
@gatewayId nvarchar(100),
@search nvarchar(100),
@numberOfDayWindows int,
@accountid int,
@profileId nvarchar(100),
@groupId nvarchar(100),
@IsRegistered bit,
@MeterSizeIndex int,
@SegmentId int =-1
)
RETURNS @table TABLE (
MeterId int,
AccountId int,
ConsumerName nvarchar(max),
[Address] nvarchar(max),
ConsumerId nvarchar(max),
MeterSize nvarchar(100),
MeterSizeIndex int,
LCDRead float,
BillingRead float,
TotalConsumption float,
ReadDate datetime,
ConsumptionsFlags nvarchar(100),
DiagnosticsFlags nvarchar(100),
ProfileId nvarchar(100),
LastConsumptionsFlagsDays nvarchar(100),
IgnoredConsumptionFlags nvarchar(100),
GatewayId nvarchar(100),
EndpointType nvarchar(50),
IsUnauthorized int,
BackflowTrigger float,
LeakRate float,
MaxTemperature int,
MinTemperature int,
Rssi int,
GroupIds nvarchar(300),
UnitRegMemo nvarchar(100),
SegmentIds nvarchar(max)
)
AS
BEGIN
Declare @tmpCustomer TABLE
(
MeterId int,
AccountId int,
ConsumerName nvarchar(max),
[Address] nvarchar(max),
ConsumerId nvarchar(max),
MeterSize nvarchar(100),
MeterSizeIndex int,
LCDRead float,
BillingRead float,
TotalConsumption float,
ReadDate datetime,
ConsumptionsFlags nvarchar(100),
DiagnosticsFlags nvarchar(100),
ProfileId nvarchar(100),
LastConsumptionsFlagsDays nvarchar(100),
IgnoredConsumptionFlags nvarchar(100),
GatewayId nvarchar(100),
EndpointType nvarchar(50),
IsUnauthorized int,
BackflowTrigger float,
LeakRate float,
MaxTemperature int,
MinTemperature int,
Rssi int,
GroupIds nvarchar(300),
UnitRegMemo nvarchar(100),
SegmentIds nvarchar(max)
)
Declare
@persistConsumptionFlags varchar(max),
@persistedDayWindows int
SELECT
@persistConsumptionFlags = persistConsumptionFlags
FROM tblAccountSettings
WHERE AccountId = @accountId
SELECT
@persistedDayWindows = Item
From [SplitFunction_Index](@persistConsumptionFlags, '|')
WHERE ID = @conditionIndex
IF @conditionIndex >=17 and @conditionIndex <= 19
SET @persistedDayWindows = null
SET @search = LTRIM(RTRIM(@search))
Declare @searchPattern nvarchar(50) = '%'+ @search +'%'
IF @profileId IS NOT NUll OR @groupId IS NOT NUll
BEGIN
SET @select_id = ''
END
INSERT INTO @tmpCustomer
SELECT
MeterId,
AccountId,
CONVERT(nvarchar(max), DecryptByKey(EncryptConsumerName)) AS ConsumerName,
CONVERT(nvarchar(max), DecryptByKey(EncryptAddress)) As [Address],
CONVERT(nvarchar(max), DecryptByKey(EncryptConsumerId)) As ConsumerId,
MeterSize,
MeterSizeIndex,
LCDRead,
BillingRead,
TotalConsumption,
ReadDate,
ConsumptionsFlags,
DiagnosticsFlags,
ProfileId,
LastConsumptionsFlagsDays,
IgnoredConsumptionFlags,
GatewayId,
EndpointType,
IsUnauthorized,
BackflowTrigger,
LeakRate,
MaxTemperature,
MinTemperature,
Rssi,
GroupIds,
UnitRegMemo,
SegmentIds
FROM [dbo].[ConsumerDetails] WITH (NOLOCK)
WHERE IsRegistered=@IsRegistered AND AccountId=@accountid
AND ((@numberOfDayWindows >= case when @status='Billing' then DATEDIFF(DAY,DailyChirpDate,DATEADD(DAY,-1,GETDATE())) else @numberOfDayWindows end)
AND (@numberOfDayWindows < case when @status='Missing' then DATEDIFF(DAY,DailyChirpDate,DATEADD(DAY,-1,GETDATE())) else @numberOfDayWindows+1 end))
AND (MeterSizeIndex like case when @MeterSizeIndex=-1 then MeterSizeIndex else @MeterSizeIndex end)
AND (MeterId like @searchPattern OR ConsumerId like @searchPattern OR ConsumerName like @searchPattern OR [Address] like @searchPattern
OR CONVERT(nvarchar(max), DecryptByKey(EncryptConsumerName)) like @searchPattern OR CONVERT(nvarchar(max), DecryptByKey(EncryptConsumerId)) like @searchPattern OR CONVERT(nvarchar(max), DecryptByKey(EncryptAddress)) like @searchPattern)
AND (
( @conditionIndex is NOT NULL AND
(
1= (select SUBSTRING(ConsumptionsFlags, @conditionIndex, 1))
OR
(@persistedDayWindows > = ( SELECT Item FROM [SplitFunction_Index](LastConsumptionsFlagsDays, '|') WHERE Id = @conditionIndex AND Item <> 0)))
)
OR
(
@conditionIndex =0 AND
(0 = dbo.[FNFindPersistedConditionCount](LastConsumptionsFlagsDays, '|',@accountid) AND
(0 = dbo.[IsCurrentConditionSet](ConsumptionsFlags,1))
)
) OR
(@conditionIndex is NULL)
)
IF @select_id='All'
BEGIN
INSERT INTO @table
SELECT *
FROM @tmpCustomer
END
ELSE IF @select_id='AIM'
BEGIN
INSERT INTO @table
SELECT *
FROM @tmpCustomer
WHERE ProfileId is Not Null and ProfileId > 0
END
ELSE IF @select_id='AIMNB'
BEGIN
INSERT INTO @table
SELECT *
FROM @tmpCustomer
WHERE (ProfileId is Null AND (ConsumerName IS NOT NULL OR [Address] IS NOT NULL OR ConsumerId IS NOT NULL ) )
END
ELSE IF @select_id='AllGateWays'
BEGIN
INSERT INTO @table
SELECT *
FROM @tmpCustomer
WHERE ((GatewayID IS NOT NUll) AND GatewayID = CASE WHEN @GatewayID IS NOT NUll THEN @GatewayID ELSE GatewayID END)
END
ELSE
BEGIN
-- Use this Block for all param like @ProfileId, @groupId, @gatewayId if they are not null
INSERT INTO @table
SELECT *
FROM @tmpCustomer
WHERE
CASE
WHEN @GatewayID IS NUll AND GatewayID IS NULL THEN 0
ELSE
CASE
WHEN @GatewayID IS NUll AND GatewayID IS NOT NULL THEN GatewayID
ELSE @GatewayID
END
END =
CASE
WHEN GatewayID IS NULL THEN 0
ELSE GatewayID
END
AND CASE
WHEN @profileId IS NUll AND ProfileId IS NULL THEN 0
ELSE
CASE
WHEN @profileId IS NUll AND ProfileId IS NOT NULL THEN ProfileId
ELSE @profileId
END
END =
CASE
WHEN ProfileId IS NULL THEN 0
ELSE ProfileId
END
AND CASE
WHEN @groupId IS NUll THEN 0
ELSE
@groupId
END
=
CASE
WHEN @groupId IS NUll THEN 0
ELSE (SELECT Item FROM [SplitFunction](GroupIds, ',') WHERE Item = @groupId)
END
AND
@SegmentId
=
CASE
WHEN @SegmentId = -1 THEN - 1
ELSE (SELECT Item FROM [SplitFunction](SegmentIds, ',') WHERE Item = @SegmentId)
END
END
RETURN
END
Also attaching the table creation script.
Thank you in advance for your help.
March 17, 2017 at 9:45 am
This is a catch-all query and it's a whopper. Read the linked article.
What's the minimum number of rows that this is likely to return? And the maximum?
The execution plans for these two extremes, and just about everything else in between, are unlikely to be the same so it will always suck for performance because it's using the wrong execution plan. There are numerous other issues, notably non-SARGable predicates.
This doesn't necessarily mean a full rewrite. I'd start with something simple like this:
SELECT *
INTO #ConsumerDetails
FROM [dbo].[ConsumerDetails]
WHERE IsRegistered = @IsRegistered
AND AccountId = @accountid
OPTION(RECOMPILE)
- then select from #ConsumerDetails with all the filters. Try a couple of different clustered indexes on the temp table (don't bother with non-clustered). Also note that NULL handling in this function is primitive: try this:
WHERE
@GatewayID IS NULL OR GatewayID = @GatewayID-which means: if the parameter is null then ignore it, otherwise filter the column by the parameter. This should get you started:
WHERE (@select_id = 'All')
OR (@select_id = 'AIM' AND ProfileId > 0)
OR (@select_id='AIMNB' AND ProfileId IS NULL AND (ConsumerName IS NOT NULL OR [Address] IS NOT NULL OR ConsumerId IS NOT NULL ) )
OR (@select_id='AllGateWays' AND (@GatewayID IS NULL OR @GatewayID = GatewayID))
If you want performance then I think you will have to forget about setting this up as a table-valued function and roll it into your sprocs instead.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 21, 2017 at 6:03 am
Hi <a title="Go to ChrisM@Works,
Thank you for your feedback, we will use it and will let you know about the result.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply