Slow Query Against Information_Schema

English Forum
1

Each time I call the report server it runs the following query. This query takes 7-10 seconds to return. Is there anything I can do to speed that up?

SELECT routine.routine_schema as RoutineSchema, routine.routine_name as RoutineName, routine.specific_name as SpecificName, params.parameter_name as ParameterName, params.udt_name as type, params.parameter_mode as mode, params.ordinal_position as ordinal
                                FROM information_schema.routines routine
                                LEFT JOIN information_schema.parameters params ON params.specific_name = routine.specific_name and params.parameter_mode='IN'
                                WHERE routine.routine_type='FUNCTION' AND routine.type_udt_name='record' and routine.routine_schema != 'pg_catalog' and routine.routine_schema != 'information_schema'
                                ORDER BY params.ordinal_position;
2

I should have mentioned this is Postgresql

3

I have found that by changing the query slightly it is much faster. Would it be possible to get a patch with this new query? Addition in bold.

SELECT routine.routine_schema as RoutineSchema, routine.routine_name as RoutineName, routine.specific_name as SpecificName, params.parameter_name as ParameterName, params.udt_name as type, params.parameter_mode as mode, params.ordinal_position as ordinal

FROM information_schema.routines routine

LEFT JOIN information_schema.parameters params ON params.specific_name = routine.specific_name and params.parameter_mode=‘IN’ and params.specific_schema!=‘pg_catalog’

WHERE routine.routine_type=‘FUNCTION’ AND routine.type_udt_name=‘record’ and routine.routine_schema != ‘pg_catalog’ and routine.routine_schema != ‘information_schema’

ORDER BY params.ordinal_position;

4

Thanks, will look at this ASAP.

5

Hello!
It takes only about 3 seconds on my server.
But almost no difference (100-200 ms) using the other query.

HP

6

Here is mine without the schema limit on params
image

Here is mine with the schema limit on params
image

7

We’ll apply a change here. Just contact our support team for a hotfix.