Increase the speed of SQL Server database access for millions of records

Morning All,

I have a website I am working on that is around 2000 pages of code it is a social media site for businesses. It has the potential for millions of users. Currently we have around 80,000 users and the site access is getting sluggish. I am using 98% stored procedures in the site to improve speed. What I want to know is what can I do to improve data extraction speed and increase the site loading times. I am my knowledge the Member table of the database is not using full text indexing, would that make a difference? I guess it would for searching. But, for example, when logging in it takes a while to load. Here is the login SP script:

SELECT
a.MemberID,
CAST (ISNULL(a.ProfileTypeID,0) AS bit) AS HasProfile,
a.TimeOffsetDiff * a.TimeOffsetUnits AS TimeOffset,
b.City,
b.StateName AS State,
b.StateAbbr AS abbr,
b.Domain,
b.RegionID,
a.ProfileTypeID,
sbuser.sf_DisplayName(a.MemberID) AS DisplayName,
a.UserName,
a.ImgLib,
a.MemberREgionID AS HomeRegionID,
a.StateID,
a.IsSales,
a.IsAdmin
FROM Member a
INNER JOIN Region b ON b.RegionID = a.MemberRegionID
WHERE a.MemberID = @MemberID

UPDATE Member SET NumberLogins = (NumberLogins + 1) WHERE MemberID = @MemberID

Considering this is hunting through only 80,000 members and can take up to 15 secs to login, I consider that to be real slow. Any thoughts on how I can increase login speed?

Obviously, extracting member lists into pages can be laborious too. I recently update outdated tf scripting that contained temporary datasets and the like for paging and replaced it with the following example:

IF @MODE = 'MEMBERSEARCHNEW'
DECLARE @TotalPages INT
BEGIN
    SELECT @TotalPages = COUNT(*)/@PageSize
    FROM Member a
    LEFT JOIN State b ON b.StateID = a.StateID
    WHERE (sbuser.sf_DisplayName(a.MemberID) LIKE @UserName + '%')
    AND a.MemberID <> @MemberID;

    WITH FindSBMembers AS
    (
        SELECT ROW_NUMBER() OVER(ORDER BY a.Claimed DESC, sbuser.sf_MemberHasAvatar(a.MemberID) DESC) AS RowNum,
        a.MemberID,                                                -- 1
        a.UserName,                                                -- 2
        a.PrCity,                                                  -- 3
        b.Abbr,                                                    -- 4
        sbuser.sf_MemberHasImages(a.MemberID) AS MemberHasImages,  -- 5
        sbuser.sf_MemberHasVideo(a.MemberID) AS MemberHasVideo,    -- 6
        sbuser.sf_MemberHasAudio(a.MemberID) AS MemberHasAudio,    -- 7
        sbuser.sf_DisplayName(a.MemberID) AS DisplayName,          -- 8
        a.ProfileTypeID,                                           -- 9
        a.Zip,                                                     -- 10
        a.PhoneNbr,                                                -- 11
        a.PrPhone,                                                 -- 12
        a.Claimed,                                                 -- 13
        @TotalPages AS TotalPages                                  -- 14
        FROM Member a
        LEFT JOIN State b ON b.StateID = a.StateID
        WHERE (sbuser.sf_DisplayName(a.MemberID) LIKE @UserName + '%')
        AND a.MemberID <> @MemberID
    )
    SELECT * 
    FROM FindSBMembers
    WHERE RowNum BETWEEN (@PG - 1) * @PageSize + 1
    AND @PG * @PageSize
    ORDER BY Claimed DESC, sbuser.sf_MemberHasAvatar(MemberID) DESC
END

Is there any further way I can squeeze any more speed out of this script..?

I have had other suggestions including gzip compression, break the Member table into 26 tables based on letters of the alphabet. I am interested to know how the big companies do it, how do they arrange their data, sites like Facebook, Yelp, Yellow Pages, Twitter. I am currently running on a shared hosting server, would an upgrade to VPS or Dedicated server help improve speed.

The site is written in Classic ASP, utilizing SQL Server 2005.

