MySql Batching Stored Procedure Calls with .Net / Connector?

Question

Is there a way to batch stored procedure calls in MySql with the .Net / Connector to increase performance?

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).

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().

I'm wondering if there's a better way to batch these calls?

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.

Any advice? Thanks!

EDIT: More info
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 domain table, and a sale table. The sale table has a many to one relationship with the domain table.

Here's the stored procedure:

    -- --------------------------------------------------------------------------------
-- 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

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.

Hope that clears up what I'm trying to achieve :)

Answer 1

I'm not entirely sure what you're trying to do but it sounds kinda house-keeping or maintenance related so I won't be too ashamed at posting the following suggestion.

Why dont you move all of your logic into the database and process it all server side ? The following example uses a cursor (shock/horror) but it's perfectly acceptable to use them in such circumstances.

If you can avoid using cursors at all - great, but the main point of my suggestion is about moving the logic from your application tier back into the data tier to save on the round trips. You'd call the following sproc once and it would process the entire range of data in single call.

call house_keeping(curdate() - interval 1 month, curdate());

Also, if you can provide just a bit more information about what you're trying to do we might be able to suggest other approaches.

Example stored procedure

drop procedure if exists house_keeping;

delimiter #

create procedure house_keeping
(
in p_start_date date,
in p_end_date date
)
begin

declare v_done tinyint default 0;
declare v_id int unsigned;
declare v_expired_date date;

declare v_cur cursor for 
  select id, expired_date from foo where 
    expired_date between p_start_date and p_end_date;

declare continue handler for not found set v_done = 1;

open v_cur;

repeat
    fetch v_cur into v_id, v_expired_date;

    /*
    if <some condition> then
      insert ...
    else
      update ...
    end if;
    */

until v_done end repeat;
close v_cur;

end #

delimiter ; 

Just incase you think I'm completely mad in suggesting cursors you might want to read this Optimal MySQL settings for queries that deliver large amounts of data?

Hope this helps :)