Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Increase performance of SQL

Posted on 2016-08-01
4
Medium Priority
?
58 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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: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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

721 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