0007758: Big query takes too much time to execute and other users cannot use database while query is selecting records

ID	Project	Category	View Status	Date Submitted	Last Update

0007758	VALENTINA SERVER	Database	public	2016-09-10 09:22	2016-09-22 11:39

Reporter	Aleksandar Spasojevic	Assigned To	Ivan Smahin
Priority	urgent	Severity	major	Reproducibility	always
Status	assigned	Resolution	open
Target Version	12.3.x

Summary	0007758: Big query takes too much time to execute and other users cannot use database while query is selecting records
Description	I have one big problem with using Valentina DB. I'm beggineer and I'm working in Real Studio. We have table with about 50 000 records, and we have search for whole table, including finding one word in selected field. When we start query, it is going through all records and selects what we want. But, that takes about 15 seconds and DB is unusable before it finishes... In Valentina Studio, Connection usage drops from for example 14 to 0 and stays like that as long as query is working. Also, during selection, HEALTH in Server Admin shows minimum value. This attached query takes more than a minute to execute directly from Valentina Studio. Can you help me with that? ALSO, I see that maximum size of upload is 500k, so I'm giving you link to our DB, and our query is attached here. http://www.megafileupload.com/ohO5/Dovecher_DB.zip
Tags	No tags attached.

Aleksandar Spasojevic 2016-09-10 09:22 reporter	query.txt (441 bytes)

Ruslan Zasukhin 2016-09-15 10:16 administrator ~0009237	status: We was able found and fix bug for this OR query. Speed jump from 27 to 9 sec then we did some optimization and to 6 sec, now we decide to make LIKE to work without field collator, and add ILIKE which will respect collator, this make again faster. We still working on this in VKERNEL.

Ruslan Zasukhin 2016-09-21 19:57 administrator ~0009243	Hi, I wonder if you have see on our forum msg about 6.6 build which speed up this query a lots. http://www.valentina-db.com/download/beta/night_build/

Aleksandar Spasojevic 2016-09-22 10:13 reporter ~0009245	Hello, we installed server from night build, and got from 1.485 minutes to 14 seconds for attached query. Its much better, but still other users cannot use db while query is selecting... Thank you very much for this build, it is much better. Also, can you send us new query if you successed in speeding up more. Thank you, Aleksandar

Ruslan Zasukhin 2016-09-22 11:39 administrator ~0009246	Hi Alexander, yes for now we made the first step to speed up this query itself. and we have todo yet 2 steps in the same direction to make it about x2 times faster even more... After that we going work in vserver more parallel when a long query works.

Date Modified	Username	Field	Change
2016-09-10 09:22	Aleksandar Spasojevic	New Issue
2016-09-10 09:22	Aleksandar Spasojevic	File Added: query.txt
2016-09-12 05:17	Ruslan Zasukhin	Assigned To	=> Ivan Smahin
2016-09-12 05:17	Ruslan Zasukhin	Status	new => assigned
2016-09-12 05:17	Ruslan Zasukhin	Target Version	=> 7.0
2016-09-15 10:16	Ruslan Zasukhin	Note Added: 0009237
2016-09-21 19:57	Ruslan Zasukhin	Note Added: 0009243
2016-09-22 10:13	Aleksandar Spasojevic	Note Added: 0009245
2016-09-22 11:39	Ruslan Zasukhin	Note Added: 0009246