Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Increase performance of SQL

Posted on 2016-08-01
4
Medium Priority
?
60 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:Stef Merlijn
  • 2
4 Comments
 
LVL 30

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 400 total points
ID: 41737679
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 30

Expert Comment

by:Olaf Doschke
ID: 41737774
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 14

Accepted Solution

by:
Nakul Vachhrajani earned 1600 total points
ID: 41737883
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:Stef Merlijn
ID: 41738126
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

972 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