Any help that any of you can provide will be greatly appreciated.

Best Regards and Happy Coding!

Paul

**** ADDITION START:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO



ALTER FUNCTION [sbuser].[sf_DisplayName](@MemberID bigint)
RETURNS varchar(150)

AS

BEGIN
DECLARE @OUT varchar(150)
DECLARE @UserName varchar(50)
DECLARE @FirstName varchar(50)
DECLARE @LastName varchar(50)
DECLARE @BusinessName varchar(50)
DECLARE @DisplayNameTypeID int

SELECT


    @FirstName = upper(left(FirstName, 1)) + right(FirstName, len(FirstName) - 1),
    @LastName = upper(left(LastName, 1)) + right(LastName, len(LastName) - 1) ,
    @BusinessName = upper(left(BusinessName, 1)) + right(BusinessName, len(BusinessName) - 1),
   开发者_C百科 @UserName = upper(left(UserName, 1)) + right(UserName, len(UserName) - 1),
    /*
    @FirstName = FirstName,
    @LastName = LastName,
    @BusinessName = BusinessName,
    @UserName = UserName,
    */
    @DisplayNameTypeID = DisplayNameTypeID
    FROM Member 
    WHERE MemberID = @MemberID

    IF @DisplayNameTypeID = 2   -- FIRST / LAST NAME
        BEGIN
            /*SET @OUT = @FirstName + ' ' + @LastName*/
            SET @OUT = @LastName + ', ' + @FirstName
        END
    IF @DisplayNameTypeID = 3 -- FIRST NAME / LAST INITIAL
        BEGIN
            SET @OUT = @FirstName + ' ' + LEFT(@LastName,1) + '.'
        END
    IF @DisplayNameTypeID = 4 -- BUSINESS NAME
        BEGIN
            SET @OUT = @BusinessName + ''
        END

    RETURN @OUT
END

**** ADDITION END

80000 isn't a whole lot of records, unless you either have no indexes, or your data types are huge. if that query really is your bottle next, then you might want to consider creating covering indexes on the members table and the region table.

create an index on the member table with memberid as the index, and include profiletypeid, timeoffsetdiff, timeoffsetunits, profiletypeid, memberid, username, imglib, memberregionid, stateid, issales, isadmin.

also, jsut noticed your function sbuser.sf_DisplayName(a.memberID). you might explore into that function to make sure that that isn't your true bottle neck.

First option to speed up sf_DisplayName is to add FirstName, LastName etc from members as parameters and use that to build the DisplayName instead of doing a lookup against the member table.

After that you could consider to add DisplayName as a computed and persisted column to the member table. That means that the DisplayName will be calculated when the member is saved and the saved value will be used when you do the query. You can also add a index on the DisplayName column.

GetDisplayName function must be created with with schemabinding

create function dbo.GetDisplayName(
  @FirstName varchar(50),
  @LastName varchar(50),
  @DisplayNameType int) 
returns varchar(102) with schemabinding
as 
begin
  declare @Res varchar(102)
  set @Res = ''
  if @DisplayNameType = 1
    set @Res = @FirstName+' '+@LastName

  if @DisplayNameType = 2
    set @Res = @LastName+', '+@FirstName

  return @Res
end

The table with the persisted column DisplayName

CREATE TABLE [dbo].[Member](
    [ID] [int] NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [DisplayNameType] [int] NOT NULL,
    [DisplayName]  AS ([dbo].[GetDisplayName]([FirstName],[LastName],[DisplayNameType])) PERSISTED,
 CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED 
 (
    [ID] ASC
 )
)

The index on DisplayName

CREATE INDEX [IX_Member_DisplayName] ON [dbo].[Member] 
(
    [DisplayName] ASC
)

You should also have a closer look at what you are doing in sf_MemberHasImages, sf_MemberHasVideo and sf_MemberHasAudio. They are used in the column list of the cte. Not as bad as used in the where clause but they could still cause you problems.

