Stored Procedure taking more time than Simple Query

• SQLCHILD

  SSC Eights!

  Points: 964

  More actions

  February 2, 2011 at 1:53 am

  #104036

  create procedure test_sp

  as

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('aempname')

  INSERT into employeedetails(ename) values ('@bempname')

  INSERT into employeedetails(ename) values ('@emfgpname')

  INSERT into employeedetails(ename) values ('@empnfsdame')

  INSERT into employeedetails(ename) values ('@empnamsdfe')

  INSERT into employeedetails(ename) values ('@empnafsdame')

  INSERT into employeedetails(ename) values ('@empsdfname')

  INSERT into employeedetails(ename) values ('32@empname')

  INSERT into employeedetails(ename) values ('@empnamsdafe')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@e32mpname')

  INSERT into employeedetails(ename) values ('@emp4name')

  INSERT into employeedetails(ename) values ('@empn54ame')

  INSERT into employeedetails(ename) values ('@empnam7873e')

  INSERT into employeedetails(ename) values ('@empna7867gdfme')

  INSERT into employeedetails(ename) values ('@empna8870-=me')

  INSERT into employeedetails(ename) values ('@empname=\')

  INSERT into employeedetails(ename) values ('@empnafsvc ,me')

  INSERT into employeedetails(ename) values ('@empna./me')

  INSERT into employeedetails(ename) values ('@empnam/,?"e')

  INSERT into employeedetails(ename) values ('@emp)(na][pme')

  INSERT into employeedetails(ename) values ('@empname6')

  INSERT into employeedetails(ename) values ('@empna)9dfgme')

  INSERT into employeedetails(ename) values ('dgs@empname')

  INSERT into employeedetails(ename) values ('dsg@empname')

  INSERT into employeedetails(ename) values ('536fgh@empname')

  INSERT into employeedetails(ename) values ('7676fdfcv 8909-@empname')

  INSERT into employeedetails(ename) values ('1~2344323@empname')

  INSERT into employeedetails(ename) values ('@empnasssfme')

  INSERT into employeedetails(ename) values ('@empnwrwrewame')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('aempname')

  INSERT into employeedetails(ename) values ('@bempname')

  INSERT into employeedetails(ename) values ('@emfgpname')

  INSERT into employeedetails(ename) values ('@empnfsdame')

  INSERT into employeedetails(ename) values ('@empnamsdfe')

  INSERT into employeedetails(ename) values ('@empnafsdame')

  INSERT into employeedetails(ename) values ('@empsdfname')

  INSERT into employeedetails(ename) values ('32@empname')

  INSERT into employeedetails(ename) values ('@empnamsdafe')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@e32mpname')

  INSERT into employeedetails(ename) values ('@emp4name')

  INSERT into employeedetails(ename) values ('@empn54ame')

  INSERT into employeedetails(ename) values ('@empnam7873e')

  INSERT into employeedetails(ename) values ('@empna7867gdfme')

  INSERT into employeedetails(ename) values ('@empna8870-=me')

  INSERT into employeedetails(ename) values ('@empname=\')

  INSERT into employeedetails(ename) values ('@empnafsvc ,me')

  INSERT into employeedetails(ename) values ('@empna./me')

  INSERT into employeedetails(ename) values ('@empnam/,?"e')

  INSERT into employeedetails(ename) values ('@emp)(na][pme')

  INSERT into employeedetails(ename) values ('@empname6')

  INSERT into employeedetails(ename) values ('@empna)9dfgme')

  INSERT into employeedetails(ename) values ('dgs@empname')

  INSERT into employeedetails(ename) values ('dsg@empname')

  INSERT into employeedetails(ename) values ('536fgh@empname')

  INSERT into employeedetails(ename) values ('7676fdfcv 8909-@empname')

  INSERT into employeedetails(ename) values ('1~2344323@empname')

  INSERT into employeedetails(ename) values ('@empnasssfme')

  INSERT into employeedetails(ename) values ('@empnwrwrewame')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('aempname')

  INSERT into employeedetails(ename) values ('@bempname')

  INSERT into employeedetails(ename) values ('@emfgpname')

  INSERT into employeedetails(ename) values ('@empnfsdame')

  INSERT into employeedetails(ename) values ('@empnamsdfe')

  INSERT into employeedetails(ename) values ('@empnafsdame')

  INSERT into employeedetails(ename) values ('@empsdfname')

  INSERT into employeedetails(ename) values ('32@empname')

  INSERT into employeedetails(ename) values ('@empnamsdafe')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@e32mpname')

  INSERT into employeedetails(ename) values ('@emp4name')

  INSERT into employeedetails(ename) values ('@empn54ame')

  INSERT into employeedetails(ename) values ('@empnam7873e')

  INSERT into employeedetails(ename) values ('@empna7867gdfme')

  INSERT into employeedetails(ename) values ('@empna8870-=me')

  INSERT into employeedetails(ename) values ('@empname=\')

  INSERT into employeedetails(ename) values ('@empnafsvc ,me')

  INSERT into employeedetails(ename) values ('@empna./me')

  INSERT into employeedetails(ename) values ('@empnam/,?"e')

  INSERT into employeedetails(ename) values ('@emp)(na][pme')

  INSERT into employeedetails(ename) values ('@empname6')

  INSERT into employeedetails(ename) values ('@empna)9dfgme')

  INSERT into employeedetails(ename) values ('dgs@empname')

  INSERT into employeedetails(ename) values ('dsg@empname')

  INSERT into employeedetails(ename) values ('536fgh@empname')

  INSERT into employeedetails(ename) values ('7676fdfcv 8909-@empname')

  INSERT into employeedetails(ename) values ('1~2344323@empname')

  INSERT into employeedetails(ename) values ('@empnasssfme')

  INSERT into employeedetails(ename) values ('@empnwrwrewame')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('aempname')

  INSERT into employeedetails(ename) values ('@bempname')

  INSERT into employeedetails(ename) values ('@emfgpname')

  INSERT into employeedetails(ename) values ('@empnfsdame')

  INSERT into employeedetails(ename) values ('@empnamsdfe')

  INSERT into employeedetails(ename) values ('@empnafsdame')

  INSERT into employeedetails(ename) values ('@empsdfname')

  INSERT into employeedetails(ename) values ('32@empname')

  INSERT into employeedetails(ename) values ('@empnamsdafe')

  INSERT into employeedetails(ename) values ('@empname')

  INSERT into employeedetails(ename) values ('@e32mpname')

  INSERT into employeedetails(ename) values ('@emp4name')

  INSERT into employeedetails(ename) values ('@empn54ame')

  when i execute the sproc then it takes more time, but when i execute only the insert statements together then they take less time.please help.

  THE EXECUTION PLAN FRO SPROC IS:

  |--Table Insert(OBJECT:([learn_stored_procedures].[dbo].[employeedetails]), SET:([learn_stored_procedures].[dbo].[employeedetails].[ename] = [Expr1004]), DEFINE:([Expr1004]='@empname'))

• Pulivarthi Sasidhar

  SSCertifiable

  Points: 6706

  More actions

  February 2, 2011 at 2:09 am

  #1281288

  Instead of the above code why dont u use the following code...

  Insert into employeedetails(empname)

  Select @empname1 Union

  Select @empname2 Union

  Select @empname3 Union

  Select @empname4 Union

  Select @empname5 Union

  Select 'Abc' Union

  Select 'Def' Union

  Select 'Ghi' Union

  Select 'JkL' Union

  Select 'ert'

• SQLCHILD

  SSC Eights!

  Points: 964

  More actions

  February 2, 2011 at 11:29 pm

  #1281729

  dear sir,

  i need to know the reason that why stored procedure is taking more time than simple query execution.

  my query is:

  insert into employeedetails values('amit')

  insert into employeedetails values('amit')

  insert into employeedetails values('stark')

  insert into employeedetails values('peter')

  insert into employeedetails values('hemant')

  insert into employeedetails values('shamit')

  insert into employeedetails values('ramit')

  insert into employeedetails values('amul')

  insert into employeedetails values('ritul')

  when i execute all these statements by selecting all of them and F5, then it takes less time

  But when i create a stored procedure for these statements, and then execute it then it takes more time. why so?because stored procedure should take less time, but its doing vice versa