Timeout 504 while running simple SQL count(*) query

Question

Question

Glenn van Bavel · Jun 15, 2017

Whenever I try to run a simple query on a production database table, I get a timeout 504 error. I'm using the SQL interface in Ensemble. One of the queries is a simple SELECT TOP 10. Even SELECT COUNT(*) gives a timeout.

I have tried to run the SQL Runtime Statistics, but this gives me a timeout as well.

Does anyone have an idea where I might look to find what is causing this?

Thanks in advance,

Best regards

Glenn van Bavel,

Discussion (3)0

Log in or sign up to continue

Brendan Bannon · Jun 15, 2017

If you really are doing just a SELECT COUNT(*) FROM Table does your class have a Bitmap extent? That is the fastest way to get the answer for a COUNT(*).

If you have a BitMap Extent and the query is still slow then you might need to clean up your bitmap. For tables that have a large number of rows deleted, Link most Ensemble table, over time a bitmap will slow down.

You can clean them up by using a system utility: %SYS.Maint.Bitmap

There is not much in the docs about this Util but you can have a look at the Class Reference to see examples of how to use it.

0 0

Vitaliy Serdtsev · Jun 15, 2017

Need:

add a bitmap extent index in the class
and/or increase the timeout in the CSP Gateway/WebServer

0 0

score 0 · Answer 1 · 2017-06-15T11:18:27-04:00

What happens if you run the query in the SQL Shell. From a terminal execute:

d $SYSTEM.SQL.Shell()

This brings up a prompt which should let you run queries. Run one and see how long it takes. You should make sure your system has plenty of global buffers, and your last startup was OK (use the cconsole.log for this).