Solved

Increase performance of SQL

Posted on 2016-08-01
4
53 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
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 29

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 12

Accepted Solution

by:
Nakul Vachhrajani earned 400 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:Delphiwizard
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
2016 SQL Licensing 7 41
Sql Server group by 10 27
optimize stored procedure 6 27
Loops and updating in SQL Query 9 29
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

773 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