dotConnect for PostgreSQL: Huge difference in favour of DbRawSqlQuery vs Select with OrderBy/Skip/Take

[costeakai]

Hi,
dotConnect for PostgreSQL works perfectly with:

Code: Select all

string esql=string.Format(@"select r.gid from pro r order by r.gid offset {0} limit {1} ",skip,take);
DbRawSqlQuery<int> nearM = __dbx.Database.SqlQuery<int>(esql);
List<int> looo = new List<int>();
if (nearM.Any()) looo = nearM.ToList();

versus:

Code: Select all

var lst11 = (from pro in __dbx.Pros select pro.Gid).OrderBy(p=>p).Skip(skip).Take(take).ToList();

The latter is much,much slower , especially in a loop, where you skip 70 times , among 1 million records.

[Shalex]

Please enable the dbMonitor tool and specify the exact SQL statement generated by LINQ query.

[costeakai]

for the fast one:

Code: Select all

Opened connection at 04-Aug-17 08:54:02 -07:00

select r.gid from pro r order by r.gid offset 0 limit 150 


-- Executing at 04-Aug-17 08:54:02 -07:00

-- Completed in 16 ms with result: PgSqlDataReader



Closed connection at 04-Aug-17 08:54:02 -07:00

Opened connection at 04-Aug-17 08:54:02 -07:00

select r.gid from pro r order by r.gid offset 0 limit 150 


-- Executing at 04-Aug-17 08:54:02 -07:00

-- Completed in 0 ms with result: PgSqlDataReader



Closed connection at 04-Aug-17 08:54:02 -07:00

Opened connection at 04-Aug-17 08:54:04 -07:00

SELECT 
Extent1.fid_perdel,
Extent1.fid_lot2_2,
Extent1.stare,
Extent1.data_crear,
Extent1.op_creare,
Extent1.data_actua,
Extent1.op_actliz,
Extent1.tip_obiect,
Extent1.judet,
Extent1.nume_com,
Extent1.comuna,
Extent1.ident_bloc,
Extent1.fol_pr_trn,
Extent1.fol_sec_tr,
Extent1.observatii,
Extent1.par_agr_nb,
Extent1.data_zbor,
Extent1.nr_polig,
Extent1.supraf_gis,
Extent1.perimetru,
Extent1.toleranta,
Extent1.versiunea,
Extent1.fid_comune,
Extent1.nume_com_1,
Extent1.fid_soluri,
Extent1.area,
Extent1.perimeter,
Extent1.romania_st,
Extent1.romania__1,
Extent1.area_ha,
Extent1.cod_u_gen,
Extent1.u_gen,
Extent1.descr_u_ge,
Extent1.cod_ea,
Extent1.supr_afect,
Extent1.color_ea,
Extent1.cod_ev,
Extent1.supr_afe_1,
Extent1.color_ev,
Extent1.cod_s,
Extent1.intensitat,
Extent1.color_s,
Extent1.cod_a,
Extent1.intensit_1,
Extent1.color_a,
Extent1.cod_g,
Extent1.intens_g,
Extent1.color_g,
Extent1.cod_w,
Extent1.intens_w,
Extent1.color_w,
Extent1.cod_tip_so,
Extent1.descr_tip_,
Extent1.shadeset,
Extent1.color_tip,
Extent1.cod_clasa_,
Extent1.descr_clas,
Extent1.cod_text,
Extent1.descr_text,
Extent1.color_text,
Extent1.cod_sch,
Extent1.desc_sch,
Extent1.color_sch,
Extent1.zona_relie,
Extent1.zona,
Extent1.nr_solutie,
Extent1.solutie,
Extent1.shape_leng,
Extent1.shape_area,
ST_AsEWKT(Extent1.geom) AS geom,
Extent1.supk_01,
Extent1.gid,
Extent1._virgin,
Extent1._atrbmod,
Extent1._added,
Extent1._deleted,
Extent1._geommod
FROM public.pro AS Extent1
WHERE Extent1.gid = (CAST(:p__linq__0 AS int))
LIMIT 1 


-- p__linq__0: '1824756' (Type = Int32, IsNullable = false)

-- Executing at 04-Aug-17 08:54:04 -07:00

-- Completed in 13 ms with result: d



Closed connection at 04-Aug-17 08:54:04 -07:00

	part:0     pkt:0  x:650,630.87   y:362,922.43
Opened connection at 04-Aug-17 08:54:05 -07:00

