Hey all!
I’m trying to run a simple select query with a where statement to get only rows with a specific Id (one row should return) my table does have around 17k rows (and three columns) but other tables with similar amounts of records perform fine one thing that I noticed is when I remove the parameter from the query and just plugmy Id (same used for the test) it works better
When running in sql server it take one second at most when running I outsystems environment it sometime works but slow or even timesout sometimes
I was hoping someone with more knowledge about how queries run on outsystems might be able to help me
thanks!!
Hi Joharah,
Can you share your advanced query please, for we understand better why not works.
Regards.
Agno Silveira wrote:
Yes sure it’s a very simple query
SELECT {entity}.*FROM {entity}
WHERE {entity}.[ID] = @ID
I’ve also tried to select only columns I want and top () and it’s still timesout sometimes
I am connected to a view though not a table could that have an effect even if the same query is running fine on ms sql server?
joharah k wrote:
Right, try put in your query the sentence whith(nolock)
Example:
SELECT {entity}.*FROM {entity} WHIT(NOLOCK)
hope it's help.
I just tried this it is still slow and will still give me time out sometimes
Hi,
Let me ask you if you use sql advance or Aggregates? With SQL advance you have more control but normally the aggregates have a nice performance because they are optimized. If you just want some rows, you can define the max of records that you want in both ways (aggregates and sql advance). Another point, you can create in entities an index with the attributes that you want use, the queries will be more fast in read but in write will be more slow.
Cheers
Miguel Verdasca wrote:
Hello
I have tried both and they both timeout sometimes currently I’m working with an advance query but my select statement is very simple. I am using an external database so I’m not sure if I’ll be able to index it (though I only need toread and not write anything)
I will try the aggragate with max records for some reason I did not try that!
First of all, you check how many records are there in your table, for this you put a count query and check it once.
After that you can check once for the top 10 or top 100 records by checking whether there is a problem of slowness or a problem of data.
Vijay Malviya wrote:
My table as a little under 19k (I’m also having the same issue with a 8k records query) records and the query I’m running has top(1) and it’s still taking time and sometimes even times out
Would that indicate it’s a problem with the data?
If you are using on-premises then you can try to restart outsystem services once .
Would doing so affect my other apps?
Apart from this, you try to validate that your query is taking time or there is some other problem, for this you can check the log from the service center as per below screenshot
Hi joharah k,
Where are you using that query? Are you displaying it on the screen? If so, I strongly advise you to use Aggregates, like Miguel mentions, the platform optimizes the number of records fetched and the attributes fetched based on what is actually used. You can use the Executed SQL attribute if you want to know exactly what SQL is executed by OutSystems to fetch the preview data displayed in the Aggregate.
Restarting the OutSystems services should have no impact on your issue, as they are not used for database access.
Can you please:
Please share that information here so we can try to help you with more knowledge.
Jorge Martins wrote:
Hello jorge,
I am using queries because I tried to use aggregates and I had the same issue of them timing out so I thought to try a query.
I have checked my reports for query performance and it only shows one that started at 07-13 00:00 and ended at 07-13 15:23 and when clicking on it it only says no reports to show.
I however did find in the service actions log an " Index was outside the bounds of the array." error and time out errors could that be an indicator of something?
I feel like it important to note that the Query does work fine at times but some other times it'll time out.
Joharan,
You have not answered my first question, and it might help you figure out which logs are relevant.
Where are you using that query?
If you are not calling your SQL from a Service Action then you shouldn't need to worry about that set of logs altogether.
...
You should check the Errors log for entries that correspond explicitly to your query (if any).
You can generate a new report like Vijay showed in the screenshot, and select whichever time-frame is suitable for you. If the existing report covers a period where you had issues, it means the timeout issue wasn't at the database query level but afterwards.
Have you checked the database logs (on SQL Server) to confirm how long it took to process the OutSystems-initiated requests and compare with equivalent queries performed from SQL Server Management Studio (or similar direct connection tool)?
If you are using a Service Action to call your SQL, that is important information to have and that exception you mention becomes potentially relevant.