Bill,
Some databases can use a live result set when retrieving a lot of
records and I really really wish MySQL could do the same. A live result set
does not create a temporary table or use memory to retrieve all the
records. It will grab 50 or so records at a time, and when scrolling
further down through the query, it will grab another 50 and so on. And yes,
these queries are bi-directional.
I've successfully created queries with other databases that
"retrieved" a million records in less than 0.1 seconds on a P133 machine.
For browsing data in a grid, it is instantaneous even when "sorting" on an
index column. Traversing the entire 1 million rows uses absolutely no
additional memory. I had a memory monitor running in the background and
from start to finish it used maybe 5k of ram and no additional disk space
was used for temporary tables which means disk activity was extremely
low. Slapping on any kind of Where clause doesn't slow it down because if
you're displaying the results to a grid, it fills the grid with the first
10 rows that it finds, then when you page down it pulls in the next 10
rows. These are the benefits of using a live result set.
Now the drawback of using a live result set is it doesn't create a
static snapshot of the table. A static result set creates a copy of the
rows at the instant the query was executed. It does this to protect the
rows from being changed by another user. You'd want a static result set
when printing reports that are doing subtotals because you don't want other
people throwing your totals off. With a live result set, if I'm going
through the query and I'm on row 100, another user can change row 150 which
may now exclude row 150 from my query because it now falls outside the
scope of the Where clause. For me 98% of the time, I don't really care if
this happens. The additional speed, lower memory use, and very low disk
activity more than makes up for it.
So yes, you can access some databases extremely fast. I wish Monty
would implement this for MySQL. It would have most people drooling on their
keyboards. :-0...
Brent
At 05:04 PM 2/14/2002 , you wrote:
>Monty, Venu, I hope you read this... :)
>
>
>I really, really want to use MySQL as the database backend for my
>datawarehouse. Mind you I have played around with merge tables quite a
>bit and know that MySQL is more than up to the task. There are numerous
>(not necessarily cost related) reasons as to why MySQL is better for my
>application. If it were just me, it would be a slam-dunk as I only use
>perl, etc. to extract data from the database. However most of my users
>use MS Access as a front end and extraction tool.
>
>When pulling datasets from a database, Access tries to be smart and if
>there is what it thinks is a primary key on a table, it will extract the
>values of the primary key for the matching records and then re-query the
>table with a parameterized query to get the rest of the values. This is
>true in both the case where a user tries to view a table or runs a
>simple query.
>
>Taking a simple case of the user opening the table in data sheet view
>(if this is solved, the other cases will be solved too), the following
>happens -- okay, this is a bit simplified, see my message "Large
>Datasets w/Access" for better background:
>http://lists.mysql.com/cgi-ez/ezmlm-cgi?5:mss:4918:200202:bjcebaokcknfmaldpokp
>
>-- Access opens a statement handle (#1) and queries the table for the
>primary key values. E.g. It would pass "SELECT idx FROM TABLE". Note
>that it only cares about getting a partial list here. I.e. if the
>screen only shows 10 records, Access only cares about 10 primary key
>values.
>
>-- Access opens a second statement handle (#2) without closing the first
>handle and then gets the values in a parameterized query. E.g.: "SELECT
>a, b, idx FROM table WHERE idx=? OR idx=?...". It then pulls the
>records it cares about with this statement and closes the statement.
>
>-- If, say, the user presses "page down", [I think] access then gets the
>next set of primary key values from statement handle #1, sets up another
>prepared query and gets the values as above.
>
>
>MyODBC, as compiled today, uses mysql_store_result to get records. This
>is fine for reasonably sized tables. However, if the table has millions
>of records, writing the results to a temporary table has many
>detrimental effects, e.g.: Access seems to hang from the user's
>perspectiv, Access crashes because there are too many records for it to
>handle at once (data requirements to great); MySQL creates HUGE
>temporary tables or bombs if SQL_BIG_RESULT was not set.
>
>So in the case of a very long table, it is important to use
>mysql_use_result instead. This makes it so that results are returned
>right away and eases the load on all programs involved. The astute
>reader will realize that if one uses mysql_use_result and does not fetch
>all of the records, the next query will return the remaining records
>from the previous query first. It follows that Access bombs because in
>statement #2 it is getting results from statement #1. (This is seen from
>the myodbc.log line: " | error: message: Commands out of sync; You
>can't run this command now" in the myodbc3.dll changed to use the said
>function.)
>
>The bottom line is that in order for MySQL + Access + MyODBC to be
>usable as a datawarehouse MySQL/MyODBC (a) must be able to return
>uncached results; and (b) be able to have multiple statements open,
>active, and with pending data to be fetched at the same time.
>
>SO....
>
>Does anyone have any suggestions on how to accomplish this?
>
>How difficult would it be (for a relatively good C/C++ programmer) to
>alter mysqld so that mysql_use_result could handle multiple statements
>open at the same time?
>
>Other suggestions...?
>
>
>Thanks for reading this and your time.
>
>
>--Bill
>(all opinions are mine, bla bla bla)
>(I am on the MyODB list but not the MySQL list at the moment)
>
>
>
>
>---------------------------------------------------------------------
>Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail <[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
