The community will be in read-only from Monday 11:59pm (PT) to Wednesday 7:30am (PT)
The community will be in read-only from Monday 11:59pm (PT) to Wednesday 7:30am (PT)
Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Anyone built a 'sql query' view?

Highlighted
Jim Bowen
Super Collector

Anyone built a 'sql query' view?

Hi Folks,

 

I know this is an odd question, but here goes...

 

Has anyone built a view in the PPM database that would allow someone to search ALL of the SQL queries in the system?  This would include validations, workflow step sources, portlet data sources, etc. 

 

My use-case is this: every so often our DBA sends me a SQL statement that is taking too long to execute, and I would like to be able to easily pinpoint the source of the statement.

 

If you've done it, I'd like to steal it!

 

Jim

2 REPLIES
Jamie Pick
Regular Collector

Re: Anyone built a 'sql query' view?

For the purposes of identifying database objects that are being obsoleted or deprecated, we have several queries that go through Validations, Portlet Data Sources, Request Type Rules, etc. to search by table name. In terms of the queries that PPM creates during it's processing, you probably have to sort of wager your best guess as to the area of the system it's happening in by the tables present in the query. You could try to narrow in on it by setting up a trace.

 

If you think it's something built into the product and not a custom query, I'd open an HP case and make them aware of the performance issue you're running into.

 

Thanks,

 

Jamie

Erik Cole
Honored Contributor

Re: Anyone built a 'sql query' view?

Jim, if a query is taking too long it's not your problem, it's his! ;-) But seriously, it will be difficult to just match up the query because the parameters are sent in realtime and the queries are stored

 

knta_validations will have the validation SQL, and kdsh_data_sources will have the base sql for portlets (which are likely the problem ones)

//Add this to "OnDomLoad" event