I have a simple stored procedure that I pass a where clause into. I've
noticed that on the first pass, I get a timeout in my application.
Subsequent requests are OK. Does anyone know why the timeout occurs on the
first connection? It seems like the server is 'asleep'.

Maybe my sp needs optimizing? Here is my sp:
CREATE PROCEDURE HermesArchiveSearch

@pWhereClause varchar(300)='',
@Flag int

AS

declare @rows int
declare @error int

if @Flag = 1 -- user searched on regno and/or hlscaseno
begin
exec
('SELECT applicant.apptno, applicant.surname, applicant.forename,
applicant.relcode,rtrim(ltrim(COALESCE(home.propname + '' '',...

So, have you read the articles by Erland Sommarskog that I suggested you read
last week?

My guess is that the compilation takes longer the first time, and it exceeds
the command timeout set by the client application. It is a command timeout,
not the connection timeout, right?

ML

To add on to Matija's response, subsequent executions may be faster because
data are cached from the first execution instead of read from disk. I
suggest you examine the execution plan for inefficiencies and perform index
and query tuning. If you need help with this, post the complete CREATE
TABLE statements and actual SQL query.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"axapta" gmail.com> wrote in message
news:3C7CD215-1C6B-4FE8-A83E-0C1A5EAE8AB0@microsoft.com...

"axapta" gmail.com> wrote in message
news:3C7CD215-1C6B-4FE8-A83E-0C1A5EAE8AB0@microsoft.com...

axapta (jas.jackie@gmail.com) writes:

As mentioned in other posts, it could be because the database is autoclose,
or because the second time data is in cache. Or a combination of them.

How could you be able to optimise a stored procedure to which you
pass a WHERE clause? Or to be more blunt, if you feel obliged to pass
WHERE clauses, why not rather build the entire SQL statement in the
client code, and stop bothering about stored procedures at all?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I connect to the database on each page's onload event.
"John Bell" hotmail.com> wrote in message
news:2B58281E-8B2A-4643-BE97-84B872E97CCF@microsoft.com...

axapta (jas.jackie@gmail.com) writes:

However, there may be connection pooling in force. Many APIs lingers to
a connection when you disconnect for around 60 seconds, and if you reconnect
within with the same connection properties within that time fram, the
connection is resued.

Which edition of SQL Server are you using? Autoclose is on by default in
SQL Express if memory serves. You can use ALTER DATABSE to turn off
autoclose.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Use SQL Profiler to diagnose where the delay is occurring, the connection or
the stored proc call. In addition to the Duration, also compare the CPU and
Reads for successive calls. Also, add the SP:Recompile event.

Also, rather than passing in an adhoc where clause as a parameter, consider
implementing it like this:
...
where (@homeno is null or homeno=@homeno)

It may be a combination of things; I wouldn't expect a relatively simple
proc call to timeout due to compilation unless the server is very busy or you
have a very low timeout configured. If you're using SQL Server 2005, then the
following query will list how many pages of buffer cache are currently
allocated for a database object. Even a simple query can perform a lot of
data reads, and it's possible that the first call timeouts simply because the
needed data isn't cached.