Check if Value Exsists in Array

[mja37]

I have an array in Informix 4GL nbarray[100] that is populated with values.

I want to check to see if a selected value is contained in that array...this will be performed for about 10,000 values so I'd like to stay away from a simple loop search through the entire array for each value.

 

[olded]

Hi:

Have you considered in your 4GL program using a temp table instead of an array? You could index on the column of interest.

Regards,


Ed

 

[mja37]

What is the cost of multiple temp tables? I already have a temp table with the 10,000+ values in it on a system that is used concurrently with about 35 other users.

 

[olded]

Hi:

> I already have a temp table with the 10,000+ values ...

I'm not certain we're on the same page as far as what constitutes a "temp table". I'm asking if you've considered using a temp table within the 4GL program instead of an array.

Anyway, I guess this is what you'd have to test. Creating an array of 10,000 elements for 35 users could cause a memory problem. Creating 35 temp tables of 10,000 elements might be manageable if your temp dbspace is large enough.

Regards,

Ed

 

[mja37]

>I'm not certain we're on the same page as far as what constitutes a "temp table". I'm asking if you've considered using a temp table within the 4GL program instead of an array....

I am loading a textfile into a temp table, that is where the 10,000+ values are located

then i am using an SQL query to populate the array with about 100 values

my followup question was is it more costly on system resources to have two temp tables open or to have an array with specific values?

Thanks man.

 

[awopbamboo]

Consider doing the following instead.

You mentioned you already have a table with the 10,000 items in. So, instead of searching you arry on 100 10,000 times.

Simply, search your temp table of 10,000 1 100 times. Turn it round the other way, it is much more effiecent.

Jamie