HMGforum.com

Exclusive forum for HMG, a Free / Open Source xBase WIN32/64 Bits / GUI Development System
http://hmgforum.com/

Re: Help to improve clause in SQL Select

http://hmgforum.com/viewtopic.php?t=4295

Page 2 of 3

Re: Help to improve clause in SQL Select

Posted: Thu Apr 09, 2015 6:49 pm
by Amarante
Wow,
before -> 2.403 sec
now-> 0.016 Sec

Code: Select all

SELECT Propriedade.Inscricao, 
Propriedade.Titular_1, Case When Propriedade.Titular_1 = 0 Then '' Else ( select t1.nome from Titulares t1 where T1.Titular_Codigo = Propriedade.Titular_1 ) End,
Propriedade.Titular_2, Case When Propriedade.Titular_2 = 0 Then '' Else ( select t1.nome from Titulares t1 where T1.Titular_Codigo = Propriedade.Titular_2 ) End,
Propriedade.Titular_3, Case When Propriedade.Titular_1 = 0 Then '' Else ( select t1.nome from Titulares t1 where T1.Titular_Codigo = Propriedade.Titular_3 ) End
FROM Propriedade
ORDER BY Inscricao
LIMIT 0, 500

Re: Help to improve clause in SQL Select

Posted: Thu Apr 09, 2015 6:52 pm
by Rathinagiri
:) Friend, I am not a master.

I have used 'Explain query plan' command with my sql query in sqlite and it gave the following result.

This was my query.

Code: Select all

explain query plan SELECT number,
Case When n.title1 = 0 Then '' Else ( select t1.name from titles t1 where T1.titlenumber = n.title1 ) End,
Case When n.title2 = 0 Then '' Else ( select t1.name from titles t1 where T1.titlenumber = n.title2 ) End,
Case When n.title3 = 0 Then '' Else ( select t1.name from titles t1 where T1.titlenumber = n.title3 ) End
FROM name n
ORDER BY number
LIMIT 0, 500

Code: Select all

"0"	"0"	"0"	"SCAN TABLE name AS n"
"0"	"0"	"0"	"EXECUTE CORRELATED SCALAR SUBQUERY 1"
"1"	"0"	"0"	"SEARCH TABLE titles AS t1 USING INTEGER PRIMARY KEY (rowid=?)"
"0"	"0"	"0"	"EXECUTE CORRELATED SCALAR SUBQUERY 2"
"2"	"0"	"0"	"SEARCH TABLE titles AS t1 USING INTEGER PRIMARY KEY (rowid=?)"
"0"	"0"	"0"	"EXECUTE CORRELATED SCALAR SUBQUERY 3"
"3"	"0"	"0"	"SEARCH TABLE titles AS t1 USING INTEGER PRIMARY KEY (rowid=?)"
This is for your previous query.

Code: Select all

explain query plan SELECT n.number,
Case When n.Title1 = 0 Then '' Else T1.Name End,
Case When n.Title2 = 0 Then '' Else T2.Name End,
Case When n.Title3 = 0 Then '' Else T3.Name End
FROM name n, titles AS T1, Titles AS T2, Titles AS T3
WHERE n.Title1 = Case When n.title1 > 0 Then T1.titlenumber Else 0 End
and n.Title2 = Case When n.Title2 > 0 Then T2.Titlenumber Else 0 End
and n.Title3 = Case When n.Title3 > 0 Then T3.Titlenumber Else 0 End
GROUP BY n.number, n.number, n.number
ORDER BY n.number
LIMIT 0, 500

Code: Select all

"0"	"0"	"0"	"SCAN TABLE name AS n"
"0"	"1"	"1"	"SCAN TABLE titles AS T1"
"0"	"2"	"2"	"SCAN TABLE Titles AS T2"
"0"	"3"	"3"	"SCAN TABLE Titles AS T3"
"0"	"0"	"0"	"USE TEMP B-TREE FOR ORDER BY"
Even though query plan of yours looks simple, it makes a full scan of t1, t2, t3. Whereas in my query, it is only a primary key search which will be fast.

Re: Help to improve clause in SQL Select

Posted: Thu Apr 09, 2015 7:05 pm
by Amarante
Rathinagiri,
Thanks for the tip to "explain query plan" in SQLite. I did not know. :o It is very useful. :D

Re: Help to improve clause in SQL Select

Posted: Thu Apr 09, 2015 7:06 pm
by Rathinagiri
I am sure this page would be helpful to you.

http://www.sitepoint.com/using-explain- ... l-queries/

Re: Help to improve clause in SQL Select

Posted: Fri Apr 10, 2015 6:15 pm
by koke
Hola intenta esto.
Hi Try this.

select P.Inscricao, P.titular_1, P.titular_2, P.titular_3,
Case When P.titular_1 = 0 Then '' Else T1.nome END,
Case When P.titular_2 = 0 Then '' Else T2.nome END,
Case When P.titular_3 = 0 Then '' Else T3.nome END From Propriedade as P
JOIN Titulares as T1 ON P.titular_1 = T1.titular_codigo
JOIN Titulares as T2 ON P.titular_2 = T2.titular_codigo
JOIN Titulares as T3 ON P.titular_2 = T3.titular_codigo

