performance issue on displaying records

Question

I have a table with just 3,000 records.
I render these 3000 records in the home page without pagination, my client is not interested in pagination...

So to show page completely it takes around 1 min, 15 sec. What can be done to make the page load more quickly?

My table structure:

customer table

• customer id
• customer name
• guider id
• and few columns

guider table

• guider id
• guider name
• and few columns

Answer 1

Check the Todos

1. sql Connection (dont open the connection in loop) for query it should be one time connection
2. check your queries and analyse it if you are using some complex logic which can be replaced
3. use standard class for sql connection and query ; use ezsql

sql query best practice

Answer 2

While you could implement a cache to do this, you don't necessarily need to do so, an introducing unnecessary cache structures can often cause problems of its own. Depending on where the bottleneck is, it may not even help you much, or at all.

You need to look in two places for your analysis:

1) The query you're using to get your data. Take a look at its plan, or if you're not comfortable doing that, run it in your favorite query tool and see how long it takes to come back. If it doesn't take too long, you've got a pretty good idea that your bottleneck isn't the query. If the query itself takes a long time, that's where you should focus your efforts.

2) How your page is rendering. What is the size of your page, in bytes? It may be too big. Can you cut the size down by formatting? Can you more effectively use CSS to eliminate duplicate styling on the page? Are you using a fixed or dynamic table layout? Dynamic is generally going to be quite a bit slower, especially for large tables. Try to avoid nesting tables. Do everything you can to make the page as small as possible, and keep testing!

Answer 3

Where's the slow down? The query or the serving?

If the former, see the comments above. If the latter:

Enable gzip on the server. Otherwise capture the [HTML?] output to a file, compress it (zip), then serve it as a download. Same for any other format if you think something else can render it better than a browser (CSV and Open Office).

If you're outputting the data into a HTML table then you may have an issue where the browser is waiting for the end of the table before rendering it. You can either break this into multiple table chunks like every 500 records/rows or try CSS "table-layout: fixed;".

Answer 4

while displaying records i want to display guidername so , i did once function that return the guider name

Sounds like you need to use a JOIN. Here's a simple example:

SELECT * FROM customer JOIN guider ON guider.id=customer.guider_id

This will change your page from using N + 1 (3001) queries to just one.

Make sure both guider.id and customer.guider_id are indexed and of appropriate data types (such as integers).

Answer 5

This is a little list, what you should think about for improving the performance, the importance is relative to each point, so the first ist not to be the most important to you - which depends on the details of your project.

1. Check your database structure. If there are just these two tables, their might be little you can do. But keep in mind that there is stuff like indices and with an increasing number of records a second denormalizes table structure will improve the speed of retrieving results.

2. Use rather one Query for selecting your data, than iterating through ids and doing selects repeatedly

3. Run a separate Query for the guiders, I assume there are only a few of them. Save all guiders in a data structure, e.g. a dictionary, first and use the foreign key to apply the correct one to the current record - this might save a lot of data which has to be transmitted from the database to your web server.

4. Get your result set by using something like mysqli_result::fetch_all() which returns a 2-dimensional array with all results. This should be faster than iteration through each row with fetch_row()

5. Sanitize your HTML Output, use (external) CSS. This will save a lot of output space if you format your stuff with style=" ... a lot of formatting code ..." attributes in each line. If you use one large table, split them up in multiple tables (some browsers wait for the complete table to load before rendering it).

6. In a lot of languages very important: Use a string builder for concatenating your results into the output string!

7. Caching: Think about generating the output once a day or once an hour. Write it to a cachefile which is opened instead of querying the database and building the same stuff on every request. Maybe you want to offer this generated file as download, rather than displaying it as plain HTML Site on the web.

8. Last but not least, check the connections to webserver and database, the server load as well as the number of requests. If your servers are running on heavy load everything ales here might help reducing the load or you just have to upgrade hardware.

Answer 6

LOL
everyone is talking of big boys toys, like database structure, caching and stuff.
While the problem most likely lays in mere HTML and browsers.

Just to split whole HTML table in chunks will help first chunk to show up immediately while others will eventually come.

Only ones were right who said to profile whole thing first.
Trying to answer without profiling results is shooting in the dark.