LOW performance with VIEW and ANDROID

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

LOW performance with VIEW and ANDROID

Gianni Sassanelli
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
Reply | Threaded
Open this post in threaded view
|

Re: LOW performance with VIEW and ANDROID

James K. Lowden
On Sat, 27 Apr 2013 07:06:31 -0700 (PDT)
Gianni Sassanelli <[hidden email]> wrote:

> I have a slow performance if i USE the view but only when i use it
> from ANDROID

I don't know if it will help, but the following query should produce
the same results and might execute faster:

SELECT id_arbox,
       cdstagioni,
       cdlinee,
       cdbox,
       ncolori,      
       radiato,      
       totalepz,
       cdar,
       cdtessuti,
       cdcart,
       case Q when 1 then cdcolori
                     else '-'
              end as 'cdcolori'
       case Q when 1 then C.descrizione
                     else 'ASSORTITO'
              end as 'colori_descrizione'
FROM (
        select id_arbox        As id_arbox,
               cdstagioni      As cdstagioni,
               cdlinee         As cdlinee,
               cdbox           As cdbox,
               nColori         As ncolori,      
               radiato         As radiato,      
               SUM(QTotRiga)   As totalepz,
               cdar            As cdar,
               cdarmp          As cdtessuti,
               cdcart          As cdcart,
               MIN(cdcolori)   As cdcolori,      
               COUNT(cdcolori) As Q
        from arbox
        group by
               id_arbox,
               cdstagioni,
               cdlinee,
               cdbox,
               nColori,      
               radiato,      
               cdar,
               cdarmp,
               cdcart          
) as AB
JOIN Colori As C ON C.CdColori = AB.CdColori
ORDER BY
      AB.cdstagioni,
      AB.cdar,      
      AB.cdarmp,      
      AB.cdbox,      
      AB.cdcolori;

I cannot explain the behavior you reported.  By mentioning the main
table only once, though, you may make it easier for the query optimizer
to do its job.  

HTH.  

--jkl

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users