SQL Server Performance

Crystal Report , tableview and lock tables

Discussion in 'T-SQL Performance Tuning for Developers' started by Nunzia, Dec 3, 2002.

  1. Nunzia New Member

    I've built report using Crystal Report Enterprise and working on SqlServer 8.0 on Windows/2000 professional. These report are launched by Web using the standard ASP page.
    These report works on table view built inside the database. Some of these tableview inquire others tables views.....and at the end the data tables.

    On my developing and test machine the running total time of queries are about 3-4 seconds , no more. On customer web machine the query time are quite quite longer , about 40 second and in same cased more then 4 minutes.

    I've noted that on SQL server on customer machine each report when runs the first query and all the other connected with the table view create lots of TAB LOCK both at table level and at view level.

    This make the query slow down in an un- acceptable way.
    DO you know if there is any way to make run these report query without setting any kind of lock ? or less heavy lock ?
    The only difference between my test machines and customer machine is the number of concurrent users , that customer machine has ( 150 more or less) and my test machine hasn't .

    Hoping to have explained my question in a clear way , I thanks you in advance for any suggestion or check you can suggest.

    -Nunzia
  2. bradmcgehee New Member

    By default, SQL Server uses row locks where it can. But if there are a lot of rows to be returned, the number of locks can be large, and in order to reduce overhead, SQL Server will escalate row locks to table locks. And as you have seen, this can be a problem in many cases.

    There are two ways to overcome this problem. First, and the best option, is to reduce the number of rows returned. The problem you are having is exactly the type of problem I face everyday, except that I am the DBA and report writers in another department write the Crystal Reports. The problem they have, over and over, is that they return too many rows in their views, and then use Crystal Report's Engine to reduce them to the proper amount. For example, they might return a million rows from a view in order to return 10 rows. This of course leads to table locking and horrible performance. What I have been trying to do is to get the report writers to use Stored Procedures instead of views, which gives them more flexibilty to return only the rows they need. When the report writers have heeded my advice, the reports have gotten much faster.

    If the above is not possible, there is a hint you can use that tells SQL Server to move directly from a row lock to a page lock instead of a table lock, which helps concurrency, but it doesn't help with all the unnecessary data being returned from the views. See this url:http://www.sql-server-performance.com/reducing_locks.asp for information on Tablock.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. KapaA New Member

    I never had problems with locks on Crystal 8, this didn't happen until moving to ver10 but yet Crystal swears their app doesn't lock tables. However, 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?
  4. backborn New Member

    [FONT=宋体]Hi,[/FONT][FONT=宋体][/FONT]
    [FONT=宋体]Try to use the tool at [/FONT][FONT=宋体]http://www.raqsoft.com/[/FONT][FONT=宋体][/FONT]
    [FONT=宋体]You can get it for free,I think it can help you solve your problem.[/FONT]
  5. Madhivanan Moderator

    [quote user="backborn"]
    Hi,
    Try to use the tool at http://www.raqsoft.com/
    You can get it for free,I think it can help you solve your problem.
    [/quote]
    Can you be more specific on how the raqsoft tool solves the problem stated this thread?
  6. satya Moderator

    Hey this is nothing but advertisement, that user simply posted same text on the posts tha talks about reports.

Share This Page