Solved

Increase performance of SQL

Posted on 2016-08-01
4
54 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 13

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

809 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