Slow performance of stored procedure

I have an .net asp application that accesses a MS SQL Server 2005 database.  The application is written in Delphi 2006.

The application sits on its own server.  It accesses the database server through a firewall on port 1433.

The application executes a stored procedure on the database server.  The store procedure is called sp_GetPatients and it takes two parameters: @pid varchar(20), and @clinicid varchar(20).

So here is the problem.  If I execute the stored procedure on the database server (though management studio) with @pid = '%' and @clinicid = '5' I get an almost instantaneous response and 4 rows of data.

However if I execute the stored procedure with the same parameters from my application on the other server then the execution takes around 30 seconds. I get the same 4 rows of data.

If I pass in something else instead of % for @pid, such as a partial match to a patient, like @pid = '19%' the stored procedure executes quicker.

So why does it execute so slowly in my application?  It can not be the volume of data returned because it is so little.  Neither does the wild card search on @pid take long when executed on the database server.

I have many other queries against the database (from my application) and all execute with normal response times.  

My application uses an TADOStoredProc componet to access the stored procedure, and a TADOConnection to connect to the database.

I have ruled out:

a)  index problems because the stored procedure runs so quick on the database server.  

b) Network problems because firstly the data returned is very small, and other network functions/database access proceed within expected norms.

The stored procedure returns the patients matching @pid for a specific clinic given by @clinicid
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I'm not sure how Delphi handles it, but make sure that your stored proc parameters are created with varchar types, not nvarchar. SQL is unable to use index seek if field is varchar and parameter is nvarchar.
soozhCEOAuthor Commented:
thanks for the tip but the stored procedure runs ok when executed on the database server...
1. What does your Delphi.Net code look like when assigning the parameter values?

2. If you fed the equivalent dynamic SQL to the database, would the response be equally slow?
soozhCEOAuthor Commented:
A luck would have it the server crashed and was re-built... and at the moment the problem has disappeared.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.