Professional Web Applications Themes

How To Query Large Data/Records ? - Coldfusion - Advanced Techniques

Hi All, Here is my Problem - In my search form I have to query 50 million records in a single transaction and to display the results . - Without sweating the machine. my Approach(s): 1) Indexing - this is bad, because the batch processing is using the same table to load data at the same time - daily. 2) Data base partition - well, the search results will be still ard 800 , 000 records/per partition. 3) Search keys - to filter results -for 50 mill ? This is a common approach, but to search over 50 mill or ...

  1. #1

    Default How To Query Large Data/Records ?

    Hi All,

    Here is my Problem - In my search form I have to query 50 million records in a
    single transaction and to display the results . - Without sweating the
    machine.

    my Approach(s):
    1) Indexing - this is bad, because the batch processing is using the same
    table to load data at the same time - daily.
    2) Data base partition - well, the search results will be still ard 800 , 000
    records/per partition.
    3) Search keys - to filter results -for 50 mill ?
    This is a common approach, but to search over 50 mill
    or with 800,000 records/per partition .Hmmm


    The Appl/ Enviroment:
    1) web server - CF MX ,win2k, RAM 133MB, Processor - x86 Family 6 Model 8
    Stepping 3
    2)Database - Oracle 9i /IBM AIX 5.2
    3)With above system requirement - The server/pc
    cannot even hold for some 60,000 records per transaction. Unfortunately for me
    is I ll still be using the same server/pc(above). Plus , to add another
    partition/sub part or indexing scheme in the database will effect the back end
    processing some how.

    System Requirement
    ------------------
    1) To display a precise total records for every search transaction - the
    results.
    2) To have a fast/ reliable / robust application.

    Any ideas , new algo or technique ? Please help

    affiza
    :confused;

    affiza Guest

  2. #2

    Default Re: How To Query Large Data/Records ?

    I assume that you are using a store proc ?

    How fast does it run in the db environment ?

    As I don't use oracle, I'm not sure this will work, but

    In sql server I had a similar problem, I created a stored procedure. This sp
    also took a param that indicated the starting point for the next recordset to
    return. That is I only return 1000 records at a time.

    But this is all dependant upon the execution time of the sp.
    When I get home I will post some example code.

    Ken

    The ScareCrow Guest

Similar Threads

  1. query of queries and large data types
    By jim1234 in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 12th, 06:38 PM
  2. query of quries and large data types
    By jim1234 in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: May 12th, 06:17 PM
  3. Replies: 2
    Last Post: September 30th, 02:20 AM
  4. data extraction from records of query
    By ms shakeel in forum Microsoft Access
    Replies: 3
    Last Post: July 22nd, 06:20 PM
  5. large number of records in query (orcale 8i)
    By chauhan in forum Oracle Server
    Replies: 2
    Last Post: December 17th, 01:32 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