Re: Dealing with large datasets
Russ Tyndall <[email protected]> Mon, 8 Feb 2016 10:22:44 -0500
| Newsgroups | gmane.lisp.clsql.general |
|---|---|
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format. --===============1743691672657629502== Content-Type: multipart/alternative; boundary="------------030504060009020705040404" This is a multi-part message in MIME format. --------------030504060009020705040404 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: quoted-printable X-MIME-Autoconverted: from 8bit to quoted-printable by boa.kpe.io id u18FWwt8017300 Howdy, I have been developing on and with CLSQL for quite a while. My work flows tend not to do too much with do-query / loop extensions.=20 map-query has the ability to accumulate the results or not, so may=20 consume memory for each row. These are supported through=20 database-store-next-row which is only supported on the ODBC backend=20 currently. The postgresql-socket3 backend uses cl-postgres, which has=20 its own cursoring / looping construct (but doesnt work with do-query). Its also more generally true: every backend has different support and=20 capabilities for most things in CLSQL land. Each backend is in its own=20 state, and often developed by multiple and different people at different=20 times. So if you mentioned more about which backends you were looking=20 at I might be able to provide more direct answers Nesting commands in sql shouldnt be a problem, but I have never tried=20 this with nested do-query stuff. Obviously database deadlocks *can* be=20 an issue. You might need to open a separate connection for the inner=20 query, but I am not sure. Cheers, hope this helps, Russ Tyndall Programmer Acceleration.net On 02/06/2016 10:07 AM, Blake McBride wrote: > Thanks! That is helpful and encouraging. It would be great to get an=20 > official word on this regarding all of the mentioned constructs. But=20 > it also leads to my second question having to do with nested queries. =20 > It goes as follows: > > In C, Java, C#, Python, etc. there is not an issue because they all=20 > have cursors. I don't see cursors on CLSQL. > > You write a utility function that encapsulates some needed=20 > functionality. It gets called when needed. What it actually does is,=20 > rightfully, abstracted. Actually, what it does is perform a select=20 > against a database, do some calculations on the records, and return=20 > the result. > > Now, let's say you have another function. I'll call this one an=20 > application function. This function does a select on the database,=20 > spins through the records and calls the utility function for each of=20 > its records. So, you have a nested select. Obviously, the utility=20 > function spin should not interfere with the application function=20 > spin. Normally, this is not a problem when cursors are used, but I=20 > see no cursors in CLSQL. Is this an issue with CLSQL? > > Thanks! > > Blake McBride > > > > On Sat, Feb 6, 2016 at 4:07 AM, <jhyiugjhvbjh234-JGs/[email protected]=20 > <mailto:jhyiugjhvbjh234-JGs/[email protected]>> wrote: > > >The CLSQL documentation documents DO-QUERY, LOOP, and MAP-QUERY bu= t doesn't > >make clear what is going on inside. Are _all_ the records > obtained first > >as one big list and than mapped over, or is it more intelligent > obtaining > >manageable sized blocks at a time, or even dealing one record at > a time and > >let the driver buffer it? > > > >This is a really big issue. I appreciate clarification. > > I am not a CLSQL developer, but I use CLSQL in production. > > I have skimmed the do-query code some time ago, and I did compare > performance of do-query and clsql:select. > > do-query never loads the entire dataset in the Lisp memory, it only > keeps the current piece there. > > do-query seems to request rows one-by-one from the driver, but it i= s > possible that the driver code does some buffering =E2=80=94 I have = never > checked. > > > > > > > _______________________________________________ > CLSQL mailing list > CLSQL-2NDrxpH/[email protected] > http://lists.kpe.io/cgi-bin/mailman/listinfo/clsql --------------030504060009020705040404 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-MIME-Autoconverted: from 8bit to quoted-printable by boa.kpe.io id u18FWwt8017300 <html> <head> <meta content=3D"text/html; charset=3Dutf-8" http-equiv=3D"Content-Ty= pe"> </head> <body bgcolor=3D"#FFFFFF" text=3D"#000000"> Howdy, I have been developing on and with CLSQL for quite a while.<br= > <br> My work flows tend not to do too much with do-query / loop extensions. map-query has the ability to accumulate the results or not, so may consume memory for each row.=C2=A0 These are supported through database-store-next-row which is only supported on the ODBC backend currently. The postgresql-socket3 backend uses cl-postgres, which has its own cursoring / looping construct (but doesnt work with do-query).<br> <br> Its also more generally true: every backend has different support and capabilities for most things in CLSQL land.=C2=A0 Each backend is= in its own state, and often developed by multiple and different people at different times.=C2=A0 So if you mentioned more about which backen= ds you were looking at I might be able to provide more direct answers<br= > <br> Nesting commands in sql shouldnt be a problem, but I have never tried this with nested do-query stuff.=C2=A0 Obviously database deadl= ocks *can* be an issue.=C2=A0 You might need to open a separate connection= for the inner query, but I am not sure. <br> <br> Cheers, hope this helps,<br> Russ Tyndall<br> Programmer<br> Acceleration.net<br> <br> <br> <br> <div class=3D"moz-cite-prefix">On 02/06/2016 10:07 AM, Blake McBride wrote:<br> </div> <blockquote cite=3D"mid:CABwHSOsdo8DPircxM4zgVdCqzUNG1hkPhPvjNudYrtEV7E=3D10Q-Rq2MYuBUFGs@public.gmane.org= ail.com" type=3D"cite"> <div dir=3D"ltr">Thanks!=C2=A0 That is helpful and encouraging.=C2=A0= It would be great to get an official word on this regarding all of the mentioned constructs.=C2=A0 But it also leads to my second questi= on having to do with nested queries.=C2=A0 It goes as follows: <div><br> </div> <div> <div>=C2=A0In C, Java, C#, Python, etc. there is not an issue because they all have cursors.=C2=A0 I don't see cursors on CLSQL.=C2=A0</div> <div><br> </div> <div>You write a utility function that encapsulates some needed functionality.=C2=A0 It gets called when needed.=C2=A0= What it actually does is, rightfully, abstracted.=C2=A0 Actually, wha= t it does is perform a select against a database, do some calculations on the records, and return the result.</div> <div><br> </div> <div>Now, let's say you have another function.=C2=A0 I'll call = this one an application function.=C2=A0 This function does a selec= t on the database, spins through the records and calls the utility function for each of its records.=C2=A0 So, you have = a nested select.=C2=A0 Obviously, the utility function spin sho= uld not interfere with the application function spin.=C2=A0 Norma= lly, this is not a problem when cursors are used, but I see no cursors in CLSQL.=C2=A0 Is this an issue with CLSQL?</div> </div> <div><br> </div> <div>Thanks!</div> <div><br> </div> <div>Blake McBride</div> <div><br> </div> <div><br> </div> <div class=3D"gmail_extra"><br> <div class=3D"gmail_quote">On Sat, Feb 6, 2016 at 4:07 AM, <spa= n dir=3D"ltr"><<a moz-do-not-send=3D"true" href=3D"mailto:jhyiugjhvbjh234-JGs/[email protected]" target=3D"_blank"= >jhyiugjhvbjh234-JGs/[email protected]</a>></span> wrote:<br> <blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class=3D"">>The CLSQL documentation documents DO-QUERY= , LOOP, and MAP-QUERY but doesn't<br> >make clear what is going on inside.=C2=A0 Are _all_ t= he records obtained first<br> >as one big list and than mapped over, or is it more intelligent obtaining<br> >manageable sized blocks at a time, or even dealing one record at a time and<br> >let the driver buffer it?<br> ><br> >This is a really big issue.=C2=A0 I appreciate clarification.<br> <br> </span>I am not a CLSQL developer, but I use CLSQL in production.<br> <br> I have skimmed the do-query code some time ago, and I did compare<br> performance of do-query and clsql:select.<br> <br> do-query never loads the entire dataset in the Lisp memory, it only<br> keeps the current piece there.<br> <br> do-query seems to request rows one-by-one from the driver, but it is<br> possible that the driver code does some buffering =E2=80=94= I have never<br> checked.<br> <br> <br> <br> </blockquote> </div> <br> </div> </div> <br> <fieldset class=3D"mimeAttachmentHeader"></fieldset> <br> <pre wrap=3D"">_______________________________________________ CLSQL mailing list <a class=3D"moz-txt-link-abbreviated" href=3D"mailto:CLSQL-2NDrxpH/[email protected]">= CLSQL-2NDrxpH/[email protected]</a> <a class=3D"moz-txt-link-freetext" href=3D"http://lists.kpe.io/cgi-bin/ma= ilman/listinfo/clsql">http://lists.kpe.io/cgi-bin/mailman/listinfo/clsql<= /a> </pre> </blockquote> <br> </body> </html> --------------030504060009020705040404-- --===============1743691672657629502== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Disposition: inline Content-Transfer-Encoding: base64 X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX18KQ0xTUUwgbWFp bGluZyBsaXN0CkNMU1FMQGxpc3RzLmtwZS5pbwpodHRwOi8vbGlzdHMua3BlLmlvL2NnaS1iaW4v bWFpbG1hbi9saXN0aW5mby9jbHNxbAo= --===============1743691672657629502==--