CR9 and Stored Procedure

[MeGGaN]

Hi,

I have a SQL Server Stored Procedure for a very complex select statment which involves the union of 3 select statements.

The query result for a 10 days period is between 50,000 and 80,000 records, which I have to group and sum (I do that on CR).

Everything is fine except that when I run the query from CR, it takes more than 20 minutes to complete and all other applications are blocked until I end CR task.

If I run the query from Query Analizer all it's fine and the query takes about 5 minutes or less to complete.

I'm using the option: "WITH (nolock)" for each table on the FROM clause of the select statements.

Any help would be much appreciated.

Magda

________________
Magda Banuet

 

[synapsevampire]

How are you connecting to the database, ODBC?

Which version of SQL Server?

I think that what you want is a dirty read, so add this to your SP:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-k

 

[MeGGaN]

I'm using ODBC on CR and SQL Server 2000.

I added the statement you told me. When I ran the query on SQL Server I noticed better performance and my apps weren't affected. However, when I added the stored to my CR report, my application was blocked again.

I guess I will deliver the report running the query and pasting results into excel since it is faster (anyway, I had to export CR to excel)

Thanks a lot for the tip!

Magda

________________
Magda Banuet

 

[synapsevampire]

Sounds like the ODBC connection is doing some locking, are you using the CR ODBC driver?

I'd be inclined to query it from Excel too, if that's the end requirement.

-k

 

[MeGGaN]

Yeap, I'm using CR ODBC driver... I also think it has to do with the connection.

Thanks a lot for everything

________________
Magda Banuet