[POOR CODE]: Long Topics Taking Ages To Load...

.

Now when I have programming on college, I think I at least understand a tiny micro bit of your post. ;)

Slovene again?

He speaks in riddles.

The fix is planned for v14.

v1.4  or v14

Elektrotehnik računalnik, huh?

Why don't you go to Univerza v Ljubljani?!

SQL optimization engines these days do a good job of avoiding the issues you describe right from the get-go.  If the SQL is bad, it's very likely it can be fixed right in the single statement without having to resort to temp tables.

I find that rarely do such tactics have to be resorted to to drive significant performance improvements from underperforming queries.  Better joins and better indexes can usually fix the issues right in place. 

I simply say 'college' instead of 'uni'. :)

We have maths, physics, electrotechnics and programming.

I'm visiting Faculty of Electrical Engineering on University of Ljubljana, therefore Fakulteta za elektrotehniko, Univerze v Ljubljani. :D

Hey TheGrobe, thanks for your answer man!

Normally, a database should avoid such a problem, in particular with correct indexes. I agree with that one.

I've still had a case where a similar, but not the same, actually quite a little different problem occurred, on an older MySQL 5. As soon as a WHERE with some search parameters was added, MySQL would go bonkers and take several seconds.

There was just 2 tables. One of them had Latitude and Longitude columns for some locations, for which the distance (air line) from a certain point had to be calculated with a simple formula. That table had to be joined with another and several search parameters added.

It was very fast without said search parameters, but as soon as they were added it would take several seconds to complete - unacceptable.

In that case I put the pure calculation and joining into a temporary table.
And then queried the temporary table with said search parameters.
Execution time was reduced to some ten miliseconds.

Though, I agree. Better joins and indexes can make a world of a difference.

Do you reckon temporary tables are (only) useful where nested queries would come into play?

Well, it really depends on your SQL flavour I think and how they handle subqueries.  Again, I think most optimization enginges do a pretty efficient job of this.  If you are using an older database version maybe there are some issues there.

It could come down to what was in the where clause and how the search parameters are applied.  I've seen indexes undermined by where clauses (i.e. the way the clause is constructed causing the index to not be used).  Again, there's often something that can be done right in the query to get the benefit of both -- without the specifics I couldn't say, but "like" or "isnull/coalesce" are common culprits for this kind of issue in my experience.

As an alternative to temp tables, if you're able, I think I'd first look at table valued functions (or even pipelined table functions if you're working in Oracle.)

Temp tables, to my mind, are best utilized in stored procedures where calculations not ideally suited to a purely cominatorial set based one-pass query need to be done.  If you find yourself about to write a cursor, give some serious thought as to whether temp tables might be able to help solve your problem instead.

This is probably because your internet speed sucks.

Really? How would you know!?

Have you tried opening the mentioned topic?!

www.chess.com/forum/view/off-topic/youre-banned-forum-game

It takes 25 (!) seconds to display anything. Doing nothing. Nothing but waiting.

I know everything I was the one who created God and told him to make the universe.

It is better in V3 version of chess.com.

V2 ~ 20 seconds
V3 ~ 5 seconds

Still too long though..

Needless to add, anything, anymore.

I agree, 5 seconds is still, too long.

I'm assuming V3 is currently available only to Premium Members?

Yes I think so.You have to get in this group then you get it. http://www.chess.com/groups/home/chesscom-v3

Oh, yeah, something's wrong there for sure.  I even got a time-out trying to open chess.com's longest thread:

http://www.chess.com/forum/search?keyword=&sortby=post_count

My guess is something in the way pagination is done is likely to blame.  If you've ever watched closely enough, when a user gets banned or a post deleted, all subsequent posts get renumbered which tells me that the post number almost certainly calculated on the fly and there is a surrogate key hiding behind the scenes.  Since post number is the basis for pagination I could see how this would become problematic trying to pick which 20 posts to display on any given page when a thread gets to a significant number of posts.

Same here, after a whole minute nothing was displayed, probably server-side execution time limit.

Good point, but, that shouldn't be a problem in any way because:

• Actual post IDs in the database remain the same. As you say it's done on-the-fly.
• You can still do:
  SELECT *
  FROM posts
  WHERE thread_id = $thread_id AND visible AND !deleted
  ORDER BY id ASC
  LIMIT 9980, 20
  Where the 9980 is the offset to the rows to be returned and 20 the amount of rows returned. So 9980 would be 499 (page) * 20 (posts per page).

IMHO the post # is purely for display purposes and doesn't serve any other purpose.

The worst thing I could imagine is them doing something like using a variable, something like:

SET @post_number := 0;

SELECT *, @post_number := @post_number + 1 AS post_number
FROM posts
WHERE thread_id = $thread_id AND visible AND !deleted
ORDER BY id ASC

Looks cool, but, don't do this, kids.

That counting would totally kill performance. But notice the LIMIT had to be left away for the counting to work. Perhaps they then do another SELECT from the above. LOL.

Also you can clearly deduce the post numbers by simply the page you're on:

Starting # = (Page - 1)  * 20
Ending # = (Page - 1) * 20 + (20 - 1)

Insert, say 338 for Page and you get:

Starting # = (338 - 1) * 20 = 6740
Ending # = (338 - 1) * 20 + (20 - 1) = 6759

That should answer it unless there's a misunderstanding from my side.

Assuming the surrogate key is numeric.  If it's a GUID, on the other hand, that won't work and you have to first sort by date and then paginate based on sorting rank.