Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212

    Question Unanswered: Ideas on method of query for Search Pages.

    I've seen web search page results where
    the results can be group into pages, e.g. 1,2,3, Last.

    Now the only way I can think of doing this is when the user does a select statement on a table,
    all the results will be inserted into a temp table with a new column grouping the results by pages.

    Are there any other way by doing this without inserting into a temp table?
    The search page I am going to design is a normal webpage where u can enter fields to search a particualr table in MSSQL.
    So anybody can do it at anytime, thus if I use the insert into temp table method, I have to generate a "temp-id" for the visitor as well.

    Any better suggestions, or any comments for better query performance ?
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  2. #2
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    You can use a temp table (if you must), as long as it's scope is restricted to the stored procedure you are running (you are running a stored procedure, I assume).

    If you preface your table name with # (#my_table), it's scope will be restricted, so it will only be visible to to your transaction, and will automatically be deleted when the procedure exits.

    Better yet, use a TABLE variable. Declare a variable of type table like this:

    DECLARE @my_table TABLE (id INT, stuff VARHCAR(50), otherstuff FLOAT)

    You can then access it as if it were a table, and have better performance than if you used a temp table (at least according to MS). One more thing, when you write your query of the TABLE variable, assign it an alias, as SQL doesn;t seem to like it in WHEREs and JOINs.
    For example:

    SELECT * FROM @my_table my_table INNER JOIN other_table ON my_table.customer_id = other_table.customer_id
    -bpd

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The best solution I know of is to sort your results, and then have the interface pass a starting value as a parameter when it requests a page of data. This parameter would either be NULL, in which case you return the first page-worth of results, or the value of the last sort-field previously returned, in which case return a page-worth of data where the sort field is greater than the supplied parameter.

    Your page size (the number of records to return) could also be submitted as a parameter.

    All that said, this functionality might be better implemented in the middle-tier, which is not as stateless as SQL server. Your table variable, for instance, would drop out of scope after your procedure completes and would not be available for the next call.

    blindman

  4. #4
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    You are correct sir. The only problem with the store procedure model is that newly added records might be missed in between calls. For example, you request records 1 through 10 (sorted). Before you can request 11 through 20, someone added a new record, which when sorted, is now number 6 -- you never see that unless you start from the top again. Only a problem if you are dealing with an active dataset (records being added often).

    The only way to avoid it is to a) return everything to the client in a recordset and deal with it in JavaScript, which is bad unless you decide to limit the total number of possible hits, or b) do it with a persistent middle-tier object, which adds complexity, but otherwise is probably the best solution.
    -bpd

  5. #5
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    Thanks for your ideas and replies.

    I did some thinking myself for other ways.
    I wanted to do something just from using select statements.

    The coding below is a start-up effort of mine manipulating the "top n" command.

    Note: For the sample code, I hard coded the numbers for "top" command instead of putting variables in it because it would be a syantax error in T-SQL. e.g "top 5" instead of "top @currentscope".

    But u can use your client side variable during real proccessing
    e.g. "top " & vbcurrentscope &""

    Code:
    declare @rows int,
            @recordperpage int,
            @pages  int,
            @startAt  int,
            @endAt  int,
            @currentpageview int,
            @currentscope int,
            @previousscope int
    
    set @rows=(select count(recordid) from TestTable)
    set @recordperpage=2
    set @pages=@rows/@recordperpage 
    if (@rows %@recordperpage<>0) 
    set @pages=@pages+1
    set @currentpageview=2
    
    if @currentpageview >1
    begin
    set @currentscope=@currentpageview*@recordperpage
    set @previousscope=@currentpageview-1*@recordperpage
    select @startAt=max(recordid)+1 from TestTable where RecordId in
                    (select top 2 recordid from TestTable)
    select @EndAt=max(recordid) from TestTable where RecordId in
                    (select top 4 recordid from TestTable)
    end else begin
    select @startAt=min(recordid),@endAt=max(recordid) from TestTable where RecordId in
                    (select top 2 recordid from TestTable)
    end
    
    
    select * from TestTable where RecordId between
    @StartAt and @EndAt

    Will this code reduce query performance instead of inserting into a temp table?
    Last edited by Patrick Chua; 08-18-03 at 19:34.
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  6. #6
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    Forgot to mention one important aspect to implementing this code is you have to design your tables with an identity column .

    I called mine "RecordId".
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  7. #7
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    You could try

    SET ROWCOUNT @currentscope

    It will set the max rows returned. Not sure off the top of my head if it works with a variable, but I think it does.

    Remember to SET ROWCOUNT 0 (reset) when you are done though, as it persist, and screw up all your other queries. ;-)
    -bpd

  8. #8
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    Good suggestion bdpwork.
    I'll consider that in my logic.
    Thanks
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •