quadratic behavior of sequential table read

Using SQLite with BlackBox: http://zinnamturm.eu/downloadsOS.htm#SQLite
Post Reply
Josef Templ
Posts: 262
Joined: Tue Sep 17, 2013 6:50 am

quadratic behavior of sequential table read

Post by Josef Templ »

I noticed that the SQLiteDriver module uses a local buffer for a result set.
This is fine in principle. However, the buffer is organized as a linked list and
for every value read it searches sequentially from the start of the list
resulting in O(N) steps where N is the number of rows in the table.
Sequentially reading the complete table results in O(N^2 * cols) behavior.

I think, it would be simple to fix that behavior by introducing an array instead of the linked list.

In addition, ResultSet.StringValue seems to be unused and can be removed.

As a minor detail:
There is also some Log output created that should only be created when SqlDB.debug or
some driver specific debug flag is set.

For an improved version see standard encoded file below.

- Josef

StdCoder.Decode ..,, ..M74...3QwdONl9RhOO9vRbf9b8R7fJHPNGomCrlAyIhgs,CbKBhZ
xi2,CoruKu4qouqm8rtuGfa4.hOO9vRb1Y66wb8RTfQ9vQRtIdvPZHWKqtCa.E.U5UZDA6.5Qw
dONlnayKmKKqCLLCJuGqayKm6F9vQ5nsH3.bnayKmKa2,Cor.kay4.qorGqmQCU2,CJuyKtQC9
8P9PP7ONbXmb.2.oqTl5ELz3,6.,U,IkIU00.bnUGLu8ro8quGrmCLWKqt.2.w26.I16.M.,.J
FA0z.U.2m,.j.1cUZj0E.s76.,UO.,E.12,Ue.E.07s95U0IkmL,6.g56.I16.z50.J,U.2GEj
k.K.Q.6.sR,T20pd8U0CyHZiYReoBgh3inpZHFf.k,U.EH4.2.bb.6.3s,GshXmzy,mQTiU.K.
yweWTeb,iQTiUmh.uMpTG2z.uPRiUtSUzcH9vR,NP99RFvP796ZON19NNuPRvN,dNTfQ,7Rn9Q
996dOM39P999,7RT96ZPN19N,NOR9R9vN9fQb96TfN,7C,dMn9R9vQ,6oPPgHj16gpt04z,qXD
pgahgm3Yo3hZ3YkxhnRidJgdZhdZit3YoxhUwhkhgi3Yo3BGqkGrk8qkCrm0mouKE8rm4ahwhi
Zht3YhxhYd9uxXuIdr.qFRiUjL.8LRuq3b.CxQiUGS.uurWO4P.auQiU7R.,EJ2.1606.P01si
,U.Y.AU0.,U.YjyC.VuIEWKKvCpmmqmCKuyanpZHhgg,GomCrlIS2.,UIB.iU.cC1.0k.6.5gw
9U.2U.E1l5EzE.0.QC2U0Ky8.,UoU.UU6.0U.4k.4.,UKhgmZgVphV7sNQ.4.072U.U2Lh,Mg5
T.8Mtn.,6..i.a,2.36.2k,.,U.4.,.wz0Qkmj.6..T.H20.2D2U000E.6B6.666.0M.1M.2.O
3..072U.U21.6..i.a,2.3.,Q.E.6U,E..O.yjX5yibxerx,2zzzzzD.6T.IyAU.U,W9.6.,k,
0.,Q.E.6U,E..1c.0U.I,AUN.h1m,0EZUAU.27IU,,JT.0E,C00k.0.3ML6.R280,6.7.36F6.
16.IUgU1IkmL,6.wH6.I16.zD.6.I3l.1.V20E0E,C.oe.k.S0AU.E,a0CE0E,G.Ck1E,q0Y.I
U2U1.3.C.IU9U1.a.0EnUV,f.1c96.7.1c7EGE08Mtf.2.m00.e,2.AU.E.e06.2UwK.606.h.
9cUZj06.,U40,UO.,6f6.c8.2Uw0W.0kbE0O.Q5gU5U.Aj,E0O.Y1gU6U.w7Y.kRk00,0kbE0.
L09c1kbE0.D0BcUZj0E.sb6.,UO.,6f6.c8.2UE0u.Yp0E14,oY0k.y.IU.6.3s4c,7.CK.6.t
.B.0k66.3stE4k.i.21oUCU.o0IUXD.o0AUUU.AU.E,O,Ok00.y7SE,9T3E.0.bc.0.,EBU.wT
2U.EJ.2U.YLE10.3M60.j.B616.d51622U.28oU7.hN.AUEU.g7o.6.ZD.A.2U.25AUV.j..9P
.A.6.3X.AUT2.0k9E1.RP..,6.Z9.AUMV.w0M1kt4U.A.2U.oh.E1i.0kG1.1622U.280E1u.g
IA.6.51BM06.T11s66.HQ..0kaE1U.g4IUy.p,BM06.h7.IUx.rN.0UP.j.B606.J.1656.56.
AUe2U.6.V.1c.k,UAU.A1oU4.h.16E2U.Q.Y.6.l,B606.h.1cF6.D016E6.5.iG.6.F0B.0ET
1.Bc06.711MNk,k.m10k,Ug3.,6.tO.AU12.Yxzzzzv.6.d.1cBE3U.UwpLU000.Wv.s0sc1U.
6.16.0k,9D2U.E1l5EzE.0.Qi.E.86E.6B6.666.0M.1M.2.Opm.0CUE0,6.6lMgcUU..s0M4E
.IU.E.5.2.0M.2.kz9k,,0.k5EtF.0.2r.E.86E.6B6.666..cJ.U.E.076.6l..0.SW.s0M4E
.I.2k,.,U.4.,.s0M.322U...,,,UkF.0.h0.U.2GE6E.G,yjAxhbpZHZimBhixgcwYPRilZhd
ZiZ3YZ3jZRgR3YbQZnZiVZiZhhZphoBZvYdjxgiYdiRbUU.6T..0.q6i.a,0E.Q.2k,.,U.4.,
.sGM.5.WzzzDN.1cwzzzzT.1U.AUezzzzTUqzzzzTUozzzzTUyzzzzTUo13.1cyzzzzL2E.C.0
E.Ey..2.Ip,6.L.fQ.6.,M.,U100E.cVs1cT6.,.C50E,,0.d,,.,,,E.1M.1U.Ef..1.V20E.
EWlM,E..Q,A16.8.0s.U.E.1U..yj102.UDUYX..W90E,,,6.Y42.222..o8.E.6.V22.YM..,
.D,L.n.0U0U.C.6.2k.6..L.1cUU.2..666.2C0E.cJ..2UE0e.Y,wTN0b0UIxhHZimhfUwYfM
8r76NuPDf9NW.U.YD..,.fO.Q,A16.C.0s.U.E.1U..o9AUh6.QSAUl6.o3c56.VO,AUIU.2HY
.kJU58.0k,k.C.ITM.2U,Yzzzzz9,6.l1B606.x,1cH6.5.420kx4.1c3,.h8.oU56.0kk6.1c
K1E.o2Y.6.p.1s5Ep0.40,6.P2B606.xE.6A2.5.1s46.k.a00kFUh.j,840E,k.q6Y,gU32.g
w.k.01,kZUO6.0Epk.S,0EH8.0E.cKvh.0pd8.XzIocjZhYRiiocjZhYZcZRiX,6.wc4E4ENO.
2.k,,,6..T.dc,E.ExU002.S00E.c4E.k.0.eWMU,UE0,sc,U,IEU...p.0.4E.EJ.,U.2GkPE
.8t,M.j7.MvszFd8HM1H6IZuH5OF7OJZOF,tEZPN19R9vCPM0hOEZ86bfC,tIX8HRtIdfQr767
fMp76bOIYcWRbUAhY3Yu2YAxdCxc7pdIRbUIiZRiu2Y7pdIhc5hcGRbUAiphgmBju2Y,JeGBcN
3Yl2ak2YDpcUQe6xdGZe13d,JevgV7Ic3xc7pdBAV7IiZRiUIbx2YHBeAtHVPNWGFGKRmJmKKv
mJl8KiGrmCLuuGl8Km8KFmGEGKlmGECpcmYHBeABdIhcTxdEhcCxfGhc,ZcLJe7Ze33Yf2YHBe
A,.sEZOF18J989,dHH8HHtCPM0HMEbuI9eId887fM,t6,dHH8HUlhiZJC,dCv763tMZPN66dPM
39P0mr8KpKqlGrtWmoGKEaKrYZUohVhhZ3YqBgmRgcBgm3ZlIasAZdQbWQbBAV7ICb1C3KIwKa
cYgWZ3XPR9Xg2YCFK0Gba2kIUlNN22IYdphnhgmZiUAhitP,tP3fOEEOrkmquKqtWmMmGESGna
KqKqHamR8mREtQCsI..Ug.N,.H,4bZFEEF....ENmGESGuKqtGrHamR80ICb1C3..69EK.kIUl
NN22IYX,.QioJidlj4KuGLtWmoGKEEK.,,UpAamAZg2YqBggNN,dR.UdQbW.ZntUH,..m0Y3.A
3MQK4,,3NO.sPCLu85.,dR19PQCX,RPMPPND79,t7Xd9d9Sdv7HtCUmtQkd...N,m0.a0AC9XU
UW.....9XmUkBgo3hbYZUwYXJbQxYdQ5EtQCsI..Ug.N,.H,aKmenS00YdVRioBdY3ZYN8rN1H
Eayqnu00Gay4HePd98b8GTWcAhYBZdQbBA,Z1.CIqyqtKKIGKlam4akWuIW0mV8rmkRgV728.Q
eZZhZlRqk2O3.QeotC.6FcQ9vQ.UlNNI5UUgalIaUwd432kVcIcE...T0..d1R,.Q8QeFZ7.I8
G2H0...50dWU.kU.66.ksgA00SmtKKqK4,d8,dNZvPP96TfMUvwYvgV7AVmtQkdkW.MQEK2emx
hXhgnRig2YCB7N,a0AC9XUUb..ktGbd,sCDtCPUmtQkd...N,.EKUd..U1,.kdKKqkRIZdU.2U
O,Etr.2.UAUF.,k.U.G..C,yzayIWKJaKIECpcAhohg2JidpiZJivgVBAVcIZegV72imxhehgX
Zi7gbUIYNxhpJiU2emxheVWgV7QipJgkJij,H.bOR39Q.3VjJibBgiBhuBgoBhjph7gbUIYWgV
7QgjphoJidJAyKtCr2qHE80Orm8rtaqruq2kdartGrmqqL8pt8rlymU8qrKLu8m4akl2itJidx
gcZi7gbU...UgBhXhgiRiZBVx2YWYcjRgpxZ0JchYddRgZVWgV72ipJikxhnhg7g58059O1fPD
PNbP0v76koCrtKrmCr2q1F9Rd9Qpt9TtRjvRRtQX9PHXiwhmxgjYgVZiVZit3iZRai2hohhghV
7gcVRgc3YqBA2YnZijNN796HfP,NMR96bOIEEG41fMEIyKt0mq4KraKsKLq4KuKKm0GlaLEGLo
K4.ggixgdphZBZU2hVRiUwhihgUwha3Yo3BOqrmKqyqvaKrSKECLuyaVxgZ3YXZhVRinhgnJbB
AViAVCheAZdi2YI3hZ3YqBggVdRiUAgUodJZdA3YqBAoZBAViMGR8J90R76d8O.00sQHvNRPN7
96YiZxAmGECLuyaZ,,NAN76Z79,tAN76d79,dBN76TfQ,7C,dMn9RCLEGqm0rmuKmaKrSKEyKr
0GuW4PPMDfPH9Rf9N996TfNUq,RVicI9OENe9,7JF1EE.B9PTPMdPORXkxhdpho3YqBAYZUQio
,4qt0mkuKEWnK8qwGrm0mYKoWKIEOKq..ohphhWhgmpZBAVi6J98Kde9,7J.2266dPNl9R,tQd
fQHXg2YnZij,fvQHfPDHuWqmYA.KaXxhYBhixAFNJdeFP7CN76f8JBmMOHVKIEyKt0meGJXkKm
oWaGLqk2cENuH3e9,7JcR.Elmqr8KEyKn0Gm4KuYZUQCUZ3jVRANPS,NMb96H9R,tR1vQ,NOR9
Qf9RRd6PM0PM0Z7AX7CPNAVN9VtA,dGR76dONP9QNfC,tQ9PQfPNR9RHPMN96Kqk8rlWKEaKro
eVZhp78H76ZPNV9P1vM99N,dM0mk8Lt4qw0mkCqlKqt2YrBho3hUQgjphnZiVpho3YoBhhhgig
,Jd8HN1PM03M1PM0HOHVuHZ8JPM0HsInuIdOFP89,7F19R9vQN767OO19PTvNN76BOONPNb99W
GJmorSKKemI0md43YddJgg2YH,CJu8rosQN76FuPb9R,dCv76.FuPb9RN76bOQN9F389,tIXXm
BhqVg2YHZimBBQbBA,Fd8,dHT9R9fC,7FT96RvPd96fvQ996b8R79HTvN,dM9vM1PRbPN,tRFP
NR963eE,tMNvPQig2Y0JcUMMN9Pb96bOIEWcRoZ2JidpCQcghgVphp3iUAgiZgUAgo3Yo3hVZi
UMO2Yo3Bmqr2YdRiUohjlkOrkaKq4Klm41fPnPPTfQ9f9,dE9vM11yKnYC1HK0mY0muCrmYdjx
AuqtGrm4Km0mrOKECJuG4uGEemIqk4AVcc.38Vl7V98Vx7V3c8HN1HsETeHb8JPM0H680mnKKr
KKt4Kq0mm8LtyKt0mlyKmKqt0GJam4ak2CqruKrKqlGroyKrCrWWrlKqmGqmGKEqHEGnRqk2ak
rKLuyInGpk84b96v76ftCPM0uqrGrWWrmCquGrk8Kq2Yx2YqQbBAV7QgVphixBT8Q9fP7eE,ND
,tBrN1Hkv8rruqnaImKKrGroOqoCqkGroyaUgbU2bvgVBAV723Grk848rm4aUggm,.J,CqroiZ
JiohgY324nRqk2YimhiiRgVZiZZAIavgV7sPhPNZfNNvPj96v76btCPM0aKrCqrqKs4KuaKlmq
mg5dVBEIsQdPMdPN00sMoBcPDnS0mMUX,ggY32.99S9vMAhi,b,CKqyqtYgUg5dV7M1HM0ZuPj
vFZvPf9QbOOpPN,ND,tAVtC,78J76dvPdPMN96bPOpPNy4ZuPjvFEEuqrGqm0moCblIas2YWBj
otQk4YeN3e3hV7AVHZimdP00VuHHeHdOFZ86duH,NEZeIaJEyIX0mVWoU.b8OTfQdvIdfQ....
Q8sEUBEf4KqKbUgbU28.EVCJe8pUCIe0GdKoVyIdGIEKIbGoRqk46J99SOpkm4..Ie3R668hOM
NXdgV7AV7Qgj7R9fPdfC,tIdfQM1HM09eHi1IcdphVJitpeVVU....kIk2.EcyoY.MEZ0EVaJe
Ko4aU3,MGR9RhOMNXU..cI..H,H..HeHw6qk2akW.Y7RvNhOMNPR00.Z0..A3A,.Y7UBA,90Ed
KqkmKf4Kq66.K2..kIk2.EdKoUmo4ak2.cITHEq1.kU.cJ11rN1P.w6kS0Gc.UG,qk2ak2uqmW
LueHE8prS5sCPM0aEtyqvCLR0mU8JdEdyqv.b0yIXIejtCPM0K2U1xhg3Yx2YGhc1,6Rn9QeHE
aIbsFQbBA,HcP1PP9fC,7IT0830mU.TW1VB..ZONbPRN9RbONd96v76V0.83.CagRiu2YEx7.A
6.whgRbU23moWuIICqrmqtaGEqHEmoWuYmxhrRiioiVZB9vQH76JN8PM0aEnaKtCLumGEmqkCb
u2YGtRD0i1QAffPdX7V5VU2Ze6RUihC3XUwhadQTvR0GJa0YhVRioRcjhii,.Uihih,.Ahi3Yg
Bgn76JN8PU3,6J1fMN103cI66Z022bOQNHtUnpZIBgWlIqk2AVmVg2YXxhghihphnJbUA7w6g,
234KmGqoGroyaVZhUIiZRipltKKu0GtKKq4KuYA7PMdPM,d8H,CagdC.83A642.UAVXlta0Cqr
mquEEemIUYBgoBgu2YE,..cISrR0GIe06N19RaGEqHE8rrSbUcN,dQT1x76V79,NNNvQ9967PM
dHEqHEuoYmIEemIkW.YcmBB2Yx6I.66Z0..Cb2JidpCam4aU7cMp7643Y7uGW8qRq.8rtKKueH
E8pmCrusIQbBA,W0d9O9ntAA996hHtaqk8KqKKEa4R1b9RZnlGLqcP9vM9vQbPMZPSNloGLEaq
t0muCrmGKEWqm8rm0Guyan3hjxiUYicllyKrCqm0LuKrkm4dPMQCJ,QioBgodC,FbsF66H68J7
65vPRfPMORHKQAR1Kag2YZ3jZ,UgxBc8H,W01XdZCUYBA1fM1nmIiZZBYAEEe0sQFvPjPFZfQp
763uHT8H9OERO1H.UKBcGh,SKW8roO5PdC,7FZPOsC6I.ZW3JimRgjZgZJZU2ZYJbUQelZBUn7
FoCaGR0mYu2k4I6.HeF,7N,ND,dH6JFOFR86796pVbZcmBB2Y37FA,ZOFdOJZeH,tIX0U3JimR
A2ZY3Z2JidVdoZYJgdg,9WUgcmJCsCP...PvQDf8,7871.QC.HdC03cIkU.sEFWvk2OpU2YnBh
u79,tOp76H0D0M0KJueHE03cI.sKffPdPMDvN99Nv86TeFC3..p76VuH..22Q6U0,.g5.Uu.UU
.sQfGEenSQ8.UhFIGKIG2M8R,r,aKxKKEenS2av2YL3d7Zd33YnheoRfnBhuhgR3YX2Yk2fUYc
D3Y7pd13ZnBhuN8,NFR8F0mYu2Uvg,uoWSJICLK0mta4Uf3Yug5.sO,7D,tQHXUYcD3YnRffhf
UIbxsQfWfhfv2Y7p7iqI0mWu2PUG,QiBAV3p7.in4qk2.U13hVpBIZU23Eq.C5UdI5HeHw6.U7
.q1.ERsN.,,.UH,U13hVpB2ZY33Udo3g,kVWqkUvgVB..mokCLuaIrcQdfITvRH9NJ76F7N.Un
,M8pFay2sCPEV..q1.ER.EE.a...6H1vQ..F7NF7FUdo3.YdVRC.UvgVB..SomGrUKLuyqlyqq
qqoGLJ0GI..C5UdI53uHUvgV7..MD,,.I5.22...U5hgoB6.HHIGKI.aGL.kXKKu.UvgVB..Op
km4duPb8RaKr2Ycoiu2YKBAQbUwdJZeUQCZfC,NEZ0.66hOEZ86ZPNbfCa2kIi1O3mqmuKVmqr
8KR0mU.d7A,tH.q..SpYGJY0GveHEGpmWLu.,7FTO1HM0HMGYd3FvumlyKrGbi77H76xND,7H9
WnZimBZUYe6h6PM0ak2CJuUnpZ33joJiVRgo3ZqpZX,Y3V79,7H9eH.P76X79,tQdXv2Ymhgn3
YuEu8ruu4M1HU3ZdHhcBAV7M0b9RZ96pND,dRRtM.7tC,dQ.5vPOrmUBU3p7PM0mLEI5HePdfJ
.UH,MGRHeyqdGbdFIOLLC4EKQio,....NuPR1..kt..W0......cI9PMN1..sQ8pm4KqGprC36
8......U0BhiBA...Cb7,.F7H9eHO5.H79,7P9fPkIin4kt66c638HTeE,78bPOpHR0GF0mJ0G
qKKrUUQZUIYdIYvgV7A,ZPNb1C4.MFkWuYUoeVVI,HXvgVB...oeVJiHZimBB0WUoiucJkR.cC
0pbkW83.w7UdQ5o8.cC.G1.i130kf.Y8o866U7o76RZ99,7H90.YYds8,NAHtC,tQdfQx86pND
O5.qk2aEyU7,..Ug6BVN8rN1sIUn,.2Zq,EKUdgV7AVw,N0..R069.sIUn,.23..a0.Z0h0.o7
Ug.UH,CbGNMG3Uc..kI.cE...UnNG.EIm2.kI.kI.R069OHMamR0mtcLEF..Ug,2280PM0HMFg
,9WU.G3UdlREIcI9vQUo3YeA3PEc.83FdQbfC,dI.GrI0mYuqoGLEWmImGEuoWSpRqU0,cHcQb
f9BPOZvQamR0GtCLLmqk66pVmRiioAsC,ltQgjhC0GRqXkEt6PsE.M1HMFMGRPOin46I.Z0..d
X,ZgY3YcIijxiu2YGxhrBZg2YC,aEfEtya5,p76ZuPS2Q582U7FtCLL.Ux2YGxBUHVUYe6FEuo
WSJIwhr,amRq.HcQbf9N1RdP99Sd96pND8rr.iHE85EE...sEUuEMM09Wvk285EL8rrQiPJinp
ZgV1,v86pND85rlYu2ZvQC4AZv2Y7,YB.gV7g60mUGKmiXB68YeVJAIZd..Z0dfC,7J11H76dO
MLPNZONb1YiU2ZmRC8pm.AZg22Q5o8cC,dITnXUv6N19RmWmxhrRiu6IUGVU.w78ZrR55vPffP
mWdJbUAdCNFDWv..df9ZvPCLEenSsQC4QbBA,dnlyKqK5Ibx6HcQbf9YhntCPM0.0GRqXm,QbB
kY6RRdQT1xVUYe6l4M0R07PMd1df9Z1HtC8LEe1cNUUQgjVUUkQbBA,HsJFOGNWm3YX2YmRCN1
,7FTO1M0BuHZ86H96pND2YIxdUIejxi5,b0qGE2Y2xdUYgVlhCqrKLrs8,NOv86pND8LL8rrQi
PBhR3Y3p7A,aEt0GRq1uqmWbv2Y7VXxhpFKIejxi5,kIqk2AV3lRU4,EMEE8rt.500mK0mM..k
J.ICZXn,M1aUAFEGqk66pNDuoYgV7Eb.dHm4Ku44YgVl4akW66dOMLPN.sC.VvPb9Rp76ZPNK5
,NDu2,NOB96ROJNWeA3H6I.ZGE..hOM66FdQTHK0mlyKqER0mYu2M8p76hOM69u2cE.42EtyaU
obx2YkYZUIakAZvg,42.YbUYCEKIalAZvg,.QgjZB2YygbU.ZN8...t76dnlyapltmGE8nNamR
q.Z06RR7N1XPJijxig.MLPM090h0in46I...IeZBgYBdiZiZxA0GI8rrY3.p,Uv2YDheIdR1HR
0mY.amREfI8KaucJ11AV0,cQ91YiioeVZhp78ZvPj1UdQbBM0HWmhA,t6,dH6J.HsJH0ZPNbfC
aIro8667uHPU7AVqBgg3Yug5ZPNbf95vPRnmsC,7RRdQ9vQ,dCQAgA.HMFNWBU7oCUkQbUYC.a
KrCqrqaV,qk2aU3p7PU3Z70GIeGEuoeEv44IZdk2oCUk..C4.EbkW66ZONUZVvg,...A3AANuP
2YcIij,Uu.kR.cCm2kIin4cJcQI5h0i130Et.G5gCF,.A3AV7,0mF..akf.I5NuPo8KbUY6M0H
cR22ICu0.UndCQA.k2mbmhAplYcJ.U7oCUmFL...C4.kWm2P.HcR.V,.ABkk.P.9eHqU7gcAFE
.cRUe.O5EM..51.cHMF22.in4...kIkkIeZBgg3YcIij,Uu...eXGh6amREfEtcCO3Q582Um,Y
CMRW0.M8M0H02YX..AVL,cC8ZVdJEEG2AV7oCUmFL..QCpVX,.akWm2P.HcR.V,.ABkk.P.9eH
qU7gcAFE..c8Uq,24.kl..u2K2,,71NvCPM1W01vQbPRPPN,7NggUAhi3YbAfNBfNhZBhdhYc2
xYUIZdEc...a04KmGokGrm2Zm,.p,..I57OMdntuGW4KuAZvg,O385pFfUv2YYJbUY6QCkR0mm
8LteX7,M1.cQ.d1Kbc..a0A7EEC0.k2S3Uu6J99S.,7Fk2akd4Jaa4b0SqtQeo,duP7OMdPNFd
Q9vQR,EKYgg2YZJCHtCk2a29fQZ96v76V76W2,dR19P,,7vC,7RsQe151UUgcARe37RRdQ.Hnl
kk.,NFR0HM098Hg,AVodQ91..g,gcCN1gcAFEW0.gCe0aUYpZYBgt3Yug5VVYpZhxhiZic3Yug
5UthgVJiUI5,VBUq,YA.kl.M1cHMF22.in4..Groq4HfP,FYWoKqoaqmdCpH0GJa0V0.G5UdUV
7JHPP9HI85.I5.22.p,QCdOOPnIin4cJcQI5h0iXoBhhdCGok.sC,NNI5.g,.ICUodJgCF,.A3
MGIC0mF.k4M0j0ERYeZ,667uHM0HsI.Un,6JGpoqqmWGtKai.69Gro69,NNA3k2a29XU.EEEEU
dlRYiiktI5C4.,,YiiICUdtMMMUU.ak2KIaM1M0d1..UBU3p7PU3Z70GIe0.K5J,HEuaqqoZcx
hpJiUI50XUYidVhBhihiohgUIbx.UnhgXxhiZgUI5M1cR.d1r,.C4.P.R09WU.UBg,.klKLt8r
muqlaLEaKr0mtGLtEEO4P961vQ4aYhgXBhhBggdNH9S99N,7QTPO2YihC3Xg2YZ3jVhhkZhZ3Y
bgZlIanYaigaqwYUIZdEc.f0.M8MMCou8Lt.FdQ.ER.EE.I5aqkELCou85M8r,h0ZXucJkRQgu
2Y2NM..r769Xu.UB.Um,Yiio8KbcsP.M8kYcQ2YX.UBk2S3Uu6J91EEG2AV7Q8.kt.Y85ORZfQ
91FdQ9HL.Y3599,NNZnI.HMGgA00.,,,,5vC,7RRVndCsP.0mWUodQ.Hnlkk.,,AV7gcAl4k2G
LL...g,gcCN1gcAFEW0.gCe0aUXpZqBA,,VFECKLCrl4KqKKEe1g,oCUXR5.kr.g,o7g600.91
rN1PEc...a04aHZimVU23.ER.EEUnFRA6.C2ZO8EfEtcCO3fnRqk282Um,YCh0W0.M8M0HWmFE
C0.q.HcJ11dmd.FdQ9vQN76b9RmWopZmtQHN1HM098H..EE.HsQ22IYWQbUYiiUUI5C4.Ebg,6
6.kR....MMOpk8rdMO22.Uu...eXHBig,QCb8Ra4HtCPUKB6ER.i130Et.G5EI..H,HMGB00mF
..aEf44GZK,EIEKUdg,KIa..UU.aEbkt694nIi1o9c6qU7gcCtC6RsQe151.K2,,.a4r,..El.
4a0ZhjJAFdQ.ER..6P9HR00.UYBA1fCw7gcGVUA6.82amREf42cC.r76HfC,,S2qU7AVcElmqr
QiUAgmhgUohjZCpPNZvP,7R9fQPPORPMdPN796J,3OF.Z1pVo,23.kIUYBA196cHmoR0Guu0b1
a4MMUvgV7MGUUQ266.akf.I53OO.6670ak2a2c9.0mF0Gb..HEqKKr0GR6HkmC5.HtCPM0A,HW
ghgi3Yy2Yk.U7EbEmYCNFqM8r,a.B0Uk22TGaKIb..2Yh226671rOOv86pVm,.rOOv869Wv.HU
o,e151.ak2aU3l4A,HMF.m2P.H6R.AB.k4kWu2gcAFE..c8M0m4e1VtC,7R.QA.UCV3FE.sCPM
1...A3AAROMPPN,785vPER.EEUnFREq..HXdU0,ME..,dDvVgEMQ5akU..Y5QAktm0X,A7GbXl
tiplyKqK5ve9RPMPXX2YC,.HcHsQY3N0df951..GmI0mJUBk2C5x0GbX,..qk2UB.,,UvM1.UC
tRb8RZPO2ZnJ510U1,pld45.kosCak2OZGtQdHR..ko.30Eb.m0sQHN8r76cLpND,tQ7tCPUG7
JktGbBAV3p7uYr,UBAVE,Uc.84.dOSVPN,7851I5..EREq..HXdU0,ME..,dD.U7A6..t,MRQC
.Qc,Re33Yo,b1kiYit3CyIXqk2aEyQeF,NWiQeFZ7KY7pdI,pltGbUUChA.Ft7bOINOG.kHam4
ak2m5..EXmob4IeeHECLu....UbAZBA,...GpWWJeeHEC5....wYdg,...IcAxd0JbUQC....s
7HN1gcAVndQU7ZdBUCN1EE83UvgVB68ohjZicBhixgUYij3YXZhZFEKLs0GoK4PM0V0g86RElU
1ZhZBAfnRqU0,68QgghA66ZPNK5bHuYgVFEe0238rmmqm4qtKKE.Un3iZRgdpgdRgUIiZtPffQ
5PNb96J,9058P91i1JN8PM1W07eQEEemIqk428.6NpFW85AZUIeZlw0GIaGR0GV.i130.CKoKq
liKEOqr22qKsmaoBhj76HfN,NMbPSRvMFfQTfPTPRb96T9QAgoBBcP,7QZvPDfQ9nt0GJM0HcI
.F7NRtQdPMdPN,t6,tMNvPM8PM0EdUvgVB6I...MFR9NT8Q9HEWmbKJeIiZdCaIb.H,W0VeQ9f
C,7N1nk8qk665vPuaXNOTXrBgnFraKuaqkGrmGaW76T8Q9n28HMqk2aEmuGdKqkGacAZ7Ial2Y
eA382U,,Ygi.9nSQA.RHKIaksCME.IeZ,mGE8nMk223GqmmqoOrm8LE8rmCru66THE4qt..7PM
44.UjFtKqsKrmCLu0GJk2W0ZHuKLtuKE8rmCLEqXkMOBnr0rmuKEaqt0mtaLrC4.J,GanZCKau
kl.Kav2YmhgndC2aBMFMFR9NkRqk4...kIQecxhrh60GIaIb0mlyKrGbsdC4Id.0069,dHk282
U7p67f9b9O.d8G90aEWaqkUH3hjNHbvNFt7bGaa4702Yb2Yf2YXdPEG0mJ0mH0mm8bjdC,t7,V
H,m2K2PXcYgiYgWBZYAZBM09WB.,tIFvPK2rN1...kIIcZxgdph33jZ76FNGRmtGrkMP9fPeX,
,.QbUYgVNMpldYBUndENvPM01vQnfPmGE2hj,pFVy2sC,tHcQI5.U7UV,F1kmWrmgCkoCbj7RH
vPRPMmGE4KEGKtaKv66PPMn96NPNDPMN9Pn9699Skm0mtaLr.6PnnoOKE442YdRCdeIfOF,d8H
,FVEJCpltG591003d6HcAVN1ohj3YjZichgm3YZ3jZVdFEC5Z9RAVmAaU.UjRioFt66v,tNDx7
699S.2hVRCb9R110mku4jPON9P,dM9965vPP1KKmYhVZiZJiUwidZCKIrgcshAPM0HcT1vQnfP
gby2YYpZG,2YeA3o8Ahg6PYZUQggsMQigcP38PTHRA7Uvg,HM0dPMH9PN76b99,tQRPMPHRQeF
,kdGLtiXmxhrJ5ZuPjvCuqkq4YCuavM0HktghoJbUQeF,.P9Rr76RPRP1p76NuHUvM0HEuKKwG
LRYeZFfYB9vC8qouqkcC8ooUK,AhiZiuMGR1Ugxhixgu2YAVKluEtKqkmKR0GdKagp8UWZhjVH
B8.8IqyavEV.A6UnZiVZCPPN22698HM661uIbOF6NCLuEEq1uKr.NFN416N2BEEenSQicxBsCA
VckSGsOGMOGMTGMOGsOGsRGMMGsSGMTGMEe0A74atVI3d3,H68YimBjUYijtQdHt2YVRitFo.0
mmku.TfNCLuYCP1emIkWsCc.3M1HEbEmuGtCrmGrIiHEGKL85umYuqo2ZdQbBkt0GRsIUH,CbI
xhHZCFtQdPM.NFqKad66Fd8yF05J09I01I0nGE8nQqGManK4HMmGEGH0vXlYkkYkmYksYkvYkU
Ia2qb2ya2Ka24c24a2Cd2ia2ib24b2ia24YoYkvYksYkxYk9ZkUAc2Kc2GTGMRGMQGME8H01mM
GMMAb2Kc24a2ic2aZU2b2Ca2qb2ORdfNl76JN8c.EtER.6H6IZPNVPMZPNF7NR7NmGECLKYhZ7
9CLuqag2YoBgdZhdQbBkYcQMD,tIX0UHB8kbiIEGJY.H.VPMbvQ,dMNXUYgVNM,NOR967PMIAs
QVPN.jPMn96JN8M0RfEN1pVlQ5AVL376N1XUQYUo70GWy2AVmVukd.EVaKrGKVmqr2ZnZihViJ
6YZUQeNReINHRNE7eIF7N1XiYgVNMxO8N7671R7P9fPNFbaYd.akYuoVWGr8IqM8U7YgVFEe17
PMoZihgsN1Hk2K2Um,Q8.Qeohgk3ZnZihN8rN1MGB0g5.UF,GobuoW0GeW2k2W01fPn9RFPORH
EEmyKESro66HeHbOFZ8JtOJV8F18J986b9R1nmUU.gcANGB86Z1...8pbSJEGJY.aEI6QZnlEE
CpWmoWCIe0mtGrk..AVXxB0GR..CormqusQFtQdPP.aEb.UXVgsMQidQ5M0B0596pVk2YIllyK
q2Yh2YlUBU76NRdQb1khgfiYitNN,dCQ8.UpFeaLsKKICLuqagsMHtCP.HEm..cP1XUIbx.sI.
CJu8LeyqVWqk8rtWmd4JaEa.Eb4qq2ZnZCUdgV7k2KIb.akf.ZPNb1.kd.U2xdBU7cHcQSLK00
CrI.HEX.6A.22.M0Q6KYHB8.C2fXI,.2YDpcBU7UH,UH,A7K2en4ak2A,ohphhWFEe1b0.50Ad
iZicQio,Q5M0MG2ZBBdC3Z7pdI,H76tND,dPfPP3Xdc7,78RPRP1,7Dv76POEl88HeHd0AZUYe
6,A,cHMOR9RHtC,NOoZXxhiFr2YuU6x7WGrK5AZvg,A,cQTnhCqi0GRMOGr4ak2A,gcANFP.H.
u2mqruadQbUYhjFLCqr.ErK5Q5HU7.Z1.NvPRn4ak2A,gcCN1aU7EyMI.Q8U4,pN1HM0a.u28r
m4adQbUICoZXxB.QeF,kV.B8PTXcQio,.a.85.8bVN1HM0a.t1.Q8UI7Ken4a.H.b1C3.C26J9
9SWmt..a.u2GrmWbdQbUYiZ3DR,..Q8.G5.CrIi1A,IC.YiZl4k26T.m2C3EVERqk2M06Puauk
d.kV.3OSdntWmtG5.M0MGYBR96x76VV6,A,cHcMHfP1nwamRq.a.o7.oZXxhi,NFqM8q.a.Igg
xhW32...m4F,.k2U4Vd,VVD7P9HEqGE410Gla4.khERcMNXPBhR3Y3,M0Et.UdpBg,Hk2kW.a.
98H.HU3p7k2g6i1A,7HtU,ZgY3ZmxBHtCP.HEtK4pVH,UHZC.H76Fd8uqmWLu0moGrm8rkGbjF
Jg,AV3,M0102ZmhgnND.m2A8EWEK0mM0HMam4a.98H.H6NCJoy42ZbQiohgkxYdg,gcCN1gcA,
YA.6QZPNV1DN8rN1cQcCohjNFUV7PiX7Ed.PM0HMFRmREt.C3.OoouqkmqoermWmtG5iHEW07n
tIijBjU2imhgkVY3YnZiV,sQdPPuGEaoa0pbMER8Jp763PN4qv4KtKKEGLo4aU6OHvQ,tM1PRb
PN4quGrrCqrUg2YpphghgnRiUAjjhiU2imdRHvPfvQN1bvQfPN7961963dE9uFHGF0mtGrk.I3
68gd2Kc2qb24Y5ZkFZkz2YeAZBkYOYmVx6A27k2GanZC2YukmW5AhixgBA,98HU7ICI5uqr.AA
g,KIbg,9WUIcZxARHwsCPM1..6FUd2Y3,22hOEZGueHECpscQktYeVVv2YD,Tnt.sQNFtKqte1
.i1H682emdCKKwMRaqrsRsQIC9XnhiXRgZRCBPRN9PnnvaKucE9Xi,HcAVN1YAZWYV7.EIkrYC
85,ND66tND2YcYiUQYUo72YDJeU2366v76aGEOGEQgj,0mS00e0o82hu2YIBgWVBcEk4ME.7nt
.vlmW5fXi79M8A6K2cI.NFN.H6NC5cCQA.7vCPUo3YuEbYdvsMT1cC2avcQT1p,MNbHRUBkY68
7HtEECGEkIo2G4c9BPOZXUQYU.GJY.HcH6OHtC,7ORNGRHuYgdQ5YeVRhZVpldYicYgi.Hl4M0
d1WqR0ml..RtMT9Pf1r76ZvP.ICCrRq.85EMqU7gcANG23UU.c7.o3Ux22Ee...kW0GIeWZ3Dk
rcN1PONPN796J,a.UixB.11PU3p7r,7HtEEe1RWBMF2Y3,Q5.cI.70A35uPPPPHHEWmkCql2io
J53uHUv.gAp,.EfEusIm4ktY8sC,dQTXg.ERA7UvEV.238om0mkUu2YtxhptM1fP,dPTHEaqtC
5,NM,tET1yGdyKqmKl4qliKEaKn2im,EK0GlKKnyKtKKEaIrCbmt9f8Q71T7F99P99R999,NST
XYBhY3Yixho,YAcEEFUoNP698aXltEr0moqKsmqoCqoGLE4quG5UUwijhigZgU2hVpiZ3YWhgZ
phUghVZgZ3YVpgohgm3YZBgX3hUQioBAUUwichA8YbdPEECqkmandFHfP11uGECpmKKE8Gokte
nLymv.NPOoZjVgBgixgTZimBgiRiVRgoBBuGoGrqIYUAgiZA37Od1...5vAZPNBv9DPNdvL1PR
dvP.R7OdPPoZUIZdU7lXKKu.UcM8,7J66ZXUUkQbUIe3ZeJFEKIb.HWVRgXVUYe6l4M0FVXxhh
hB0mkCapBgg7RZPMR1EEe0AVYdE9vNH12ZWQcDhdBBdIJYg2YCFKQ8GIVumtaLrCKKYgiQic,m
GE85HtCPk2a.9eP7PFl1d99,dQT1.Y3k4AV3Z7q.H68IijZhgNMMM51...YA30R1IYGxdAZd0B
c1RdWYZUo7....ICQ5a.K2Uo,..UBkWuYB.,tETPPsCP..Z0.M8YBkRA,h0ZXu...PUc6HTHLY
CHXcw2m20Gm8bqFECKq.DN8r76N0NWeAZBA,FFECKq.,7N1nk8qkEm44,d8.8rmm4.84QC.85T
XXtQ.Z1sIUAV1ZhjFIo3amREIAd43YmtQ,t60HEG30GmumdW468CKqKqkkH2Y3VeAZBEmQCggU
I5CKqy47vCPUb,cCo7q.Eq.rl423,NMbPSCKo8bi,0rm8rkG5AByaoVVVV7N.PnwknkwwhkdP,
tQnfP.YB,FnAA66HvQGJdEJEI6IC585q1,7DsMTfPko66F1Agm,1fPwidVW765nq0LqYC7HqEt
0mvYCK2TmmgV7IiZ,.7965,cT1XiFH0GI8rm.HlSuHEoZG,226I.sHVXe2YcAhYZZU2iVRCjvP
Z9NNFm4aVRCfnlIbUA6AfUwd432sC4qta5NltW4cC82kR.7fC,tIcQktY6kR0mbEtcC.A3cJ6Q
19RFfC,NEUmgaq2YDp6.FfC,7F.GKleXH,U2FEmqmI5.iHEOKq4qnCLR0mY.g,.Ad4ltEEGJYM
1AVcEu.,tQEECqru42YmNQUoxB7PM.2YeU7g6in4MEg6ojc2ZYBgo,kS0mHenqKqqyKtaLRSmI
0GH0Wd76vFF8pW4IWypbuIaaJFamImGE8HMQbBEIMORnqKqqy4,7N396bPRV9QTfQd96J,a27P
M...eXUY82YkBA0GR6N.67PM0Y70GYyqtoZ1xhipC2eVZic3ZYBA.mGE0rkGbdg,KIb.cQ4ahc
PsQf9QV1.HWdZgU..66d8Gg,oggBgb76pVH,UH,wdEh6zeI4IWyIbmogqk2akWm2PU7......S
Z7Ze33YfsI...sE6JqU7gcC,85e1.kb0rm2ZHBeAVHVitQG32ZkBgo79,7PAZg223XaZhVFK0G
bM8r,HeF8bn3YxsIUAVH,sG.cH.MGEFyHFamRk2WKLGKl0GRqXYJgvg,H.b9OUUUntPkR0Goum
tG5996sMTfPR1HfPiX7Em0GR6OrFtER6AP.N0AV7ltWqrUUY8.H6F.b8OgdnlH6H6FUiwdkFEK
KtwBwYU.EakWMP6N3P87,gcC,YAcHmoRICUXBgipBdXZ7F3O1HkW.S4EEe1FP1HMF2YD3iZtCc
.3k4KIbGYHB86FUiIklbeZ3D5uPRHtyKqggmRiiQ87vEdfQN9F9vQ59.XDJW5.ELCoruKu.G25
9.CoruaVBBQCEt...ktu0.Y62Umb.2.YW.6.,.HE.0U..U,IklbeZFdKLqIinpZHFdKLq6F6.X
DJ.QiiIepZBG20EtD.,6.,Uf8.SE.0E.CO.0U.2.5Qw2DUnpZHldAjghg2hgnRg.AS.c9Ajg,0
EtXU.2U..602UO3U.2.,U0CS91UiAcoZimBhWlmCLWKqtCK.Y.2..EG0E.2U,2.,E.EECOh0E.
0U.AqXDU.wnjl.0.1.,6.IkmH.6..EBU.U,.J,U.2m,2.2..Oz0...
--- end of encoding ---
manumart1
Posts: 67
Joined: Tue Sep 17, 2013 6:25 am

