Help identifying culprit in Crystal Super slugish in Visual Basic 2012
We are developing in Visual Basic 2012 and noticed that when executing the crystal reportings, the process is super lazy in displaying results. After typing in the data in the interface user-entry filter screen, it just stays there for the longest.
What is the SQL the report is using?
Did you use tables or a command?
If tables did you link on indexed fields?
What filtering is being done?
How many records are being read/displayed?
mlmcc
Did you use tables or a command?
If tables did you link on indexed fields?
What filtering is being done?
How many records are being read/displayed?
mlmcc
ASKER
What is the SQL the report is using?
We are still troubleshooting the codes ...
answ: Ms Sql
Did you use tables or a command?
answ: i think is data tables
If tables did you link on indexed fields?
answ: will check
What filtering is being done?
answ: please explain
How many records are being read/displayed?
answ: ranges from 1,000 to 150,000
We are still troubleshooting the codes ...
Filtering - WHat do you have in the Crystal select expert?
What SQL is sent to the database
Use REPORT --> SHOW SQL
mlmcc
What SQL is sent to the database
Use REPORT --> SHOW SQL
mlmcc
ASKER
Hi just got in the PC to check the points... i am not an expert, but let me see (i usually work with vb6 but going to check these in our vb2012)...
ASKER
Checked "selects expert" and the filtering is:
How do i get to see "What SQL is sent to the database"? (can't seem to find "REPORT --> SHOW SQL") - i am a bit rusty on crystal specially in vb2012
{TableParent.InvNumber} = {?InvoiceNo} and
{TableChild.IncludeInvNumb
{TableChild.IncludeInvNumb
How do i get to see "What SQL is sent to the database"? (can't seem to find "REPORT --> SHOW SQL") - i am a bit rusty on crystal specially in vb2012
I think the option that he's referring to is normally under Database, not Report. Try Database > "Show SQL Query".
James
James
ASKER
Ok
Try to upgrade the Crystal runtime to Service Pack 7. It provide fixes to two known issues that might lead to slow performance.
ASKER
The crystal is from the visual studio 2012, that is Visual Basic 2012.
Still ok to upgrade runtime?
Please advice.
Still ok to upgrade runtime?
Please advice.
SO long as you are updating the Crystal for VS2012 and not trying to use a full version update
mlmcc
mlmcc
ASKER
Ok will do
ASKER
Just discovered problem, the linkings of tables (Database Expert >> Links).
Noticed that the main columns to print was linked to it master table. That is Vendors -- > Report Column List.
When changed it around, that is Column List --> Vendors, it ran much faster.
Please orientate as to why this happened?
Noticed that the main columns to print was linked to it master table. That is Vendors -- > Report Column List.
When changed it around, that is Column List --> Vendors, it ran much faster.
Please orientate as to why this happened?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent observation! Last night we came to the first conclusion. Since there are more vendors not match or linked to the translation (FYI: yes, the column list is he transaction table), it seemed to tJe longer.
We haven't consider the table index. I don't think the vendor column matching the transaction column is index. I would like to check it myself before getting to the office;
Another question I would like to asked; these days we have been researching ourselves and noticed that the programmer when doing the reports used the designer (adding column by column graphically or GUI to the reporting from the transaction table instead of creating first a script, joining all related tables and start the report from there). The question is,
We haven't consider the table index. I don't think the vendor column matching the transaction column is index. I would like to check it myself before getting to the office;
How can I determine if the column that the transaction being linked or matched to is index? (The vendors column being matched)
Another question I would like to asked; these days we have been researching ourselves and noticed that the programmer when doing the reports used the designer (adding column by column graphically or GUI to the reporting from the transaction table instead of creating first a script, joining all related tables and start the report from there). The question is,
What is your recommendation when creating a report specially if the transaction table have over 5 related master tables? (We would like to know your personal opinion in an optimum manner when creating reports)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh. Duh. Good point mlmcc. :-) I forgot all about that. I guess that shows how little I've used tables in reports.
James
James
Don't feel bad. I had to look to make sure I wasn't confusing the Crystal link page with the WebI query page.
mlmcc
mlmcc
ASKER
Checked the report (prior fixing the link between tables) and there are icons next to certain columns (see below).
Also note that the link (arrow) is from the master table (PM00200) to the transaction report. This was the problem (until now). We just linked that the arrow is pointing from the transaction table to the master table and until now is not slow.
Also, what is your recommendation when creating a report? ( I read that the way we mention how the reports were created is "normal" but would like to know you guys specific view)
And to close the question, there was a mention on "Did you use tables or a command?", by "command", do you mean using SQL script? (please explain)
When there are those icons means each are index? Why the different colors?
Also note that the link (arrow) is from the master table (PM00200) to the transaction report. This was the problem (until now). We just linked that the arrow is pointing from the transaction table to the master table and until now is not slow.
We ok doing this? You guys think this was the culprit?
Also, what is your recommendation when creating a report? ( I read that the way we mention how the reports were created is "normal" but would like to know you guys specific view)
And to close the question, there was a mention on "Did you use tables or a command?", by "command", do you mean using SQL script? (please explain)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You are correct, there is a button (see below).
And yes, as where the tables linked before in the previous pic, when the report was slow.
To understand, the command is not the first choice in Crystal Reporting rather, link tables (data and/or views)?
But if command more easy to handle since the query is tested outside the CR (prior developing the report), why not use it as first choice?
And yes, as where the tables linked before in the previous pic, when the report was slow.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you all very much!!! Super informative and helpful!
ASKER
Thank you all a lot! Tried to award as just as possible.
You're welcome. Glad I could help.
James
James
ASKER