Hi folks,while loop taking so much time,need to improve the performance

Transact-SQL
1

declare @projectname as nvarchar(max)='DBEU01001',
@dbversion as nvarchar(max)='5d676dc9',
@boxid as nvarchar(max)=null,
@startdate as nvarchar(max)='2020-01-01',
@enddate as nvarchar(max)='2020-01-31'

declare @result TABLE(
RowID int not null primary key identity(1,1),
recordid int,
projectname nvarchar(1000),
apiname nvarchar(100),
device nvarchar(50),
brand nvarchar(max),
model nvarchar(max),
deviceid nvarchar(max),
searchdate datetime ,
feedback nvarchar(10),
details nvarchar(max)
)
declare @minrow int
declare @maxrow int
declare @recid int
declare @json nvarchar(max)
declare @loopcounter int

insert into @result
SELECT SearchLog.SearchLogId,ProjectDetails.ProjectName,'Auto Search' as apiname, SearchLog.Device, SearchLog.Brand, SearchLog.Model, DeviceAuthentication.BoxId, SearchLog.CreatedDate,
'' as feedback,'' as details
FROM SearchLog INNER JOIN
DeviceAuthentication ON SearchLog.FK_DeviceId = DeviceAuthentication.DeviceID INNER JOIN
ProjectInfo ON DeviceAuthentication.FK_ProjectID = ProjectInfo.ProjectInfoId INNER JOIN
ProjectDetails ON ProjectInfo.FK_ProjectId = ProjectDetails.ProjectId
WHERE (SearchLog.Model is NULL) and
(ProjectDetails.ProjectName =@projectname) and (ProjectInfo.EmbeddedDBVersion=@dbversion) and
--(SearchLog.CreatedDate BETWEEN @startdate AND @enddate)
(convert(date,SearchLog.CreatedDate,105) BETWEEN @startdate AND @enddate)
select @minrow= MIN(RowId) from @result
select @maxrow= MAX(RowId) from @result
set @loopcounter=@minrow
--below while loop takes times
while(@loopcounter>=@minrow and @loopcounter<=@maxrow)
begin
select
@recid= recordid
from @result
where RowID=@loopcounter

set @Json = (
select SearchLog.Device,SearchLog.Brand,SearchLog.Model,SearchLog.VendorId,SearchLog.OSD,SearchLog.Edid from SearchLog
where SearchLog.SearchLogId=@recid
FOR JSON AUTO
)
update @result
set
details=@Json
where recordid=@recid

set @loopcounter=@loopcounter+1
end

select * from @result

2

Hello there, welcome!

Can you please give us permission to your SQL server?

1 Like
3

Hi Yosiasz,

Thank you for the reply,

the problem with the above Query is only the below part can you please help me to write the below query in other way which takes time in ms.

while(@loopcounter>=@minrow and @loopcounter<=@maxrow)
begin
select
@recid= recordid
from @result
where RowID=@loopcounter

set @Json = (
select SearchLog.Device,SearchLog.Brand,SearchLog.Model,SearchLog.VendorId,SearchLog.OSD,SearchLog.Edid from SearchLog
where SearchLog.SearchLogId=@recid
FOR JSON AUTO
)
update @result
set
details=@Json
where recordid=@recid

set @loopcounter=@loopcounter+1
end

4

I would avoid loop altogether and directly update @result

update tgt
set tgt.details=src.Json
From @result tgt join
(
SearchLog.Device,SearchLog.Brand,SearchLog.Model,SearchLog.VendorId,SearchLog.OSD,SearchLog.Edid from SearchLog FOR JSON AUTO ) src on tgt.recordid = src.searchLogId

Type of thing, I am not at my computer. Good night

5

Yosiasz,

i have added your part of query in mine it leads to an error.

error description.

Msg 8155, Level 16, State 2, Line 46
No column name was specified for column 1 of 'src'.
Msg 207, Level 16, State 1, Line 46
Invalid column name 'SearchLogId'.

declare @projectname as nvarchar(max)='DBEU01001',
@dbversion as nvarchar(max)='5d676dc9',
@boxid as nvarchar(max)=null,
@startdate as nvarchar(max)='2020-01-01',
@enddate as nvarchar(max)='2020-01-31'

declare @result TABLE(
RowID int not null primary key identity(1,1),
recordid int,
projectname nvarchar(1000),
apiname nvarchar(100),
device nvarchar(50),
brand nvarchar(max),
model nvarchar(max),
deviceid nvarchar(max),
searchdate datetime ,
feedback nvarchar(10),
details nvarchar(max)
)
declare @minrow int
declare @maxrow int
declare @recid int
declare @json nvarchar(max)
declare @loopcounter int

