SQL Server performance

I'm about to implement a paging system for SQL Server 2000, but it uses the old "copy all rows into a #temptable" method.

So I'm curious about the performance hits, so remind me again:

• The database I'm doing this in, will probably hold between 150 and 1,000 records in the particular table, will there be much of a performance hit as SQL Server copies all these into a #temptable?
• ...and doing this about 5 times a second?
• #temptables are kept in memory, right? It's the table type that is stored in disk?
  

I believe #temptables are likely to be stored in memory so long as memory is available for them.

How many columns do you have in the #temptable?  That can affect performance.

Bear in mind #temptables are not indexed.  Sometimes you can get away with making a #temptable of key values you're interested in, then joining to the original table to take advantage of the indices.

Maurits wrote:

I believe #temptables are likely to be stored in memory so long as memory is available for them.

How many columns do you have in the #temptable?  That can affect performance

Not more than 10 or so, but they may contain ntext fields (with up to 100KB of data each)

I might be wrong about this but I think text fields are copy-on-write*.  Perhaps a SQL Server team member could correct me?

In other words, copying a text field from one place to another just copies a pointer and updates a reference count (fast.)  Changing the text creates an updated copy of the data (slow) but TRANSACT-SQL will do everything in its power to stop you modifying text data

That's also why text fields don't count toward the 8060-byte row limitation.

On the other hand a 3000-nchar field will be much slower to copy over.

EDIT:
* Unless you're crossing servers, of course

Temp tables can really hurt you under load, so you may want to prototype and give it a quick perf test. The main problem is that the writes are transactional so backed to the transaction log - which implies synchronous writes to disk.

Under load, with multiple simultaneous transactions going on this can make you write I/O bound which is nasty on what should be a read only operation.


How complicated is the paging you need to do? Could you maybe use the select top x in ( select top y ) trick to do it in a query? Of course you;d have to measure to see if this helps, and would need the appropriate indexes...

( example if u dont know the trick.. )
-------------------------------
create table foo
( id int not null identity(1,1) primary key,
data varchar(20) not null  )
go

insert foo( data ) values('a' )
insert foo( data ) values('b' )
insert foo( data ) values('c' )
insert foo( data ) values('d' )
insert foo( data ) values('e' )
insert foo( data ) values('f' )
insert foo( data ) values('g' )
insert foo( data ) values('h' )
insert foo( data ) values('i' )
insert foo( data ) values('j' )

go
-- read the second page ( assuming 3 rows per page )
select top 3 id, data from foo
where id not in ( select top 3 id from foo order by id)
order by id

-- read the third page ( assuming 3 rows per page )
select top 3 id, data from foo
where id not in ( select top 6 id from foo order by id)
order by id
go

Too bad if you have a 100 page result set with 100 rows per page. then selecting the last page would create the worlds largest execution plan ever devised. In addition to that you can't specify a parameter for the TOP function so it's all dynamic SQL if you use it. We use temp tables despite their downfalls, as they provide the best scaling perf as your queried table grows. As with everything in SQL it depends on your data. One technique is to create a temp table within a Stored Proc with a primary key on it, which you can filter with parameters supplied to the proc. This makes for a very flexible query.

Here's the paging sproc I'm using:

ALTER PROCEDURE ams_ArticlePage (
    @Page int,
    @RecsPerPage int,
    @ContainerID bigint
) AS

-- Paging code hacked 'n' slashed from http://www.aspfaqs.com/webtech/062899-1.shtml

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON

--Create a temporary table, a direct copy of ams_Articles
CREATE TABLE #TempItems (
    TempID          bigint IDENTITY (1, 1),
    [ID]            bigint        NOT NULL,
    [InContainer]   bigint        NOT NULL,
    [CompositeName] varchar(200)  NOT NULL,
    [Name]          nvarchar(100) NOT NULL,
    Username nvarchar(50) NOT NULL
-- and about 15 other fields (not shown for readability)
)


-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (
    [ID],[InContainer],[CompositeName], [Name] Username

)   SELECT
        ams_Articles.[ID],[InContainer],[CompositeName], [Name] Username
    FROM
        ams_Articles
        INNER JOIN ams_Users ON ams_Articles.InUser = ams_Users.[ID]
    WHERE
        ams_Articles.InContainer = @ContainerID
    ORDER BY
        ams_Articles.[ID] DESC

-- Find out the first and last record we want
DECLARE @FirstRec bigint, @LastRec bigint
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of if we have more records or not!
SELECT
    *,
    MoreRecords = (
        SELECT
            COUNT(*)
        FROM
            #TempItems TI
        WHERE
            TI.[TempID] >= @LastRec 
    ) 
FROM
    #TempItems
WHERE
    [TempID] > @FirstRec
    AND
    [TempID] < @LastRec


-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

Flatliner wrote:

Too bad if you have a 100 page result set with 100 rows per page. then selecting the last page would create the worlds largest execution plan ever devised.

well maybe. If you look at the execution plan for simple cases at least ( like the example ), the query essentially resolves to a single right anti-semi join, which can be resolved in a single I/O read pass over the data. This makes sense if you think about what you are really asking - scan past the first n rows, and then return me the next m. Of course you need to get your indexes right and like you say every case is different, but i have seen order of magnitude improvements in commercial applications with this approach...

Try sticking 1/2 million rows in the table and see how quickly it gives you back rows 300,000 to 300,050 say...

Can't argue with you about the dynamic sql ...Only good thing is that the inputs are integers and cant easily be exploited by sql injection attacks etc.

spod wrote:

Can't argue with you about the dynamic sql ...Only good thing is that the inputs are integers and cant easily be exploited by sql injection attacks etc.

Perhaps T-SQL needs something like preprocessor directives so we don't need to make our dynamic SQL strings.