Professional Web Applications Themes

generic array query optimisation help? - MySQL

Suppose I have a very basic class in an arbitrary OOP: class MyClass { int Array1[]; int Array2[]; }; I made the following 3 tables Array1 ####################### array1_id, data, myclass_id Array2 ####################### array2_id, data, myclass_id MyClass ####################### myclass_id I simply want to read all MyClasses from the database and fill the arrays, using 1 query and yet not send unnecessary data over the network... I made two naive attempts, yet I fail to reach both my "design goals" at the same time. Version 1 ========= "SELECT * FROM MyClass M;" foreach(MyClass m in result) { "SELECT * FROM Array1 A ...

  1. #1

    Default generic array query optimisation help?

    Suppose I have a very basic class in an arbitrary OOP:

    class MyClass
    {
    int Array1[];
    int Array2[];
    };

    I made the following 3 tables

    Array1
    #######################
    array1_id, data, myclass_id

    Array2
    #######################
    array2_id, data, myclass_id

    MyClass
    #######################
    myclass_id

    I simply want to read all MyClasses from the database and fill the
    arrays, using 1 query and yet not send unnecessary data over the
    network... I made two naive attempts, yet I fail to reach both my
    "design goals" at the same time.

    Version 1
    =========

    "SELECT * FROM MyClass M;"
    foreach(MyClass m in result)
    {
    "SELECT * FROM Array1 A WHERE myclass_id="+m.myclass_id;
    foreach(int i in result)
    m.AddToArray1(i);

    "SELECT * FROM Array2 A WHERE myclass_id="+m.myclass_id;
    foreach(int i in result)
    m.AddToArray2(i);
    }

    ======================
    Queries: 1+2*(MyClass) (Bad)
    Rows: (MyClass) + (MyClass*Array1) + (MyClass*Array2) =
    (MyClass)*(1+(Array1)+(Array2)) (Few)
    Columns: 1-3 (Few)
    Data amount over the wire: Rows*Columns (Ok)

    Version 2
    =========

    SELECT * FROM MyClass M
    LEFT JOIN Array1 USING(myclass_id)
    LEFT JOIN Array2 USING(myclass_id)
    ORDER BY M.myclass_id;

    foreach(MyClass m in result)
    {
    // Greatly simplified, to the point of beeing buggy... just meant
    for illustration.
    if(m.myclass_id is the same as it was in the previous iteration)
    {
    first_m_with_a_given_class_id.AddUniqueToArray1(m. Array1);
    first_m_with_a_given_class_id.AddUniqueToArray2(m. Array2);
    }
    }

    ==================
    Queries: 1 (OK)
    Rows: (MyClass)*(Array1)*(Array2) (Excessive amount)
    Columns: 5 (Many)
    Data amount over the wire: Rows*Columns (Massive)

    Greatful for any assistance.
    Nicklas

    Nicklas Guest

  2. #2

    Default Re: generic array query optimisation help?

    Sorry for replying to my own message, but thought of one optimisation
    myself:

    SELECT * from MyClass
    LEFT OUTER JOIN
    (
    SELECT data as data1, null as data2, array1_id as array_id,
    myclass_id from Array1
    UNION
    SELECT null as data1, data as data2, array2_id as array_id,
    myclass_id from Array2
    ) arrays
    USING (myclass_id)
    ORDER BY myclass_id;

    Would this be considered a good approach?

    Nicklas Guest

  3. #3

    Default Re: generic array query optimisation help?

    I've written some general remarks about database handling in
    object-oriented programs in
    [url]http://www.w-p.dds.nl/article/wrtabrec.htm[/url]

    Why is it bad to use two queries? Especially when they are much more
    simple to p by the server and therefore probably faster?
    If you want to speed things up (not suitable for all engines, though),
    take a look at the HANDLER statement.

    But that is not necessary either. If the queries are a performance
    problem, you probably forgot to use indexes on myclass_id. But I
    sincerely doubt that you have a performance problem anyway.
    However, you DO have a maintenance problem if you start writing code the
    least understandable way...

    Best regards

    Nicklas wrote:
    > Suppose I have a very basic class in an arbitrary OOP:
    >
    > class MyClass
    > {
    > int Array1[];
    > int Array2[];
    > };
    >
    > I made the following 3 tables
    >
    > Array1
    > #######################
    > array1_id, data, myclass_id
    >
    > Array2
    > #######################
    > array2_id, data, myclass_id
    >
    > MyClass
    > #######################
    > myclass_id
    >
    > I simply want to read all MyClasses from the database and fill the
    > arrays, using 1 query and yet not send unnecessary data over the
    > network... I made two naive attempts, yet I fail to reach both my
    > "design goals" at the same time.
    >
    > Version 1
    > =========
    >
    > "SELECT * FROM MyClass M;"
    > foreach(MyClass m in result)
    > {
    > "SELECT * FROM Array1 A WHERE myclass_id="+m.myclass_id;
    > foreach(int i in result)
    > m.AddToArray1(i);
    >
    > "SELECT * FROM Array2 A WHERE myclass_id="+m.myclass_id;
    > foreach(int i in result)
    > m.AddToArray2(i);
    > }
    >
    > ======================
    > Queries: 1+2*(MyClass) (Bad)
    > Rows: (MyClass) + (MyClass*Array1) + (MyClass*Array2) =
    > (MyClass)*(1+(Array1)+(Array2)) (Few)
    > Columns: 1-3 (Few)
    > Data amount over the wire: Rows*Columns (Ok)
    >
    > Version 2
    > =========
    >
    > SELECT * FROM MyClass M
    > LEFT JOIN Array1 USING(myclass_id)
    > LEFT JOIN Array2 USING(myclass_id)
    > ORDER BY M.myclass_id;
    >
    > foreach(MyClass m in result)
    > {
    > // Greatly simplified, to the point of beeing buggy... just meant
    > for illustration.
    > if(m.myclass_id is the same as it was in the previous iteration)
    > {
    > first_m_with_a_given_class_id.AddUniqueToArray1(m. Array1);
    > first_m_with_a_given_class_id.AddUniqueToArray2(m. Array2);
    > }
    > }
    >
    > ==================
    > Queries: 1 (OK)
    > Rows: (MyClass)*(Array1)*(Array2) (Excessive amount)
    > Columns: 5 (Many)
    > Data amount over the wire: Rows*Columns (Massive)
    >
    > Greatful for any assistance.
    > Nicklas
    >
    Dikkie Dik Guest

Similar Threads

  1. Question how can save query in array by used sql
    By Unregistered in forum Brainstorming Area
    Replies: 0
    Last Post: April 29th, 09:53 AM
  2. Sort array from a Query
    By bettina@coaster.ch in forum MySQL
    Replies: 3
    Last Post: November 29th, 10:06 PM
  3. Query+Array
    By incubusm in forum Coldfusion Database Access
    Replies: 4
    Last Post: September 11th, 03:31 AM
  4. Feed query an Array
    By Clumsy Hamster in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: March 9th, 12:36 AM
  5. [newbie] Array and query
    By Nexus in forum PHP Development
    Replies: 4
    Last Post: September 16th, 08:13 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