Visokio website     Downloads     Video tutorials     KnowledgeBase  
Performance: Custom SQL - Data retrieval time? - Visokio Forums
Performance: Custom SQL - Data retrieval time?
  •     kattasureshkumar November 14, 2012 7:50AM
    We are using the Database Table as input to retrieve data from the table which is having about 2 Million Records. But the Omniscope is taking long time to load the data more than an hour. Please let us know if we can shorten this time.

    System used to check this performance : 16 GB RAM, i3 3.3 GHz Processor, 64 Bit.

    Thanks, Suresh.
  • 14 Comments
  •     chris November 14, 2012 7:54AM
    Hi Suresh,

    Have you tried reading data without a custom SQL statement? Is this any quicker? You need to be careful when creating a custom SQL statement to ensure that any filtering is done on indexed columns, otherwise the database can take a long time to retrieve the data. Is it possible that you are querying on none-indexed columns?
  •     kattasureshkumar November 14, 2012 7:56AM
    Hi Chris,

    We had also used by directly selecting the table name from the drop menu. Same results.
    We have created indexes on the column for all the tables.
  •     chris November 14, 2012 8:09AM
    Hi,

    So when you select a table name from the drop-down menu, it's still slow.

    What type of database is it? Can you post a screenshot of the database configuration? What version of Omniscope are you using?
  •     kattasureshkumar November 14, 2012 8:19AM
    Yes Chris , even if we select from the drop-down menu it's the same result.

    Database : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
    Omniscope Version : 2.8-alpha b329 (Oct 5, 2012 11:08:02 PM) r72129

    Using the Alpha version as multi-insert feature for data in oracle tables is there in this version.
  •     chris November 14, 2012 8:22AM
    Can you send us a screenshot of the database configuration?
  •     kattasureshkumar November 14, 2012 8:32AM
    Pls check this file
  •     chris November 14, 2012 8:37AM
    Hi,

    Thanks for posting these. Are you defining any filters (is anything specified in the "Filter" tab)? In the configuration you are showing us you are reading 6000 records from the database, is this slow?
  •     kattasureshkumar November 14, 2012 8:41AM
    Hi Chris,

    we had not defined any filters.
    Just selecting only the tables and querying the data.
    Attachments
    Filter.JPG 171K
  •     chris November 14, 2012 8:58AM
    Hi,

    How long does the 6000 records take to download?
  •     kattasureshkumar November 14, 2012 9:02AM
    for 6000 records it is taking 10 Mins.
    We have another tables which are having 4 Lakhs, 21 Lakhs records.
    These tables are not able to load into Omniscope. The progress bar is stuck at 8% for a long duration.
    Attachments
    Stuck.JPG 179K
  •     chris November 14, 2012 9:13AM
    Hi,

    6000 records should not take 10 minutes. I've just tried downloading from 200,000 records from SQL server and it takes around 30 seconds. Other users have not reported this problem, so it suggests that it may be something to do with your setup. There appears to be a bottleneck somewhere. Here are some suggestions:

    1. Can you install the latest version of Omniscope 2.8 (b398). I don't think there have been any bug fixes in this area, but it would be worthwhile ensuring you have the same version as me.

    2. Can you publish a 6000 record dataset to the database and try downloading it, are you still experiencing the same problem with the new table?

    3. Is there a possibility that there are network issues which are causing the slowdown? Do you have an equivalent database at a different location you can test?

    4. Can you ensure you are using the correct database driver. If necessary clear your driver by choosing "Settings>Advanced>Data sources>Reset database driver locations".

    Please let us know how you get on with this.
  •     kattasureshkumar November 14, 2012 9:35AM
    Installed the latest Alpha version of Omniscope 2.8 (b398).
    Our Oracle DB Version is Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod , so used the JDBC Driver "ojdbc14.jar" to connect to the DB.

    Now we are able to load the data into the Omniscope for about 6000 records in less than a second.

    But again the issue is same for tables with huge data of 4 lakhs & 21 lakhs.
    Attachments
    New Version.jpg 320K
  •     kattasureshkumar November 14, 2012 9:57AM
    Tried the same on a different system with the same Omniscope version & OJDBC Driver.

    6000 records had took 3 Mins to load the data into Omniscope.

    I Suppose in the prior test, the tool had loaded the data from the prior results and loaded the data quickly into omniscope.

    So the issue is now same for all the tables.
  •     chris November 14, 2012 10:31AM
    Hi,

    I don't think this is an Omniscope issue, since we can read a large amount of data from the database relatively quickly. Can you try all of the suggestions above. If you are not able to resolve this after trying these is the database in a location that we could temporarily access to see whether we experience the same problem?

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership