Re: Dealing with large datasets
Russ Tyndall <[email protected]> Thu, 11 Feb 2016 11:33:22 -0500
| Newsgroups | gmane.lisp.clsql.general |
|---|---|
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format. --===============6745662774658874195== Content-Type: multipart/alternative; boundary="------------040802040101050307070902" This is a multi-part message in MIME format. --------------040802040101050307070902 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 u1BGXh1j005692 The problem is definitely one of developer hours and interest. Having a=20 knowledgeable developer who actually needs these features to work, would=20 certainly help get there, but I *feel* like its >90% of the way there=20 for the ODBC backend. You can successfully write large business applications without cursors=20 using a variety of other techniques (such as manually cursoring over the=20 rows, putting the cursor logic inside the database etc, but all=20 compromises). Lisp has never been a language touted as being ideal for=20 large numbers of developers, so no arguments to be had there. Its just=20 not even trying to be java. As for nested queries, they may work just fine (probably do), I really=20 have never needed them, and so don't want to really commit to it. My=20 guess is that it would come down to how the internals are handling the C=20 bits, which is probably mostly correct. I use separate connections for=20 things like writing logging outside of the current transaction, so they=20 are required. While I certainly appreciate your comments and agree that CLSQL could=20 and should be better, patches speak louder than words ;) Having never=20 needed this feature, its hard to justify spending the time to make sure=20 it works right on a back end that I use less and less. I have been one=20 of the only consistently active developers for the past few years - so=20 most development has centered around what I need to accomplish my task=20 at hand and patches sent to the list from sporadic sources. While CLSQL=20 is definitely production ready (I have and continue to use it), as a=20 project it is not moving forward very fast. Lack of parameterized queries for ODBC seems like a bigger oversight to=20 me than cursors or nested queries *shrug*. Thanks for your comments, Russ Tyndall Acceleration.net On 02/11/2016 09:11 AM, Blake McBride wrote: > Thanks. In the interest of multi-database support, and consistent=20 > operation, I suppose my main interest is ODBC. > > It sounds like you are saying that CLSQL (with ODBC) can (essentially)=20 > read one record at a time into memory. That's great. I'll need to=20 > play with that. > > Having to get multiple connections to a database for nested queries is=20 > a show stopper. If you have a multi-threading web server, you can't=20 > be getting multiple connections on multiple threads or you'd very=20 > quickly reach the database connection limit. Support for cursors is=20 > necessary. > > There are many SQLDB interfaces for many languages. All of the high=20 > use, more main stream languages that support ODBC all support=20 > cursors. For example, C, C++, C#, Java, Python, and many more. =20 > Other, less use, languages that support SQL interfaces seem to miss=20 > the need for cursors. This includes Lisp and Unicon as I am currently=20 > aware. Having 35 years experience writing business applications, I=20 > can assure you, you cannot write a substantial business application=20 > without cursors. It can't be done because of the nesting problem and=20 > the database connection limits. My last large app had 9500 classes. =20 > It took multiple programmers years to write. There is just no way to=20 > coordinate nesting on a large application. Languages that don't=20 > clearly and unequivocally support cursor operations can never be=20 > considered for a significant project. > > Lisp is my favorite language. Now that web apps have become the=20 > dominant architecture, all a language has to support to be a viable=20 > alternative is serving pages, REST services, and good SQL access. I=20 > get the impression that CL has viable support for web and REST, but I=20 > haven't found a viable solution for SQL access. > > Having said all that, I am sure CLSQL is used successfully on many=20 > real applications. But, these application must have a limited number=20 > of developers on it and a significant limit on their size /=20 > complexity. Without support for cursors, there is a hard limit on the=20 > complexity of the applications it can support. > > I write this email not to complain, but to share my experience and=20 > expertise. I would really like to see more lisp in the business=20 > 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]=20 > <mailto:[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 consume 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 this 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 goes 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 database, 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] >> <mailto:jhyiugjhvbjh234-JGs/[email protected]>> wrote: >> >> >The CLSQL documentation documents DO-QUERY, LOOP, and MAP-QUE= RY >> but 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 comp= are >> 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 list >> CLSQL-2NDrxpH/[email protected] <mailto:CLSQL-2NDrxpH/[email protected]> >> http://lists.kpe.io/cgi-bin/mailman/listinfo/clsql > > > _______________________________________________ > CLSQL mailing list > CLSQL-2NDrxpH/[email protected] <mailto:CLSQL-2NDrxpH/[email protected]> > http://lists.kpe.io/cgi-bin/mailman/listinfo/clsql > > --------------040802040101050307070902 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 u1BGXh1j005692 <html> <head> <meta content=3D"text/html; charset=3Dutf-8" http-equiv=3D"Content-Ty= pe"> </head> <body bgcolor=3D"#FFFFFF" text=3D"#000000"> The problem is definitely one of developer hours and interest.=C2=A0 Having a knowledgeable developer who actually needs these features to work, would certainly help get there, but I *feel* like its >90% of the way there for the ODBC backend.<br> <br> You can successfully write large business applications without cursors using a variety of other techniques (such as manually cursoring over the rows, putting the cursor logic inside the database etc, but all compromises).=C2=A0 Lisp has never been a langu= age touted as being ideal for large numbers of developers, so no arguments to be had there.=C2=A0 Its just not even trying to be java.= <br> <br> As for nested queries,=C2=A0 they may work just fine (probably do), I really have never needed them, and so don't want to really commit to it.=C2=A0 My guess is that it would come down to how the internals ar= e handling the C bits, which is probably mostly correct.=C2=A0 I use separate connections for things like writing logging outside of the current transaction, so they are required.<br> <br> While I certainly appreciate your comments and agree that CLSQL could and should be better, patches speak louder than words ;)=C2=A0 Having never needed this feature, its hard to justify spending the time to make sure it works right on a back end that I use less and less.=C2=A0 I have been one of the only consistently active developer= s for the past few years - so most development has centered around what I need to accomplish my task at hand and patches sent to the list from sporadic sources.=C2=A0 While CLSQL is definitely productio= n ready (I have and continue to use it), as a project it is not moving forward very fast.<br> <br> Lack of parameterized queries for ODBC seems like a bigger oversight to me than cursors or nested queries *shrug*.<br> <br> Thanks for your comments,<br> Russ Tyndall<br> Acceleration.net<br> <br> <br> <br> <br> <div class=3D"moz-cite-prefix">On 02/11/2016 09:11 AM, Blake McBride wrote:<br> </div> <blockquote cite=3D"mid:CABwHSOu=3DaAXrEcTBKd3uaJUfG=3DD1FGrRg0KGDwuaZ3CAXAFcRA@mail.= gmail.com" type=3D"cite"> <div dir=3D"ltr">Thanks.=C2=A0 In the interest of multi-database su= pport, and 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 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.=C2=A0 Support for cursors is necessary.</div> <div><br> </div> <div>There are many SQLDB interfaces for many languages.=C2=A0 Al= l of the high use, more main stream languages that support ODBC all support cursors.=C2=A0 For example, C, C++, C#, Java, Python, a= nd many more.=C2=A0 Other, less use, languages that support SQL interfaces seem to miss the need for cursors.=C2=A0 This includ= es 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 connection limits.=C2=A0 My last large app had 950= 0 classes.=C2=A0 It took multiple programmers years to write.=C2=A0= There is just no way to coordinate nesting on a large application.=C2= =A0 Languages that don't clearly and unequivocally support cursor operations can never be considered for a significant 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.</div> <div><br> </div> <div>Having said all that, I am sure CLSQL is used successfully on many real applications.=C2=A0 But, these application must ha= ve a limited number of developers on it and a significant limit on their size / complexity.=C2=A0 Without support for cursors, the= re 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 hel= p 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 moz-do-not-send=3D"true" href=3D"mailto:[email protected]" target=3D"_blank">r= [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"> <div bgcolor=3D"#FFFFFF" text=3D"#000000"> Howdy, I have be= en 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 o= ften developed by multiple and different people at different times.=C2=A0 So if you mentioned more about which backend= s 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 deadlocks *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 <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 i= s 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 actual= ly does is, rightfully, abstracted.=C2=A0 Actual= ly, 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 applica= tion function.=C2=A0 This function does a select o= n 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 should not interfere with the application function spin.=C2=A0 Normally, this is not a problem w= hen cursors are used, but I see no cursors in CLSQL.=C2=A0 Is this an issue with CLSQL?</di= v> </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 moz-do-not-send=3D"true" href=3D"mailto:jhyiugjhvbjh234-JGs/[email protected]" target=3D"_blank"><a class=3D"moz-txt-lin= k-abbreviated" href=3D"mailto:jhyiugjhvbjh234-JGs/[email protected]">jhyiugjhvbjh234@ma= il.ru</a></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>>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_ 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.<b= r> <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 moz-do-not-send=3D"true" href=3D"mailto:CLSQL-2NDrxpH/[email protected]" target=3D"= _blank">CLSQL-2NDrxpH/[email protected]</a> <a moz-do-not-send=3D"true" href=3D"http://lists.kpe.io/cgi-bin/mailman/l= istinfo/clsql" target=3D"_blank">http://lists.kpe.io/cgi-bin/mailman/list= info/clsql</a> </pre> </blockquote> <br> </div> <br> _______________________________________________<br> CLSQL mailing list<br> <a moz-do-not-send=3D"true" href=3D"mailto:CLSQL-2NDrxpH/[email protected]= io">CLSQL-2NDrxpH/[email protected]</a><br> <a moz-do-not-send=3D"true" href=3D"http://lists.kpe.io/cgi-bin/mailman/listinfo/clsq= l" rel=3D"noreferrer" target=3D"_blank">http://lists.kpe.io/= cgi-bin/mailman/listinfo/clsql</a><br> <br> </blockquote> </div> <br> </div> </div> </blockquote> <br> </body> </html> --------------040802040101050307070902-- --===============6745662774658874195== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Disposition: inline Content-Transfer-Encoding: base64 X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX18KQ0xTUUwgbWFp bGluZyBsaXN0CkNMU1FMQGxpc3RzLmtwZS5pbwpodHRwOi8vbGlzdHMua3BlLmlvL2NnaS1iaW4v bWFpbG1hbi9saXN0aW5mby9jbHNxbAo= --===============6745662774658874195==--