Re: Help to improve clause in SQL Select

Posted: Fri Apr 10, 2015 6:40 pm
by Amarante
koke,
Thanks for the tip.
The problem is that in its select the records with titular_2 field with zero are not displayed.

Re: Help to improve clause in SQL Select

Posted: Fri Apr 10, 2015 7:56 pm
by koke
koke,
Thanks for the tip.
The problem is that in its select the records with titular_2 field with zero are not displayed.

try this one

select P.Inscricao, P.titular_1, P.titular_2,P.titular_3,
Case When P.titular_1 = 0 Then '' Else T1.nome END,
Case When P.titular_2 = 0 Then '' Else T2.nome END,
Case When P.titular_3 = 0 Then '' Else T3.nome END
From Propriedade as P
LEFT JOIN Titulares as T1 ON P.titular_1 = T1.titular_codigo
LEFT JOIN Titulares as T2 ON P.titular_2 = T2.titular_codigo
LEFT JOIN Titulares as T3 ON P.titular_3 = T3.titular_codigo

Re: Help to improve clause in SQL Select

Posted: Fri Apr 10, 2015 8:01 pm
by koke
On my pc it works faster with this one

select P.Inscricao, P.titular_1, P.titular_2, P.titular_3,
IF (P.titular_1 = 0,' ', T1.nome),
IF (P.titular_2 = 0,' ', T2.nome),
if (P.titular_3 = 0,' ', T3.nome)
From Propriedade as P
LEFT JOIN Titulares as T1 ON P.titular_1 = T1.titular_codigo
LEFT JOIN Titulares as T2 ON P.titular_2 = T2.titular_codigo
LEFT JOIN Titulares as T3 ON P.titular_3 = T3.titular_codigo

Re: Help to improve clause in SQL Select

Posted: Sat Apr 11, 2015 2:03 pm
by Amarante
Thank you all for the help.
I look for a query that runs on MySQL and SQLite.
Did some testing in MySQL and SQLite, and found interesting things and share them with you:

The Database
Table Propriedade -> 369 records
Table Titulares -> 1074 records

Query from Rathinagiri

Code: Select all

SELECT Propriedade.Inscricao, 
Propriedade.Titular_1, Case When Propriedade.Titular_1 = 0 Then '' Else ( select t1.nome from Titulares t1 where T1.Titular_Codigo = Propriedade.Titular_1 ) End,
Propriedade.Titular_2, Case When Propriedade.Titular_2 = 0 Then '' Else ( select t2.nome from Titulares t2 where T2.Titular_Codigo = Propriedade.Titular_2 ) End,
Propriedade.Titular_3, Case When Propriedade.Titular_1 = 0 Then '' Else ( select t3.nome from Titulares t3 where T3.Titular_Codigo = Propriedade.Titular_3 ) End
FROM Propriedade
MySQL -> 369 records :D
SQLite -> 367 records :?:

Query from Koke

Code: Select all

select P.Inscricao, 
  P.titular_1, Case When P.titular_1 = 0 Then '' Else T1.nome END,
  P.titular_2, Case When P.titular_2 = 0 Then '' Else T2.nome END,
  P.titular_3, Case When P.titular_3 = 0 Then '' Else T3.nome END
  From Propriedade as P
  JOIN Titulares as T1 ON P.titular_1 = T1.titular_codigo
  JOIN Titulares as T2 ON P.titular_2 = T2.titular_codigo
  JOIN Titulares as T3 ON P.titular_2 = T3.titular_codigo
MySQL -> 367 records :(
SQLite -> 367 records :(

Query 2 from Koke

Code: Select all

select P.Inscricao, P.titular_1, P.titular_2, P.titular_3,
IF (P.titular_1 = 0,' ', T1.nome),
IF (P.titular_2 = 0,' ', T2.nome),
if (P.titular_3 = 0,' ', T3.nome)
From Propriedade as P
LEFT JOIN Titulares as T1 ON P.titular_1 = T1.titular_codigo
LEFT JOIN Titulares as T2 ON P.titular_2 = T2.titular_codigo
LEFT JOIN Titulares as T3 ON P.titular_3 = T3.titular_codigo
MySQL -> 369 records :D
SQLite -> Error: no such function: IF :?

Re: Help to improve clause in SQL Select

Posted: Sat Apr 11, 2015 3:15 pm
by Rathinagiri
The reason might be because two records having nonzero values but not found in the second table. When you do a LEFT JOIN, it is just an addition of fields to the left table with records wherever available. If matching record is not available in the joined tables, even then those records in the first table are shown. That's why there are 2 records more in LEFT JOIN.
All times are UTC
Page 2 of 3

Powered by phpBB® Forum Software © phpBB Limited