logo
down
shadow

How to improve MySQL INSERT and UPDATE performance?


How to improve MySQL INSERT and UPDATE performance?

By : Pedro Maciel Corgozi
Date : October 18 2020, 11:12 AM
To fix the issue you can do You could change the settings to speed InnoDB inserts up. And even more ways to speed up InnoDB ...and one more optimization article
code :


Share : facebook icon twitter icon
How to improve data insert/update performance?

How to improve data insert/update performance?


By : Damian Kasperski
Date : March 29 2020, 07:55 AM
it fixes the issue DON'T use Locate(). If you use locate() then Delphi searches row on the client side just scanning row set from your query it takes a LOT of time.
If you have access to MSSQL to create stored procedures then create following procedure and just run it for each line from your TEXT file without any conditions (Use TAdoStoredProc.ExecProc in Delphi). So in this case your don't need first select and Locate procedure. It updates record if Filed1 and Field2 are found and insert if don't.
code :
CREATE PROCEDURE dbo.update_table1 
@Field1 int, --key1
@Field2 int, --key2
@Field3 int, -- data fileds
@Field4 int

AS

SET NOCOUNT ON
update table1 set Field3=@Field3,Field4=@Field4 
        where Field1=@Field1 and Field2=@Field2;
IF(@@Rowcount=0)
BEGIN
     insert into table1(Field1,Field2,Field3,Field4) 
                values (@Field1,@Field2,@Field3,@Field4);
END
GO
......
var 
     ADOStoredP: TADOStoredProc;

  ......
begin

........
    ADOStoredP:=TADOStoredProc.Create(nil);
   try
      ADOStoredP.Connection:=DataMod.SQL_ADOConnection; //Your ADO Connection instance here
      ADOStoredP.ProcedureName:='Update_table1';
      ADOStoredP.Parameters.CreateParameter('@Field1', ftInteger, pdInput, 0, 0);
      ADOStoredP.Parameters.CreateParameter('@Field2', ftInteger, pdInput, 0, 0);
      ADOStoredP.Parameters.CreateParameter('@Field3', ftInteger, pdInput, 0, 0);
      ADOStoredP.Parameters.CreateParameter('@Field4', ftInteger, pdInput, 0, 0);

      While () -- Your text file loop here
      begin

      ADOStoredP.Parameters.ParamByName('@Field1').Value:=Field1 value from text file here;
      ADOStoredP.Parameters.ParamByName('@Field2').Value:=Field2 value from text file here;
      ADOStoredP.Parameters.ParamByName('@Field3').Value:=Field3 value from text file here;
      ADOStoredP.Parameters.ParamByName('@Field4').Value:=Field4 value from text file here;

      ADOStoredP.ExecProc;

      end

    finally
      if Assigned(ADOStoredP) then
        begin
         ADOStoredP.Free;
        end;
    end;

........
end;
How to improve performance of an `INSERT INTO SELECT x FROM y GROUP BY z` MySQL query

How to improve performance of an `INSERT INTO SELECT x FROM y GROUP BY z` MySQL query


By : Ankita shrivastava
Date : March 29 2020, 07:55 AM
To fix this issue A composite index on (name, time, price) will help, because it is a covering index.
I would also write the query as:
code :
INSERT INTO cache (`time`, name, price, low, high, week, month, season)
    SELECT MAX(`time`) AS `time`,
           name,
           MIN(CASE WHEN `time` = 1500254967 THEN price END) AS price,
           MIN(price) AS low, MAX(price) AS high,
           AVG(CASE WHEN `time` > 1499650167 AND price = 1 THEN 1.0
                    WHEN `time` > 1499650167 THEN 0
               END) AS week,
           AVG(CASE WHEN `time` > 1497835767 AND price = 1 THEN 1.0
                    WHEN `time` > 1497835767 THEN 0.0
               END) AS month,
           AVG(CASE WHEN `time` > 1499995767 AND price = 1 THEN 1.0
                    WHEN `time` > 1499995767 THEN 0.0
               END) AS season
    FROM low_price
    GROUP BY name;
How to improve performance of MySQL query with frequent insert rate?

