on 05-04-2009 9:09 PM
Hello,
I have some issues in a report that shares tables with another process. After some delay the report is sending and exception with error code 1205, that is known in SQL Server as deadlock.
I've made some tests and I've added in the registry the key
Software\Business Objects\Suite 11.5\Crystal Reports\Database\ODBC = 1
That assumes a read uncommited transaction and no locks.
However, It's not enought because the error persists.
Is there a way to avoid Crystal locks tables in the query execution??
Thanks in advance.
Regards.
Hi,
Thanks for reply.
There are some reports that are linked by Crystal, some others use views. There is a special case with a view that already uses no lock clause and still it's being locked by Crystal at run the report.
Any suggest?
Thanks.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Ah, afraid not - well, no easy to implement solutions at any rate...
We actually had such an issue with locking using Crystal (Enterprise) and general performance degredation, that I built a reporting server which has it's data shipped over from the 'live' db, so it seperates the production/reporting areas.
If anyone else has any ideas, I'd be glad to hear them! (I didn't know about the reg tweak you posted earlier, for instance...)
Hi Alex,
What version of Crystal are you using and can you confirm you are using ODBC as your data source driver? If you are then that key should work but it's not recommended. Uncommited data also include any info that has also been marked to be deleted so your info may not be up to date.
As with any database tool we simply request the data in the for of a SQL statement and the server does the processing. If your queries are returning that much data that it does affect performance on your DB server you may want re-design your reports so they return less data or use a stored procedure rather than using individual tables. This way if the server is that busy CR will simply wait for the data to complete before processing client side.
Stored Procedures are processed completely before passing the data to CR so there won't be any locks coming from CR side. If you have one talk to your DBA, that person should be able to optimize the SP or SQL Server to maximize performance.
Also it's known that ODBC does momentarily, nano-seconds, lock the tables while reading them so as Jaime suggested you should use OLE DB, much better performance. You may also want to try using the DataDirect ODBC driver if you must use ODBC which you can download from our support site or the link from the start page in the Designer.
Thank you
Don
Are you building your report by joining tables in Crystal, or sourcing your data from a single view/SQL command?
If the latter, you could try adding a nolock hint to your query to really force the issue - this takes the form of:
SELECT
u.UserID,
n.Forenames,
n.Surname
from Users u WITH (NOLOCK)
inner join UserNames u WITH (NOLOCK)
on u.UserID = n.UserID
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Does it work any better if you use an OLE DB connection instead of ODBC?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.