cancel
Showing results for 
Search instead for 
Did you mean: 

Stored procedure Performance issue in SQLserver 2005

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Sateesh,

how about a different approach.

Add 2 more tables to your solution. The first table is used as a staging table, where PI inserts all the data without making any checks, whatsoever. The second table is used as a control table. If the insertion is finished, a log entry is inserted into this second table, containing the information about success or failure or how many rows had been inserted. Put an insert trigger on this table, which in term starts a stored procedure. This stored procedure can read all the data from the staging table and put it into the desired target tables. Additionally you can perform plausiblitiy checks inside this SP.

Okay I know, this is a complete new solution in comparison to what you did before. But in my experience, this will be much more performant than 10000 calls to one stored procedure who only does inserts as you described.

Regards

Sven