Insert Activity taking Long time to execute

Help
1

Hi All,

I am inserting data into the table tblTextData using Insert Activity but this activity taking too much time to execute

insert
Here, TextDT datatable is having 2 lac records and this insert activity took 10 mins to insert the data.

exetime
Can anyone explain me what might be the reason?

Thanks

Today I cleared all the records in table tblTextData and ran the above insert query in local database, it took 1 min 15 secs and it inserted 2 lac records

I did the same thing in production db, cleared the table and ran the bot there it is took 9 min 54 secs and it inserted 2 lac records

In both cases the table structure and indexes, everything is same

what might be the issue here with production db?

1 Like
2

Hi @Karun
Actually its not taking much time, as we have around 2 lakh records
May be it might be due to database server we use is slower to insert the record…
Cheers

2 Likes
3

Hi @Palaniyappan,

Thank you for giving reply to my question

May be it might be due to database server we use is slower to insert the record

means what ?

2 Likes
4

May i know which database you are connecting to …?
Cheers @Karun

1 Like
5

SQL Server 11

2 Likes
6

No worries, we have something called bulk insert
Fine try to use a bulk insert statement as mentioned here

have that script in a text file, read it with read text activity and get the output as a string variable and pass it to the insert query activity
Kindly try this and let know buddy
Cheers @Karun

2 Likes
7

Hi @Karun

Have a try of using timeout Ms property by setting a delay of 2 seconds

Thanks
Ashwin.S

2 Likes
8

Thank for the reply @AshwinS2

There is no timeout property for Insert Activity

1 Like
9

were you able execute with bulk insert @Karun

10

Not yet tried

It seems, it’s not that much easy to implement

1 Like
11

same syntax buddy

BULK INSERT TableName
FROM 'filePath'
WITH
(
  FIELDTERMINATOR = '','',
  ROWTERMINATOR = ''\n'',
  ROWS_PER_BATCH = 10000, 
  FIRSTROW = 2,
  TABLOCK
)

just we need to keep this in a text file
read that text with READ TEXT activity and get as string output
and pass the string variable to the insert query
Simple buddy
Cheers @Karun

12

here filepath is input text file path i need to give ?

1 Like
13

yes once after writing the text file with script between double quotes
save that file
and pass that file path to READ TEXT activity and we will be getting a string variable
now pass that string to insert query
Cheers @Karun

14

Hi @Karun

Why don’t you write a stored procedure for bulk insert and use execute query statement

Thanks
Ashwin.s

2 Likes
15

@AshwinS2 great idea I will look into it

@Palaniyappan also suggesting bulk insert from the bot

Thank you guys for your support

2 Likes
16

Hi Guys,

Today I cleared all the records in table tblTextData and ran the above insert query in local database, it took 1 min 15 secs and it inserted 2 lac records

I did the same thing in production db, cleared the table and ran the bot there it is took 9 min 54 secs and it inserted 2 lac records

In both cases the table structure and indexes, everything is same

what might be the issue here with production db?

1 Like
17

i think this should be in vice versa…
production should be faster and the local database must be slower comparatively
and this is how it should be as the production environment is set in that way only to take up things faster
i think when you were running the same bot to insert into the production db, that table must be used by some process in the production at that time…kindly check with the db team, the difference between the local database schema structure and the production db
and get the logs and execution time,
That must solve the issue for sure buddy
Cheers @Karun

1 Like
18

Thanks @Palaniyappan

I’m sure that the table is not used by any other processes

Ok I will do that :+1:

1 Like