Query - Fast , Report SLOW ! Why ?

[saustin]

Hi All,
What possible reason could there be for a report based upon a query to run slow (1 min) when the query returns (when run by itself) data instantly ?
Any thoughts or hints in the right direction appreciated. Think Access 2000 is getting confused, very bizzare.
Thanks, Steve.

 

[BanditWk]

What slows down a report even if the query is fast is usually calculations within the report itself - unbound fields that you have calculations performing.

HTH Roy
aka BanditWk
Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

 

[saustin]

Thanks Roy,
Do have two query fields that are the result of called functions. raw2str(fieldname), is a function located in a module (STRCONV(DATA, VBUNICODE). So I have a situation this data appears instantly in the query but takes 50 seconds to appear (looks great when it does) in the report. Am basing this on the query field and have no unbound controls. This is exactly where it slows down.

Steve.

 

[KyleS]

Steve,
What about formatting - lines, boxes, pics, sorting?

To much of that will slow a dB down like nobody's business... Kyle

 

[saustin]

Hi Roy + Kyle,
Thanks so much for your replies. Am still stumped on this one. Just double checked the query and got my 1 second super fast response time.
When trying to start the report wizards from the query it error's out saying there are too many fields to choose. So, went into design view picked just a few fields and again ended up with the slow response 1min plus. 1. Sorting was the same as the query sort field and order. 2. No calculations (beside page n of m), 3. No percentages, sums, etc.
Keep in mind the same report is instant in Access97. The 2000 version has two modified fields based upon a str2conv function (but again these are returned correctly instantly in the query). Where have I gone wrong ?
Steve.

 

[MichaelRed]

Sorting and (PARTICULARLY) Grouping is usually the culprip for most of my reports. In particular, the various options can get crossed up and make a report pratically infinitly slooooooooo ... ooooo ... W

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

Question. When you refer to the query being fast, is that to display the first data page or when moving to the end of the data? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[saustin]

Good Morning Terry,
All of the data is returned. 99 % of the invoices ONLY have < 3 items (most have one with considerable dollars).
Steve.

 

[saustin]

Hi Michael,
Sorting is the same field used as the primary sort (and first field) in the query. Even tried changing the name to remove any confusion but with no change in performance.
The search continue ........
Steve.

 

[MichaelRed]

It is often not what is sorted / grouped, but the &quot;PROPERTIES&quot; associated - particularly w/ the grouping. For instance, if you attempt to Group on Each and Keep Entire group together, the process may take a LOT longer than if you keep with first detail. This may not be the correct example, but it is the concept.


BTW, unless you need the query to be sorted for other purposes, it is a COMPLETE waste of time to sort it for the report AND specify the sort / group. Ms. A.'s reporting tool will go ahead and sort the recordset REGARDLESS of its' orgin.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[saustin]

Hi Michael,
Took all sorting off the query and is still returned data instantly.
Changed the default printer, also changed the sort field (keep entire group was set to NO) but with no change in the snail like performance.
Steve.

 

[saustin]

Hi All,
Just an UPDATE on our fast query slow report issue.
It has gone away !!!!!
In our query we use a function that involves an odbc connection to an Oracle db. Went through an Oracle conversion, memory addition and fine tuning a month ago. Revisited the reports which were instant in A97 but slow in A2k and now they are all fast. Tripled the amount of memory on the server, optimized it for throughput (according to a suggestion for running oracle on NT from Oracle), changed some other Oracle server parameters ..... and we are back in lightning performance city.


Steve.