Skip to Content
0
Former Member
Apr 23, 2010 at 04:53 AM

Stored procedure Performance issue in SQLserver 2005

28 Views

Hi All,

i am inserting the data to Database by using of Stored procedure in target DB.

My source structure and target structures are looking below

I have the source structure having lot of rows and look like my structure is below:

<?xml version="1.0" encoding="utf-8" ?> 
<ns0:POCA0013_KANLOG_REQUEST_MT_response xmlns:ns0="urn:com:POCA0013:sample">
 <SCMDB_response>
-  <row>
      <PROJK>O-USA</PROJK> 
      <KOLLO>123</KOLLO>
   </row>
-  <row>
      <PROJK>O-Denmark</PROJK> 
      <KOLLO>256</KOLLO>
   </row>
    ...
    .....
    n  number of rows
 </SCMDB_KANLOGVIEW_response>
</ns0:POCA0013_KANLOG_REQUEST_MT_response>

and after mapping my target structure is coming to like this.
<?xml version="1.0" encoding="UTF-8" ?> 
<ns0:POCA0013_DB_MT xmlns:ns0="urn:pg-com POCA0013:sample">
 <StatmentName>
   <XI_SP_DATA action="EXECUTE">
     <PROJEK isInput="TRUE" type="CHAR">O-USA</PROJEK> 
     <KOLLO isInput="TRUE" type="CHAR" >123</KOLLO> 
   </XI_SP_DATA>
 </StatmentName>
 <StatmentName>
   <XI_SP_DATA action="EXECUTE">
     <PROJEK isInput="TRUE" type="CHAR">O-Denmark</PROJEK> 
     <KOLLO isInput="TRUE" type="CHAR" />256</KOLLO> 
   </XI_SP_DATA>
 </StatmentName>
  ..
  ..
  ..
  N number of times
</ns0:POCA0013_DB_MT>

this is working perfectly to insert the records into the database by using stored procedure. each record it call the stored procedure for insert the records, for example we had 100 records and it call 100 times stored procedure.

But in case of huge data, for example 10000 records, it call the 10000 times to stored procedure.in that case we had a problem for database side.

we have one reason to use the stored procedure here, because once insert the data into table, if successful log table is created with successful status , if not log table is created with error status. for that purpose i am using stored procedure here.

Our customer wants to call the stored procedure for one time for all records.How i can manage this situation.

Can you give me your valuble ideas about this problem.

Thank you very much.,

Sateesh

Edited by: sateesh kumar .N on Apr 23, 2010 6:53 AM

Edited by: sateesh kumar .N on Apr 23, 2010 6:54 AM

Edited by: sateesh kumar .N on Apr 23, 2010 7:54 AM