TN Query Performance

, ,
1

Need to create a report from TN for last one day worth of documents/records.
Below are the steps followed

1)wm.tn.query:doQuery
2)wm.tn.query:getQueryResults

3)Loop over the results and work on the data.

as for now I have more than a million records/documnets in TN and the query is taking almost forever.

I know archving or purging the TN database shud help but as of now thats not an option.

Is there any way I can make the query run faster?

Any Ideas are appreciated.

Thanks!
-vkram

2

Add indexes for the columns you regularly query against.

3

Apart from Indexing suggestion above:

Also It’s better you use these services instead of mentioned above for your daily reports:

1.wm.tn.query:createDocumentQuery,
2.wm.tn.query:documentQuery
3)loop over the results and work on the data – Extract/mapping etc…

HTH,
RMG

4

Thanks! reamon.

Is it a best practice to add indexes to TN tables?( as they are wM tables)

the doQuery is done on documents ie query type ‘1’.

Where wud I have more information on what exact tables will the query search & what exact tables to have index.

Also One more question.

When I run the service which has the doquery in it and is taking forever and when it is stopped/close from developer window ,wud it bring the server down. Looks like It happened to twice …dont know if it is a coincidence.

Server log :

[ISS.0098.0036E] DefaultProducer encountered Transport Exception:
com.wm.app.b2b.server.dispatcher.exceptions.EndpointUnavailableException:
[ISS.0098.9014] BrokerException: Timeout (112-1450): The request timed out.

[ISC.0063.9998E] Exception →
java.sql.SQLException: [wm-cjdbc40-0034][Oracle JDBC Driver]Login has timed out.

which doesnt say anything abt TN.

Any Ideas

Vkram

5

Did you also check with DBA and see what activity going on during that time span in the logs?

6

There are no issues with adding indexes to wM-defined tables. At places where we’ve done so performance improved dramatically.

The tables can be viewed in the DB. Visio can reverse engineer them if you find ER diagrams helpful.

The TN creation and execution scripts used to be in a directory in your installation. That may have changed with 7 or 8 so I’m not sure if they are still there. If they are there you can see the table definitions and such.