Performance issue in insert queries

Question

0.00/5 (No votes)

See more:

HI guys,

Just a quick questioN:

I am create an sqlfile with lot of insert queries (ex: 8000 different insert queries)
.

I am running this file through cmd

from cmd:
i will connect with a database run the file with insert queries than i will commit.

Can i do something to make it more faster ?

What I have tried:

I do not know what can i do that's why i am asking you .

Posted 26-Aug-18 23:26pm

Joe Doe234

Updated 30-Aug-18 0:48am

Add a Solution

Comments

Mike V Baker 27-Aug-18 11:39am

Can you create the sql file with 8 inserts of 1000 records each rather than 8000 inserts? What I usually do is set a threshhold like 1000 records. As I process items I keep count of how many inserts are in the list and when it reaches the threshold it runs that insert.

Joe Doe234 28-Aug-18 1:04am

it may be good idea , but when it comes to cmd , the cmd will insert each query one by one and that is taking time. i will try bcp maybe it works

Mike V Baker 28-Aug-18 9:09am

What I mean is that the cmd will only need to execute 8 queries instead of 8000. Even though each qry has 1000 records it still runs faster than 8000 single record inserts. Perhaps if you posted the SQL that the cmd will run I can show you what I mean

Joe Doe234 4-Sep-18 6:21am

if i have insert into table1 values ("hello", "bye");

for 8000 times but the values are all different .. how can i split them into 8 queries ?

Thanks you for your help.

Mike V Baker 4-Sep-18 9:09am

INSERT INTO ComicCharacters (FirstName, LastName) VALUES
("Bugs", "Bunny"),
("Yosemite", "Sam"),
("Elmer", "Fudd"),
("Grape", "Ape");

Start with the INSERT INTO line, then just keep listing the values in parens. When it reaches the batch size you run the cmd, clear it and start over. Use comma between records, semi-colon on the end. 1000 is an arbitrary number I picked so that I could show progress in the program.

Joe Doe234 5-Sep-18 1:04am

is this SQL syntax ? as i tried this method but it is not working, maybe because i am using oracle ?

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 13853105 · Answer 1 · 2018-08-30T00:49:00

Solution 2

Can you please check if there is any Triggers/Indexes is created on that table in which you are going to insert data.If you find that then please disable triggers/indexes then you can try to insert.

Posted 30-Aug-18 0:49am

Member 13853105

Comments

Mike V Baker 4-Sep-18 9:05am

Be aware that if you do disable the triggers and/or indexes during the operation then you'll need to rebuild the indexes and perform the operations in the triggers to ensure the data is correct.

Giridhari Mishra · Answer 2 · 2018-08-28T05:28:00

Solution 1

when you execute inserting 8000 records it will definitely kill the performance. In this case memory and cpu speed also counts. Instead split them into 8 queries each of 1000 records. That will improve performance.

Posted 28-Aug-18 5:28am

Giridhari Mishra

Comments

Joe Doe234 4-Sep-18 4:29am

so how can i split them can you please help me ?

if i have :

insert into table1 values ("hello1");

for 8000 times .. how can i split it into 8 queries ?

Mike V Baker 4-Sep-18 9:01am

INSERT INTO ComicCharacters (FirstName, LastName) VALUES
("Bugs", "Bunny"),
("Yosemite", "Sam"),
("Elmer", "Fudd"),
("Grape", "Ape");

Start with the INSERT INTO line, then just keep listing the values in parens. When it reaches the batch size you run the cmd, clear it and start over. Use comma between records, semi-colon on the end. 1000 is an arbitrary number I picked so that I could show progress in the program.

Performance issue in insert queries

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Performance issue in insert queries

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us