Stored Proc runs quickly in Query Analyzer but not on Retrieve

[Cullenward]

I have a stored procedure that runs in less than a second in SQL Server Query Analyzer, but sometimes it takes up to 3-5 minutes to run that same stored procedure in a data window retrieve.

This doesn't constantly happen, it's been about a month since it last happened, and there were no changes between when it last worked yesterday and today.

I've used the parameters in both settings (SQL Server, and PowerBuilder) at the same time with the same user accounts. What would cause this to return instantly in SQL Server, but take several minutes in PowerBuilder?

In the instance I tested there was only 15 rows of data returned, and parameters that previously processed quickly are now slow on the retrieve.

Thanks

 

[thekl0wn]

My vote would be something on the driver. How are you accessing the DB? OLE?

 

[Cullenward]

It's being accessed via OLE. It's worked fine since there was last an issue and nothing was changed. No new updates/patches were applied. Again, the issue seems to happen sporadically.

 

[thekl0wn]

How long is the server sitting idle? We had an issue like this a while back, where we could read just fine, but the first write would take FOREVER.

 

[Cullenward]

It can take up to 3-4 minutes. It does the same thing when I open the datawindow object in the powerbuilder editor and hit the retrieve button.

 

[mbalent]

Have you run a trace on the DB connection to see what is being set to the database?

Matt

"Nature forges everything on the anvil of time