Insert Activity taking Long time to execute

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

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

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

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

1 Like

SQL Server 11

2 Likes

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

Hi @Karun

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

Thanks
Ashwin.S

2 Likes

Thank for the reply @AshwinS2

There is no timeout property for Insert Activity

1 Like

were you able execute with bulk insert @Karun

Not yet tried

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

1 Like

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

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

1 Like

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

Hi @Karun

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

Thanks
Ashwin.s

2 Likes

@AshwinS2 great idea I will look into it

@Palaniyappan also suggesting bulk insert from the bot

Thank you guys for your support

2 Likes

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

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

Thanks @Palaniyappan

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

Ok I will do that :+1:

1 Like