on 01-13-2010 4:51 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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'
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
User | Count |
---|---|
82 | |
9 | |
9 | |
7 | |
7 | |
7 | |
7 | |
6 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.