CR10 and SQL Server.... locking out other users?

[Lochbel]

I am a bit new to CR and SQL server...
Connecting CR10 to SQL Server via ODBC connection to tables.

I am finding when the report is executed other clients (viewing the data via a web app)are experiencing some form of lockout.

Is there a setting that i'm missing somewhere? What could be happening?

TIA




Lochbel

 

[synapsevampire]

Are they receiving an error, or? Some form of lockout leaves much to the imagination, try stating facts.

How is the report executed? A web app isn't very descriptive.

They could be experiencing delays if the query is taxing the system.

-k

 

[Lochbel]

No they are not receiving an error.

The web access is a .net app on a web server accessing the data server.
The Crystal report is running on a CE10 server.

The database is 5,000,000 records. The clients view of the data (via the web access) is ok until a CR activates.

The data server does not appear to be under load, but that is a possiblity. Under testing the performance fall will occur with just 1 CR and 1 viewer. The viewer will timeout.

It is possible that it is a resource issue, but i am intially trying to determine whether some denial process is occuring. I have not spent much time with SQL server.




Lochbel

 

[synapsevampire]

So what does a lockout mean? If they don't receive an error, they're not locked out.

If you're speaking of bad performance of a report, the common problem is NOT passing what's in the Report->Select Formula->Record (or Group) to the database. You can check this using Database->Show SQL Query.

In particular Crystal may be pulling in massive amounts of data and filtering locally because the record selection formula isn't passed to the database.

Try posting what's in the Record Selection and the Database->Show SQL Query.

-k

 

[Lochbel]

The SQL query has passed some selections through on indexed fields, but that said, the records i am trying to totalise still amount to 500,000 records. I am grouping on formula fields, so it is probably pulling large amounts across to handle the grouping at the client end.

The strange thing is that the data server doesn't seem to be "working"... which parameter would highlight if it iuner load in this instance?





Lochbel

 

[fredp1]

Can you post your SQL as synapsevampire mentioned.

Also, you'll need to identify which fields are index fields.

If a report is not carefully written, Crystal may not be able to pass the selection/'WHERE' clause to the db server, causing all the records to be passed to CE/Crystal for local processing. This is bad for performance.

By showing the SQL, this an be easily identified.

Fred
P.S.
I've never seen Crystal lock a table as it generally only ever reads data.

 

[synapsevampire]

As I mentioned, you need to supply technical information.

I've no idea what "The strange thing is that the data server doesn't seem to be "working"... which parameter would highlight if it iuner load in this instance?" means.

-k

 

[Lochbel]

Select formula below...
SELECT "e2lotoffer"."lo_sale", "e2lotoffer"."lo_season", "e2lotoffer"."lo_sellregion", "e2lotoffer"."lo_woolcat", "e2lotoffer"."lo_bales", "e2lotoffer"."lo_lotno", "e2lotoffer"."lo_reoffer", "e2lotoffer"."lo_prcstatus"
FROM "ehe2"."dbo"."e2lotoffer" "e2lotoffer"
WHERE "e2lotoffer"."lo_season"=N'02' AND "e2lotoffer"."lo_reoffer"=0 AND "e2lotoffer"."lo_prcstatus"<>N'WD'



Lochbel

 

[fredp1]

You have 3 filters, lo_Season, lo_reoffer and prcstatus.

Looks like the where cluase is being generated, but is there any more 'filters' in the record selection formulae expert than Crystal is not being able to pass? i.e. if you have 5 selections, then Crystal will be doing some local filtering on the 2 remaining fields(not the best senario).

If the above 3 columns are not indexed on the table, SQL will do a complete scan of the table... this will be slow and also effecting other users.

If you are only selecting 3 columns to filter and you have no indexes... talk to the dba to look at the applying some indexes to 'best' columns.


Cheers
Fred