Professional Web Applications Themes

How to deal with big query result? - ASP

Greetings, In my search application, user can type a number to search. I use LIKE in my query. If a query result generates over 10,000 recordsets, it may several minutes to run. Is there a way to only query certain recordset at a time? I found in SQL you can use "TOP". Like select TOP 100.... it will return only top 100 records. Is there a way to query may be from 101 to 200 then from 201 to 300... My query is like: Select * from Product where PartNumber like '%" PartNumber "%' Any idea is welcome. Thanks a ...

  1. #1

    Default How to deal with big query result?

    Greetings,

    In my search application, user can type a number to search. I use LIKE in my
    query.

    If a query result generates over 10,000 recordsets, it may several minutes
    to run.

    Is there a way to only query certain recordset at a time?

    I found in SQL you can use "TOP". Like select TOP 100.... it will return
    only top 100 records.

    Is there a way to query may be from 101 to 200 then from 201 to 300...

    My query is like: Select * from Product where PartNumber like '%" PartNumber
    "%'

    Any idea is welcome.

    Thanks a lot.

    Lin Ma





    Lin Ma Guest

  2. #2

    Default Re: How to deal with big query result?

    Yes Lin, this is called "paging"

    [url]http://www.aspfaq.com/2120[/url]
    [url]http://www.aspfaq.com/2352[/url]

    The use of a temp table to divide all of the records tends to scare people.
    Typically, much of the processing time is in transferring the data over the
    network and rendering the HTML required to display the data, than the
    initial filtering. So give that method a shot.

    Also, if a query returns 10,000 rows, you might consider requiring that the
    user narrow it down a bit better... e.g. picking categories, or only
    choosing results that start with one letter at a time, or ...




    "Lin Ma" <aa.com> wrote in message
    news:#SHbhf0jDHA.360TK2MSFTNGP10.phx.gbl...
    > Greetings,
    >
    > In my search application, user can type a number to search. I use LIKE in
    my
    > query.
    >
    > If a query result generates over 10,000 recordsets, it may several minutes
    > to run.
    >
    > Is there a way to only query certain recordset at a time?
    >
    > I found in SQL you can use "TOP". Like select TOP 100.... it will return
    > only top 100 records.
    >
    > Is there a way to query may be from 101 to 200 then from 201 to 300...
    >
    > My query is like: Select * from Product where PartNumber like '%"
    PartNumber
    > "%'
    >
    > Any idea is welcome.
    >
    > Thanks a lot.
    >
    > Lin Ma
    >
    >
    >
    >
    >

    Aaron Bertrand - MVP Guest

  3. #3

    Default Re: How to deal with big query result?

    [url]http://www.aspfaq.com/2120[/url]
    [url]http://www.aspfaq.com/2096[/url]

    Ray at work

    "Lin Ma" <aa.com> wrote in message
    news:%23SHbhf0jDHA.360TK2MSFTNGP10.phx.gbl...
    > Greetings,
    >
    > In my search application, user can type a number to search. I use LIKE in
    my
    > query.
    >
    > If a query result generates over 10,000 recordsets, it may several minutes
    > to run.
    >
    > Is there a way to only query certain recordset at a time?
    >
    > I found in SQL you can use "TOP". Like select TOP 100.... it will return
    > only top 100 records.
    >
    > Is there a way to query may be from 101 to 200 then from 201 to 300...
    >
    > My query is like: Select * from Product where PartNumber like '%"
    PartNumber
    > "%'
    >
    > Any idea is welcome.
    >
    > Thanks a lot.
    >
    > Lin Ma
    >
    >
    >
    >
    >

    Ray at Guest

Similar Threads

  1. How to pass query result from pop-up?
    By Mariusz Topczewski in forum Macromedia Flex General Discussion
    Replies: 1
    Last Post: December 21st, 04:09 PM
  2. Format query result.
    By Kasper K in forum MySQL
    Replies: 2
    Last Post: June 29th, 02:19 PM
  3. displaying query result of 0 as N/A
    By eggwater in forum Macromedia ColdFusion
    Replies: 3
    Last Post: May 10th, 11:34 AM
  4. Make query result into hyperlink.
    By Richard in forum PHP Development
    Replies: 2
    Last Post: February 2nd, 02:08 PM
  5. how to add another column to a query result?
    By Junaili Lie in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 7th, 06:03 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139