Re: quadratic behavior of sequential table read

Post by manumart1 »

Hello, I have test these changes, and there are problems executing SQL statements that do not return any row, e.g.:

Code: Select all

db: SqlDB.Database

db.Exec("PRAGMA foreign_keys = ON")

db.Exec("BEGIN IMMEDIATE")

db.Exec("INSERT INTO ... VALUES ...");
There is a trap in method SQLiteDriver.Table.TakeResultSet because rs.cols is NIL:

Code: Select all

t.columns := LEN(rs.cols);
manumart1
Posts: 67
Joined: Tue Sep 17, 2013 6:25 am

Re: quadratic behavior of sequential table read

Post by manumart1 »

With this line:

Code: Select all

IF rs.cols = NIL THEN t.columns := 0 ELSE t.columns := LEN(rs.cols) END;
everything seems to be ok.
Josef Templ
Posts: 262
Joined: Tue Sep 17, 2013 6:50 am

Re: quadratic behavior of sequential table read

Post by Josef Templ »

> With this line: ...

Absolutely right, thanks a lot.

BTW, I think that SQLite fits nicely with the philosophy of BlackBox.
It even reminds me of a database subsystem distributed with the very first BlackBox (then called Oberon/F).
I think it was named dtF or so and it was also very compact and fast.
SQLite is impressive in size and speed, indeed, but autocommit mode must be switched off.
With autocommit off it is about a 1000 times faster than with autocommit on allowing for simple INSERTs within
10 microseconds, for example.

