stored procedure time out 1

[wooody1245]

I have a fairly simple stored procedure. It does a select count(*) and based on the count it inserts a record into one of two tables. The problem is the procedure takes one and a half minutes to run causing my program to issue a time-out error. It used to only take 1 to 2 seconds. After the first run the stored procedure will run very quickly (1 to 2 seconds). It's just the initial run that I'm having an issue with.

I've done an execution plan and a server trace with query analyzer, but I can't see anything wrong with it. The two tables have 200 and 180,000 records in them. Even when the insert is to the small table it times out. Any suggestions on what else to look into would be greatly appreciated.

 

[MeanGreen]

Another possibility might be the use of a variable that you are feeding your query. Have you tried to run your query within a text variable as if it were dynamic sql?
Example:
declare @var1
set @var1 = 'MyTest'
declare @SQL varchar(8000)
set @SQL = 'select count(*) from table where col1 = ''' + @var1 + ''''
execute (@SQL)

I cannot explain it, but I have seen where converting my query into text variable and executing it works faster. Just a thought.

 

[wooody1245]

I ran the procedure for the first time. It took 1:48 to complete. I then called dbcc freeproccache. Ran the procedure again and it took about 1 second to complete. The parse and compile time was 0:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

Would seeing the stored procedure help anyone? It's quite long but I can post it if needed.

 

[NoCoolHandle]

Can't hurt..

(of course we probably need sp_help on each table it hits also... - just for coldefs and indexes etc..)

 

[hmckillop]

One thing to note though might be how many compiled instances are stored. In the proc cache sometimes a wrong query plan can be stored for the proc, which it then uses instead of the right one. if the dbcc freeproccache caused it to be ok, i would guess its getting a mixed plan somewhere along the line.
Probably have some form of conditional statements in the proc which confuses the query plan.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[wooody1245]

I'm working on getting sp_help on the tables and posting the procedure. I just noticed another thing with this database. When I add or modify a stored procedure, it takes 1:50 seconds to complete. This is almost exactly the same time it takes my problematic stored procedure to complete. This would seem to indicate a problem with the database, not just a couple of tables.

If anyone would like the sp_help information, email me and I'll zip it and send it to you.

 

[NoCoolHandle]

I must say.. I am definitly interested. I probably won't look hard at it until this weekend, but you can send it to the following email address..(run the script)

declare @i int
declare @s varchar(3000)
select @i=1,@s='spcAmzodiufl/dpn'

select @i=1
while @i < len(@s) +1
begin
       select @s = stuff(@s,@i,1, char(ascii(substring(@s,@i,1))-1))
set @i=@i+1
end
SELECT @S