diff options
Diffstat (limited to 'docs/performance/slow-probesetdata-sql.org')
| -rw-r--r-- | docs/performance/slow-probesetdata-sql.org | 51 |
1 files changed, 50 insertions, 1 deletions
diff --git a/docs/performance/slow-probesetdata-sql.org b/docs/performance/slow-probesetdata-sql.org index 85858d1..e1693ee 100644 --- a/docs/performance/slow-probesetdata-sql.org +++ b/docs/performance/slow-probesetdata-sql.org @@ -61,7 +61,6 @@ compression and/or ZFS. My prediction is that we can not get around this. - ** Exploration In the following steps I reduce the complex case to a simple case explaining @@ -316,3 +315,53 @@ MariaDB [db_webqtl]> select count(*) from ProbeSetData where strainid = 1 and id +----------+ 1 row in set (0.000 sec) #+END_SRC + +** Original query + +This is the original query generated by GN2 that takes 1 hour on +Penguin2 and 3 minutes on Tux01. Note it fetches all values for these 'traits' so essentially +traverses the full 53GB database table (and even larger index) for each of +them. + +#+BEGIN_SRC SQL +SELECT ProbeSet.Name,T4.value, T5.value, T6.value, T7.value, T8.value, T9.value, T10.value, T11.value, T12.value, T13.value, T14.value, T15.value, T16.value, T17.value, T18.value, T19.value, T20.value, T21.value, T22.value, T23.value, T24.value, T25.value, T26.value, T28.value, T29.value, T30.value, T31.value, T33.value, T35.value, T36.value, T37.value, T39.value, T98.value, T100.value, T103.value FROM (ProbeSet, ProbeSetXRef, ProbeSetFreeze) + left join ProbeSetData as T4 on T4.Id = ProbeSetXRef.DataId and T4.StrainId=4 + left join ProbeSetData as T5 on T5.Id = ProbeSetXRef.DataId and T5.StrainId=5 + left join ProbeSetData as T6 on T6.Id = ProbeSetXRef.DataId and T6.StrainId=6 + left join ProbeSetData as T7 on T7.Id = ProbeSetXRef.DataId and T7.StrainId=7 + left join ProbeSetData as T8 on T8.Id = ProbeSetXRef.DataId and T8.StrainId=8 + left join ProbeSetData as T9 on T9.Id = ProbeSetXRef.DataId and T9.StrainId=9 + left join ProbeSetData as T10 on T10.Id = ProbeSetXRef.DataId and T10.StrainId=10 + left join ProbeSetData as T11 on T11.Id = ProbeSetXRef.DataId and T11.StrainId=11 + left join ProbeSetData as T12 on T12.Id = ProbeSetXRef.DataId and T12.StrainId=12 + left join ProbeSetData as T13 on T13.Id = ProbeSetXRef.DataId and T13.StrainId=13 + left join ProbeSetData as T14 on T14.Id = ProbeSetXRef.DataId and T14.StrainId=14 + left join ProbeSetData as T15 on T15.Id = ProbeSetXRef.DataId and T15.StrainId=15 + left join ProbeSetData as T16 on T16.Id = ProbeSetXRef.DataId and T16.StrainId=16 + left join ProbeSetData as T17 on T17.Id = ProbeSetXRef.DataId and T17.StrainId=17 + left join ProbeSetData as T18 on T18.Id = ProbeSetXRef.DataId and T18.StrainId=18 + left join ProbeSetData as T19 on T19.Id = ProbeSetXRef.DataId and T19.StrainId=19 + left join ProbeSetData as T20 on T20.Id = ProbeSetXRef.DataId and T20.StrainId=20 + left join ProbeSetData as T21 on T21.Id = ProbeSetXRef.DataId and T21.StrainId=21 + left join ProbeSetData as T22 on T22.Id = ProbeSetXRef.DataId and T22.StrainId=22 + left join ProbeSetData as T23 on T23.Id = ProbeSetXRef.DataId and T23.StrainId=23 + left join ProbeSetData as T24 on T24.Id = ProbeSetXRef.DataId and T24.StrainId=24 + left join ProbeSetData as T25 on T25.Id = ProbeSetXRef.DataId and T25.StrainId=25 + left join ProbeSetData as T26 on T26.Id = ProbeSetXRef.DataId and T26.StrainId=26 + left join ProbeSetData as T28 on T28.Id = ProbeSetXRef.DataId and T28.StrainId=28 + left join ProbeSetData as T29 on T29.Id = ProbeSetXRef.DataId and T29.StrainId=29 + left join ProbeSetData as T30 on T30.Id = ProbeSetXRef.DataId and T30.StrainId=30 + left join ProbeSetData as T31 on T31.Id = ProbeSetXRef.DataId and T31.StrainId=31 + left join ProbeSetData as T33 on T33.Id = ProbeSetXRef.DataId and T33.StrainId=33 + left join ProbeSetData as T35 on T35.Id = ProbeSetXRef.DataId and T35.StrainId=35 + left join ProbeSetData as T36 on T36.Id = ProbeSetXRef.DataId and T36.StrainId=36 + left join ProbeSetData as T37 on T37.Id = ProbeSetXRef.DataId and T37.StrainId=37 + left join ProbeSetData as T39 on T39.Id = ProbeSetXRef.DataId and T39.StrainId=39 + left join ProbeSetData as T98 on T98.Id = ProbeSetXRef.DataId and T98.StrainId=98 + left join ProbeSetData as T100 on T100.Id = ProbeSetXRef.DataId and T100.StrainId=100 + left join ProbeSetData as T103 on T103.Id = ProbeSetXRef.DataId and T103.StrainId=103 + WHERE ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id + and ProbeSetFreeze.Name = 'UMUTAffyExon_0209_RMA' + and ProbeSet.Id = ProbeSetXRef.ProbeSetId + order by ProbeSet.Id +#+END_SRC |