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