DQL Queries on Audit trail table causing performance hit

Hi Experts,

I am using a dql query on audittrail table, to get the username,last webtop access date(custom application),Total numbers of days in webtop(custom application).

following are the queries to get the required out put.we have three environments(development,QA and production),in dev environment it's working fine.

but in QA & Production ,because huge amount of data it's taking lot time due to that which session is time out and it's not returing data.

Please advice how to reduce the execution time.

main query
select user_name,'$1$','$2$' from dm_user where r_is_group = FALSE and user_state = 0 and user_name not like 'dm%' order by user_name

sub queries

select time_stamp from dm_audittrail where user_name ='%s' and event_name = 'dm_connect' order by time_stamp desc enable(RETURN_TOP 1)

select distinct DATETOSTRING(time_stamp,'mm/dd/yyyy') from dm_audittrail where user_name ='%s' and event_name in ('dm_connect') and DATEDIFF(day, time_stamp, DATE(TODAY))<=180

your help is highly appriciated

thanks

Hari

Find more posts tagged with

Comments

bacham2

You can add indexes on the dm_audittrail type. But don't add too many otherwise it will slow down your system during inserts/updates. For example, just adding an index on event_name should solve your problem.

NewWindow_Hexaware_ey

HI bachm,

Thanks for your quick reply.

i have created the index query like below, and executed.but no luck.still result is not showing,only 0 result is displaying.

EXECUTE make_index WITH type_name='dm_audittrail',

attribute='user_name', attribute='event_name'

is it need to create index for same table in database(sqlserver) level also?

In my sql database dm_audittrail table is not available.instead of this,dm_audittrail_s,dm_audittrail_sp,dm_audittrail_acl_r,etc.. is available in sqlserver database..

please suggest

thanks

hari.

bacham2

The type dm_audittrail maps to the table dm_audittrail_s in the database. No need to create index in database. This is what the DQL statement does.

It might be better to invert the order of the attributes, i.e. event_name first and then user_name. This way, the index will also benefit if you query only for event_name (which is usually more common than querying just the user_name).

NewWindow_Hexaware_ey

Hi bacham,

Thanks for your quick response.

by your suggestion ,if i change the order of the attributes then it will help,so I am trying to drop the index ,created earlier.

i have executed the below query to drop index ,it is giving is result 'F'. after executed drop command ,i have verified it's deleted.

is the below query is correct to drop the index created earlier.??

EXECUTE drop_index WITH name='dm_audittrail'

2.is it possible invert order of the attributes with out droping the index created earlier?

Please help.

thanks

Hari.

bacham2

The value of name must be the name of the index, not of the type (there can be more than one index on a type).

Use the following query to find the name of your index:

select * from dmi_index where index_type ='<id of dm_audittrail dm_type object>'

NewWindow_Hexaware_ey

Hi bachm,

Thanks a lot for your response

I have droped the index created earlier successfully, and created index again newly by inverting the attributes order according to suggession. but no luck.still the query is giving 0 results.

EXECUTE make_index WITH type_name='dm_audittrail',

attribute='event_name',attribute='user_name'

Please suggest, how to solve this issue.

Thanks

Hari

bacham2

Which query are you talking about? The EXECUTE statement? I think it's normal that it doesn't return results.

Run the following query again to check if your index got created:

select * from dmi_index where index_type ='<id of dm_audittrail dm_type object>'

You can also double-check in your database if the index is there.

NewWindow_Hexaware_ey

HI bacham,

Index is created successfully.i have verified.

I am talking about my normal dql queries.my normal dql queries will fetch the data in to the datagrid.bcs of huge data it's not returning data on the datagrid on the webtop.

Actally i have created the one component called reports in webtop. after lunching this component , this auditrail report will come in to dropdownlist and will select the name of the report..once if i click the execute button(like in dql editor,in my jsp i have customised the dql_editor.jsp according to my requirement),the above my dql quries will execute and will return the data.

This report is giving data in development environment.it's not giving data in QA and Production environments bcs of huge data in both two environments(QA & prod).

code level just i am passing these dql queries.

please suggest,how can i solve this isse.

Thanks

Hari.

bacham2

This is a completely different issue. Adding an index should not change the results of your query. Please start a new thread.

NewWindow_Hexaware_ey

Thanks for your comments.

I have posted the new thread.(Querying on audittrail table is not giving results.)

Thanks

Hari