Re: Dealing with large datasets
Blake McBride <[email protected]> Thu, 11 Feb 2016 08:11:07 -0600
| Newsgroups | gmane.lisp.clsql.general |
|---|---|
| Message-ID | <CABwHSOu=aAXrEcTBKd3uaJUfG=D1FGrRg0KGDwuaZ3CAXAFcRA@mail.gmail.com> |
--===============0235783683246427106== Content-Type: multipart/alternative; boundary=001a1143d0e4296dbf052b7f1f6e --001a1143d0e4296dbf052b7f1f6e Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Thanks. In the interest of multi-database support, and consistent operation, I suppose my main interest is ODBC. It sounds like you are saying that CLSQL (with ODBC) can (essentially) read one record at a time into memory. That's great. I'll need to play with that. Having to get multiple connections to a database for nested queries is a show stopper. If you have a multi-threading web server, you can't be getting multiple connections on multiple threads or you'd very quickly reach the database connection limit. Support for cursors is necessary. There are many SQLDB interfaces for many languages. All of the high use, more main stream languages that support ODBC all support cursors. For example, C, C++, C#, Java, Python, and many more. Other, less use, languages that support SQL interfaces seem to miss the need for cursors. This includes Lisp and Unicon as I am currently aware. Having 35 years experience writing business applications, I can assure you, you cannot write a substantial business application without cursors. It can't be done because of the nesting problem and the database connection limits. My last large app had 9500 classes. It took multiple programmers years to write. There is just no way to coordinate nesting on a large application. Languages that don't clearly and unequivocally support cursor operations can never be considered for a significant project. Lisp is my favorite language. Now that web apps have become the dominant architecture, all a language has to support to be a viable alternative is serving pages, REST services, and good SQL access. I get the impression that CL has viable support for web and REST, but I haven't found a viable solution for SQL access. Having said all that, I am sure CLSQL is used successfully on many real applications. But, these application must have a limited number of developers on it and a significant limit on their size / complexity. Without support for cursors, there is a hard limit on the complexity of the applications it can support. I write this email not to complain, but to share my experience and expertise. I would really like to see more lisp in the business world. I think my comments could help make that a reality. Blake McBride On Mon, Feb 8, 2016 at 9:22 AM, Russ Tyndall <[email protected]> wrote: > 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. > map-query has the ability to accumulate the results or not, so may consum= e > memory for each row. 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). > > Its also more generally true: every backend has different support and > capabilities for most things in CLSQL land. Each backend is in its own > state, and often developed by multiple and different people at different > times. So if you mentioned more about which backends you were looking at= I > might be able to provide more direct answers > > Nesting commands in sql shouldnt be a problem, but I have never tried thi= s > with nested do-query stuff. Obviously database deadlocks *can* be an > issue. You might need to open a separate connection for the inner 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 > official word on this regarding all of the mentioned constructs. But it > also leads to my second question having to do with nested queries. It go= es > as follows: > > In C, Java, C#, Python, etc. there is not an issue because they all have > cursors. I don't see cursors on CLSQL. > > You write a utility function that encapsulates some needed functionality. > It gets called when needed. What it actually does is, rightfully, > abstracted. Actually, what it does is perform a select against a databas= e, > do some calculations on the records, and return the result. > > Now, let's say you have another function. I'll call this one an > application function. This function does a select on the database, spins > through the records and calls the utility function for each of its > records. So, you have a nested select. Obviously, the utility function > spin should not interfere with the application function spin. Normally, > this is not a problem when cursors are used, but I 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]> wrote: > >> >The CLSQL documentation documents DO-QUERY, LOOP, and MAP-QUERY but >> doesn't >> >make clear what is going on inside. Are _all_ the records obtained fir= st >> >as one big list and than mapped over, or is it more intelligent obtaini= ng >> >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 is >> possible that the driver code does some buffering =E2=80=94 I have never >> checked. >> >> >> >> > > > _______________________________________________ > CLSQL mailing listCLSQL-2NDrxpH/[email protected]://lists.kpe.io/cgi-bin/mailman/l= istinfo/clsql > > > > _______________________________________________ > CLSQL mailing list > CLSQL-2NDrxpH/[email protected] > http://lists.kpe.io/cgi-bin/mailman/listinfo/clsql > > --001a1143d0e4296dbf052b7f1f6e Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr">Thanks.=C2=A0 In the interest of multi-database support, a= nd consistent operation, I suppose my main interest is ODBC.<div><br></div>= <div>It sounds like you are saying that CLSQL (with ODBC) can (essentially)= read one record at a time into memory.=C2=A0 That's great.=C2=A0 I'= ;ll need to play with that.</div><div><br></div><div>Having to get multiple= connections to a database for nested queries is a show stopper.=C2=A0 If y= ou have a multi-threading web server, you can't be getting multiple con= nections on multiple threads or you'd very quickly reach the database c= onnection limit.=C2=A0 Support for cursors is necessary.</div><div><br></di= v><div>There are many SQLDB interfaces for many languages.=C2=A0 All of the= high use, more main stream languages that support ODBC all support cursors= .=C2=A0 For example, C, C++, C#, Java, Python, and many more.=C2=A0 Other, = less use, languages that support SQL interfaces seem to miss the need for c= ursors.=C2=A0 This includes Lisp and Unicon as I am currently aware.=C2=A0 = Having 35 years experience writing business applications, I can assure you,= you cannot write a substantial business application without cursors.=C2=A0= It can't be done because of the nesting problem and the database conne= ction limits.=C2=A0 My last large app had 9500 classes.=C2=A0 It took multi= ple programmers years to write.=C2=A0 There is just no way to coordinate ne= sting on a large application.=C2=A0 Languages that don't clearly and un= equivocally support cursor operations can never be considered for a signifi= cant project.</div><div><br></div><div>Lisp is my favorite language.=C2=A0 = Now that web apps have become the dominant architecture, all a language has= to support to be a viable alternative is serving pages, REST services, and= good SQL access.=C2=A0 I get the impression that CL has viable support for= web and REST, but I haven't found a viable solution for SQL access.</d= iv><div><br></div><div>Having said all that, I am sure CLSQL is used succes= sfully on many real applications.=C2=A0 But, these application must have a = limited number of developers on it and a significant limit on their size / = complexity.=C2=A0 Without support for cursors, there is a hard limit on the= complexity of the applications it can support.</div><div><br></div><div>I = write this email not to complain, but to share my experience and expertise.= =C2=A0 I would really like to see more lisp in the business world.=C2=A0 I = think my comments could help make that a reality.</div><div><br></div><div>= Blake McBride</div><div><br></div><div><br></div><div><br></div><div class= =3D"gmail_extra"><br><div class=3D"gmail_quote">On Mon, Feb 8, 2016 at 9:22= AM, Russ Tyndall <span dir=3D"ltr"><<a href=3D"mailto:russ@acceleration= .net" target=3D"_blank">[email protected]</a>></span> wrote:<br><blo= ckquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #c= cc solid;padding-left:1ex"> =20 =20 =20 <div 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 i= n its own state, and often developed by multiple and different people at different times.=C2=A0 So if you mentioned more about which backends 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 deadloc= ks *can* be an issue.=C2=A0 You might need to open a separate connection f= or the inner query, but I am not sure. <br> <br> Cheers, hope this helps,<br> Russ Tyndall<br> Programmer<br> Acceleration.net<div><div class=3D"h5"><br> <br> <br> <br> <div>On 02/06/2016 10:07 AM, Blake McBride wrote:<br> </div> </div></div><blockquote type=3D"cite"><div><div class=3D"h5"> <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 question 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 W= hat it actually does is, rightfully, abstracted.=C2=A0 Actually, what = 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 select = 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 shoul= d not interfere with the application function spin.=C2=A0 Normall= y, 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, <span = dir=3D"ltr"><<a 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;bo= rder-left:1px #ccc solid;padding-left:1ex"><span>>The CLSQL documentatio= n documents DO-QUERY, LOOP, and MAP-QUERY but doesn't<br> >make clear what is going on inside.=C2=A0 Are _all_ the 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></fieldset> <br> </div></div><pre>_______________________________________________ CLSQL mailing list <a href=3D"mailto:CLSQL-2NDrxpH/[email protected]" target=3D"_blank">CLSQL-2NDrxpH/[email protected]<= /a> <a href=3D"http://lists.kpe.io/cgi-bin/mailman/listinfo/clsql" target=3D"_b= lank">http://lists.kpe.io/cgi-bin/mailman/listinfo/clsql</a> </pre> </blockquote> <br> </div> <br>_______________________________________________<br> CLSQL mailing list<br> <a href=3D"mailto:CLSQL-2NDrxpH/[email protected]">CLSQL-2NDrxpH/[email protected]</a><br> <a href=3D"http://lists.kpe.io/cgi-bin/mailman/listinfo/clsql" rel=3D"noref= errer" target=3D"_blank">http://lists.kpe.io/cgi-bin/mailman/listinfo/clsql= </a><br> <br></blockquote></div><br></div></div> --001a1143d0e4296dbf052b7f1f6e-- --===============0235783683246427106== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: base64 Content-Disposition: inline X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX18KQ0xTUUwgbWFp bGluZyBsaXN0CkNMU1FMQGxpc3RzLmtwZS5pbwpodHRwOi8vbGlzdHMua3BlLmlvL2NnaS1iaW4v bWFpbG1hbi9saXN0aW5mby9jbHNxbAo= --===============0235783683246427106==--