0

I've seen a lot of examples but none of them seem to help me enough.

Here are the ones I've implemented and working, but very slow:

Approach 1:

SELECT *
FROM 
    LogVisits T (NOLOCK)
WHERE
    T.Msisdn = @TempMsisdn
ORDER BY 
    T.[VisitUrlCreatedDate], T.[VisitId] DESC
OFFSET @RecordStartIndex ROWS
FETCH NEXT (@RecordEndIndex - @RecordStartIndex) ROWS ONLY;

Approach 2:

SELECT
    *
    , (SELECT COUNT(*) FROM vLogVisit) AS [TotalRecordCount]
FROM
    (
        SELECT
            ROW_NUMBER() OVER(ORDER BY T.[VisitUrlCreatedDate] DESC, T.[VisitId] DESC) AS IndexRank --, T.VisitUrlId DESC
            , T.*
        FROM
            vLogVisit T
        WHERE
            T.Msisdn = @TempMsisdn
    ) AS VisitLogs
WHERE 
    IndexRank BETWEEN @RecordStartIndex AND @RecordEndIndex
ORDER BY
    VisitUrlCreatedDate DESC
  , VisitId DESC

I think these both approaches take all first and then get the portion I want.

Since the count of the data before taking the portion is more than 100k, is there a faster way?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
E-A
  • 1,995
  • 6
  • 33
  • 47
  • 3
    Do you have indices and are those being used? I see nothing wrong with 1). Why would you do 2)? – mlt Jun 14 '14 at 06:41
  • 2
    Is there any reason why you are looking at nolock. You just get dirty records and generally, in my experience, just causes 7105 (credit @RichardVivian) errors in my logs. Generally shouldn't lock and messes with the optimisation plans. What do your indexes look like? Also - Avoid "*", rather specify the columns where you are linking against index fields. Also see http://stackoverflow.com/a/3639964/1662973 – Anthony Horne Jun 14 '14 at 06:42
  • vLogVisit is a view which has a NOLOCK within it. So I did not write nolock again. But I forgot it by writing in the first approach :). As for "mit", I see that approach and wanted to try but the results are the same of those 2. I just wanted to show that I explored that option. – E-A Jun 14 '14 at 06:46
  • >100k rows should be no issue. Don't underestimate the power of indexes. We have had issue where items taking a minute to return a single record (from a few million rows) went to <1 second by just "respecting" the full index, i.e. I added another column to the join. – Anthony Horne Jun 14 '14 at 06:53
  • I see, and there are also given indexes on necessary locations. Still taking more than 20 secs. The problem I see is that the query itself taking the whole data, I am more like looking for an approach that could only get the portion without taking 'em all. – E-A Jun 14 '14 at 06:55
  • For a start, try and limit T.* and the outer query "select *", unless there are possibilities of null values (that will influence the count(*)) – Anthony Horne Jun 14 '14 at 06:57
  • Thank Anthony, but there are actually no null values. In fact, I am eliminating the most of them (there are more than 300 million data at that table) down to few 100k. – E-A Jun 14 '14 at 07:05
  • Read this http://dba.stackexchange.com/questions/20643/sql-server-query-slow-when-paginated – Andrzej Reduta Jun 14 '14 at 07:12

0 Answers0