View Issue Details

IDProjectCategoryView StatusLast Update
0007758VALENTINA SERVERDatabasepublic2016-09-22 11:39
ReporterAleksandar SpasojevicAssigned ToIvan Smahin 
PriorityurgentSeveritymajorReproducibilityalways
Status assignedResolutionopen 
Product Version 
Target Version9.3.xFixed in Version 
Summary0007758: Big query takes too much time to execute and other users cannot use database while query is selecting records
DescriptionI 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
TagsNo tags attached.

Activities

Aleksandar Spasojevic

Aleksandar Spasojevic

2016-09-10 09:22

reporter  

query.txt (441 bytes)
Ruslan Zasukhin

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

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

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

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.

Issue History

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