I’m creating a dynamic query on the server side beside the parameter. However, my query is taking 2 seconds to fetch the records. I’m passing the query through active records let me share the query and Active record rails code
SELECT (custom_attribute_values.attributable_id) FROM custom_attribute_values WHERE ((“custom_attribute_id” = ’12’ AND “value_string” = ‘Female’) OR (“custom_attribute_id” = ’12’ AND “value_string” = ‘Male’)) INTERSECT SELECT custom_attribute_values.attributable_id FROM custom_attribute_values WHERE ((“custom_attribute_id” = ’17’ AND “value_string” = ‘Widowed’) OR (“custom_attribute_id” = ’17’ AND “value_string” = ‘Divorced’) OR (“custom_attribute_id” = ’17’ AND “value_string” = ‘Never married’) OR (“custom_attribute_id” = ’17’ AND “value_string” = ‘Married’) OR (“custom_attribute_id” = ’17’ AND “value_string” = ‘Separated’))
Here is the time had taken by Postgres SQL
def fetch_members
begin
@filter_result = CustomAttributeValue.find_by_sql(segmentation_query).size
rescue Exception => e
render_json_response(response, @success, e.message, e)
end
end
Above is the Rails code
Do you know that how can Speedup my query not to mentioned I have tired the indexing but indexing is very cost paying operation in my case
Advertisement
Answer
For start I would probably try something like this:
SELECT DISTINCT a.attributable_id FROM custom_attribute_values a
where custom_attribute_id = '12'
and value_string IN ('Female', 'Male')
and EXISTS(
SELECT 1 FROM custom_attribute_values b
where custom_attribute_id = '17'
and value_string IN ('Widowed', 'Divorced', 'Never married', 'Married', 'Separated')
and b.attributable_id = a.attributable_id
)
(there might be some errors I haven’t try to execute the query, but it should give you an idea)
But we will need more information as Frank Heikens said in his comment to the question.