Problem with Select statement accessing database table with huge data

[Former Member]

Hi Guys,

Kindly help me with this issue..

I need to count the number of records in a database table comparing on another internal table containing more than 5000 records. This database table contains more than 2000000 records in development client itself. Please let me know which of the following alternatives is best. Thank you

1)

Loop at ITAB1.

Select count(*) from DBTAB into a variable where field1 = ITAB1-field1.

Endloop.

OR OR OR OR

2)

Select * from DBTAB into ITAB2

for all entries in ITAB1 where field1 = ITAB1-field1.

Loop at ITAB1.

Loop at ITAB2 where field1 = ITAB1-field1.

count = count + 1.

Endloop.

Endloop.

The 1) (first) option is getting executed correctly but the database access is 75% on SE30 (Run time analysis) in Dev client, still the program needs to be moved to Production

I tried with 2nd option too but it takes long time and i stopped the execution as i could not wait longer. Do not know whether this works or not. The first option is working but my main concern is SELECT within a LOOP.

In the 2nd option, when i try to move 2000000 records into ITAB2 i am not sure whether ITAB2 is capable of holding those many records at a time or not. The program execution does not stop even after waiting for 4 or 5 mintues. ITAB2 is popluated with all the records but when i look at it in DEBUG mode the record number after 100000 record is starting with a * on left side.

Awaiting your reply, Thanks...

[Former Member]

Hi Kamal,

Why not you try with Views. Either instead of select * use select field name in your program. I think it better option in your case. Check the primary indexes and select your data according to index key field.

If it not works then let me know. I will try to solve to your problem.

regds,

Rakesh

[Former Member]

check the help of select and search for aggregates in queries. You can give count directly in the select statement.

Both the methods mentioned above by you are not going to give you good performance.

Abdullah.

[romanweise]

Hi Kamal,

you should neither do database selects in a loop nor use nested loops if there is any way to avoid it. And even if you have to nested loops which are restricted by where clause always ensure that the tables are sorted by the fields of the where clause so the runtiime does not always scan the complete internal table.

To speed up your program I'd try one of these 2 ways:

1) Use the GROUP BY part of the select to count the rows per ID. As group by is not working well with for all entries you would either have to get the whole table content or restrict by other table fields which you might have in the program context. although aggregation operations are bypassing the table buffer this can be a performant way if you use a huge part of the table rows. You´woulkd have to evaluate this according to your development context.

2) get the table rows with a SELECT using FOR ALL ENTRIES IN. But as you only want to count do not read the whole line but only the field with the ID to lower memory consumption. Define a structure with the ID and a field type interger and a internal table based on this linetype. Loop about the result of the select, move the ID and a 1 into the structure and use the COLLECT statement to build a table with IDs and their count. COLLECT can use internal temporary hash keys to work very performant check documentation on details for that.

Kind Regards

Roman

[Former Member]

Try below In your code:

Loop at ITAB1.
Select count(*) from DBTAB into a variable where field1 = ITAB1-field1.
Endloop. 

1) The easy way out here is make sure that field1 is the primary key in table DBTAB.

2) remove duplicates from ITAB1 by sorting on field1.

[Former Member]

I would generally say, that this task if not really necessary, what does it help if you really

know how many records appear in a table, it will change with the next inserts.

And both approaches are much too slow, the second one is a performance suicide.

It is not necessary to come with an internal table. Check the statistics on the dbtable

Select field1 count(distinct field1)
          into table ...
          from DBTAB 
          group by field1.

The results can be mixed up with your internal table. But use correct nested loops

with binary search or hashed tables!

Siegfried

[Former Member]

As u say, I'm not selecting * , am selecting the required fields only..

The program works fine when I count the number of matched records using select count(*) but it is not working or hanging forever when i try to select all the values into ITAB from DBTAB and then count them in the loop based on where condition.

I think since the first option is working fine i will proceed with that, because as in the 2nd option if i select the 2000000 records into ITAB at once, the records are present in the ITAB but the record numbers are listed from 1,2,3....100000 only after that the record numbers are starting * i.e.

Num Field1

-

---

1 12000345

2 12000346

-

-

100000 18000678

*123456 1800756

Thanks for your replies

take care

[Former Member]

why do you ask, if you don't want to learn?

I am not writing answers because I am so bored.

The aggregate solution which I propose works much faster than all solution if you cover more or less

the whole table with you 5000 field in itab. This can be checked by the distinct values of field1.

Or you execute my solution and if bring less than 10.000 lines, then it is better to do the camparison

with itab as postprocessing.

Siegfried

[Former Member]

Sorry i was unable to understand your answer..

DBTAB1 (Primary Keys) (Holds 5000 records for ex)

-

---

Invoice_num

DBTAB2 (Primary Keys) (Holds 25 Lac records)

-

---

Invoice_num

Line_item_no

Line_detail_itm_no

Select Invoice_num from DBTAB1 into table ITAB1.

delete duplicates and sorted it

Loop at ITAB1.

Select count(*) from DBTAB2 into lv_count where invoice_num = ITAB1-invoice_num

Endloop.

I just need the number of rows from DBTAB2 matching the invoice number from ITAB1. Please suggest me how i can do it effectively. If possible write the code lines for me.

Thank you very much

[Rui_Dantas]

What criteria are you using to select from DBTAB1? None?

What do you want? Total number of records in DBTAB2 with a matching record in DBTAB1, or number of records per invoice?

If per invoice:

SELECT dbtab1.invoice_num, count(*)
FROM    dbtab1 JOIN dbtab2 ON dbtab1.invoice_num = dbtab2.invoice_num
WHERE  ... your criteria for dbtab1 ...
GROUP   BY dbtab1.invoice_num.

If total:

SELECT count(*)
FROM    dbtab1 JOIN dbtab2 ON dbtab1.invoice_num = dbtab2.invoice_num
WHERE  ... your criteria for dbtab1 ...

This seems kind of obvious, so I am not sure if I understood your questions.

Hope this helps.

Rui Dantas

[Former Member]

Thank you all,