Is there anyway to batch delete from Information Register?

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.

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 ...

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.

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.

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?

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


You can completely delete all data:

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

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

Attached Files

Download 1.jpg (8.3 KB)

Download 2.jpg (22.77 KB)

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

Hi Aleksandr Biryukov!

Is there a quick record insertion in bulk?

Hi Veysel KORUYUCU!

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

For example:

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);   

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.

   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()));

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.

Attached Files

Download scr1.jpg (12.72 KB)

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.

Can I see the sample code?

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.

There the difference in speed is really small

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

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.

  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()));