large volumes of data being returned

boo-boo

Hi,
Has anyone got any ideas as to how I might alert users if a dynamic query might return a very large amount of data ? I don't want to go down the web page type of solution if possible, ie first 1000, 2nd 1000 etc. Ideally if theres any links out there that would discuss various methods that'd be ideal. I'm sorta thinking of a matrix / table in the db which could spew out various messages to the user based on the potential volume of data returned, at least this could be tweaked etc. Any links , ideas .

thanks,

stevenmu

I can't think of any way other than to actually run the query and get a count on the results. You could then return an error to the user instead of sending the data to be drawn in their browser. I think some databases might let you set a max number of records returned per query and will an return an error if that's exceeded but I'm not sure.

ressem

What's the query? And is it the same query that's always run on that table?

In simple cases if you can run and display the results of the select count query, without returning the data, it should be reasonably quick and you'd imagine that the volume shouldn't change too dramatically before the data returning version.



http://www.techonthenet.com/sql/count.htm

TIP: Performance Tuning

Since the COUNT function will return the same results regardless of what field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields.

For example, based on the example above, the following syntax would result in better performance:

SELECT department, COUNT (1) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Now, the COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.

boo-boo

thx folks - alas the count(1) idea did't improve the perf much - its an Oracle db so they might already have some preprocessing stuff parsing queries. The queries involve quite a few joins on different tables, so the problem is any delay is expontential when large amounts might be returned.
anyhoo - thx again

Third_Echelon

Do you actually want to improve performance or do you just want to let the user know beforehand that a certain number of records will be returned?

If there is a lot of joins as you have stated you better make sure that your indexes are up to scratch. Try and create indexes so that they are unique scans rather than range scans etc. Try to cut down on full table scans if you want to improve performance.

Im not sure of the function that will tell you the number of rows that will be returned by a certain query.

bambam

I haven't used oracle in a while but it must have something akin to DB2's Explain tool. This will take a query and break in down for you, showing where index or full table scans occurs. And how costly each part of the operation is.

Alternatively, have a check on Quest.com for their Quest Central / Spotlight software. I find it much more friendly than the DB vendors native tools

Third_Echelon

yeah oracle shows an 'explain plan' and shows the associated cost of each access.

I've only ever viewed this through SAP, so I dont know where it is viewable in Oracle...

boo-boo

Third_Echelon wrote:

...do you just want to let the user know beforehand that a certain number of records will be returned?
QUOTE]
thats exactly what I'm looking for - its for a whole set of queries across an application. - thx again folks