Unanswered: Crystal Reports Accessing the Database with Preview
Our production database is having this issue where someone writing a report on production (ok I know this is bad practice but I just recently took the DBA role over and have to change things slowly). After they create the report they are previewing the data in Crystal. This seems to be obtaining an ASYNC_NETWORK_IO on the query they are running until the preview is closed.
1. Can anyone confirm that this is an issue?
2. If so do you have a proposals on how to fix the issue (aside from changing reports being written in production. Unfortunately I can't currently fix that one, but I'm fighting that battle).
This seems to be obtaining an ASYNC_NETWORK_IO on the query they are running until the preview is closed.
ASYNC_NETWORK_IO wait types are network related issues caused by the client application not processing results from the database fast enough. This results in filling the network buffers which causes the database not being able to send anymore data to the client.
To fix the issue, you need to check the data source to review the codes to optimize the data source and/or the report.
Because you did not mentioned what is the data source (query, stored procedure etc) and database and you did not attached the problem report, it will be difficult to pin point the exact problem area.
The query that is being run is a simple SELECT on a table returning only two of the fields an Int and Money.
Running the query in SSMS shows me that it's returning 31,394 rows in 0 seconds.
I'm not actually the one creating the report but I'm monitoring what other are doing on the database. Because everything in the query is encapsulated with quotes (ex SELECT "Name" FROM "Sales"."dbo"."Customers") then it looks to me like it's being generated from Crystal.
This is just one example of something that I've seen happen multiple times from several users. I can try to get an original copy of the report if you think that will help troubleshoot the issue. What I seemed to have narrowed it down to was while in Crystal looking at the "preview". In this particular instance a lock block was occurring which once preview was closed went away.
Also, I realize that usually an ASYNC_NETWORK_IO usually is due to some sort of network issue or large results set. From this particular query (and the others I've seen previously) it never seemed like there was enough data being returned to warrant a wait time of ~20 minutes. My assumption was that the preview in Crystal was also causing this.
Because you say the query is a simple SELECT on a table returning only two columns, I do not think the issue is with the query.
You can further confirm this by changing the query to a SELECT TOP n query to test.
Once you are satisfied it is not the query, your next step is to look at the Crystal Reports report fields. Are there many formulas on the report? Are the formulas just place holders or calculations? Is the calculations complicated? Are there any images or graphics? etc.