Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2002
    Posts
    33

    Unhappy Unanswered: Out of options, Need performance help.

    hey guys, i need your help please.
    here is the scenario:

    1. I need to return a data back to client (result set varies 20-10,000)
    2. I only want to show 20 records at a time
    3. To get info i need to display i need to join 10 tables

    When there are small #s of records it works but when i get over 8000 then it becomes a problem:

    1. The first version was:
    Get all data using big query and return everything back to client and display only 20 at a time (not very proficient).
    Takes around 15 seconds to view 20 records.

    2. Inspired by 4GuysFromRolla (http://www.4guysfromrolla.com/webtech/062899-1.shtml)
    Use Stored Procedure w/ server side paging logic to get 20 records at the time. I had to pass every filter parameter and stuff. SP had to sort resultset and return only 20 records i need to display.
    Takes around 5 seconds to view 20 records.

    I still think it's slow, i know this is a very broad question but is there any other way to do it, logically?

    Any help is appreciated.
    Wojo.

  2. #2
    Join Date
    Jan 2004
    Posts
    31
    You coud try running a few instances of the search in anticipation of the Next/Prev scenario.

    While users are reading 20 in their window the application is downloading a further 20 behind the scenes asynchronously. I couldn't give an example. At least they wouldn't be aware of the 5 seconds if it took longer to read 20.

    The only other example I have is for Delphi, sorry.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well...you need to order by something....and you need to know what page your on...

    How about..

    Code:
    USE Northwind
    GO
    
    DECLARE @Page int, @sql varchar(8000)
    SELECT @Page = 2
    SELECT @sql = 'SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP ' 
    		+ CONVERT(varchar(10),@Page * 20) 
    		+ ' * FROM Orders ORDER by OrderId) AS A '
    		+ ' ORDER By OrderID Desc) AS B ' 
    		+ ' ORDER BY OrderId'
    EXEC(@sql)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Sep 2002
    Posts
    33
    Thanks guys,
    Brett, how does this work?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    To see it work...go to QA...cut and paste the code and execute it....

    What it does roughly is that if you want page x, the inner query will select the TOP x * 20 rows (last 20 will be page x...

    Then order by a key descending to get the last 20 rows..the reorder those by the key, so page 20 will be in the correct order to be displayed....


    test it, let me know if it works for you....

    Oh, and btw, this will only transmit the 20 rows across..you don't want to be moving large chunks for no reason...especially since data is fluid and may change at any time....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Sep 2002
    Posts
    33
    Hmm,

    What if i want to sort by other columns, plus my IDs aren't sequential

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You have to order by something....

    The order of data in a database is meaningless.....

    And yes can can order by more than 1 thing...

    And it doesn't have to be an int...

    Look at a phonebook...they don't order by phone number....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Sep 2002
    Posts
    33
    Thanks Brett,

    it looks like it works preety well, i'm getting 1-2 seconds on couple of first pages!

    One last question: is there any way to get total number of records from the inner query? and i would rather not requery this, any way to get the count while doing what we are doing?

    thanks

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's the total row count?

    Did you try it for the last page yet?

    And...
    One last question: is there any way to get total number of records from the inner query? and i would rather not requery this, any way to get the count while doing what we are doing?
    Why does this matter to you? Remembering the page number should be more important...no?

    And cool site btw...do you sell just parts or whole systems...where are you based out of?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Sep 2002
    Posts
    33
    The row count was ~9k
    I did and it took 6-7 seconds, which is not that bad considering almost nobody looks at last pages

    It matters to me because i'm displaying total number of records and i need this number to drive my paging logic (ex. page 1 out of 12)

    Re: my site, its just my personal project, this question is for work and no i actually don't sell anything in there, i wrote this site to allow people to sell buy hardware for no charge...

    thanks again for all your help man!

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What are you SELECTING, and what's the ORDER BY on?

    Are they all indexed?

    I don't like 6/7 seconds.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Sep 2002
    Posts
    33
    i will look into indexes later, i would like to get this running first, so i need total count of records...

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Wouldn't that just be @Pages*20?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Sep 2002
    Posts
    33
    lol, i need to know what @PAGES is don't i?

    i don't think i made myself clear, i want to know the total number of records i'm gonna page myself though, ex:

    Found 100 records, you are on page number 1 out of 5 (since there is 20 recs per page)

    thanks.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "i will look into indexes later"

    If you are concerned about performance NOW, then you need to look into indexes NOW. I wondered what kind of indexes you had when I read that it started getting slow at 8000 records. 8000 records is peanuts.

    Doing a select count(*) to get the rowcount should not be too expensive. Another method is to select the rowcount from the sysindexes table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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