Indexing Really Helps

Today I needed to modify a relatively simple, single table selection [tag]query[/tag] in my web app to include [tag]left join[/tag] with another table. One table holds info about submitted reports, while the other has the results of an evaluation form for a given report. The whole application started as just a very basic online evaluation form. Then I got a request for some sort of online submission mechanism. Initially they just wanted an upload page, but I built in a tracking system to go along with it. They liked it so much that they now decided to join the two.

I needed to show which reports were evaluated and which were not. The way my tables are structured, this is essentially a basic left join. Each table has almost a thousand rows in it, so I was expecting a major slowdown. No matter what you do, that [tag]Cartesian product[/tag] necessary to do a join just kills performance-wise. I ran a test query to see exactly how painful is this going to be:

SELECT report.id, report.name, eval.id, eval.reportid
FROM report LEFT JOIN eval
ON report.id=eval.reportid

The result: 802 rows in set (13.44 sec). Painful! 13 seconds is unacceptable. People whine that the application is slow as it is.

Luckily though, I could fix this! [tag]Indexing[/tag] is your friend!

ALTER TABLE eval ADD INDEX(reportid);

I re-ran the same exact query, only to see: 802 rows in set (0.02 sec).

Wohoo! This is a world of difference! Looking at this improvement, I’m planning to carefully examine all the big queries and put indexes on pivotal columns to [tag]optimize[/tag] the db processing time. If I can shave off few seconds here and there, the overall user experience may improve.

Unfortunately the speed bump that most of my users experience is not the query time, but the crappy [tag]IE rendering engine[/tag]. It just can’t handle rendering a large [tag]HTML table[/tag] (20 columns by 50-100+ rows) without temporarily locking up the UI. Firefox users don’t even notice this issue because gecko renders the page incrementally.

Any good ideas how to optimize displaying large HTML tables in IE? I was thinking about just using span elements (or some sort of XML makup) and css to space out text on the page. It could potentially render a little bit faster than a table. Worse comes to worse, I’ll just start generating tab delimited output surrounded by <pre> tags.

I’m currently working on a feature that will dump a report into an Excel file so that they can play around with the numbers. This could be another potential solution.

This entry was posted in programming. Bookmark the permalink.



6 Responses to Indexing Really Helps

  1. Jorge PORTUGAL Mozilla Firefox Windows says:

    Try AJAX! You can create a page that returns only some results (say 20 each page), and build a navigation bar with AJAX without having to post back the request to the server. Users seem to like this kind of feature and since the page won’t reload, everything seems faster.

    All you have to do is build an URL that returns XML with a set of the results and parse it in Javascript.

    Jorge

    Reply  |  Quote
  2. Jorge PORTUGAL Mozilla Firefox Windows says:

    Regarding that Excel report you are working on, you could try SpreadSheetML. I’ve been working with it lately and it’s pretty cool :-)!!!!

    All you have to do is to build an XML using that schema/namespace, set the contentype right and you’re ok to go.

    You can find some info on SpreadsheetML here.

    Jorge

    Reply  |  Quote
  3. Luke UNITED STATES Mozilla Firefox Ubuntu Linux says:

    I took out that long URL from you post and made it into a link – sorry. It was breaking the layout. I need to conjure up get a line-breaking script like the slashdot lameness filter for the comments section one of these days.

    Thanks for the SpreadsheetML link. I will definitely look into it. I was actually using a PHP class (sourceforge is your friend) to generate the files, but this may be a good alternative.

    The AJAX thing is a good idea – thanks. But then again, I know they like to print out the big tables so I still want to give them the ability to have all the information loaded on the page at once.

    Reply  |  Quote
  4. Fr3d UNITED KINGDOM Mozilla Firefox Windows says:

    All tables in a database should really have either a Primary Key or an Index on one of the columns – it’s good practise and, as you found out, *really* speeds it up!

    You could put a disclaimer on the page saying IE is bad and should not be used :P

    Reply  |  Quote
  5. Luke UNITED STATES Mozilla Firefox Windows says:

    All my tables have primary keys, so that’s not the problem. :mrgreen: I just want to optimize the queries that do not rely on them.

    Reply  |  Quote
  6. eddie INDONESIA Safari Windows says:

    i found this link on google, hanks nice tuts

    Reply  |  Quote

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>