Is there anyway to batch delete from Information Register?

Understanding basics of 1C:Enterprise platform. To start working with 1C:Enterprise platform visit Getting started page

#1
People who like this:0Yes/0No
Just came
Rating: 0
Joined: Apr 9, 2019
Company:

Hello;

We are working on a project which is serving for Pharmacy WareHouses. And we want to trace Medicines with QR Code. But we have some problems about the records. Couse those records can be several hundred thousands. And we are keeping those records in Information Registers. But, the program getting slower while we are deleting those records.

So, is there anyway to batch delete from Information Registers?

By the way, if you have an idea to keeping those records. We will be pleased to hear that.

 
#2
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

Hi Ebu Bekir Ertekin!

Yes, of course there are several ways to delete records in a package.

In general, information registers work quite quickly, and I think the problem is not in the registers, but in the program code ...

May you show me your code? I`ll try to analyze it and try to understand - why this code is slow ...

 
#3
People who like this:0Yes/0No
Just came
Rating: 0
Joined: Feb 25, 2019
Company: Atakod Bilişim Teknolojileri Ltd. Şti.

Hello,

Normally when I add 100000 records from MSSQL, it adds in 1 minute. When I add 100000 records from 1c to the inforeg table, it adds in 10 minutes. Likewise, 1c deletes the 100000 record in 10 minutes because it deletes it in one cycle. MSSQL deletes 100000 records in 10 seconds. In fact, we want to delete the bulk quick record from inforeg.

Edited: Veysel KORUYUCU - Oct 29, 2019 10:53 AM
 
#4
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

Hi!

Undoubtedly, MS SQL will always work faster than 1C, especially when adding data.

When deletting, there are ways to make it faster.

Please show me the structure of your information register and program code.

 
#5
People who like this:0Yes/0No
Just came
Rating: 0
Joined: Feb 25, 2019
Company: Atakod Bilişim Teknolojileri Ltd. Şti.

Hello,

We haven't started the project yet. We do R & D. I tested the codes with a normal and simple loop. What I want to ask is, are we able to perform a quick batch deletion from the inforeg table without looping?

 
#6
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

For example, you have a register (see screenshot 1 and 2).


You can completely delete all data:


Code
   Set = InformationRegisters.ExchangeRates.CreateRecordSet();
   
   Set.Write();



And you can delete only a part:

Code
   Set = InformationRegisters.ExchangeRates.CreateRecordSet();
   
   Set.Filter.Currency.Set(Currency);
   
   Set.Write();

Download 1.jpg (8.3 KB)
Download 2.jpg (22.77 KB)
 
#7
People who like this:0Yes/0No
Just came
Rating: 0
Joined: Feb 25, 2019
Company: Atakod Bilişim Teknolojileri Ltd. Şti.

Many thanks did not know this command. I tested it now. It wipes it too fast.

 
#8
People who like this:0Yes/0No
Just came
Rating: 0
Joined: Feb 25, 2019
Company: Atakod Bilişim Teknolojileri Ltd. Şti.

Hi Aleksandr Biryukov!

Is there a quick record insertion in bulk?

 
#9
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

Hi Veysel KORUYUCU!

Unfortunately, there is no batch insertion, but there are some settings that can speed up a regular insertion.

For example:

Code
RecordSet = InformationRegisters.ExchangeRates.CreateRecordSet();
   
   // This mode uses a minimum of write checks...
   RecordSet.DataExchange.Load = True;   
                                                      
   // Some your code...
   
   // This option - "false" - also allows you to speed up the recording. 
   // But you need to be sure that there are no double entries in the register.
   RecordSet.Write(False);   



Of course, in speed this can not be compared with MS SQL - but this is the fastest way to write to the information register.

 
#10
People who like this:0Yes/0No
Just came
Rating: 0
Joined: Feb 25, 2019
Company: Atakod Bilişim Teknolojileri Ltd. Şti.

Hi Alexander Biryukov,

Thank you for your answers. With the following code I can add record very quickly. Added 1 million recordings in 1 minute 35 seconds.

Code
   Message("Start:"+String(CurrentDate()));
   
   RecordSet = InformationRegisters.Serials.CreateRecordSet();
   
   For i = 1 To 1000000 Do
      
      Add = RecordSet.Add();
      Add.SN = StrReplace(String(i),".","");
      Add.ExpDate = "2019-12";
      Add.Lot  = Add.SN + " Lot";
      Add.Number = i;
      
   EndDo;
   
   RecordSet.Write();
   
   Message("End:"+String(CurrentDate()));

 
#11
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

Oh, I get it — you created an object "RecordSet" for every insert operation. It is possible to do this, but this is not the best way to add entries to the register :-)

That's why I asked you for a sample of your code to understand where the error is ...

If you now apply my recommendations, the recording time will be even shorter.

I just checked: your code runs on my server for 2 minutes 10 seconds, and if with my recommendations - 2 minutes 3 seconds. I inserted 2 million records.

Download scr1.jpg (12.72 KB)
 
#12
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

And another recommendation - it is better to do the insertion in portions, for example, 100 thousand records, and use transactions.

For example, we open a transaction, create an object, add 100 thousand records to it, write an object, close the transaction.

This will be more correct.

 
#13
People who like this:0Yes/0No
Just came
Rating: 0
Joined: Feb 25, 2019
Company: Atakod Bilişim Teknolojileri Ltd. Şti.

Can I see the sample code?

 
#14
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

Code
Message("Start:"+String(CurrentDate()));
   
RecordSet = InformationRegisters.Serials.CreateRecordSet();
   
RecordSet.DataExchange.Load = True;
   
For i = 1 To 2000000 Do
      
   Add = RecordSet.Add();
   Add.SN = StrReplace(String(i),".","");
   Add.Number = i;
      
EndDo;
   
RecordSet.Write(False);
   
Message("End:"+String(CurrentDate()));   

 
#15
People who like this:0Yes/0No
Just came
Rating: 0
Joined: Feb 25, 2019
Company: Atakod Bilişim Teknolojileri Ltd. Şti.

Thank you Aleksandr Biryukov. I'm still doing this. But I didn't see the speed difference. I was wondering if there was a different way, so I wanted sample code.

 
#16
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

There the difference in speed is really small :)

Unfortunately - and this is the fastest way to write to registers

 
#17
People who like this:0Yes/0No
Active user
Rating: 4
Joined: Mar 10, 2017
Company: Rufinor

Hi Veysel KORUYUCU!

You can use load command to information register. If you use this code the time for upload takes 1 minutes 47 seconds.

Code
  Message("Start:"+String(CurrentDate()));
   
   NewValueTable = New ValueTable;
   NewValueTable.columns.Добавить("SN");
   NewValueTable.columns.Добавить("ExpDate");
   NewValueTable.columns.Добавить("Lot");
   NewValueTable.columns.Добавить("Number");
   
   
   RecordSet = InformationRegisters.Serials.CreateRecordSet();
   
   RecordSet.DataExchange.Load = True;
   
   For i = 1 To 2000000 Do
      
      Add = NewValueTable.Add();
      Add.SN = StrReplace(String(i),".","");
      Add.ExpDate = "2019-12";
      Add.Lot  = Add.SN + " Lot";
      Add.Number = i;
      
   EndDo;
   
   RecordSet.load(NewValueTable);
   RecordSet.Write(False);
   
   Message("End:"+String(CurrentDate()));      

 
Subscribe