SQL Sever getting total count from view

Question

I'm Currently using SQL Server 2008 R2 and ASP.Net with Linq

On Server 1

We have database with a table that contains address info for about a million records.

On Server 2

I have a customer table with about 1/2 million records which contains an AddressID

I have created a synonym to link to Server 1 for the address table. And have created a view to link both tables together within Server 2.

A stored procedure to handle paging of the data so that it returns me a page of 20 records, also tried adding RowNumber into the actual view which made no speed improvements

CREATE PROCEDURE [dbo].[GetCustomerAddressesByPage] 
(
    @pageIndex int,
    @pageSize int,
    @totalRows int output
)
AS
BEGIN
  DECLARE @startRowIndex int
  DECLARE @endRowIndex int

  SET @startRowIndex = (@pageIndex * @pageSize) + 1;
  SET @endRowIndex = (@pageIndex + 1) * @pageSize;

  --SELECT * FROM
  --  (SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNumber,*
  --     FROM View_CustomersAddress) xx
  --WHERE RowNumber >= @startRowIndex AND RowNumber <= @endRowIndex

  SELECT *
    FROM View_CustomersAddress
    WHERE RowNum >= @startRowIndex AND RowNum <= @endRowIndex

  SELECT @totalRows = COUNT(*) FROM View_CustomersAddress  
END

This returns me the data within 2 seconds, but to handle the number of pages I also need the total rows and the following bit of code is taking another 20 or so seconds to complete

SELECT @totalRows = COUNT(CustomerID) FROM View_CustomersAddress

Server code is as follows to populate the data within a gridview

public IQueryable<GetCustomerAddressLinesByPageResult2> GetAddress(int startRowIndex, int maximumRows)
{
    var data = dbContext.GetCustomerAddressLinesByPage(startRowIndex, maximumRows);
    return data;
}

The view doesn't contain any indexing as you can't due to using a synonym, so I'm currently storing the total rows in a viewstate in my asp.net program so on initial load it takes 25 seconds+ but paging is fine.

Is there anyway to increase the performance of the count() or maybe another way of doing it without using count() which I've not thought of.

Just to clarify

Address table on Server 1 has a primary key AddressID and is unique

Customer table on Server 2 has a primary key CustomerID and is unique

The View_CustomersAddress does not have any index due to the synonym between Server 1 and Server 2

What is index on CustomerId in underlying table? Also do you really want count(customerid) or count(distinct customerid) or customerId is unique in this table? — Kannan Kandasamy, Jan 09 '17 at 15:37
take a look at his post. Perhaps it can help you http://stackoverflow.com/a/6069288/3877877 — Martin E, Jan 09 '17 at 15:44
I tried using **sys.dm_db_partition_stats** before but that only works on a table, and not a view — fizgog, Jan 09 '17 at 15:55
This will not help you but I would like to know why you do count(CustomerID) and not just Count(1) or Count(*) ? — GuidoG, Jan 09 '17 at 16:19
No reason really, they all seem to return the same timed result — fizgog, Jan 09 '17 at 16:26

score 0 · Accepted Answer · answered Jan 09 '17 at 19:28

You're always going to have a problem when you need to display "Total Rows". In order to do that, it must do the full amount of work, the only savings is in the amount of data that is transferred.

If you must get a count of "Total Rows" and page the data I suggest doing it in two different steps:

Step 1 - Get Total Rows

SELECT COUNT(1) FROM Customer

Notice that I didn't join on the synonym which is part of your slowdown.

Step 2 - Get Pages Results

(you already know how to do this step)

This works for my initial problem and I've marked it as answered, but later I'm going to be adding searching capabilities so would need to search across both tables. — fizgog, Jan 10 '17 at 08:54

SQL Sever getting total count from view

1 Answers1

Step 1 - Get Total Rows

Step 2 - Get Pages Results