Hi experts,
i'm using a SQLLITE for my ANDROID APP
I have a slow performance if i USE the view but only when i use it from
ANDROID
the scenario is the following:
I have a table defined as
CREATE TABLE ArMpCol (
Id Integer PRIMARY KEY,
Id_Ar int,
Id_ArMpCol int,
CDAR char(20) Collate RTRIM,
CdTessuti char(20) Collate RTRIM,
CdStagioni char(6) Collate RTRIM,
CdLinee char(6) Collate RTRIM,
CdCart char(6) Collate RTRIM,
CdColori char Collate RTRIM,
Sequenza smallint,
Radiato bit);
this table have about 70000 rows
i also have a view on this table:
CREATE VIEW V_Ar_Elenco_Box AS
SELECT
ab.id_arbox As id_arbox,
ab.cdstagioni As cdstagioni,
ab.cdlinee As cdlinee,
ab.cdbox As cdbox,
ab.nColori As ncolori,
ab.radiato As radiato,
ab.QTotRiga As totalepz,
ab.cdar As cdar,
ab.cdarmp As cdtessuti,
ab.cdcart As cdcart,
ab.cdcolori As cdcolori,
C.Descrizion As colori_descrizione
from arbox as AB
inner join Colori As C ON C.CdColori = Ab.CdColori
where
ab.ncolori = 1
union
select
ab.id_arbox As id_arbox,
ab.cdstagioni As cdstagioni,
ab.cdlinee As cdlinee,
ab.cdbox As cdbox,
ab.nColori As ncolori,
ab.radiato As radiato,
SUM(ab.QTotRiga) As totalepz,
ab.cdar As cdar,
ab.cdarmp As cdtessuti,
ab.cdcart As cdcart,
' - ' As cdcolori,
'ASSORTITO' As colori_descrizione
from arbox as AB
where
ab.ncolori > 1
GROUP BY
ab.id_arbox ,
ab.cdstagioni ,
ab.cdlinee ,
ab.cdbox ,
ab.nColori ,
ab.radiato ,
ab.cdar ,
ab.cdarmp ,
ab.cdcart
order by
Ab.Cdstagioni,
Ab.Cdar,
Ab.CdArmp,
Ab.CdBox,
Ab.CdColori;
the problem is:
if i do from my pc this query:
SELECT *
FROM V_Ar_Elenco_Box
WHERE CdStagioni = 'AI12' and CdAr = 'TC20A' and CdLinee = 'PAS'
the result is
3 rows in 29 ms
now i copy this db on my Android device and run the some query on some Db
from android
the result is
3 rows in 1890 ms !!!!
now on Android i try to use the query without VIEW ....
SELECT
ab.id_arbox As id_arbox,
ab.cdstagioni As cdstagioni,
ab.cdlinee As cdlinee,
ab.cdbox As cdbox,
ab.nColori As ncolori,
ab.radiato As radiato,
ab.QTotRiga As totalepz,
ab.cdar As cdar,
ab.cdarmp As cdtessuti,
ab.cdcart As cdcart,
ab.cdcolori As cdcolori,
C.Descrizion As colori_descrizione
from arbox as AB
inner join Colori As C ON C.CdColori = Ab.CdColori
where
ab.ncolori = 1 AND
ab.cdstagioni = 'AI12' AND ab.cdar 'TC20A' and CdLinee = 'PAS'
union
select
ab.id_arbox As id_arbox,
ab.cdstagioni As cdstagioni,
ab.cdlinee As cdlinee,
ab.cdbox As cdbox,
ab.nColori As ncolori,
ab.radiato As radiato,
SUM(ab.QTotRiga) As totalepz,
ab.cdar As cdar,
ab.cdarmp As cdtessuti,
ab.cdcart As cdcart,
' - ' As cdcolori,
'ASSORTITO' As colori_descrizione
from arbox as AB
where
ab.ncolori > 1 AND
ab.cdstagioni = 'AI12' AND ab.cdar 'TC20A' and CdLinee = 'PAS'
GROUP BY
ab.id_arbox ,
ab.cdstagioni ,
ab.cdlinee ,
ab.cdbox ,
ab.nColori ,
ab.radiato ,
ab.cdar ,
ab.cdarmp ,
ab.cdcart
order by
Ab.Cdstagioni,
Ab.Cdar,
Ab.CdArmp,
Ab.CdBox,
Ab.CdColori;
the result on android device is
3 rows in 31 ms !!!!
is possible that in android device, the view are not well supported or i
have do some error ?
can someone help me please?
thank's
Gianni
--
View this message in context:
http://sqlite.1065341.n5.nabble.com/LOW-performance-with-VIEW-and-ANDROID-tp68517.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
