Skip to Content
avatar image
Former Member

100.000 Records in Query Result. Performance Problems

Hi all,

our query has several variables on the entry screen, but they aren't all mandatory. So, if the user select only one of them, there is a possibility to get results with nearly 100.000 record sets. The result is a "Connection Time out". Aggregation, Indexing or Compression make no sense in our case. Perhaps enlarging the temporary Table "PSAPTEMP"?

Does somebody have any experiences with those massive data occurrences?

Thanks for help

Andreas Wendt

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    Aug 10, 2004 at 06:48 PM

    Hello Andreas

    Excel is limited to 65535 rows, so even if you fix your time out issue, it won't work. You should use the open hub (spoke) to generate a text file. That also runs in background, therefore you won't get a timeout neither.

    Good luck

    Ioan

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Ioan,

      normaly we don't use Excel, only web. There we should'nt have any restriction. Any idea for web?

      Thanks a lot

      Andreas

  • avatar image
    Former Member
    Aug 11, 2004 at 01:49 PM

    If they are executing it in Excel, the the users must be educated that if they bring back such a large recordset in Excel, they will have problems.

    In our case we execute on the web and provide a link to export to Excel or CSV. In your case if the users <b>truly</b> <b>need</b> a recordset that large, then <b>CSV</b> or as sugested Open Hub may be your options, but you would need to have it execut on the web, not in Excel to get to the CSV option.

    Aggregation, Indexing and Compression of course would only help in the speed in which the dataset is returned, not in the amount of data.

    I would also try and look at the user requirements to see if you can provide more initial variables to allow the user to reduce the recorset or find a common mandatory variable that you can apply.

    If the query design crosses functional areas or departments, you may look to create multiple queries for each department with different mandatory variables for each to suite all the users needs. A bit redundant yes, but it could be worth it.

    - Stephen

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Even in Excel you need to extccat the file in a CSV format, right? Than the Info Spoke makes sense as it runs in background

      Ioan