Code Optimization

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

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

Hi friends,

today we are going to work on something that looks and sounds crazy, we are going to speed up multiplication and division.

First, lets look into a code that we will try to speed up: 

            double p1 = 0, p2 = 0;
            int count = 100000000;

            Stopwatch sw = new Stopwatch();
            Int32 a = 0, b = 0;
            sw.Start();
            for (int i = 0; i < count; i++)
            {
                a = i;
                a *= 2;
                b += a;
            }
            sw.Stop();
            p1 = sw.ElapsedMilliseconds;
            Console.WriteLine(sw.ElapsedMilliseconds + " : " + a);

As you can see the code just measures time that multiplication takes, on my PC this cycle takes about 160 ms.

Now lets add similar code but optimized and we are expecting it will be faster: 

-

        static void Main(string[] args)
        {
            double p1 = 0, p2 = 0;
            int count = 100000000;

            Stopwatch sw = new Stopwatch();
            Int32 a = 0, b = 0;

            sw.Start();
            for (int i = 0; i < count; i++)
            {
                a = i;
                //multiply by 2
                a *= 2;
                b += a;
            }
            sw.Stop();
            p1 = sw.ElapsedMilliseconds;
            Console.WriteLine(sw.ElapsedMilliseconds + " : " + a + " : " + b);
            sw.Reset();
            sw.Start();
            b = 0;
            for (int i = 0; i < count; i++)
            {
                a = i;
                //multiply by 2
                a <<= 1;
                b += a;
            }
            sw.Stop();
            p2 = sw.ElapsedMilliseconds;
            Console.WriteLine(sw.ElapsedMilliseconds + " : " + a + " : " + b);
            Console.WriteLine("percent faster: " + 100.0 / p1 * (p1 - p2));
            Console.WriteLine("times faster: " + (p1 / p2));
            Console.ReadLine();
        }

If you will compile it and run (make sure you compile it in Release mode and run outside of Visual Studio) you will see almost no difference

in speed between these two cases of multiplication. On my PC it shows something like that:

161 : 199999998 : 1774919424
157 : 199999998 : 1774919424
percent faster: 2.48447204968944
times faster: 1.02547770700637

but we can say that the result almost the same. Interesting and not really, but do not be  sad, every next step will amaze you ;)

Another type

So, the next step is to test it with another type of integer - Int64 (or long), and what we see? We see that now it is about two times faster, here is my result:

846 : 199999998 : 9999999900000000
342 : 199999998 : 9999999900000000
percent faster: 59.5744680851064
times faster: 2.47368421052632

ha, interesting? So using simple bit scrolling to multiply integer value by 2, 4, 8, 16 ... much faster than multiplication itself.

Lets continue with division.

Division

Ok, now, lets go through the same steps but this time with division, here is the code:

        static void Main(string[] args)
        {
            double p1 = 0, p2 = 0;
            int count = 100000000;

            Stopwatch sw = new Stopwatch();
            Int32 a = 0, b = 0;

            sw.Start();
            for (int i = 0; i < count; i++)
            {
                a = i;
                //div by 2
                a /= 2;
                b += a;
            }
            sw.Stop();
            p1 = sw.ElapsedMilliseconds;
            Console.WriteLine(sw.ElapsedMilliseconds + " : " + a + " : " + b);

            sw.Reset();
            sw.Start();
            b = 0;
            for (int i = 0; i < count; i++)
            {
                a = i;
                //div by 2
                a >>= 1;
                b += a;
            }
            sw.Stop();
            p2 = sw.ElapsedMilliseconds;
            Console.WriteLine(sw.ElapsedMilliseconds + " : " + a + " : " + b);
            Console.WriteLine("percent faster: " + 100.0 / p1 * (p1 - p2));
            Console.WriteLine("times faster: " + (p1 / p2));
            Console.ReadLine();
        }

Compile and run it and you will see that the speed also almost the same:

180 : 49999999 : -1728753792
158 : 49999999 : -1728753792
percent faster: 12.2222222222222
times faster: 1.13924050632911

And as you may expect changing from Int32 to Int64 should be even more faster, and this is true, and in my case it more than 8 times faster:

2978 : 49999999 : 2499999950000000
344 : 49999999 : 2499999950000000
percent faster: 88.4486232370719
times faster: 8.65697674418605

So, the main thing here is that multiplication and division on non-native types are complex operations that involve more processor consumption than simple bit scrolling.

On the other hand the Int64 type is not native type for 32 bit environment and it takes more time to operate with it.

But if you will compile this code as x64 and run it under 64 bit operating system, you will see completely another result, here is mine:

229 : 49999999 : 2499999950000000
151 : 49999999 : 2499999950000000
percent faster: 34.061135371179
times faster: 1.51655629139073

this is because of under 64 bit operating system the Int32 and Int64 are native types (means a CPU register may handles whole value).

One more thing is the Any CPU compilation - it seems not so good as you may expect.

Thank you, and do not hesitate to comment it.

1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y