Skip to main content
K
kirank4843
December 19, 2023
Question

Facing issue while querying data with too many filter values

  • December 19, 2023
  • 11 replies
  • 0 views

Hi everyone,

I'm getting an error while querying data. Scenario here is logged in user will have multiple countries associated. so when user logs in we are fetching loggedin user counties and  passing the list of country id's to the view to get data. when user having upto 70 countries it is working fine but when exceeds it is throwing error. Can anyone please help me how to resolve the issue. 

PFA image

11 replies

stefanhelzle0001
stefanhelzle0001
Brainy
December 19, 2023

When you query a view, I suspect a timeout. Try to optimize that view.

Did you consider to use a single filter using the "IN" operator instead of adding a filter for each country?

Next, it is a anti-pattern to load lots of data into memory, just to query something else. Did you consider to either use synced records or a database view to query the final data for a given user in a single request?

K
kirank4843Author
December 19, 2023

Hi Stefan,

country field is of text type and there are multiple countries in each field separated by ";" . so i'm using "includes" operator here.

To use synced records , these tables were used in multiple places across the application and updating the data through multiple sources. so we are not considering record centric approach.

stefanhelzle0001
stefanhelzle0001
Brainy
December 19, 2023

Feels like a problematic database design ...

Maybe a stored procedure that takes the user and return the final data might be an option.

karumurua531442
karumurua531442
December 20, 2023

Hi [mention:4f1ca303245e454890f21dc5ffe804da:e9ed411860ed4f2ba0265705b8793d05]  As Stefan said there could be an query time out issue when data is loading from view, You can use n stored procedure to query the data in that stored procedure construct your view using TEMPORARY TABLE so this could push the performance a little bit.  Temporary table will be automatically dropped after each successful session.

K
kirank4843Author
December 20, 2023

Hi [mention:126a676c85024855948336691b0a7b92:e9ed411860ed4f2ba0265705b8793d05] , [mention:3510a5189ce1477eaf2e09f5858c87e9:e9ed411860ed4f2ba0265705b8793d05] 

We created a stored procedure using the view to query the data but it is throwing error again if we pass more than 60 to 70 country id's,
in SP instead of list i'm concating all the country id with ";" separated and passing as input to the SP. Again in SP splitting the id's and fetching the data.

example country id's :  "1;20;35;43;67"

karumurua531442
karumurua531442
December 20, 2023

Hi [mention:4f1ca303245e454890f21dc5ffe804da:e9ed411860ed4f2ba0265705b8793d05] Could you let me know once if you are passing 3-4 country IDs to SP and getting the expected output ?

Terms & ConditionsAccessibility statement