MySQL Forums
Forum List  »  Performance

4.1.8 summary: C-API/ODBC remote performance slow, Prepared Statements stall server, subselects ???, OR clauses in SELECTS
Posted by: Andr Schršder
Date: December 31, 2004 12:36AM

1. Remote vs. local performance. Remote performance is only 25% of local performance, even with better hardware on the server side - especially when it comes to a high number of queries. Obviously something is very wrong with the client/server communication. Few queries with large results work pretty good though. Many queries with small results seem to be the problem.

2. Prepared statements do not work in a concurrent environment. Sending a mysql_stmt_prepare will stall the server. During this time the server will not be responsive to other connections. On top of this, the client/server communication problem remains. Basically, when you have more than one connection or you are working against a remote server, you can't use prepared statements in the C-API. Otherwise you'll take the server offline for seconds every time you send a prepare.

3. Subselects are totally useless in terms of performance. SELECTing into a temporary table has just about the same result in terms of performance. I find it pretty hard to consider them a feature.

4. ODBC API is affected by the above since it uses the native C-API from what I understand.

5. When SELECTing on ranges, you get faster results when you are able to break up the query into several smaller queries. If you try and use a query with a WHERE clause such as "(a=1 AND b=2 AND c>3) OR (a=1 AND b>2) OR (a>1) ORDER BY a,b,c" - obviously with an index over all these columns - you will keep the server very busy (and quite unresponsive). If you run the 3 different OR pieces separately, you get the results in a fraction of the time. I find it hard to see the sense in that.

Options: ReplyQuote


Subject
Views
Written By
Posted
6340
December 20, 2004 06:10AM
4.1.8 summary: C-API/ODBC remote performance slow, Prepared Statements stall server, subselects ???, OR clauses in SELECTS
3503
December 31, 2004 12:36AM
2136
January 04, 2005 05:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.