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
Discussion posts and replies are publicly visible
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?
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.
Feels like a problematic database design ...
Maybe a stored procedure that takes the user and return the final data might be an option.
Hi Druva 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.
Hi , Abhishek Karumuru 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"
Hi Druva Could you let me know once if you are passing 3-4 country IDs to SP and getting the expected output ?
Hi Abhishek Karumuru ,
Yes, when we pass limited number of country id's getting output as expected.
Hi Druva Then it could be a limitation issue for SP input parameter
is there any way to overcome this issue?
Hi Druva one possible way is, to run the query using batch wise sending limited country IDs per each batch.