Am trying to run a procedure in SQL Server 2000 that returns about 70000 records. These 70000 records are then fed into a temporary table within the procedure. I then use an technique to faciltate paging to only pull back only a small number of records (e.g. 15) to populate a web page. I basically feed the procedure with the page number - then it works out what records to return, of the 70000.

My problem is, when I run the procedure in Query Analyser is returns records within 1-2 seconds - no problem. But when I run the same procedure through a web page then it takes 40-50 seconds sometimes more to run.
Note:
- It run perfectly fine with a small number of records (e.g. 5000)
- I am using a DataReader in the web application

Please can anyone help or know this has slowed down the web page although still runs ok in Query Analyser.

Many thanks.
Andy

__________________
Andy has a keen interest in home security and home alarm systems. Improve your home security now by going to the The Best Home Security Guide.

If indeed the final result set is small, this is strange.

Can you try filling a dataset with a DataAdapter instead of using the DataReader? This won't seem to make much a difference but it might help track down the problem.

Can you check and see in query analyzer that the records *start* to return within that 1-2 time period but that they still take a long time to return all of them? Maybe you are just getting a false impression by the way query analyzer returns data.

-Peter

The records appear in Query Analyzer all at once. I'm only actually returning 15 records - so hardly any network traffic.
Also the method I call is as follows:
public SqlDataReader GetMPRListAndDropDowns(SqlConnection conn, int CurrentPage, int PageSize, int UsrId, string Mpo_num, int Batch, int Band, string Status, int Comp_Id, string sortCriteria , int RecordCount, int Reject, int send_to_billing)
        {
            SqlCommand cmd = new SqlCommand("SpMainStatus_Details", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 120;

            cmd.Parameters.Add("@CurrentPage", SqlDbType.Int, 4).Value = CurrentPage;
            cmd.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = PageSize;
            cmd.Parameters.Add("@batch", SqlDbType.Int, 4).Value = Batch;
            cmd.Parameters.Add("@Mpo_num", SqlDbType.VarChar, 50).Value = Mpo_num;
            cmd.Parameters.Add("@UsrId", SqlDbType.Int, 4).Value = Util.fnToNullInt(UsrId);
            cmd.Parameters.Add("@Band", SqlDbType.Int, 50).Value = Band;
            cmd.Parameters.Add("@Status", SqlDbType.VarChar, 50).Value = Status;
            cmd.Parameters.Add("@Comp_Id", SqlDbType.Int, 4).Value = Comp_Id;
            cmd.Parameters.Add("@SortColumn", SqlDbType.VarChar, 50).Value = sortCriteria;
            cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Value = RecordCount;
            cmd.Parameters.Add("@Rej", SqlDbType.Int, 4).Value = Reject;
            cmd.Parameters.Add("@send_to_billing", SqlDbType.Int, 4).Value = send_to_billing;

            conn.Open();
            SqlDataReader result = cmd.ExecuteReader();
            return result;
        }

When running it in Debug, it hangs on "SqlDataReader result = cmd.ExecuteReader();" for a long time, sometimes over a minute!

Also, could you explain how using a Dataset/DataAdapter would help track down the problem?

Many thanks.

Nope, I can't provide any logical sense to that suggestion. Just that the way the two objects behave is a little different (connected versus "dis"connected). I'd actually expect the data adapter method to be slower, though with 15 records it'd be nearly impossible to persieve such a difference. I was simply suggesting an alternative for analytical purposes.

You might look into the additional things you can do to precompile a stored procedure call in .NET with the SqlClient classes. I haven't done it myself but I know that capability exists. Perhaps the delay is the additional overhead of that process between .NET and the SQL server whereas you don't see it when you're working within the native MSSQL environment.

-Peter

Yes, I would imagine its some sort of ADO.NET issue as it runs fine in Query Analyser. Is there any diagnotics that can be done on ADO.NET at all - or is it a "black box" type thing?

I've had good luck with ADO.NET performance so I haven't had the opportunity to explore such diagnostics.

-Peter

You dont need to get those 7000 records.
Here's how we do it here and we don't use temporary tables...

Try this:

CREATE TABLE TableName
(
INT ID IDENTITY,
NAME NVARCHAR(30)
)
go

CREATEPROCEDURE RetrieveRecords(@pagenum INT,@pagesize INT,@totalpages INT OUT,@totalrows INT OUT)
AS
    SET NOCOUNT ON

    DECLARE @ubound INT,@lbound INT,@pages INT,@rows INT
    DECLARE @based_id INT

    --set the number of pages
    SELECT @rows=COUNT(*), @pages=COUNT(*)/@pagesize
    FROM TableName

    --if record count is not exactly divisible by pagesize,add pagecount by 1
    IF @rows%@pagesize <> 0 SET @pages = @pages + 1

    IF @pagenum < 1 SET @pagenum = 1

    IF @pagenum > @pages SET @pagenum = @pages

    SET @ubound = @pagesize * @pagenum
    SET @lbound = @ubound - (@pagesize-1)

    SET @TotalPages = @pages
    SET @TotalRows = @rows

    SET ROWCOUNT @lbound

    SELECT @based_id=T_ID
    FROM TableName
    ORDER BY T_ID

    SET ROWCOUNT @pagesize

    SELECT T_ID,T_Name
    FROM TableName
    WHERE T_ID >= @based_id
    ORDER BY T_ID

    SET ROWCOUNT 0
RETURN
go

In my example I am using the identity column as reference but you can also use other unique columns in your table.

You can use a dataset or a datareader in your ado.net code,and sproc will only give you the rows you want.That should just work..




Aldwin Enriquez
President,Software Engineer - Narrasoft Philippines Inc.