Thanks for the quick response guys.. I had cross posted it due to the
fact that I had a seen a previous posting on
database.pick regarding attributes..

I am in fact using DB2. I didn't fully understand tony's explanation
regarding the MV values. but the relational
part doesn't seem to really work well since in my example I listed two
attributes types.. if the attributes are
going to  dynamically be added and removed change in nature.. I think
it will be incredibly hard to group
combinations of them to store the item ids in deliminated fields.
which is why I was hopeing a EAV (not EVA as I had previously
mentioned) schema would be useful.

In regards to serge's response.. I definately found the pivot example
a step in the right direction. I have reduced
my thousand selects to one for a count and one for retrieving the
data.. However it is proving to still be very slow
when executing.

I'll briefly describe the setup and the sql queries.. please tell me
if there is anything I could do to optimise
this.. or is it just unavoidable with this size table.

Attribute table:
property_id (BIGINT), ATTRIBUTE_ID (BIGINT), ATTRIBUTE_VALUE (BIGINT)

Index1:
property_id,ATTRIBUTE_ID,ATTRIBUTE_VALUE (as primary)

Index2:
ATTRIBUTE_ID,ATTRIBUTE_VALUE

Dimension:
ATTRIBUTE_ID

I filled the table with 1,000,000 sample entries each one with 10
attribute definitions yeilding 10million rows.
I think one of the the biggest issues here is disk performance..
executing the count query takes a few minutes to complete.

I have a feeling it might go faster if it didn't have to select ALL
the property_ids and THEN then filter them.. but I couldn't find a way
to reference the CASE columns by alias. so I had to use a derived
table to capture all of them
then select ids from it where the values are what I need them to be!

The general idea is to get a count for each individual attribute value
NOT selected after having satisfied the
selected attributes. So the derived table sql counts the instances of
the attributes then returns a 1 if they
existed then the select is executed on that table with a where clause
ensuring all returned fields are = 1.

Any assistance would be greatly appriciated once again. I'm starting
to think I might have to scrap what I have made

and the thought is getting to me since the rest of the app is complete

SQL STATEMENTS:

////////////////////////////////////////////////////////////////////////
SELECT SQL:
////////////////////////////////////////////////////////////////////////

select

count (CASE WHEN attribute_id = 4 AND attribute_value = 46
          THEN attribute_value ELSE NULL END) as
property_position_count,
count (CASE WHEN attribute_id = 2 AND attribute_value = 7
          THEN attribute_value ELSE NULL END) as
property_base_type_count,
count (CASE WHEN attribute_id = 4 AND attribute_value >10
          THEN attribute_value ELSE NULL END) as
property_price_rent_count,
count (CASE WHEN attribute_id = 8 AND attribute_value = 22
          THEN attribute_value ELSE NULL END) as turnkey_count

from test2 INNER JOIN

table(

select property_id from
    table(
        select  property_id,
   COUNT(CASE WHEN test2.attribute_id = 4 AND test2.attribute_value =
46 THEN 1 ELSE  NULL END) as property_characteristics,

   COUNT(CASE WHEN test2.attribute_id = 5 AND attribute_value >1000
THEN 1 ELSE NULL END) as property_price_rent

   from test2 group by property_id
   ) table_of_items_that_satisfy_filter

   where table_of_items_that_satisfy_filter.property_price_rent=1
and
   table_of_items_that_satisfy_filter.property_characteristics=1

) B

ON test2.property_id = B.property_id

////////////////////////////////////////////////////////////////////////
FETCH DATA SQL:
////////////////////////////////////////////////////////////////////////

select *
from

table(
    select property_id from table(
        select property_id,
        COUNT(CASE WHEN ATTRIBUTE_ID = 5 AND ATTRIBUTE_VALUE = 4 THEN 1 ELSE
NULL END) as property_characteristics,
        COUNT(CASE WHEN ATTRIBUTE_ID = 7 AND ATTRIBUTE_VALUE >344633 THEN 1
ELSE NULL END) as property_price_rent
        from test2 group by property_id
    ) B

    where B.property_price_rent=1 and B.property_characteristics=1
) filtered_table

INNER JOIN places ON places.places_id = filtered_table.property_id

Thanks again,

Vasili