The last one I spotted as a potential problem is sf_MemberHasAvatar. It is used in a order by at two places. But the order by in row_number() is used like a where because of the filtering in the main query where clause WHERE RowNum BETWEEN (@PG - 1) * @PageSize + 1.

The technique described with persisted column might be possible to use on the other functions as well.

Quick n dirty way to take the UDF call out of "every row"

SELECT *, sbuser.sf_DisplayName(MemberID) FROM (

    SELECT
    a.MemberID,
    CAST (ISNULL(a.ProfileTypeID,0) AS bit) AS HasProfile,
    a.TimeOffsetDiff * a.TimeOffsetUnits AS TimeOffset,
    b.City,
    b.StateName AS State,
    b.StateAbbr AS abbr,
    b.Domain,
    b.RegionID,
    a.ProfileTypeID,
    a.UserName,
    a.ImgLib,
    a.MemberREgionID AS HomeRegionID,
    a.StateID,
    a.IsSales,
    a.IsAdmin
    FROM Member a
    INNER JOIN Region b ON b.RegionID = a.MemberRegionID
    WHERE a.MemberID = @MemberID

)

another way, if you don't want to modify any tables, is to just put the udf logic in the select statement:

case DisplayNameTypeID
        when 2 then upper(left(LastName, 1)) + right(LastName, len(LastName) - 1) + ', ' + upper(left(FirstName, 1)) + right(FirstName, len(FirstName) - 1)
        when 3 then upper(left(FirstName, 1)) + right(FirstName, len(FirstName) - 1) + ' ' + upper(left(LastName, 1))
        when 4 then upper(left(BusinessName, 1)) + right(BusinessName, len(BusinessName) - 1)
    end as DisplayName

yeah it looks a bit gorey, but all you have to do is modify the sp.

Put indexes on the primary and foreign keys (MemberID, RegionID, MemberRegionID)

@Tom Gullen - In this instance the fact that Classic ASP is used would seem to be an irrelevance, since the actual cost in terms of computing in this instance seems to be with SQL (or whatever db tech this is running on).

@the question - I'd agree with Cosmin that indexing the relevant fields within the tables would provide a definite performance gain, assuming they're not already done.

We had this case about a week ago where my boss was trying to do multiple conditional inserts from a batch file which was taking forever. We place a single index on a userid fields, and hey presto, the same script took about a minute to execute.

Indexing!

Initial Thoughts

The problem here probably isn't with your stored procedures. Especially in regards to the login script, you are focussing your attention in a small and irrelevant place, as a login command is a one off cost and you can have a much much higher tolerance for script execution time of those sort of pages.

You are using classic ASP, which is quite out of date now. When you are dealing with so many visitors, your server is going to need a lot of power to manage all those requests that it is interpreting. Interpreted pages will run slower than compiled pages.

Time Events

If you are convinced the database is being slow, use times in your script. Add a general timer at the top of the page, and an SQL timer.

Page start load, initialise general time. When you reach a stored procedure, start the SQL timer. When query has finished, stop the SQL timer. At the end of the page you have two timers, one totalling the time spent running SQL, and the other timer - SQL timer gives you total time for executing code. This helps you separate your database from your code in regards to efficiency.

Improving ASP Page Performance

I've detailed good ASP page design here:

VBScript Out Of Memory Error

Also consider:

• Use Option Explicit at the top of your pages.
• Set Response.Buffer = True
• Use response.write inside <% %>, repeatedly opening and closing these is slow

I'll re-iterate what I said in the linked answer, by far, by far the best thing you can do for performance is to dump recordset results into an array with .getRows(). Do not loop recordsets. Do not select fields in queries you do not use. Only have 1 recordset, and 1 ado connection per page. I really recommend you read the link for good ASP page design.

Upgrade if no Glaring Issues

What are the specs of the server? Upgrading the hardware is probably your best route to increased performance in this instance, and most efficient in regards to cost/reward.

To replace the UDF, if that is the problem, I recommend having one field in the Member table to store the DisplayName as the data seems to be rather static from the looks of your function. You only need to update the field once in the beginning, and from then on only when someone registers or DisplayNameTypeID is changed. I hope this is helpful for you.