Professional Web Applications Themes

Getting selected rows out of an access database - Coldfusion Database Access

I'm currently using this code to output my blog entries. The problem i'm having is getting all the information from the database everytime is slowing down the server a little. Is there a way to only get 10 entries at a time from the database, and incrementing that through <cfquery> instead of <cfoutput>? <cfquery name="blogEntry" datasource="#request.dsn#"> SELECT * FROM entry ORDER BY entryID DESC </cfquery> <cfoutput query="blogEntry" maxrows="10" startrow="#url.startrow#"> **formatting** </cfoutput>...

  1. #1

    Default Getting selected rows out of an access database

    I'm currently using this code to output my blog entries. The problem i'm having
    is getting all the information from the database everytime is slowing down the
    server a little. Is there a way to only get 10 entries at a time from the
    database, and incrementing that through <cfquery> instead of <cfoutput>?




    <cfquery name="blogEntry" datasource="#request.dsn#">
    SELECT *
    FROM entry
    ORDER BY entryID DESC
    </cfquery>

    <cfoutput query="blogEntry" maxrows="10" startrow="#url.startrow#">
    **formatting**
    </cfoutput>

    badLarry Guest

  2. #2

    Default Re: Getting selected rows out of an access database

    If the query data does not change much, cache the query.

    Two things that can make an immediate impact:
    Don't use "SELECT *"! List only the columns that are needed, this can save
    quite a bit of time and memory.
    Use TOP in the query itself to limit the actual data.
    For example:
    Set iLastRow = StartRow + maxrows
    Then, use:
    SELECT TOP #iLastRow#
    Column A, Column B, Column C, etc.
    FROM entry ... ...


    There are ways to limit Access to just the 10 records desired but they require
    redesigning the actual query somewhat (Use "TOP" and self joins).

    Note that you can use the maxrows attribute in cfquery but that does not
    usually help much.


    MikerRoo Guest

  3. #3

    Default Re: Getting selected rows out of an access database

    Thanks Mike. I'm using the SELECT * because I do use all the columns in the table. But I tried using TOP in the statement like you suggested and it was exactly what I was looking for . Thanks a ton.
    badLarry Guest

Similar Threads

  1. Database Display - How can I repete the rows both ways?
    By heartless in forum Coldfusion Database Access
    Replies: 3
    Last Post: January 21st, 01:28 AM
  2. Getting a list of ID's from Selected Rows
    By Thomasa Gregg in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: October 21st, 09:03 PM
  3. [PHP-DEV] database driver: no more rows
    By Lukas Smith in forum PHP Development
    Replies: 17
    Last Post: October 23rd, 04:02 PM
  4. how to return all rows selected in subroutine?
    By Shaunn Johnson in forum PERL Beginners
    Replies: 1
    Last Post: August 29th, 08:33 PM
  5. user access to only selected pages
    By joe in forum ASP.NET General
    Replies: 8
    Last Post: July 6th, 02:27 AM

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