| Author |
Topic |
|
Ramnadh
Starting Member
23 Posts |
 Posted - 2004-09-16 : 09:05:45
|
| Hi,When i am giving the Parameters to the Stored Procedure and executing the execution is taking 1mt.whereas when i am executing the same query outside with out SP...it is taking 34 Sec...how can it be ....as Stored procdure is pre-compiled it should come quick right... what may be the problem can anyone explain it.Thanx,Ramnadh.Ramnadh |
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2004-09-16 : 09:31:44
|
| You could try recompiling the SP to force it to redo the query planDid you call it SP..... these have special meaning for tsql and it will check the master db for it firstAre you providing the owner for the tables? this speeds things up Can anyone think of anything else ??Graham |
 |
|
|
Ramnadh
Starting Member
23 Posts |
Posted - 2004-09-16 : 09:41:11
|
| Graham ,Thanx for your reply.Sorry for confusing you . what i mean by SP is the Stored Procedure in the shorter form...and you have given me the suggestion to recompile the procedure.Is this the sytax for re-compiling the stored procedure ...EXEC sp_recompile <Table Name>Does we have to specify all the tables that are used for that Stored Procedure to execute. Please suggest me.Thanx,Ramnadh.Ramnadh |
|
|
|
Ramnadh
Starting Member
23 Posts |
Posted - 2004-09-16 : 09:42:01
|
| Graham ,Thanx for your reply.Sorry for confusing you . what i mean by SP is the Stored Procedure in the shorter form...and you have given me the suggestion to recompile the procedure.Is this the sytax for re-compiling the stored procedure ...EXEC sp_recompile <Table Name>Does we have to specify all the tables that are used for that Stored Procedure to execute. Please suggest me.Thanx,Ramnadh.Ramnadh |
|
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2004-09-16 : 16:01:39
|
| RamnadhI usually just paste the creation script into Query Analyser and execute it again, it removes the old version and recreates it.Graham |
|
|
|
Ramnadh
Starting Member
23 Posts |
Posted - 2004-09-17 : 04:25:31
|
| Graham,I want to recompile that Stored Procedure Dynamically means from IDE, which i am using was the VS.Net.Could you please tell me how should we recompile a stored procedure with out copying and pasting and executing.Thanx,Ramnadh.Ramnadh |
|
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2004-09-17 : 06:14:46
|
| RamnadhI have looked at books online and the easiest method I think is your suggestion - EXEC sp_recompile StoredProcedureNameI think you have to enclose the name in quotes, but the example is not very clearGraham |
|
|
|
Ramnadh
Starting Member
23 Posts |
Posted - 2004-09-17 : 07:36:47
|
| Graham,I found the way to recompile the Stored Procedure, if we say WITH RECOMPILE when creating the stored procedure, it doesn't keep the execution plan. It recompiles every time.Ex: CREATE PROCEUDRE <owner.ProcName> WITH RECOMPILEThanx,RamnadhRamnadh |
|
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2004-09-17 : 14:09:23
|
| Im not sure that you want it to recompile every time, this adds a significant overhead, possibly making it worse than it was before you changed anything !Graham |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-09-17 : 14:24:50
|
| A few alternatives:Rather than CREATE .. WITH RECOMPILE you can select when it is recompiled when it is invoked. For exampleexec p_myproc @parm1=n WITH RECOMPILEProbably more importatantly, you should find out why it is slowing down. It is probably related to paramater sniffing. Search this site for a post about it. (Edit: Here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39558)You may be able to prevent it by declaring local variables in your procedure and assigning them the values of the paramaters passed to the procedure, then 'select ... where col = @localvariable' instead of 'select ... where col = @param1'.This may prevent SQL Server from making (wrong?) guesses about the best query plan.--KenI want to die in my sleep like my grandfather, not screaming in terror like his passengers. |
|
|
|
|
query optimization