David A. Lee
|
| Posted: 07/15/2001, 9:49 AM |
|
Posting here for comments because this isnt actually a *bug*.
I have a table with about 10,000 rows ... I display on a grid form
(showing 20 rows at a time).
Now usually this is from a search which should limit it a LOT (100s instead
of thousands ..) but if a large result set is returned then
hitting "Next" keeps taking longer and longer each time.
I looked at the perl code and its immedeatly obvious. The "Start Page"
logic is handled by the perl side ... This if you display
row 1000-1020 the perl code actually reads the first 1000 rows and discards
them.
This would be a LOT more efficient if the SQL code was agumented
to include "LIMIT" statements to tell the server to only produce
what rows are needed.
Is there any obvious reason this (seemingly simple) optimization wasnt done
?
Maybe not all databases support LIMIT ? (I'm using MySQL)
--------------------------------------------------
David A. Lee
Dal Enterprises Inc.
dave@calldei.com
http://www.calldei.com
|
|
|
 |
Jeff Stuart
|
| Posted: 07/15/2001, 3:28 PM |
|
David, I THINK that only mysql supports the limit statement. Even if others
do, I can pretty well guarantee that Access doesn't. 
--
Jeff Stuart
jstuart@neo.rr.com
"David A. Lee" <dave@calldei.com> wrote in message
news:9ishht$nbe$1@news.codecharge.com...
> Posting here for comments because this isnt actually a *bug*.
> I have a table with about 10,000 rows ... I display on a grid form
> (showing 20 rows at a time).
> Now usually this is from a search which should limit it a LOT (100s
instead
> of thousands ..) but if a large result set is returned then
> hitting "Next" keeps taking longer and longer each time.
>
> I looked at the perl code and its immedeatly obvious. The "Start Page"
> logic is handled by the perl side ... This if you display
> row 1000-1020 the perl code actually reads the first 1000 rows and
discards
> them.
>
> This would be a LOT more efficient if the SQL code was agumented
> to include "LIMIT" statements to tell the server to only produce
> what rows are needed.
> Is there any obvious reason this (seemingly simple) optimization wasnt
done
> ?
> Maybe not all databases support LIMIT ? (I'm using MySQL)
>
>
>
>
> --------------------------------------------------
> David A. Lee
> Dal Enterprises Inc.
>dave@calldei.com
> http://www.calldei.com
>
>
|
|
|
|
CodeCharge
|
| Posted: 07/15/2001, 6:28 PM |
|
The issue is not only with the support of the LIMIT statement across
databases.
Keep in mind that you can use an SQL query instead of a table as the basis
of your form.
The main problem is the handling of complex queries, where CodeCharge
wouldn't be able to simply add "LIMIT", "TOP" or other statements to your
query.
But I'll pass it on onto engineering and maybe they'll come up with some
ideas.
Adam
"David A. Lee" <dave@calldei.com> wrote in message
news:9ishht$nbe$1@news.codecharge.com...
> Posting here for comments because this isnt actually a *bug*.
> I have a table with about 10,000 rows ... I display on a grid form
> (showing 20 rows at a time).
> Now usually this is from a search which should limit it a LOT (100s
instead
> of thousands ..) but if a large result set is returned then
> hitting "Next" keeps taking longer and longer each time.
>
> I looked at the perl code and its immedeatly obvious. The "Start Page"
> logic is handled by the perl side ... This if you display
> row 1000-1020 the perl code actually reads the first 1000 rows and
discards
> them.
>
> This would be a LOT more efficient if the SQL code was agumented
> to include "LIMIT" statements to tell the server to only produce
> what rows are needed.
> Is there any obvious reason this (seemingly simple) optimization wasnt
done
> ?
> Maybe not all databases support LIMIT ? (I'm using MySQL)
>
>
>
>
> --------------------------------------------------
> David A. Lee
> Dal Enterprises Inc.
>dave@calldei.com
> http://www.calldei.com
>
>
|
|
|
|
Scott R. Senay
|
| Posted: 07/16/2001, 7:27 AM |
|
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I forget the exact syntax but believe it or not Access does have a
means of limiting how much information is returned and then the
ability to do further subselects using NEXT 100 or what have you...
So if you run into the need, just dig into it a bit... If you get
stuck just drop me a note and I'll dig it out of my dusty records...
The important thing to remember is that Access rides on top of
Jet/DAO and those two have LOTS of power burried in them because they
also have to work with SQL Server...
Scott...
"Jeff Stuart" <jstuart-devel@neo.rr.com> wrote in message
news:9it5f5$reb$1@news.codecharge.com...
> David, I THINK that only mysql supports the limit statement. Even
> if others do, I can pretty well guarantee that Access doesn't.
>
> --
> Jeff Stuart
>jstuart@neo.rr.com
>
>
> "David A. Lee" <dave@calldei.com> wrote in message
>news:9ishht$nbe$1@news.codecharge.com...
> > Posting here for comments because this isnt actually a *bug*.
> > I have a table with about 10,000 rows ... I display on a grid
> > form (showing 20 rows at a time).
> > Now usually this is from a search which should limit it a LOT
> > (100s
> instead
> > of thousands ..) but if a large result set is returned then
> > hitting "Next" keeps taking longer and longer each time.
> >
> > I looked at the perl code and its immedeatly obvious. The "Start
> > Page" logic is handled by the perl side ... This if you display
> > row 1000-1020 the perl code actually reads the first 1000 rows
> > and
> discards
> > them.
> >
> > This would be a LOT more efficient if the SQL code was agumented
> > to include "LIMIT" statements to tell the server to only produce
> > what rows are needed.
> > Is there any obvious reason this (seemingly simple) optimization
> > wasnt
> done
> > ?
> > Maybe not all databases support LIMIT ? (I'm using MySQL)
> >
> >
> >
> >
> > --------------------------------------------------
> > David A. Lee
> > Dal Enterprises Inc.
> >dave@calldei.com
> > http://www.calldei.com
> >
> >
>
-----BEGIN PGP SIGNATURE-----
Version: PGP Personal Privacy 6.5.3
iQA/AwUBO1L6O5lYYi/ptzWoEQJHjwCg8oB/RJbdmG3iQCBbGkfMsmsu2XUAn1gA
sztix3c1bo1a0hqDS5x80I+a
=lgDI
-----END PGP SIGNATURE-----
|
|
|
|
Jeffrey A. Stuart
|
| Posted: 07/16/2001, 1:57 PM |
|
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Mon, 16 Jul 2001 10:29:16 -0400, "Scott R. Senay"
<drsenay@conceptech.com> wrote:
Well, that goes to show ya never say that something DOESN'T have a
feature cause usually you're wrong! :D
Scott, I don't normally do Access but if I ever have to, You're the
first one I'll ask. :)
>I forget the exact syntax but believe it or not Access does have a
>means of limiting how much information is returned and then the
>ability to do further subselects using NEXT 100 or what have you...
>So if you run into the need, just dig into it a bit... If you get
>stuck just drop me a note and I'll dig it out of my dusty records...
>The important thing to remember is that Access rides on top of
>Jet/DAO and those two have LOTS of power burried in them because
>they also have to work with SQL Server...
>
>Scott...
-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 6.5.8 for non-commercial use <http://www.pgp.com>
iQA/AwUBO1NSho2gaWDOuGieEQLYMACeK5arwxRS06dVPOhvniQoZNqI9/cAoIi4
oCr4YLGvUnYqcNwSlpRhSLyO
=X/h4
-----END PGP SIGNATURE-----
|
|
|
|
|