- Josef
manumart1
Posts: 67
Joined: Tue Sep 17, 2013 6:25 am

Re: quadratic behavior of sequential table read

Post by manumart1 »

(..) SQLite is impressive in size and speed, indeed, but autocommit mode must be switched off.
With autocommit off it is about a 1000 times faster than with autocommit on (..)
I thought it was the reverse, i.e. if autocommit mode is on (the default in SQLite) then the speed is bigger, because there are no locks to maintain in order to avoid any other to update the database.

Regards
Josef Templ
Posts: 262
Joined: Tue Sep 17, 2013 6:50 am

Re: quadratic behavior of sequential table read

Post by Josef Templ »

My measurements with autocommit OFF contain thousands of little INSERTs within
a single transaction. The overhead of committing a transaction is thereby minimized.
A commit writes back all changed data to the file system and flushes any OS side file buffers in order to
make the transaction complete. A transaction also sets and releases a file lock that is used for synchronizing
potentially parallel running transactions from other processes.
This overhead is large. In my tests it is about 10 milliseconds per transaction.

Note that with autocommit ON every single INSERT is its own transaction.
The default setting of autocommit ON is quite natural, I think. It is the same in ODBC and JDBC.
Otherwise you would start inside a transaction and the matching of BEGIN TRANSACTION/COMMIT TRANSACTION
would be lost.

