Dealing with large datasets

--===============4265419808117654740==
Content-Type: multipart/alternative; boundary=047d7bd75984498ee6052b10597d

--047d7bd75984498ee6052b10597d
Content-Type: text/plain; charset=UTF-8

Hi,

I've been looking for a good SQL library for CL and came across CLSQL.  I
have two questions about cursors.  I thought I would split the questions
into two separate emails.

I see DO-QUERY, LOOP, and MAP-QUERY but it is unclear what is happening.
For example, let's say I have a select that returns one million rows.  I
would like to iterate over the result set, one record at a time.  I don't
want all one million records in memory at one time - only one record at a
time.  This is a huge difference in memory (and time - but this is a little
more complex).  It would be expected that the ODBC driver would obtain and
cache the records in manageable sized blocks so I am not going to the
database with each record.

One way to deal with this is the use of read-only, forward-only cursors.

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 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.

Thanks.

Blake McBride

--047d7bd75984498ee6052b10597d
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi,<div><br></div><div>I&#39;ve been looking for a good SQ=
L library for CL and came across CLSQL.=C2=A0 I have two questions about cu=
rsors.=C2=A0 I thought I would split the questions into two separate emails=
.</div><div><br></div><div>I see DO-QUERY, LOOP, and MAP-QUERY but it is un=
clear what is happening.=C2=A0 For example, let&#39;s say I have a select t=
hat returns one million rows.=C2=A0 I would like to iterate over the result=
 set, one record at a time.=C2=A0 I don&#39;t want all one million records =
in memory at one time - only one record at a time.=C2=A0 This is a huge dif=
ference in memory (and time - but this is a little more complex).=C2=A0 It =
would be expected that the ODBC driver would obtain and cache the records i=
n manageable sized blocks so I am not going to the database with each recor=
d.</div><div><br></div><div>One way to deal with this is the use of read-on=
ly, forward-only cursors.</div><div><br></div><div>The CLSQL documentation =
documents DO-QUERY, LOOP, and MAP-QUERY but doesn&#39;t make clear what is =
going on inside.=C2=A0 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?</div><div><br></div><div>This is a really big issue.=C2=A0 I app=
reciate clarification.</div><div><br></div><div>Thanks.</div><div><br></div=
><div>Blake McBride</div><div><br></div><div><br></div></div>

--047d7bd75984498ee6052b10597d--

--===============4265419808117654740==
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: base64
Content-Disposition: inline

X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX18KQ0xTUUwgbWFp
bGluZyBsaXN0CkNMU1FMQGxpc3RzLmtwZS5pbwpodHRwOi8vbGlzdHMua3BlLmlvL2NnaS1iaW4v
bWFpbG1hbi9saXN0aW5mby9jbHNxbAo=

--===============4265419808117654740==--