SELECT 
Extent1.fid_perdel,
Extent1.fid_lot2_2,
Extent1.stare,
Extent1.data_crear,
Extent1.op_creare,
Extent1.data_actua,
Extent1.op_actliz,
Extent1.tip_obiect,
Extent1.judet,
Extent1.nume_com,
Extent1.comuna,
Extent1.ident_bloc,
Extent1.fol_pr_trn,
Extent1.fol_sec_tr,
Extent1.observatii,
Extent1.par_agr_nb,
Extent1.data_zbor,
Extent1.nr_polig,
Extent1.supraf_gis,
Extent1.perimetru,
Extent1.toleranta,
Extent1.versiunea,
Extent1.fid_comune,
Extent1.nume_com_1,
Extent1.fid_soluri,
Extent1.area,
Extent1.perimeter,
Extent1.romania_st,
Extent1.romania__1,
Extent1.area_ha,
Extent1.cod_u_gen,
Extent1.u_gen,
Extent1.descr_u_ge,
Extent1.cod_ea,
Extent1.supr_afect,
Extent1.color_ea,
Extent1.cod_ev,
Extent1.supr_afe_1,
Extent1.color_ev,
Extent1.cod_s,
Extent1.intensitat,
Extent1.color_s,
Extent1.cod_a,
Extent1.intensit_1,
Extent1.color_a,
Extent1.cod_g,
Extent1.intens_g,
Extent1.color_g,
Extent1.cod_w,
Extent1.intens_w,
Extent1.color_w,
Extent1.cod_tip_so,
Extent1.descr_tip_,
Extent1.shadeset,
Extent1.color_tip,
Extent1.cod_clasa_,
Extent1.descr_clas,
Extent1.cod_text,
Extent1.descr_text,
Extent1.color_text,
Extent1.cod_sch,
Extent1.desc_sch,
Extent1.color_sch,
Extent1.zona_relie,
Extent1.zona,
Extent1.nr_solutie,
Extent1.solutie,
Extent1.shape_leng,
Extent1.shape_area,
ST_AsEWKT(Extent1.geom) AS geom,
Extent1.supk_01,
Extent1.gid,
Extent1._virgin,
Extent1._atrbmod,
Extent1._added,
Extent1._deleted,
Extent1._geommod
FROM public.pro AS Extent1
WHERE Extent1.gid = (CAST(:p__linq__0 AS int))
LIMIT 1 


-- p__linq__0: '1824757' (Type = Int32, IsNullable = false)

-- Executing at 04-Aug-17 08:54:05 -07:00

-- Completed in 0 ms with result: d



Closed connection at 04-Aug-17 08:54:05 -07:00

for the slow one:

Code: Select all

Opened connection at 04-Aug-17 08:47:41 -07:00

SELECT 
Extent1.gid
FROM public.pro AS Extent1
ORDER BY Extent1.gid ASC
LIMIT 150 OFFSET 0 


-- Executing at 04-Aug-17 08:47:41 -07:00

-- Completed in 22 ms with result: d



Closed connection at 04-Aug-17 08:47:41 -07:00

Opened connection at 04-Aug-17 08:47:59 -07:00

SELECT 
Extent1.fid_perdel,
Extent1.fid_lot2_2,
Extent1.stare,
Extent1.data_crear,
Extent1.op_creare,
Extent1.data_actua,
Extent1.op_actliz,
Extent1.tip_obiect,
Extent1.judet,
Extent1.nume_com,
Extent1.comuna,
Extent1.ident_bloc,
Extent1.fol_pr_trn,
Extent1.fol_sec_tr,
Extent1.observatii,
Extent1.par_agr_nb,
Extent1.data_zbor,
Extent1.nr_polig,
Extent1.supraf_gis,
Extent1.perimetru,
Extent1.toleranta,
Extent1.versiunea,
Extent1.fid_comune,
Extent1.nume_com_1,
Extent1.fid_soluri,
Extent1.area,
Extent1.perimeter,
Extent1.romania_st,
Extent1.romania__1,
Extent1.area_ha,
Extent1.cod_u_gen,
Extent1.u_gen,
Extent1.descr_u_ge,
Extent1.cod_ea,
Extent1.supr_afect,
Extent1.color_ea,
Extent1.cod_ev,
Extent1.supr_afe_1,
Extent1.color_ev,
Extent1.cod_s,
Extent1.intensitat,
Extent1.color_s,
Extent1.cod_a,
Extent1.intensit_1,
Extent1.color_a,
Extent1.cod_g,
Extent1.intens_g,
Extent1.color_g,
Extent1.cod_w,
Extent1.intens_w,
Extent1.color_w,
Extent1.cod_tip_so,
Extent1.descr_tip_,
Extent1.shadeset,
Extent1.color_tip,
Extent1.cod_clasa_,
Extent1.descr_clas,
Extent1.cod_text,
Extent1.descr_text,
Extent1.color_text,
Extent1.cod_sch,
Extent1.desc_sch,
Extent1.color_sch,
Extent1.zona_relie,
Extent1.zona,
Extent1.nr_solutie,
Extent1.solutie,
Extent1.shape_leng,
Extent1.shape_area,
ST_AsEWKT(Extent1.geom) AS geom,
Extent1.supk_01,
Extent1.gid,
Extent1._virgin,
Extent1._atrbmod,
Extent1._added,
Extent1._deleted,
Extent1._geommod
FROM public.pro AS Extent1
WHERE Extent1.gid = (CAST(:p__linq__0 AS int))
LIMIT 1 


