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.
rayluvsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rayluvsAuthor Commented:
Please note that it super slow and is not all reports.  Also noticed that some reports display a total page at then off the reality (don't know if this is related).  We are currently troubleshooting, any observation is greatly appreciated.
0
mlmccCommented:
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
0
rayluvsAuthor Commented:
What is the SQL the report is using?

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 ...
0
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

mlmccCommented:
Filtering - WHat do you have in the Crystal select expert?

What SQL is sent to the database
Use REPORT --> SHOW SQL

mlmcc
0
rayluvsAuthor Commented:
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)...
0
rayluvsAuthor Commented:
Checked "selects expert" and the filtering is:

{TableParent.InvNumber} = {?InvoiceNo} and
{TableChild.IncludeInvNumber} = {?IncludeInvNumber}

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
0
James0628Commented:
I think the option that he's referring to is normally under Database, not Report.  Try Database > "Show SQL Query".

 James
0
rayluvsAuthor Commented:
Ok
0
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Try to upgrade the Crystal runtime to Service Pack 7. It provide fixes to two known issues that might lead to slow performance.
0
rayluvsAuthor Commented:
The crystal is from the visual studio 2012, that is Visual Basic 2012.  

Still ok to upgrade runtime?

Please advice.
0
mlmccCommented:
SO long as you are updating the Crystal for VS2012 and not trying to use a full version update

mlmcc
0
rayluvsAuthor Commented:
Ok will do
0
rayluvsAuthor Commented:
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?
0
James0628Commented:
I don't know what "Column List" is, but assuming that it's something like a transaction table, with entries for some vendors and not others, the first couple of things that come to mind are:

 If you link from Vendors to Column List, the query could be reading every row in Vendors and then looking for matching rows in Column List (unless the db is optimizing the query).  If you have a lot of vendors that don't have entries in Column List, or don't have entries that match your parameters, then it's reading a lot of extra vendors.  If the link is from Column List to Vendors, then only the vendors that have matching rows in Column List are read.

 The other, possibly more important, thing would be the table indices.  Is the vendor ID (whatever you use to link Vendor to Column List) an index in Column List?  If not, trying to find each vendor in Column List is going to take time (depending on how many rows are in Column List).  If the columns that are used to link them are an index in Vendor, then finding the matching Vendor (when linking from Column List to Vendor) will be much faster.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rayluvsAuthor Commented:
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;

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)
0
James0628Commented:
To see if a column is a key, you'd look at the table structure somewhere, like in SQL Server Management Studio (since you said that the db is MS SQL).

 As for building a report, what you described sounds like the "normal" method: Add tables to the report, link them, and put the fields on the report.

 Almost all of my reports used stored procedures, but reading the tables directly wasn't really an option for most of the reports, because they had to get data from two separate sets of tables.

 James
0
mlmccCommented:
You can also look in the DATABASE EXPERT on the LINKING tab.  Indexed fields are noted with an icon.  Click the INDEX LEGEND button to see what the icons mean

mlmcc
0
James0628Commented:
Oh.  Duh.  Good point mlmcc.  :-)  I forgot all about that.  I guess that shows how little I've used tables in reports.

 James
0
mlmccCommented:
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
0
rayluvsAuthor Commented:
Checked the report (prior fixing the link between tables) and there are icons next to certain columns (see below).

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?


crt
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)
0
mlmccCommented:
The transaction table doesn't have any indexes or keys.  If you were selecting based on that table, it would probably cause slower execution

I generally select the tables or views from the database then link them manually in the order I need them.
I then create the report by dragging the fields onto the report.

In some cases I use a command.
When you create a new report and add the data source, one of the options in Add a Command.  A command is just the SQL required to get the data you want for the report.  I use this when there are issues with how tables are linked or is there is a need for a subselect.

mlmcc
0
James0628Commented:
What do the index colors mean?

 As mlmcc mentioned, there should be an "Index Legend" button on the right side in the Links tab.  Basically, the color just identifies the index "number" (eg. red is the 1st index).


 Just to make sure, the link in your screenshot, from the vendors (PM00200) to the transactions, is how you had the tables linked before, when the report was slow.  Correct?

 It would make sense for that to be slow.  Since the vendor field in the transaction table (FK_VENDOR) does not appear to be an index in that table, the db would have to read _every_ transaction record to find the records for a specific vendor, and it would have to do that for _every_ vendor.  IOW, it reads vendor 1 from the vendor table, then reads _every_ transaction record, looking for transactions for vendor 1.  Then it reads vendor 2 from the vendor table, then reads _every_ transaction record again, looking for transactions for vendor 2.  And so on for every record in the vendor table.  If you've got 1000 vendors, it would read the entire transaction table 1000 times (unless the db was doing some query optimization somewhere behind the scenes).

 With the link changed to go from the transaction table to the vendor table (PM00200), the db reads a transaction record, then uses the index in the vendor table (VENDORID) to quickly find the corresponding vendor record, then reads the next transaction and gets that vendor, and so on.  That should be _much_ more efficient.

 James
0
mlmccCommented:
Here is a link expert snapshot with the legend displayed

mlmcc
LinkExpert.JPG
0
rayluvsAuthor Commented:
You are correct, there is a button (see below).

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.

crv
0
mlmccCommented:
WHen you create a new report and select the data source the first option under the data source is ADD COMMAND

Data SOurce

The command is written in the Crystal command editor.  You can write SQL in the database and test it then copy it into Crystal.

When you select ADD COMMAND , a new window opens to allow you to enter the SQL

mlmcc
0
James0628Commented:
Adding the tables to the report is just the "user-friendly" option.  You don't have to know how to write a query.  You select the tables, tell CR how to link them if necessary, and select the fields, and CR writes the query for you.

 If you know how to write a query, then you might prefer to do that yourself.  For relatively simple things, either approach works.  For more complicated situations, you may be able to do things more efficiently if you write the query, and for some situations, you may not have any choice but to write the query.

 If you really want to make the query as efficient as possible, you may want to use a stored procedure.  MS SQL will try to optimize those to make them more efficient.  But if the query isn't very complicated, there may be little or nothing to be gained by putting it in a stored procedure.

 James
0
rayluvsAuthor Commented:
Thank you all very much!!! Super informative and helpful!
0
rayluvsAuthor Commented:
Thank you all a lot!  Tried to award as just as possible.
0
James0628Commented:
You're welcome.  Glad I could help.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.