Slow query performance with DQL query using FTI attributes

michael_bailey

Hello everyone,

I have a DQL query that is running quite badly at the moment when it contains the FTI attributes score and summary as below:

SELECT r_object_id, object_name, r_version_label, i_position, summary, score

FROM dm_document

WHERE ( object_name LIKE 'qwerty%' ESCAPE '\' )

ORDER BY object_name, r_object_id, i_position desc

This takes about 45 seconds to return with results. I remove score and summary, it drops to 29 seconds, however since the query is generated automatically, it cannot be changed very easily in that I cannot remove the score and summary attributes.

I could, based on this EMC article (https://community.emc.com/docs/DOC-19053 - specially the section on Unselective full text NOFTDQL queries), remove the order by clause  but if I do that, then the position of the repeating attributes goes. Has anyone come across this and how they worked around this?

The xPlore version is 1.3

The Content Server version is 6.7 SP2

Thanks in advance!

Prak

Hi Michael,

Did you get the solution of this problem?

Andrea Paci

Hi @michael_bailey,

If summary and score are removed from the above DQL, the query will entirely run on the database. If performance is slow in that case, it's a database tuning problem.

With summary and score in place the DQL query will run on xPlore if it is FTDQL compliant (see DQL Reference Guide for details). If sorting is required for a particular attribute you must configure this manually. See section "Sort support" in the xPlore Administration Guide (starting with xPlore 1.3).

Hi @Prak,

What problem are you actually facing?

Regards,

Andrea Paci

Prak

Hi Andrea,

I have a DQL query that contains the summary keyword similar to what Michael has posted and which takes around 44 seconds to return the results. When I remove the summary and execute the query , it drops to 5 to 6 seconds.

Any suggestions which can help me to reduce the execution time of the query. (The xPlore version is 1.4)

Thanks,

Prashant

Andrea Paci

Hi Prak,

Can you share that FTDQL statement for us to have a look at it?

Where are you running that DQL from? DA, iDQL, dQman, Samson?

Is it slow as well from iDQL on the Content Server?

Did you check the dsearch.log in xPlore to see the corresponding xQuery? How long is that xQuery taking in xPlore? The dsearch.log will print the time it took.

If the xQuery is running fast on xPlore, the problem could be caused by security checks on CS (if enabled) due to table joints, Webtop rendering results slowly, etc...

If the xQuery takes time on the xPlore, your xPlore server may be undersized (very comon), returning too many results, running an unselective or wildcarded query, etc...

Performance problems can take time to be resolved, finding the root cause can a challenge at times.

Good luck!