-- p__linq__0: '1824756' (Type = Int32, IsNullable = false)

-- Executing at 04-Aug-17 08:47:59 -07:00

-- Completed in 18 ms with result: d



Closed connection at 04-Aug-17 08:47:59 -07:00

	part:0     pkt:0  x:650,630.87   y:362,922.43
Opened connection at 04-Aug-17 08:48:03 -07:00

SELECT 
Extent1.fid_perdel,
Extent1.fid_lot2_2,
Extent1.stare,
Extent1.data_crear,
Extent1.op_creare,
Extent1.data_actua,
Extent1.op_actliz,
Extent1.tip_obiect,
Extent1.judet,
Extent1.nume_com,
Extent1.comuna,
Extent1.ident_bloc,
Extent1.fol_pr_trn,
Extent1.fol_sec_tr,
Extent1.observatii,
Extent1.par_agr_nb,
Extent1.data_zbor,
Extent1.nr_polig,
Extent1.supraf_gis,
Extent1.perimetru,
Extent1.toleranta,
Extent1.versiunea,
Extent1.fid_comune,
Extent1.nume_com_1,
Extent1.fid_soluri,
Extent1.area,
Extent1.perimeter,
Extent1.romania_st,
Extent1.romania__1,
Extent1.area_ha,
Extent1.cod_u_gen,
Extent1.u_gen,
Extent1.descr_u_ge,
Extent1.cod_ea,
Extent1.supr_afect,
Extent1.color_ea,
Extent1.cod_ev,
Extent1.supr_afe_1,
Extent1.color_ev,
Extent1.cod_s,
Extent1.intensitat,
Extent1.color_s,
Extent1.cod_a,
Extent1.intensit_1,
Extent1.color_a,
Extent1.cod_g,
Extent1.intens_g,
Extent1.color_g,
Extent1.cod_w,
Extent1.intens_w,
Extent1.color_w,
Extent1.cod_tip_so,
Extent1.descr_tip_,
Extent1.shadeset,
Extent1.color_tip,
Extent1.cod_clasa_,
Extent1.descr_clas,
Extent1.cod_text,
Extent1.descr_text,
Extent1.color_text,
Extent1.cod_sch,
Extent1.desc_sch,
Extent1.color_sch,
Extent1.zona_relie,
Extent1.zona,
Extent1.nr_solutie,
Extent1.solutie,
Extent1.shape_leng,
Extent1.shape_area,
ST_AsEWKT(Extent1.geom) AS geom,
Extent1.supk_01,
Extent1.gid,
Extent1._virgin,
Extent1._atrbmod,
Extent1._added,
Extent1._deleted,
Extent1._geommod
FROM public.pro AS Extent1
WHERE Extent1.gid = (CAST(:p__linq__0 AS int))
LIMIT 1 


-- p__linq__0: '1824757' (Type = Int32, IsNullable = false)

-- Executing at 04-Aug-17 08:48:03 -07:00

-- Completed in 0 ms with result: d

[costeakai]

latest news: now it seems that the slow method , has suddenly turned out to be the fastest.
I am regenerating the data in the tables, and verify again and again...

After regenerating the table and indexes,
the slow method takes between 45-47 seconds to complete the loop,
and the fast one takes 59-61 seconds;

i would say , now, that :

Code: Select all

(from pro in __dbx.Pros select pro.Gid).OrderBy(p=>p).Skip(skip).Take(take).ToList();

is faster than

Code: Select all

string esql=string.Format(@"select r.gid from pro r order by r.gid offset {0} limit {1} ",skip,take);
 DbRawSqlQuery<int> nearM = __dbx.Database.SqlQuery<int>(esql);
 List<int> looo = new List<int>();
 if (nearM.Any()) looo = nearM.ToList();

[Shalex]

The results say that behavior of PostgreSQL Server is not stable and depends on unknown reasons. We do not know how to improve provider for this case.

[costeakai]

your conclusion coincides perfectly with mine.
Thank you.
I will keep trying until i find a solid solution, with minimal potential surprizes.