Speed and best DB

Posted by: openda-dd on 3 August 2017, 3:50 pm EST

  • Posted 3 August 2017, 3:50 pm EST

    Hi, We have been playing with the product and works really well and gives me another option to provide something that is in our control.

    The problem we have at the moment is the speed compared to a competitive product.



    We have been using a MySQL and Progress database with about 360000 records in via ODBC.



    We are just using the standard generated schema so don't know if it relates to that?



    Any ideas?



    Thanks
  • Replied 3 August 2017, 3:50 pm EST

    Hi, thanks for coming back to me.

    The speed issue is when you first start as it seems to move the whole table into memory (from looking at task manager) and takes about 10-15 mins to do so.

    I have attached the schema so you can see the number of measures.

    The actual MySQL table is about 96mb but uses about 900mb in task manager.



    Cheers
  • Replied 3 August 2017, 3:50 pm EST

    Attached schema
    2008/09/mysql.txt
  • Replied 3 August 2017, 3:50 pm EST

    Openda,
    Thank you for your patience while I've looked into this.  After remodeling my test database to more closely match your schema, I was able to reproduce similar slow performance.  I have addressed this to our development staff as case 117845 and included your email address with the list of affected users for the case.  If you would like to be notified when a new build is made available that addresses this issue, you can subscribe to the announcements forums here: http://www.datadynamics.com/forums/81/ShowForum.aspx.

    Based on the schema you provided, I have noticed a couple of things that might help to improve the performance.  For starters I would recommend moving the measures that are related to dates from the measures dimension to an attributes dimension.  However, please note that if the date fields are the result of using the MySql date functions within your query string you could move the date fields by simply regenerating the schema without using these functions as the Schema AutoGenerator will automatically generate a date dimension with date attributes and a hierarchy for each date field.  If this is not the case and each field is stored in the database separately, you can move the fields from the measures dimension to the attributes dimension (or a new attribute dimension) by either using the schema builder API or by directly modifying the XML of the Schema file in a text editor so that the items pertaining to the date fields in the measures dimension are moved to the attributes dimension.  Another potential problem I have noticed is the "Description" field. If this field contains detailed descriptions of each item (i.e. one sentence to multiple paragraph descriptions), I would recommend removing this field from the schema altogether as this may greatly reduce the amount of data loaded into the internal data manager and in turn may reduce the loading time.

    If you have any additional questions please feel free to let me know.
  • Replied 3 August 2017, 3:50 pm EST

    Hi, Has anything been done on this at all as we have come back to review this area and would love to move forward with your product but cannot based on the present performace problems.
  • Replied 3 August 2017, 3:50 pm EST

    Openda,
    In my testing, ddAnalysis has worked well using an ODBC connection to a MySQL database (with the MySQL ODBC 3.51 Driver) containing over 500,000 records.  Could you please provide me with more detailed information (such as what you are doing prior to and when this occurs, how many attributes or measures are currently in the pivot view, etc.) so that I may better understand where you are experiencing the performance problems?
  • Replied 3 August 2017, 3:50 pm EST

    Openda,
    Our development staff is looking into this.  Unfortunately, I do not have a time frame as to when this may be addressed.  However, I have addressed your concerns for this case to our development staff. 

    If you have any additional questions please feel free to let me know.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels