title: MySql Batching Stored Procedure Calls with .Net / Connector?
body: <p>Is there a way to batch stored procedure calls in MySql with the .Net / Connector to increase performance?</p>
  
  <p>Here's the scenario... I'm using a stored procedure that accepts a few parameters as input.  This procedure basically checks to see whether an existing record should be updated or a new one inserted (I'm not using INSERT INTO .. ON DUPLICATE KEY UPDATE because the check involves date ranges, so I can't really make a primary key out of the criteria).</p>
  
  <p>I want to call this procedure a lot of times (let's say batches of 1000 or so).  I can of course, use one MySqlConnection and one MySqlCommand instance and keep changing the parameter values, and calling .ExecuteNonQuery(). </p>
  
  <p>I'm wondering if there's a better way to batch these calls?</p>
  
  <p>The only thought that comes to mind is to manually construct a string like 'call sp_myprocedure(@parama_1,@paramb_1);call sp_myprocedure(@parama_2,@paramb2);...', and then create all the appropriate parameters.  I'm not convinced this will be any better than calling .ExecuteNonQuery() a bunch of times.</p>
  
  <p>Any advice? Thanks!</p>
  
  <p><strong>EDIT: More info</strong><br>
  I'm actually trying to store data from an external data source, on a regular basis.  Basically I'm taking rss feeds of Domain auctions (from various sources like godaddy, pool, etc.), and updating a table with the auction info using this stored procedure (let's call it sp_storeSale).  Now, in this table that the sale info gets stored, I want to keep historical records for sales for a given domain, so I have a <em>domain</em> table, and a <em>sale</em> table.  The <em>sale</em> table has a many to one relationship with the <em>domain</em> table. </p>
  
  <p>Here's the stored procedure:</p>
  
  <pre><code>    -- --------------------------------------------------------------------------------
  -- Routine DDL
  -- Note: comments before and after the routine body will not be stored by the server
  -- --------------------------------------------------------------------------------
  DELIMITER $$
  
  CREATE PROCEDURE `DomainFace`.`sp_storeSale` 
  (
      middle VARCHAR(63),
      extension VARCHAR(10),
      brokerId INT,
      endDate DATETIME,
      url VARCHAR(500),
      category INT,
      saleType INT,
      priceOrBid DECIMAL(10, 2),
      currency VARCHAR(3)    
  )
  BEGIN
      DECLARE existingId BIGINT DEFAULT NULL;
      DECLARE domainId BIGINT DEFAULT 0;
  
      SET @domainId = fn_getDomainId(@middle, @extensions);
  
      SET @existingId = (
          SELECT id FROM sale
          WHERE 
              domainId = @domainId
              AND brokerId = @brokerId
              AND UTC_TIMESTAMP() BETWEEN startDate AND endDate
      );
  
      IF @existingId IS NOT NULL THEN
          UPDATE sale SET
              endDate = @endDate,
              url = @url,
              category = @category,
              saleType = @saleType,
              priceOrBid = @priceOrBid,
              currency = @currency
          WHERE
              id = @existingId;
      ELSE
          INSERT INTO sale (domainId, brokerId, startDate, endDate, url,
                  category, saleType, priceOrBid, currency)
              VALUES (@domainId, @brokerId, UTC_TIMESTAMP(), @endDate, @url,
                  @category, @saleType, @priceOrBid, @currency);
      END IF;
  END
  </code></pre>
  
  <p>As you can see, I'm basically looking for an existing record that is not 'expired', but has the same domain, and broker, in which case I assume the auction is not over yet, and the data is an update to the existing auction.  Otherwise, I assume the auction is over, it is a historical record, and the data I've got is for a new auction, so I create a new record.</p>
  
  <p>Hope that clears up what I'm trying to achieve :)</p>