CRW10 blocking SQL tables - how to stop this.

[kapaa]

I recently upgraded from crystal 8 to 10 and discovered a big issue, crystal 10 is blocking the SQL tables in which it pulls data from. My tests show that all is okay in SQL until an application attempts to update a table being used by crw10 and bam!! 'blocking'.

I guess my best bet is to make a copy of the db each night just for reporting, but I'm also curious if anyone knows of a setting in crw10 to turn this off because this was not an issue with crw8. I heard there's a "no block" statement that can be placed in the select statement but I'm not familiar with this or the proper syntax to make it work.

 

[kapaa]

Wow, so nobody has run into this, perhaps I'm the only one hitting a production db. How about using the 'no block' statement, is this a global statement that can be added at the beginning of the select statement or does it get added to each reference?

 

[kapaa]

I'm still struggling with this and finding nothing on the internet that will help. Any suggestions?

 

[Turkbear]

Hi,
What connection method are you using ? ( I am assuming you mean a SqlServer database - SQL is a language)

Usually the connection will be read-only which should never, in a decent multi-user database, cause table-level blocking





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kapaa]

Sorry, yes I'm using SQL Server database, or should I say our db group is. I can go into SQL Enterprise Manager while running a lengthy report.. say 10 mins to complete, then go into Management/Locks/Process ID and there it is, shows a SPID and next to it "blocking". I click this spid and it will show the tables used in my report getting blocked. I beginning to think it's a SQL setting but the DB admin won't even look into it, just says "no way". So if it is SQL then I have to prove it.

 

[Turkbear]

Hi,
read here:

http://sql-server-performance.com/Community/forums/p/15619/92178.aspx

Exactly your situation ( Are You Nunzia?)



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kapaa]

I'll pass this on to our dba, it won't go far... straight to his trash can, he pretty much said 'you figure it out'. I want him to create views to the tables I hit to see if that helps (when he figures out how to do this). Stored procedures would follow if that fails. One more quick question...

I discovered something today and I'm not sure if this might be the road to a solution, however, I don't know enough about SQL Server. First, my reports seem fairly simple in nature, I don't think I'm pulling a million rows. So I run report ABC and I see blocking issues in SQL Server Enterprise Mgr. If I run report XYZ that isn't too much different than ABC, I do not see blocking issues. Hmm, what could be different? In SQL-Mgr when I look at the SPID info generated by the report I can see the tables the report is referencing.

The one difference I see is under the "Index" column - the report blocking shows "XPKcall_req" where call_req is the table I'm pulling from. The report that IS NOT blocking shows call_req_x0. I checked with our dba and he wasn't sure, other than to say XPK is the primary key index. Okay, then why is one report hitting the primary key index (whatever that is) and the other isn't? In Crystal I'm not sure how to tell it to stop doing this, I trying shaking my finger and yelling but like my kids this does little.

Any ideas?

 

[Turkbear]

Hi,'
In general you want to hit the index - that's one of the main things to try and do in a report design - it is the most efficient way to retreive the data you want from the database..
If just using the primary key in a read-only request causes a table level lock tjhen your DBA may have somehow missed some classes in database design or you have a very unusual table structure ( even in SqlServer this seems very odd)..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kapaa]

Very interesting Turkbear. I'm not sure why one report would hit the primary index and the other hits the index, the only thing I see different is that the report that hits the index has 1 table joined to a table called call_req whereas the other has 4 tables joined to the call_req table. call_req is the table that holds service ticket info. The dba said he has no clue how Computer Associates created their mdb db so if my reports hit the primary key then there's no way for him to know why that is. I still feel there's something on SQL server side that out of place so it's very aggrevating when I have to do the dba's job. If I don't, this issue will go on forever.

 

[kapaa]

I finally got our DB Admin to create views to the tables I use in my reports and I'm not sure if this help or the fact that he added a (nolock) option to view. Anyway, so far this seems to be working.