open report is slow

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm working with ADP 2003.
When I open the database and I open any report for the first time it take
time, after I can open any report quickly.
For example, I have a report, when I open the database and I open it, it
takes about 30 sec. If I open it again is very quickly, about 2 sec.
I think the database window is not refresh while none report is open.

How can increase the speed to open reports for the first time?

Thanks
jcp
 
What is the exact record source used for the report and what happens if you
try record source this in the Query Analyser?

Does this happens each time you close the ADP project or if you open a form
first? Did you take a look with the SQL-Server Profiler?
 
The record source is sp for this specific report
I have a menu where I manage all reports.
I open the database, from mainmenu, go to reports menu.
Here we have many reports, where we can select parameters, date etc.
I select the report and for the first time it takes time, about 30sec, I
close, re-open it again and is quickly, 2 sec. I select another parameters, I
re-open it again and 2sec is open. I select another report and is also faster.

If I close the database and I open it again, I have the same scenario.

I didn't look for SQL.server profile.

Could you give a tips or a site where I can see how to analyze the database
via SQL-server profile?

Thanks Sylvain

jcp
 
Yes but how do you write exactly the record source? Only the name of the SP
(aka MyStoredProcedure) or with an EXEC statement ("Exec MyStoredProcedure
FirstParameter, SecondParameter, ThirdParameter, ...") ?

Did you set the Record Source Qualifier to dbo if it's the first case? Are
all table fully qualified (with the prefix dbo.) in the SP? Are you using
any other schema than dbo? Parameters sniffing (aka: how is the execution
plan from call to call) ?

I would say that this looks like a time-out problem somewhere in your code
but it's hard to tell with this information. Any autoexec macro or ADO code
running inside the report? Any image? What happens if you open two ADP
projects? Any VPN or firewall?
 
I use the name of the SP.
I checked the Record Source Qualifier and was blank in the report, I put
dbo. I is important to set to dbo?
Normally in forms, by default the set is dbo. If is importantm if gives more
consistent, mor speed, I will check in all my forms and reports.
The tables in adp, doesn't show dbo.Name, show only the name.
But if a link the tables for a file mdb thru odbc, it shows dbo.name
 
Yes, it's important to put dbo for two reasons. First, on SQL-Server, you
can have two tables, SP or View with the same name but with different
schemas (or owner). If you don't explicitely mention the schema, SQL-Server
cannot make the assumption that it's always the same table, view, function
or SP that is called and as such, this can lead to an unusual amount of
recompilation, missing cache hittings and performance problem. The second
is that ADP has often the bad idea of writing the current user as the owner
to be used if you don't specify dbo. If the current user is not dbo, then
you will have problem with items not found (table, view or SP).

Try your procedures with the WITH RECOMPILE option. What happens if you
create a blank ADP project with a very simple report or if you replace the
name of the SP with a call to an EXEC statement as the record source of the
report?

I think that in your case, only the use of the Profiler will give more
details on this problem.
 
Back
Top