Extremely Slow Report Performance

[Former Member]

I have a report that I have been using for several years with no problems until recently. I made a change to the stored procedure that the report uses, and now the report will take several hours to run; whereas previously it took less than a minute. The change to the proc was very minor and involved only changing some verbage on a static text field that the procedure returned. The output of the procedure was not changed, and I can run the procedure using SQL management studio in less than 2 seconds. However, as I said Crystal Report takes several hours to run this same stored procedure. I even tried creating a brand new report and using this procedure as the datasource, and it did the same thing. This makes absolutely no sense to me. Can anyone tell me why this is happening? I'm using Crystal Reports 2008 with sp2.

[Former Member · Accepted solution]

And there were no changes to the report itself? That does seem odd...

Are there any formulas in the report that reference the static text field that was changed? Perhaps the old value caused different logic to be executed or caused a different number of iterations of one or more loops or caused subreoprts to run that weren't running before, or...

HTH,

Carl

[Former Member · Accepted solution]

No, the report file itself was not changed at all, and there are no formulas in the report. It is actually a very simple report and the stored procedure only returns 2 rows! The sql generated by the report is just a call to a stored procedure:

exec "iris_PickupLetter";1 27608, NULL, 3000

The report seems to be stuck on retrieving the data from the database. I can see the connection on the DB when I run the report and it indicates that it is doing a "SELECT" but for whatever reason it takes hours to perform this simple select.

[Former Member · Accepted solution]

Can you post the proc or at least the section that was modified?

Fuskie

Who hopes he will get some ideas if he looks at the code...

[Former Member · Accepted solution]

Did you try to Verify the Database (Database -> Verify Database)? This is a WAG, for sure, but might help...

HTH,

Carl

[Former Member · Accepted solution]

The verify database takes just as long as running the report itself, so it doesn't help. The portion of the proc that was changed was as follows:

This:

set @bodyBlock2 =

'NOTA: En caso de no tener la(s) devolucion(es) solicitada(s), favor de ' +

'anotar en la presente el motivo por el cual no se est entregando la ' +

'devolucion.

Para cualquier duda y/o aclaracion al respecto, quedo de usted en la linea ' +

'de Help Desk

58 64 17 07 or 01 800 509 17 84, lada sin costo.'

Was changed to this:

set @bodyBlock2 =

'NOTA: En caso de no tener la(s) devolucion(es) solicitada(s), favor de ' +

'anotar en la presente el motivo por el cual no se est entregando la ' +

'devolucion.

Para cualquier duda, aclaración o reporte ponemos a su disposición los siguientes

teléfonos 01800 509 1784, 01800 581 2490, lada sin costo o al (55) 5864 1700

extensiones 1757, 1802, 1811, 1791, 1707, 1726, 1762, 1767, 1781, 1792, 1820 o 1838'

[Former Member · Accepted solution]

Another WAG: The new text is much longer than the original. I'm wondering if CR is having issues with the length being more than expected? To see if this is the case, make the text as long as the old text was and see if it speeds up. You may need to remove the field from the report, verify the database (so Crystal knows the correct length - after putting the correct text in, of course) then re-add the field to the report.

The other thing that I noticed is that the last part of the text spans multiple lines. I'm not sure why that would matter, but maybe splitting the constant into separate strings that are concatenated (like the first few lines are) might help.

As I said at the start, these are just wild guesses, but this change shouldn't make a report's run time substantially longer, so anything's possible...

HTH,

Carl

[former_member11696]

Depending on the connection method depends on which connection API's we use.

[Former Member]

FYI...This issue was finally resolved by doing database maintenance on the tables used by the stored procedure (re-indexing, run stats, etc...). This still doesn't make much sense to me since the stored procedure always worked fine except when it executed by CR. Maybe CR caused a different execution plan to be used for some reason.

Edited by: Clint Sowell on Mar 2, 2010 11:12 PM

[Former Member]

Have you taken a look at the SQL query being formed (View SQL Query)? Does it look like what you expected? You can also look at the performance stats under the Report menu to see whether it is the query that is causing performance degradation or if massive numbers of rows of data are being returned to the report and the report is struggling to process them.

Fuskie

Whose experience is that often times slow performance is a result of more data being returned to the report than can fit in memory, resulting in heavy hard drive thrashing...