Code Optimization

Interesting things in software development and code optimization

MS SQL - Speed up Order By with OFFSET FETCH paging

UPDATE:

So adding nonclustered index, as the MS SQL Execution Plan suggests, seems reduced the query time even more, here is the suggested nonclustered index:

USE [myDB]

GO


CREATE NONCLUSTERED INDEX NONCLUSTERED_INDEX2_tblPictures

ON [dbo].[tblPictures] ([RatingTotal],[shape])

INCLUDE ([PictureID],[OwnerID],[PictureTypeID])

GO

but using only nonclustered index, without my #temp table technic, seems does not help too much.


Hello,

This time I'm going to share my solution on how I did reduce time of my SQL query with Order By clause by almost x2 times.

Firstly let me show my original query:

select @rowstotal = count(*)

From [dbo].[tblPictures]

where (@OwnerId = 0 OR @OwnerId = [OwnerID])

and (@FilterBy = 0 OR @FilterBy = PictureTypeID)

and (@Shape = '' OR @Shape = [shape])



Select

@rowstotal as TotalCount

, PictureID

, OwnerID

, PictureName

, [Description]

, Description2

, Description3

, aspectRatio

, [min]

, [max]

, [percent]

, thumbWidth

, thumbHeight

, processState

, keywords

From [dbo].[tblPictures]

where (@OwnerId = 0 OR @OwnerId = [OwnerID])

and (@FilterBy = 0 OR @FilterBy = PictureTypeID)

and (@Shape = '' OR @Shape = [shape])

Order By RatingTotal Desc

OFFSET @pageNumber ROWS FETCH NEXT @pageSize ROWS ONLY;


So to be able to calculate pages we have to know the total number of rows in the DB. Each row will contain TotalCount - total number of rows


(pay attention, that the techinc COUNT(*) OVER () as TotalCount is slower than just select @rowstotal = count(*) From that I use in my queries )


Then we select all needed columns with the order and paging technic.

Everything looks great, simple and fast unless you got more than 300 000 rows in the table.

Main problem here is that the Order By clause takes 98% of the whole stored procedure and in my case it takes 5 sec in total.

How to speed it up? 

I did google a lot of posts and almost everyone suggests to use indexes or nonclustered indexes, or other things.

As I'm not DBA and do not know a lot about all these things I decided to check what if I would select only one column instead of all of them? 

And when I got my 40 rows for one page I would select the rest of columns?

I did write some test query and had been surprised that it took almost x2 time less than before.


So here is the new query:

IF OBJECT_ID(N'tempdb..#tempPage', N'U') IS NOT NULL

DROP TABLE #tempPage;


create table #tempPage

(

TotalCount int null

, PictureID int

, OwnerID int null

, PictureName nvarchar(500) null

, [Description] nvarchar(500) null

, Description2 nvarchar(500) null

, Description3 nvarchar(max) null

, aspectRatio decimal(18,2) null

, [min] money null

, [max] money null

, [percent] decimal(18,2) null

, thumbWidth decimal(18,2) null

, thumbHeight decimal(18,2) null

, processState varchar(50) null

, keywords varchar(500) null

)


insert into #tempPage

Select

null --@rowstotal

,PictureID

,null

,null

,null

,null

,null

,null

,null

,null

,null

,null

,null

,null

,null

From [dbo].[tblPictures]

where (@OwnerId = 0 OR @OwnerId = [OwnerID])

and (@FilterBy = 0 OR @FilterBy = PictureTypeID)

and (@Shape = '' OR @Shape = [shape])

Order By RatingTotal Desc

OFFSET @pageNumber ROWS FETCH NEXT @pageSize ROWS ONLY


Update t

Set t.OwnerID = p.OwnerID

,t.TotalCount = @rowstotal

,t.PictureName=p.PictureName

,t.[Description]=p.[Description]

,t.Description2=p.Description2

,t.Description3=p.Description3

,t.aspectRatio=p.aspectRatio

,t.[min]=p.[min]

,t.[max]=p.[max]

,t.[percent]=p.percent

,t.thumbWidth=p.thumbWidth

,t.thumbHeight=p.thumbHeight

,t.processState=p.processState

,t.keywords=p.keywords

From #tempPage as t

INNER JOIN [dbo].[tblPictures] as p on p.PictureID = t.PictureID;

select * from #tempPage;

DROP TABLE #tempPage;


 Now we use a temp sql table to select only IDs firstly and then we update all 40 rows and set all other columns' values.

Also do not forget to drop temp table before and after to avoid existing table errors.


So this is the way that reduced my query time from 5 seconds to almost 2 seconds in total.


(SQL Server 2012 version: x64 11.0.3156.0)


Of course, if I would add nonclustered index, as MS SQL Graphical Execution Plan suggests, it may be even faster, but to be able to add them we have to understand what it is and how to use it. So maybe next step will be to learn and add nonclustered index ;)



Thank you and see you soon :)

 







1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



Comments (11) -

  • jual kaos online

    6/12/2017 5:50:45 PM | Reply

    I like this post, enjoyed this one thanks for putting up. "The difference between stupidity and genius is that genius has its limits." by Albert Einstein.

  • small antique desk

    9/4/2017 5:56:57 AM | Reply

    Magnificent goods from you, man. I've understand your stuff previous to and you're just extremely fantastic.

  • antiques

    9/5/2017 12:09:35 PM | Reply

    Wonderful goods from you, man. I have understand your stuff previous to and you are just extremely magnificent.

  • antique

    9/5/2017 9:09:03 PM | Reply

    Fantastic goods from you, man. I have understand your stuff previous to and you are just extremely great.

  • vintage

    9/7/2017 2:47:48 AM | Reply

    Magnificent goods from you, man. I've understand your stuff previous to and you are just too great.

  • war commander

    9/10/2017 2:16:21 PM | Reply

    Wonderful goods from you, man. I have understand your stuff previous to and you're just extremely great.

  • war commander

    9/10/2017 2:26:28 PM | Reply

    Fantastic goods from you, man. I've understand your stuff previous to and you are just too great.

  • war commander

    9/10/2017 4:36:09 PM | Reply

    Magnificent goods from you, man. I've understand your stuff previous to and you're just too excellent.

  • war commander

    9/10/2017 9:33:08 PM | Reply

    Excellent goods from you, man. I've understand your stuff previous to and you are just extremely excellent.

  • Kacie Fiely

    10/19/2017 7:35:22 PM | Reply

    Very interesting information!Perfect just what I was searching for!

    • okarpov

      10/20/2017 9:35:19 AM | Reply

      Thank you.
      Nice to hear I can help Smile

Loading