Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Running Reports Online - WAY TOO SLOW

Status
Not open for further replies.
DianaStewart

DianaStewart

Programmer
Apr 16, 2002
58
0
0
CA
I am running a report by typing in the url in the browser. I am using Access 2000 as a backend. The database contains 30000 records. A majority of the fields are indexed. My report contains 3 parameter fields, Name, Date1 and Date2...

Here is what I have in my Record Selection forumla editor:

{DB1.Date} >= {?Date1} and {DB1.Date} <= {?Date2} and {DB2.Name} = {?Name}

It takes excatly 2 minutes for the parameter fields to appear online and then takes a good 15 minutes to run the report... this is way too long ...


I am actually quite disappointed with Crystal Reports, this seems to be a very common problem... How can you ask a customer to stay onlione for 15 minutes waiting, waiting and waiting...

Any suggestions?
 
Sort by date Sort by votes
I suspect that your record selection criteria is the culprit, not Crystal.

Whenever you add something to the record selection formula, make sure that you test the Database->Show SQL Query to assure that this is being passed to the database.

For instance, if you have a datetime field, and a date parameter, the datatypes are different, and Crystal will NOT pass this to the database, so all of the processing si done locally in CR.

If you use a DATE parameter, convert it to a datetime type, or if you don't need to display the time in the report anywhere, use the File->Report Options->Convert Date-Time field TO DATE.

This should pass the SQL and make that report pop right away, unless you have a horrid database.

Perhaps you're not using a SQL database, or you have an old version of Crystal, you didn't share anything specific about your report, if you do so in the future, it will simplify the task of providing good advice.

-k kai@informeddatadecisions.com
 
Upvote 0 Downvote
I indicated in my thread that I am using an Access2000 database. The two date parameter fields are Date data type that the user can select from the Date control...

Any suggestions
 
Upvote 0 Downvote
As I indicated in my thread, are you passing through the SQL?

Perhaps you're using the native connectivity, which does not pass through SQL.

ODBC will.

Try saving your report to a different name and changing the connectivity to ODBC and make sure that the SQL is passed, perhaps this will improve performance for you.

-k kai@informeddatadecisions.com
 
Upvote 0 Downvote
hi,
Could you please check your show sql query to see if 2 tables are joined correctly? If you can't figure it out, why don't copy and paste your sql query in the threat, let others figure it out for you.

Ted
 
Upvote 0 Downvote
I am using this as my query and this is placed in the Edit Selection Formula..

{DB1.Date} >= {?Date1} and {DB1.Date} <= {?Date2} and {DB2.Name} = {?Name}


I am not using ODBC, rather when I add a database to the report from Data Explorer, I double click Database Files and then select Find Database File....

Is it faster viewing reports VIA ODBC then a direct connection to the database?

I really like CR but loosing and receiving a lot of pressure from Upper Management...

I do want to continue to use CR
 
Upvote 0 Downvote
Where is your join between DB1 and DB2 tables? I assume your where clause should be like this:

select DB1.*, DB2.* from DB1, DB2 where {DB1.Date} >= {?Date1} and {DB1.Date} <= {?Date2} and {DB2.Name} = {?Name}

If is really like this, Crystal Report will bring all the records or even all in DB1 plus all in DB2 or even worse to front-end then query it, I can't remember exactly. It will be extremely slow.

just go to database in the upper menu then show sql query, open it and add join inside, after that, it should be popup in seconds.

hope it helps.

Ted

 
Upvote 0 Downvote
When clicking on the Database menu the SHOW SQL QUERY item is greyed out... I cannot select it..
 
Upvote 0 Downvote
sorry, need to input your parameter fields first and login to database already, it will show up.

Ted
 
Upvote 0 Downvote
I made a copy of my db and renamed the db and the report... and made an odbc connection... when I goto refresh the report I encounter this error:

Blob field or memo field cannot be used in a formula..

The thing is in my backend the field being refused is Text data type.. works fine with a direct connection to the db but when i change the connection to odbc it will not recognize the field in the forumla eventhough it shows as a text datatype any ideas?
 
Upvote 0 Downvote
Ted: I think that you're mistaken, it's not a SQL database when using direct connectivity - test it.

Diana: The problem you're encountering is the 254 character limit with formulas, though I didn't realize that you could use > 254 with direct connectivity using Access, interesting...

You can create a SQL Expression (or multiple if you need to check > 254 chars), and use that in your formula.

An example:

{fn LEFT(Customer.`Customer Name`,254 )}

Hopefully this won't slow things back down, I'd just eliminate this from the report for the time being to determine if ODBC provides a significant performance increase before I spent much time on it.

-k kai@informeddatadecisions.com
 
Upvote 0 Downvote
It is definitely faster using an ODBC connection rather than directly connected to the database. There is only one problem I encounter and that is when you type in the url of the report, it takes a good minute and a half for the parameter field to appear on the screen. How can I fix this, if I can?
 
Upvote 0 Downvote
-K,

Thanks for what I don't know. But we used the native connection with stored procedures 2 years ago and didn't see any slow in the process. So, I can't see any difference between ODBC and Native Connection. Theoritically, native connection should be faster then odbc without talking with crystal report.

Thanks.

Ted
 
Upvote 0 Downvote
Once I type in the url of the report in the browser it takes a long time for the first parameter field to appear.. why? and how can I fix this?
 
Upvote 0 Downvote
Ted: The database is Access, there are no Stored Procedures. And you cannot use Show SQL when using a direct connection against an Access database. If you use ODBC, you can, so that means that you can pass SQL, hence the solution offered.

Reread the thread.

-k kai@informeddatadecisions.com
 
Upvote 0 Downvote
Here is what I have placed in the Report\Edit Selection Formula:

{DB1.Date} >= {?Date1} and {DB1.Date} <= {?Date2} and {DB2.Name} = {?Name}


Are you suggesting to remove this and replace it with a sql string?

Where and how, and what should I put using my example...

Help is appreciated..

Diana
 
Upvote 0 Downvote
The overall speed is an improvement but it takes 4 minutes for the parameter fields to appear, why and how can I fix this?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

D
  • Question
Crystal Reports for Visual Studio 2022 Download
Replies
1
Views
178
hilfy
hilfy
David Richardson
  • Locked
  • Question
Adding BLOB Fields to Reports in Crystal Reports Professional 8.5
Replies
7
Views
141
hilfy
hilfy
SarahSCPDAdmin
  • Locked
  • Question
Crystal Reports - Group question 1
Replies
2
Views
109
SarahSCPDAdmin
SarahSCPDAdmin
CodeWell
  • Locked
  • Question
Crystal Reports RPTR Format Not Available
Replies
2
Views
88
CodeWell
CodeWell
Niki_S
  • Locked
  • Question
Can't link tables in crystal reports
Replies
1
Views
53
kray4660
kray4660

Part and Inventory Search

Sponsor

Back
Top