6 Replies Latest reply on Dec 18, 2009 11:47 PM by Sameer Bhatt

    Efficiently retrieve MANY records with Advanced Datagrid?

    lathoudero

      Hi all,

       

      I've encountered the following issue:


      I have a database with more then 120.000 records, that I want to show with an advanced datagrid.

      It's grouped by 2 ID's, thus showing the results a group within a group. This works perfect!

       

      Only: it takes a long time before it shows the results, probably because it's downloading all the records at once (with the getAll() function).

       

      Is there a way to use advanced datagrid more efficiently? Like: first download the two columns that are used to define the groups, and only retrieve data that's visible once you click the group? (In this case, the 2nd group within the 1st group)

       

      This would greatly increase the loading-time of the whole application.

       

      Or should I take an other aproach to this application, like, using a tree-component, a simple datagrid, writing alternative update & retrieve functions in AS3 & PHP?

      Thanks!

        • 1. Re: Efficiently retrieve MANY records with Advanced Datagrid?
          lathoudero Level 1

          Just an observation:

           

          I've correctly setup my data services, and correctly configured the Paging operation.

          When I bind it to a simple datagrid, it works perfect, as I'd expect it to. It'll just retrieve data in a 'batch' with the amount of rows defined in the pagination-setup.

           

          This DOESN'T work with "Advanced Datagrid with TreeView", because it probably needs to retrieve ALL data first to create the "TreeList".

          So, don't use pagination with Advanced Datagrid. It's useless.

           

          The question remains: How can I efficiently use Advanced datagrid with a LOT of data?

           

          Because my next option is to use 2 components instead:

          1. A Treelist component with a getTreeData() service, with a (DESTINCT) query that retrieves the data I need to group by
          2. A DataGrid component with a getPaginatedDatagridData() service, with a variable in the WHERE query that's executed when a selection is made in the Treelist.

           

          But I guess this kind'a defeats the purpose of the "Advanced Datagrid"...

          • 2. Re: Efficiently retrieve MANY records with Advanced Datagrid?
            Sameer Bhatt Adobe Employee

            AdvancedDataGrid displaying hierarchical data works with pagination but GroupingCollection requires all the data to be present before it starts grouping.

            So, you can try -

            1. Async refresh in GroupingCollection, i.e., GroupingCollection.refresh(true)

            2. Lazy loading of properties.

            OR

            1. Grouping on the Server and sending the grouped data to the AdvancedDataGrid and using it through HierarchicalData.

            1 person found this helpful
            • 3. Re: Efficiently retrieve MANY records with Advanced Datagrid?
              lathoudero Level 1

              Well, I guess I'm almost there. I chose the last option, as it made more sence:

              • Grouping on the Server and sending the grouped data to the AdvancedDataGrid and using it through HierarchicalData.

               

              So this is what I did:

              PHP

                   public function getMptGrouped_paged($startIndex, $numItems) {
                        $stmt = mysqli_prepare($this->connection, "SELECT mpt_id, phone_nr, naam, tussenvoegsel, voorletters, aanhef, huisadres, nummer, toevoeging, postcode, plaats, emailadres, instelling, aangemeld_date, date(besteld_date) as 'besteld_date', IFNULL(date(export_date),'nieuwe bestellingen') as 'export_date' FROM $this->tablename WHERE besteld_date IS NOT NULL GROUP BY export_date, besteld_date LIMIT ?, ?");
                        $this->throwExceptionOnError();
                        
                        mysqli_bind_param($stmt, 'ii', $startIndex, $numItems);
                        mysqli_stmt_execute($stmt);
                        $this->throwExceptionOnError();
                        
                        $rows = array();
                        
                        mysqli_stmt_bind_result($stmt, $row->mpt_id, $row->phone_nr, $row->naam, $row->tussenvoegsel, $row->voorletters, $row->aanhef, $row->huisadres, $row->nummer, $row->toevoeging, $row->postcode, $row->plaats, $row->emailadres, $row->instelling, $row->aangemeld_date, $row->besteld_date, $row->export_date);
                        
                       while (mysqli_stmt_fetch($stmt)) {
                         $rows[] = $row;
                         $row = new stdClass();
                         mysqli_stmt_bind_result($stmt, $row->mpt_id, $row->phone_nr, $row->naam, $row->tussenvoegsel, $row->voorletters, $row->aanhef, $row->huisadres, $row->nummer, $row->toevoeging, $row->postcode, $row->plaats, $row->emailadres, $row->instelling, $row->aangemeld_date, $row->besteld_date, $row->export_date);
                       }
                        
                        mysqli_stmt_free_result($stmt);          
                        mysqli_close($this->connection);
                        
                        return $rows;
                   }
              
                      //This is used for Paginating
                   public function countBestellingenTotaal() {
                        $stmt = mysqli_prepare($this->connection, "SELECT COUNT(*) AS COUNT FROM $this->tablename WHERE besteld_date IS NOT NULL");
                        $this->throwExceptionOnError();
              
                        mysqli_stmt_execute($stmt);
                        $this->throwExceptionOnError();
                        
                        mysqli_stmt_bind_result($stmt, $rec_count);
                        $this->throwExceptionOnError();
                        
                        mysqli_stmt_fetch($stmt);
                        $this->throwExceptionOnError();
                        
                        mysqli_stmt_free_result($stmt);
                        mysqli_close($this->connection);
                        
                        return $rec_count;
                   }
              

               

              This works fine, and it works fast when I test this service (getMptGrouped_paged():Mpt[]) through "Test Operation".

               

              First test, I'm binding this service to a regular datagrid: Works perfect. Retrieving new rows as soon as the datagrid is supposed to show more then there is retrieved already.

               

              Now Using an Advanced Datagrid: new application, drag & drop, apply binding to service, testrun:

              result: Application hangs. I see the advanced datagrid, but it's stuck in retrieving the data.

               

              I'm new to using [HierarchicalData], so here goes my first attempt:

               

                   <fx:Script>
                        <![CDATA[
                             import mx.events.FlexEvent;
                             import mx.controls.Alert;
              
                             protected function adg1_creationCompleteHandler(event:FlexEvent):void
                             {
                                  getMptGrouped_pagedResult.token=mptService.getMptGrouped_paged();
                             }
                        ]]>
                   </fx:Script>
                   <fx:Declarations>
                        <s:CallResponder id="getMptGrouped_pagedResult"
                                   result="getMptGrouped_pagedCollection.refresh()"/>
                        <mptservice:MptService id="mptService"
                                          fault="Alert.show(event.fault.faultString + '\n' + event.fault.faultDetail)"
                                          showBusyCursor="true"/>
                        <mx:GroupingCollection id="getMptGrouped_pagedCollection"
                                          source="{getMptGrouped_pagedResult.lastResult}">
                             <mx:Grouping>
                                  <mx:GroupingField name="export_date"/>
                                  <mx:GroupingField name="besteld_date"/>
                             </mx:Grouping>
                        </mx:GroupingCollection>
                   </fx:Declarations>
                        <mx:AdvancedDataGrid y="10"
                                   id="adg1"
                                   designViewDataType="tree"
                                   left="10"
                                   right="10"
                                   height="500"
                                   creationComplete="adg1_creationCompleteHandler(event)"
                                   ><!--dataProvider="{getMptGrouped_pagedCollection}"-->
                        <mx:dataProvider>
                             <mx:HierarchicalData source="{getMptGrouped_pagedCollection}" />
                        </mx:dataProvider>
                        <mx:columns>
                             <mx:AdvancedDataGridColumn headerText="mpt_id"
                                               dataField="mpt_id"/>
                             <mx:AdvancedDataGridColumn headerText="phone_nr"
                                               dataField="phone_nr"/>
                             <mx:AdvancedDataGridColumn headerText="naam"
                                               dataField="naam"/>
                             <mx:AdvancedDataGridColumn headerText="tussenvoegsel"
                                               dataField="tussenvoegsel"/>
                             <mx:AdvancedDataGridColumn headerText="voorletters"
                                               dataField="voorletters"/>
                             <mx:AdvancedDataGridColumn headerText="aanhef"
                                               dataField="aanhef"/>
                             <mx:AdvancedDataGridColumn headerText="huisadres"
                                               dataField="huisadres"/>
                             <mx:AdvancedDataGridColumn headerText="nummer"
                                               dataField="nummer"/>
                             <mx:AdvancedDataGridColumn headerText="toevoeging"
                                               dataField="toevoeging"/>
                             <mx:AdvancedDataGridColumn headerText="postcode"
                                               dataField="postcode"/>
                             <mx:AdvancedDataGridColumn headerText="plaats"
                                               dataField="plaats"/>
                             <mx:AdvancedDataGridColumn headerText="emailadres"
                                               dataField="emailadres"/>
                             <mx:AdvancedDataGridColumn headerText="instelling"
                                               dataField="instelling"/>
                             <mx:AdvancedDataGridColumn headerText="aangemeld_date"
                                               dataField="aangemeld_date"/>
                             <mx:AdvancedDataGridColumn headerText="besteld_date"
                                               dataField="besteld_date"/>
                             <mx:AdvancedDataGridColumn headerText="export_date"
                                               dataField="export_date"/>
                        </mx:columns>
                   </mx:AdvancedDataGrid>
              

               

              No luck. When I launch this application, it gets stuck on retrieving the data. I'm sure it goes wrong somewhere between [GroupingCollection] and [HierarchicalData], but I haven't been able to find a good example that can help me with HierarchicalData.

              • 4. Re: Efficiently retrieve MANY records with Advanced Datagrid?
                Sameer Bhatt Adobe Employee

                You need to create hierarchical (nested) data structure on the server and return it.

                And then, use this returned data structure in the AdvancedDataGrid as hierarchical data, like -

                 

                <AdvancedDataGrid dataProvider="{new HierarchicalData(OPERATION_RESULT)}" />

                 

                The format for the data structure should be similar to -
                [
                {
                label:node1, 
                     children: [
                                      {label:sub_node11,......more properties here},
                                      {label:sub_node12,......more properties here}
                                      ]
                },
                {
                label:node2, 
                     children: [
                                      {label:sub_node21,......more properties here},
                                      {label:sub_node22,......more properties here}
                                      ]
                }
                ]

                1 person found this helpful
                • 5. Re: Efficiently retrieve MANY records with Advanced Datagrid?
                  lathoudero Level 1

                  Hi Sam, thanks for your help!

                   

                  I'm not sure though how to create the returned datastructure?

                   

                  Example:

                  Product_id
                  productname
                  category_a
                  category_b
                  1Toy Aboy0-3
                  2Toy Bgirl0-3
                  3Toy Cgirl3-6
                  4Toy Dboy6-9
                  5Toy Egirl3-6
                  6Toy Fboy0-2
                  7Toy Gboy6-9

                   

                  Grouping by [category_a] and [category_b], I would like to get the following in the Advanced Datagrid, but paginated:

                   

                  Product_IDProductname
                  boy0-2
                  6Toy F
                  0-3
                  1Toy A
                  6-9
                  4Toy D
                  7Toy G
                  girl0-3
                  2Toy B
                  3-6
                  3Toy C
                  5Toy E

                   

                  In my PHP-service, I would create the following query:

                   

                       public function getProducts_paged($startIndex, $numItems) {
                            $stmt = mysqli_prepare($this->connection, "
                  SELECT product_id, category_a, category_b, productname FROM $this->tablename GROUP BY category_a, category_b LIMIT(?,?)");
                            $this->throwExceptionOnError();
                           
                            mysqli_bind_param($stmt, 'ii', $startIndex, $numItems);
                            mysqli_stmt_execute($stmt);
                            $this->throwExceptionOnError();
                           
                            $rows = array();
                           
                            mysqli_stmt_bind_result($stmt, $row->
                  product_id, $row->category_a, $row->category_a, $row->productname);
                           
                           while (mysqli_stmt_fetch($stmt)) {
                             $rows[] = $row;
                             $row = new stdClass();
                             mysqli_stmt_bind_result(
                  $stmt, $row->product_id, $row->category_a, $row->category_a, $row->productname);
                           }
                           
                            mysqli_stmt_free_result($stmt);         
                            mysqli_close($this->connection);
                           
                            return $rows;
                       }

                   

                  This will give me the right structure, Right?

                   

                  Now:

                  - How to tell the Advanced Datagrid which 2 groups to apply?

                  • 6. Re: Efficiently retrieve MANY records with Advanced Datagrid?
                    Sameer Bhatt Adobe Employee

                    Hi,

                     

                    I'm not an expert in PHP but here is a simple function I wrote to group based on category_a -

                     

                    public function getProducts_grouped() {
                           
                              $stmt = mysqli_prepare($this->connection, "SELECT * FROM $this->tablename ORDER BY category_a");
                              $this->throwExceptionOnError();
                             
                              mysqli_stmt_execute($stmt);
                              $this->throwExceptionOnError();
                             
                              $rows = array();
                              $children = array();
                             
                              mysqli_stmt_bind_result($stmt, $child->Product_id, $child->productname, $child->category_a, $child->category_b);
                             
                              $firstTime = true;
                             
                              $category = "Dummy Category";
                             
                              while (mysqli_stmt_fetch($stmt)) {
                              
                               // if firstTime is true then just add the child row in the children array
                               // match category from the next time
                               if (!$firstTime && $category != $child->category_a)
                               {
                                   // if the category is different then create a group like -
                                   // {category_a = category, children:[{...},{...}]
                                   $row->category_a = $category;
                                   $row->children = $children;
                                   $rows[] = $row;
                                  
                                   // reset variables
                                   $children = array();
                                   $row = new stdClass();
                               }
                               $firstTime = false;
                              
                               // add the new child row in the children array
                               $children[] = $child;
                              
                               // get the new category
                               $category = $child->category_a;
                              
                               $child = new stdClass();
                               mysqli_stmt_bind_result($stmt, $child->Product_id, $child->productname, $child->category_a, $child->category_b);
                             }
                            
                             // handle the last group here
                             $row->category_a = $category;
                             $row->children = $children;
                             $rows[] = $row;
                                  
                              mysqli_stmt_free_result($stmt);         
                              mysqli_close($this->connection);
                             
                              return $rows;
                         }

                     

                    In the MXML file, bind to the AdvancedDataGrid like -<fx:Script>
                            <![CDATA[
                                import mx.collections.HierarchicalData;

                                import mx.events.FlexEvent;
                                import mx.rpc.events.ResultEvent;

                     

                                protected function adg_creationCompleteHandler(event:FlexEvent):void
                                {
                                    getProducts_groupedResult.token = productService.getProducts_grouped();
                                }

                     

                                protected function getProducts_groupedResult_resultHandler(event:ResultEvent):void
                                {
                                    adg.dataProvider = new HierarchicalData(getProducts_groupedResult.lastResult);
                                }

                            ]]>
                        </fx:Script>
                        <fx:Declarations>

                            <s:ProductService id="productService" />
                            <s:CallResponder id="getProducts_groupedResult" result="getProducts_groupedResult_resultHandler(event)" />
                        </fx:Declarations>
                        <mx:AdvancedDataGrid id="adg" creationComplete="adg_creationCompleteHandler(event)">
                            <mx:columns>
                                <mx:AdvancedDataGridColumn dataField="category_a" />
                                <mx:AdvancedDataGridColumn dataField="Product_id" />
                                <mx:AdvancedDataGridColumn dataField="productname" />
                                <mx:AdvancedDataGridColumn dataField="category_b" />
                            </mx:columns>
                        </mx:AdvancedDataGrid>

                     

                    I've attached the image of the result AdvancedDataGrid.

                     

                    In a similar manner, you can extend this to group based on both the categories.