Re: Re : Last attempt - Daniel da Veiga - com.mysql.lists.win32

13 messages in com.mysql.lists.win32Re: Re : Last attempt

From	Sent On	Attachments
Greg Quinn	12 Jan 2005 08:29
SGr...@unimin.com	12 Jan 2005 09:06
Daniel da Veiga	12 Jan 2005 12:06
PF: MySQL	12 Jan 2005 12:40
Danny Willis	12 Jan 2005 13:17
SGr...@unimin.com	12 Jan 2005 13:20
John L.Utz III	12 Jan 2005 13:24
Danny Willis	12 Jan 2005 13:28
Danny Willis	12 Jan 2005 13:33
Fredrick Bartlett	12 Jan 2005 13:47
SGr...@unimin.com	12 Jan 2005 13:49
Danny Willis	12 Jan 2005 14:02
Danny Willis	12 Jan 2005 14:02

Subject:	Re: Re : Last attempt
From:	Daniel da Veiga (dani...@gmail.com)
Date:	01/12/2005 12:06:51 PM
List:	com.mysql.lists.win32

Greetings again,

On Wed, 12 Jan 2005 12:06:54 -0500, SGr...@unimin.com <SGr...@unimin.com> wrote:

"Greg Quinn" <gr...@i-online.co.za> wrote on 01/12/2005 11:30:12 AM:

No, the bottleneck is the query data read. I have rebooted my PC, and accessed a query that is in the query cache and the results appear in less than 0.5 seconds in my app.

Yes, I understand, but you must see that between your server cache (feeded by ODBC) and the MySQL Server, there's a long path, ODBC speed is limited by a lot of stuff, and its a large ammount of data you're requesting... Try runnning the same query at command prompt like this:

C:\MySQL\bin> mysql -D <database> -e "<your select>" > <temp_file>

and see how long will it take... This way you disk will work (not your screen) wich is a lot faster... But Shawn's state about the console query returns (at the end of the WHOLE screen of data) the time spent by the server processing the query like this:

19827 row in set (1.36 sec)

The app is web-based, but all the user mail message headers are bound to an ActiveX control.

Let me see if I understand your architecture: MySQL data store ODBC ADO ASP (VBScript/JScript)

Where does your ActiveX control fit in? What does it do? Can you change its behavior? Does it need to read ALL of the user's headers EVERY TIME it does what it does or could it do something less DB intensive (like a "SELECT count(1) from .... WHERE ...."). If you can make each page database friendly, your entire app will become more responsive. I know of very few effective web-apps that deal with thousands of lines of data on a single page. It usually creates information overload and your users will almost always hate it. However, if the user has ASKED to see ALL of his headers, and the user KNOWS that he has thousands of messages, then he is more than likely willing to wait a few extra seconds for all of that data.

I completely agree with Shawn, your app must use some "optimization" too. If the query I was talking about before (the one at the console) takes at least HALF the time your web app does, it means your app is the bottleneck.

Also, if you are not preserving some "state" between certain pages, your control (depending on what it does) may need to "work from scratch" every time that page is requested. There are several ways to cache information (not necessarily through IIS) so that the control doesn't need to perform a full table read every time it needs just one record. Again, considering this kind of optimization depends heavily on what your control is doing and especially if you can change its behavior in any way.

It's really hard for a web based app to completely get data and at the first refresh be forced to do the whole process again, even if you're testing the app in a LAN based connection, when your app goes "out" to the real world, you may find it just too slow over a connection that has any bandwidth limitation.

What I am trying to suggest is that you may be abusing the disks for no useful purpose. If you are only showing the user the headers from messages 100-199 then use a LIMIT clause to only pull those into your app. If you only need 3 fields from the header information, only query for those fields. In this case the phrase "less is more" implies that the less data you manage in any single read, the more pages you can present per second. That means that your application will seem more responsive and will generally provide a better user experience.

If you manually execute the same query using the MySQL command-line client, how long does the database take to respond? (count only until you see data _start_ to scroll, not until you reach the end of the scroll). Generally, if you can tune your database to perform well when working through the command-line client, then the same queries when presented from your application should also perform well.

-----Original Message----- From: Daniel da Veiga <dani...@gmail.com> To: MySQL Win32 List, Sent: 1/12/2005 6:25:54 PM Subject: Re: Last attempt

I would bet that your SQL query is returning as fast as it can, but keep in mind that even if you're not dealing with the data (showing just small parts of it on your app frontend), all of it is comming from the server to your app, so, based on the information you gave, I would say that MySQL is not responsable for the slow response, instead, what could be limiting your response is the ammount of data and the time to receive, process and show it on your app.

Try selecting only small parts of the table and only demand the rest of the data once the user has asked for it... (assuming its a kind of webmail app) You would show and index of mesgs, and clicking on it you'll select the rest of the data and show it.

Its just my 2 cents, I can't SEE your app, so, its hard to tell.No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.6.10 - Release Date: 1/10/2005