| SGreen@unimin.com 2006-01-31, 9:23 am |
| --=_alternative 004EC31B85257107_=
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
Oops - I too forgot the list!=20
----- Forwarded by Shawn Green/Unimin on 01/31/2006 09:19 AM -----
Shawn Green/Unimin
01/31/2006 09:06 AM
To
Jacques Brignon <jacbrignon@online.fr>
cc
Subject
Re: Trans.: Re: Finding the row number satisfying a conditon in a result=20
set
Thank you for your response! :-)
How to implement option 1 depends on your client-side environment. If you=20
have an application that runs completely client-side then your results are =
already client-side when you ask for them and you don't have to worry=20
about copying the data to the client in an array. If you are developing a =
web site then things change a bit. It is possible using DHTML(XHTML, or=20
whatever they are calling it this week) to send all of the data to the=20
client in the form of the HTML to create an array (usually a javascript=20
array) within the browser page used to view the data. Then client-side=20
scripting is used to scroll through the results (by creating and=20
recreating a <TABLE> ) and show the user just the "pages" you want them to=20
see. A variant on this is to have a data browser page surrounding a data=20
retrieval page (inside an IFRAME) and you manipulate the inner page from=20
the outer page by controlling the scrolling in code (a variant of this=20
theme would be to have the data frame hidden and you use client-side=20
script to pick just portions of it for display.) Another way to speed this =
up would be to cache the results server-side in a session-level variable=20
or in a static table that is uniquely identified within the session. Then=20
as the user browses through the data, you don't need to run the original=20
query multiple times to get to the particular subset of records you want=20
to show. You can take it straight from your cache on the web server. A=20
fourth option could be to use a client-server protocol like SOAP to=20
actually query the database from the client interactively. However, this=20
would still cause the database to execute your main query every time you=20
wanted just a page of data.=20
You already identified the need to minimize trips into the database. You=20
just need to workout the best way for your application's design how to do=20
that. Odds are, it's going to involve the temporary storage of your main=20
query somewhere (a cache of the results). It may also require the building =
of an index array or two. Look up the "quicksort" and "binary search"=20
algorithms if you take this route. They are very efficient and I have used =
them before on large sets of data with good performance results.
I am sorry I can't be more specific but there are many approaches to this=20
technique and I am not sure which one will work best for your situation.=20
Let me know if I can help in any way.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Jacques Brignon <jacbrignon@online.fr> wrote on 01/31/2006 07:52:16 AM:
> Thanks Shawn for the detailed answer,
>=20
> What I am currently doing is basically what you propose, I do a full=20
query to
> retreive the row numbers of the subset I want to display and of the=20
"selected"
> record if any in that subset, then I use another query with LIMIT toget=20
those
> rows for display.
>=20
> What I am trying to do is to improve the performance by limiting=20
thenumber of
> queries and by identifying the most efficient way of finding the=20
rownumber of
> the search record. I am currently using brute force by loopiong through=20
the
> result set until I find the record. The proposal of storing the set in a =
temp
> table should improve that, allowing to retrieve the row by a query on=20
that
> table which we can expect to be faster.
>=20
> So As you correctly describe, what I need is to allow the user to=20
> scroll through
> the set, and as you correctly describe, I am therefore usiong your=20
option 2
> doing one query to locate the rows and one with limit to get those to be
> displayed and of course I am hitting performance issues. I also noticed=20
that
> all the queries using limit do not run at the same speed, the more you=20
get
> close to the end of the data set the more it takes time.
>=20
> I uderstand the approach number 3 using a temp table, I am also=20
intersted in
> your approach number 1 but I am not sure to understand what you mean and =
how
> you do that using the PHP MySql function libray.
> Do you mean passing all the rows of the result at once to the client
> application
> and storing them in memory (an array)? If the result set is big,=20
> couldn't we hit
> some limits or experience other performance issues? I see how to getin=20
PHP the
> values of one row of the result set, how do you get all the rows at once =
other
> than looping through the result set and getting one row after the other?
>=20
> --
> Jacques Brignon
>=20
> Selon SGreen@unimin.com:
>=20
AM:[color=darkred]
=20
-----[color=darkred]
result[color=darkred
]
the[color=darkred]
the[color=darkred]
all[color=darkred]
result[color=darkred
]
seem[color=darkred]
meaning in[color=darkred]
away[color=darkred]
as an[color=darkred]
10[color=darkred]
in[color=darkred]
row #[color=darkred]
criterion[color=dark
red]
rows[color=darkred]
result[color=darkred
]
just[color=darkred]
in[color=darkred]
figured[color=darkre
d]
some[color=darkred]
query. In[color=darkred]
recordset[color=dark
red]
results[color=darkre
d]
and[color=darkred]
the[color=darkred]
wanted[color=darkred
]
those[color=darkred]
client-side[color=darkred]
each[color=darkred]
actually[color=darkr
ed]
>=20
>=20
--=_alternative 004EC31B85257107_=--
|