How to speedup the iterative insertion process in a stored procedure?

Question

I am trying to execute a stored procedure which calls "INSERT" command multiple times in a loop. I want to speedup the insertion process and trying to use "RAM DISK" for this. Let me know the approach to deal with this kind of problem in MySql.

I am on windows 32bit environment

Answer 1

I have a ram disk approach which will help You can create a plugin for MySQL which will dump your data into a ram-disk file and your stored procedure can use this ram disk file for query operation.

1. Create a MySQL Plugin with the following features.

   • The plugin should have information about the ramdisk file either hard-coded or read via configuration file.
   • The plugin should have interface to take data and store it in a ram disk file.
   • The plugin should be able to return the path of ram-disk file.

2. Stop the MySQL server, copy the plugin binary into plugin folder on mysql and restart the MySQL Server.

3. create functions in MySQL to expose the plugin API.

   CREATE FUNCTION add_info_to_ramdisk RETURNS int SONAME 'yourPluginName.dll'; CREATE FUNCTION get_ramdisk_filename RETURNS String SONAME 'yourPluginName.dll';

4. Update your stored procedure to use the plugin APIs and populate the data in ram-disk file.

5. Utilizes the ramdisk file to speedup your query performance.

Let me know if this will helps you out.

Answer 2

You may try CreateFileMapping. The CreateFileMapping function returns a handle to the file mapping object. This handle will be used when creating a file view so that you can access the shared memory. When you call CreateFileMapping, you specify an object name, the number of bytes to be mapped from the file, and the read/write permission for the mapped memory. The first process that calls CreateFileMapping creates the file mapping object.

For more details refer this link

Remember:To ensure that other processes cannot write to the portion of the file that is mapped, you should open the file with exclusive access.