With the option of using WAL (write ahead log) for SQLite it may be possible to
reduce the transaction overhead but I don't know any details about that.

- Josef
manumart1
Posts: 67
Joined: Tue Sep 17, 2013 6:25 am

Re: quadratic behavior of sequential table read

Post by manumart1 »

Thanks for the explanations.
(..) with autocommit ON every single INSERT is its own transaction. (..)
I thought that with autocommit ON, there were no commits nor transactions at all, and that the changes were simply and blindly written to the bare file, with no possibility to confirm via an explicit COMMIT.

This link is useful: https://www.sqlite.org/faq.html#q19
Josef Templ
Posts: 262
Joined: Tue Sep 17, 2013 6:50 am

Re: quadratic behavior of sequential table read

Post by Josef Templ »

manumart1 wrote: This link is useful: https://www.sqlite.org/faq.html#q19
This confirms my measurements results.

My latest findings are related to a memory leak in the driver.
It seems to allocate a string object from Windows per executed statement but never releases it.
Probably also easy to fix.

- Josef
Josef Templ
Posts: 262
Joined: Tue Sep 17, 2013 6:50 am

Re: quadratic behavior of sequential table read

Post by Josef Templ »

I have now an improved version of the driver which:
1. avoids the quadratic behavior in reading,
2. fixes the memory leak,
3. uses SQLite functions that return Unicode instead of Utf-8,
4. uses only a single dynamic object for very small strings and NULLs instead of two as required for larger strings.

In principle it would now be possible to put everything in a single module (except for the interface definitions)
but so far I left the overall module structure as it was before.

If anybody wants to test it, here is the link to my changes (in standard encoded format):
http://www.software-templ.com/temp/SQLi ... _JT_01.txt

- Josef
manumart1
Posts: 67
Joined: Tue Sep 17, 2013 6:25 am

Re: quadratic behavior of sequential table read

Post by manumart1 »

Excellent work.

All the tests I made are ok.

Thank you
Post Reply