How to improve performance of MySQL query with frequent insert rate?


By : marko.binary
Date : March 29 2020, 07:55 AM
it fixes the issue You need an index for tb_post that covers both the ORDER BY and WHERE clause.
How to improve INSERT performance on a very large MySQL table

How to improve INSERT performance on a very large MySQL table


By : Vijay
Date : March 29 2020, 07:55 AM
will help you Your linear key on name and the large indexes slows things down.
LINEAR KEY needs to be calculated every insert. http://dev.mysql.com/doc/refman/5.1/en/partitioning-linear-hash.html
Improve performance of multiple insert and update queries

Improve performance of multiple insert and update queries


By : Nicolli Almeida
Date : October 25 2020, 07:10 AM
I wish did fix the issue. I have two tables: Sale_Source (10.000 rows) and Sale_Target (1 billion rows). I have four queries to INSERT and UPDATE Sale_Target with the data from Sale_Source. ,
Target Tables has no Index.
Related Posts Related Posts :
  • How to order result by the variable from the CASE statement
  • mySQL how to display NULL as 0
  • Need help partitioning a MySQL table on a bit column with subpartitioning on resulting partitions
  • MySQL error with WHERE clause and GROUP BY clause
  • MySQL return only the first transaction between dates
  • Is it possible to join 2 views with duplicated data?
  • sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `suck_t`/!d;q' bak.sql; how does this command work?
  • Syntax error executing if - else statement when else is equal to NULL
  • How can I construct a SQL to find the MAX of products in different date and time for each one?
  • MYSQL Update column with Sequential numeric value IF NULL - Continued Sequence
  • MySQL Query change row if higher?
  • Advanced SQL Select Query (unique rows) (group by)
  • How to find a result with multiple different values on same column?
  • One data result requires leading zero (MYSQL Workbench)
  • writing script in scala to join two mysql tables and create one object (quill)
  • Wagtail connect to MySQL and import data
  • how to backup mysql database outside phpmyadmin
  • How to recover a MySQL database: Incorrect information in file: './xxx.frm'
  • Need help to create a join Mysql
  • Mysql query using where and group by clause
  • Any way to view ALTER TABLE after its been executed? -MySQL
  • MySQL - Duplicate elimination and Preserving Valuable Data?
  • MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  • Why does MySQL return two different data sets if I do a `WHERE field=1` and `WHERE field='1'`?
  • How to index a MySQL table, where 99% of time I need to query 1% of the data
  • Ordering data results in columns instead of rows
  • whats the best way to allow users to edit a site wiki style
  • how to activate trigger after all the bulk insert(s) in mysql
  • Getting avg without counting hits twice
  • Why is this query slow? Should I use MyISAM rather than InnoDB here?
  • CHECK mysql not working (no error)
  • how B-tree indexing works in mysql
  • Mysql DB Table Rows Disappearing
  • Is 20K reads going to make INSERTing into my table slow? How about a foreign key failure?
  • How to re-sync the Mysql DB if Master and slave have different database incase of Mysql replication?
  • How can I "select *" from a table in MySQL but omit certain columns?
  • Convert blob to text in a mysql export
  • How do I store latitudes and longitudes in MySQL?
  • How do I upload a new Mysql database of tables?
  • PK for web, autoincrement vs UUID style, wich is better and why?
  • MySQL: how to know whether an item is already in the database?
  • why cant i set varchar to be 1000 in length
  • Mysql problem to store only different data
  • Mysql: money and floats
  • MySQL: a huge table. can't query, even a simple select!
  • Mysql Slave not updating
  • MySQL multi table queries
  • Accent-insensitive sorting in MySQL
  • Why doesn't MySQL define a boolean data type?
  • How to relate two tables without a foreign key?
  • whats best way to create documents Archive of images in the database's?
  • MySQL id sequence
  • How can I enable MySQL's slow query log without restarting MySQL?
  • self join to select consecutive numbers
  • Finding largest time interval between list of dates
  • Change entire db's collation and solve illegal mix of collations
  • shadow
    Privacy Policy - Terms - Contact Us © bighow.org