OK novice stored proc writer here, learned mostly from Google. I'm having a lot of problems with recompilation of a stored procedure slowing it down. I've used dynamic SQL to build the query and execute to run it and I think this may be the issue. Here's a piece of what I'm trying to doSELECT @SearchSQL = ' insert into #admin_stats ' + ' SELECT ''client_tickets'', count(*), ''nct'', ''n'', '''', '''' ' + ' FROM Ticket ' + ' WHERE ticket_status = 10 ' IF @admin<>'' SELECT @SearchSQL = @SearchSQL + ' AND ticket_assigned_to=''' + @admin + '''' EXECUTE(@SearchSQL)<lots of similar blocks like this then...>select * from #admin_stats OPTION (KEEPFIXED PLAN);
It seems to take an age to run first time, then is very quick if run a few times quickly after that... leave it for a while and it's back to being slow hence my theory about recomplile... you can see I've added OPTION (KEEPFIXED PLAN) to try and force no recompile but no difference as far as I can tell...Any ideas on making this work without recompiling OR how to write it a little more correctly?It's being called from an asp webpage if that's relevant
How best to handle this
Posted - 2013-06-04 : 18:09:36