insert into @result
SELECT SearchLog.SearchLogId,ProjectDetails.ProjectName,'Auto Search' as apiname, SearchLog.Device, SearchLog.Brand, SearchLog.Model, DeviceAuthentication.BoxId, SearchLog.CreatedDate,
'' as feedback,'' as details
FROM SearchLog INNER JOIN
DeviceAuthentication ON SearchLog.FK_DeviceId = DeviceAuthentication.DeviceID INNER JOIN
ProjectInfo ON DeviceAuthentication.FK_ProjectID = ProjectInfo.ProjectInfoId INNER JOIN
ProjectDetails ON ProjectInfo.FK_ProjectId = ProjectDetails.ProjectId
WHERE (SearchLog.Model is NULL) and
(ProjectDetails.ProjectName =@projectname) and (ProjectInfo.EmbeddedDBVersion=@dbversion) and
(convert(date,SearchLog.CreatedDate,105) BETWEEN @startdate AND @enddate) option(recompile)

update tgt
set tgt.details=src.Json
From @result tgt join
(
SearchLog.Device,SearchLog.Brand,SearchLog.Model,SearchLog.VendorId,SearchLog.OSD,SearchLog.Edid
from SearchLog FOR JSON AUTO
) src on tgt.recordid = src.searchLogId

select * from @result

6

maybe this?

update tgt
set tgt.details=src._json
From @result tgt join
(
  select sl1.searchLogId,
         (select SearchLog.Device,SearchLog.Brand,SearchLog.Model,
SearchLog.VendorId,SearchLog.OSD,SearchLog.Edid 
		    from SearchLog sl2 
		   where sl2.searchLogId = sl1.searchLogId
FOR JSON AUTO
		  ) as _json
   from SearchLog sl1
) src on tgt.recordid = src.searchLogId
1 Like
7

First things first - lets cleanup your select statement so it is a bit easier to read:

 Select sl.SearchLogId
      , pjd.ProjectName
      , 'Auto Search' As apiname
      , sl.Device
      , sl.Brand
      , sl.Model
      , da.BoxId
      , sl.CreatedDate
      , '' As feedback
      , '' As details
   From SearchLog                        sl
  Inner Join DeviceAuthentication        da On da.DeviceID = sl.FK_DeviceId
  Inner Join ProjectInfo                 pj On pj.ProjectInfoId = da.FK_ProjectID
  Inner Join ProjectDetails             pjd On pjd.ProjectId = pj.FK_ProjectId
  Where (sl.Model Is Null)
    And (pjd.ProjectName = @projectname)
    And (pj.EmbeddedDBVersion = @dbversion)
    And --(sl.CreatedDate BETWEEN @startdate AND @enddate)
        (convert(date, sl.CreatedDate, 105) Between @startdate And @enddate)

We can no see a big problem with this portion - that is the statement (convert(date, sl.CreatedDate, 105) Between @startdate And @enddate)

Which leads to the problem of comparing dates as strings...

This should be changed to:

AND sl.CreatedDate >= @startdate 
And sl.CreatedDate < dateadd(day, 1, @enddate)

The variables @startdate and @enddate can then be set to the appropriate data type, which will be the same as the column CreatedDate. This should improve the performance of that select statement if there are any indexes available on CreatedDate.

Now - we can add in your JSON detail data using CROSS APPLY:

 Select sl.SearchLogId
      , pjd.ProjectName
      , 'Auto Search' As apiname
      , sl.Device
      , sl.Brand
      , sl.Model
      , da.BoxId
      , sl.CreatedDate
      , '' As feedback
      , dtl.jsonValue As details
   From SearchLog                        sl
  Inner Join DeviceAuthentication        da On da.DeviceID = sl.FK_DeviceId
  Inner Join ProjectInfo                 pj On pj.ProjectInfoId = da.FK_ProjectID
  Inner Join ProjectDetails             pjd On pjd.ProjectId = pj.FK_ProjectId

  Cross Apply (Select jsonValue = (
                        Select sl2.Device
                             , sl2.Brand
                             , sl2.Model
                             , sl2.VendorId
                             , sl2.OSD
                             , sl2.Edid
                          From SearchLog         sl2
                         Where sl2.SearchLogId = sl.SearchLogId
                           For JSON Auto)
              )                         dtl

  Where sl.Model Is Null
    And pjd.ProjectName = @projectname
    And pj.EmbeddedDBVersion = @dbversion
    And sl.CreatedDate >= @startDate
    And sl.CreatedDate <  dateadd(day, 1, @enddate);

I am going to assume that this is just a portion of the code - and that your next step will be to 'update' the feedback column. Instead of performing an update, just add another CROSS APPLY and return all of the results you need in a single query.

If that doesn't perform well enough - then we can look at other alternatives to improve performance, but that will require more information (execution plans, table definitions, indexes, etc...).

2 Likes
8

HI jeffw8713,

Thanks for the reply,

i have used query it is taking exactly one minute if i add cross apply for another column "feedback" than may be it takes few more seconds.

can we have any other alternate way ?

Thank you

9

yosiasz,

Thanks for the reply,

i have used your query it works but it is taking 2 and half minutes.

Thank you

10

As I stated before - if that did not resolve your issues then we would need additional information. The execution plan would be a good start - along with the table and index definitions.

BTW - what is the difference in performance between my version and your original version?

11

jeffw8713,

i was just had an doubt since we are not updating feedback column as of now may be in future any way your query is returning exactly what i need.

Earlier it was time out,your's and yosiasz query helped me to complete my task thanks for your support.