How to handle large result set of a SQL query

[Former Member]

Hi,

I have a question about how to handle large result set of a SQL query.

My query returns more than a million records. However, the Query Template has a "row count" parameter. If I don't specify it, it by default returns only 100 lines of records in the query result. If I specify it, then it's limited to a specific number.

Is there any way to get around of this row count issue? I don't want any restriction on the number of records returned by a query.

Thanks a lot!

[Former Member]

Hi,

Can someone please give me a hint how I can use Business Logic Service to get back all my data (exceeds 120,000 records)? Let's forget about the applet and client computer's JRE limitation. Let's say just have a html to store the query result. I am new to BLS.

That would help me a lot.

[Former Member]

Please help us understand the application better - from a user's or business scenario point of view - and we can make suggestions for you.

[Former Member]

Hi, Mike,

I am using an applet (iGrid) to display the result set. So I should use Business logic service?

[jamie_cawley]

Hi Yue,

Mike is referring to an applet can only handle about 120,000 rows of data by default. This is due to the jre running out of memory and can be changed, but this is a client setting. As Erik suggested, the amount of data you are returning would be very hard for someone to read. I would suggest that you alter the query and allow the user to filter the data to produce a more readable result set.

Regards,

Jamie

[Former Member]

No human can manage that much data...in a grid, a chart, or a direct-connected link to the brain.

What you want to implement (much like other customers with similar requirements) is a drill-in and filtering model that helps the user identify and zoom in on data of relevance, not forcing them to scroll through thousands or millions of records.

You can also use a time-based paging model so that you only deal with a time "slice" at one request (e.g. an hour, day, etc...) and provide a scrolling window. This is commonly how large datasets are also dealt with in applications.

I would suggest describing your application in more detail, and we can offer design recommendations and ideas.

- Rick

[Former Member]

Yue,

The hard-stop is 120,000 rows of data...Using Business Logic Services there are ways to bring back the data you want...How are you trying to output the data? Applet, IRPT page, txt file, etc?

[Former Member]

Hi Erik,

Thank you very much for your thoughts.

However, my application is for back-end data analysis. The data is about all the users activities. I need to show the large data sets for the user who might be the administrator of the whole application. So I have to deal with the row count thing...

[erik_schrampf]

Yue,

There is no way around it you have to specify the row count of a query.

What are you doing in your query that you would want to view over a million rows? The reason you have to set a limit is for control of the application being created. No user can possibly use/view 1 million rows of data. You want the data being displayed be specific to what the client wishes to view. Have some filtering criteria to bring the count down to something reasonable the end user can use.

Thanks,

Erik