Solved

Increase performance of SQL

Posted on 2016-08-01
4
51 Views
Last Modified: 2016-08-01
Hi,

What I try to accomplish is to load data per page. In this example I want 5 records on a page. If @SkipPages is 2 then 10 records will be skipped (equals 2 pages). Below you find a SQL view which performs pretty bad. The higher value @SkipPages is set, the longer it takes to process.
Is there a way to speed this up?
Declare @Zoektekst nvarchar(255),
        @RecordsOnPage int,
        @SkipPages int
SET @Zoektekst = N'%Merlijn%'
SET @RecordsOnPage = 5
SET @SkipPages = 2
SELECT TOP (@RecordsOnPage) Z1.ID, Z1.GevondenTekst, Z1.Herkenningstekst, Z1.Veldnaam, Z1.Tabel, Z1.VeldType, Z1.IsVerwijderd
FROM dbo.vwZoekFunctie AS z1 
WHERE GevondenTekst like @Zoektekst 
AND ID NOT IN 
  (SELECT TOP (@SkipPages * @RecordsOnPage) ID FROM dbo.vwZoekFunctie AS Z2
         WHERE GevondenTekst like @Zoektekst 
         AND Z1.Tabel = Z2.Tabel
  )

Open in new window

0
Comment
Question by:Delphiwizard
  • 2
4 Comments
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 100 total points
Comment Utility
What version of MSSQL is this?

In 2012 you can make use of OFFSET and FETCH:

...ORDER BY something
OFFSET @SkipPages*@RecordsOnPage ROWS
FETCH NEXT @RecordsOnPage ROWS ONLY;

Open in new window


You use this instead of TOP, not additionally.

Bye, Olaf.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
Besides the new feature the classic way is to fetch bottom 5 of top 15, for example. To get bottom 5 of something, you order inverse and take top 5 of that, so that's SELECT TOP 5 FROM (SELECT TOP 15 ORDER BY something ASC) tmpresult ORDER BY something DESC. That's way faster than a NOT IN subquery.

Bye, Olaf.
0
 
LVL 11

Accepted Solution

by:
Nakul Vachhrajani earned 400 total points
Comment Utility
What you are looking for are paging mechanisms. The SQL 2008 and SQL 2012 methods are demonstrated here: https://nakulvachhrajani.com/2016/02/08/0373-sql-server-interview-questions-parameters-required-for-server-side-paging-mechanisms/

If you are on SQL 2012, recommend using the OFFSET...FETCH method because it is more readable and efficient.
0
 

Author Comment

by:Delphiwizard
Comment Utility
I'm one SQL Server 2008 and on 2014, dependent on the customer. So I need a solution that works for both.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now