Error message: Query Killed

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

pardeep.naik
Posts: 5
Joined: Fri Feb 16, 2018 2:20 pm

Error message: Query Killed

Post by pardeep.naik » Thu Apr 19, 2018 12:00 pm

Hi all,

I am using PATSTAT 2017 Autumn version. I ran a query multiple times but it never give results and show the error "[PATSTAT Online error] Query killed". The query is given below:
SELECT tls211_pat_publn.appln_id, publn_auth, publn_nr, publn_kind, publn_date, appln_title, appln_abstract
FROM tls211_pat_publn
JOIN tls202_appln_title ON tls211_pat_publn.appln_id = tls202_appln_title.appln_id
JOIN tls203_appln_abstr ON tls211_pat_publn.appln_id = tls203_appln_abstr.appln_id
WHERE publn_date >= '1980-01-01'
AND (appln_title LIKE '%rhamnolipid%' OR appln_abstract LIKE '%rhamnolipid%')

Well, when I am replacing this keyword "rhamnolipid" with other keyword like "polymer", or not using % operator in the start (for both title and abstract) then it is working fine. The error message is shown only when I use % operator in front as well as in last of "rhamnolipid". Do anyone know why this error message is shown for this case only?

Thanks in advance.
Regards,
Pardeep


EPO / PATSTAT Support
Posts: 425
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Error message: Query Killed

Post by EPO / PATSTAT Support » Mon Apr 23, 2018 1:50 pm

Hello Pardeep,
from a computational point of view, using wildcards alongside words in title and abstract is a nightmare for the back end servers. When a query takes too much time to execute, then it is automatically aborted. That is what is happening here.
Searching for '%rhamnolipid% takes longer to execute then '% rhamnolipid % .
And to be more comprehensive, one would probably even use a LEFT JOIN to include application that do not have a title or an abstract. The query below should execute correctly.

Code: Select all

SELECT tls211_pat_publn.appln_id, publn_auth, publn_nr, publn_kind, publn_date, appln_title, appln_abstract
 FROM tls211_pat_publn
LEFT JOIN tls202_appln_title ON tls211_pat_publn.appln_id = tls202_appln_title.appln_id
LEFT JOIN tls203_appln_abstr ON tls211_pat_publn.appln_id = tls203_appln_abstr.appln_id
 WHERE publn_date >= '1980-01-01'
 AND (appln_title LIKE '% rhamnolipid %' OR appln_abstract LIKE '% rhamnolipid %')
We are looking into the possibility to "full text index" the title and abstracts fields so that functions such as "CONTAINS" can be used instead of LIKE.
The query below would run -very fast- on a PATSTAT MS SQL platform with full text indexes on title and abstract.

Code: Select all

SELECT distinct tls211_pat_publn.appln_id, publn_auth, publn_nr,publn_kind ,publn_date, appln_title, appln_abstract
 FROM tls211_pat_publn
 LEFT JOIN tls202_appln_title ON tls211_pat_publn.appln_id = tls202_appln_title.appln_id
 LEFT JOIN tls203_appln_abstr ON tls211_pat_publn.appln_id = tls203_appln_abstr.appln_id
 WHERE publn_date >= '1980-01-01'
 AND contains (appln_title, '"rhamnolipid*"' )

 union

 SELECT distinct tls211_pat_publn.appln_id, publn_auth, publn_nr,publn_kind ,publn_date, appln_title, appln_abstract
 FROM tls211_pat_publn
 LEFT JOIN tls202_appln_title ON tls211_pat_publn.appln_id = tls202_appln_title.appln_id
 LEFT JOIN tls203_appln_abstr ON tls211_pat_publn.appln_id = tls203_appln_abstr.appln_id
 WHERE publn_date >= '1980-01-01'
 AND (contains (appln_abstract, '"rhamnolipid*"' ))
Result:
RHAMNOLIPID.xlsx
(170.75 KiB) Downloaded 399 times
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply