Store procedure takes too long to execute when run from IS

, ,
1

Hi.

I am running into a frustrating problem when using the JDBC Adapter to call a stored procedure.

I use the Stored Procedure with Signature template to call a stp on a SQL Server 2000 database. The procedure receives a datetime range and selects distincts values of a column based on it. Its body looks something like:

SELECT DISTINCT column_1 FROM table 
WHERE column_2 BETWEEN @minDatetime AND @maxDatetime

Both column_1 and column_2 are indexed on the server.

From time to time, a call to this procedure through the JDBC Adapter will take far too long to execute (up to 45 min!!! :eek:). At one time, I thought it was the table that was locked. But then I set the transaction isolation level to read uncommited and I still had the same problem.

If during the time where the stp is locked in the IS I call it with the same parameters from Query Analyser it takes about 30s to execute. So I’m confident this is not a problem with the stored procedure in itself.

I have noticed that this occurs mainly when the call for the adapter service is nested within other code. If I call it directly it’ll rarely block.

Connections are made on LOCAL_TRANSACTION mode. I use the startTransaction / commitTransaction services arround every call to the db. I am not stepping through my code.

I saw the [URL=“wmusers.com”]wmusers.com thread, that is somewhat related to my problem, and tried to apply the suggestion given there. But it had no effect for me.

I am using JDBC Adapter version 6.0.3 with sqljdbc1.1 and IS version 6.1 ( IS_6-1_FP2, IS_6-1_SP1, TNS_6-1_Fix15).

If someone could shed some light into this matter, I’d be immensly greatful.
Carlos

2

Carlos,

did you figured it out??

Thanks
Kevin

3

Nope.

Any help is welcome.

Tomorrow I’ll look at what’s going on on the database side with a DBA to see if we can find any possible reasons. I’ll keep this thread up to date.

Carlos

4

We are invoking few storedproc on SQL Server, it’s taking longer when invoked through IS when compared to SQL analyzer…I am waiting for the response to this query…it might be helpful :-).

5

Six years later on version 8.2, I am also having this problem. I have a stored procedure that takes less than a second to run in sql management studio, and takes ten seconds to run with the same parameters when called from IS.

6

Are you sure it’s not the connection taking 9seconds to setup?

[URL]sql server - Why does it take so long for SQL Management Studio to connect? - Super User

[URL]http://social.msdn.microsoft.com/Forums/en-US/sqlkjmanageability/thread/ea7fabf7-3711-40a7-b46d-f131054049f7/[/URL]

[URL]c# - Initial connection to SQL Server Connection Is Slow. Why? - Stack Overflow

I had a similar issue with DB2 where if you don’t setup os security correctly it takes 30 seconds extra to create the connection.

7

Positive. It turned out to be parameter sniffing done by sql server that caused it to choose a horrible execution plan. This happens when SQL server decides that it knows your execution plan based on the parameters being passed to the stored procedure. The choice of different execution plans between SSMS and WM has to do with connection setting differences that are behind the scenes. There are ways to break its ability to do parameter sniffing (for instance, by not using parameter variables directly in the queries in the procedure). When I made those changes, the performance came into the realm of what one would normally expect.