Optimizing code

Square1

Hi all:

I am switching my site over from ASP classic to PHP and am now in the final stretch. I am finding that between the equivalent versions of the ASP/MSSQL pages VS the PHP/MySQL pages, the ASP/MSSQL pages run significantly faster.

So far I have indexed all my MySQL tables and that helped a lot, but still, they don't compare to ASP/MSSQL.

I am on shared DB servers for both MS and My SQL so I may be limited as to server tweaks.

I have searched the web and PHP Builder for help on this but haven't really found much that I can understand.

Are there some simple things I can do to the PHP settings to speed things up? Code tips? As mentioned, I already use indexes on the DB tables. Any other suggestions?

Thanks!

johanafm

Square1;10992118 wrote:
the ASP/MSSQL pages run significantly faster.

As far as I know, there shouldn't be any significant difference.

As for general guidelines on how to improve speed with PHP / MySQL, the topic is rather big when not looking at a very specific issue, ranging from configuration setup, use of one or more caching techniques and writing efficient code/queries. Maybe someone else here knows about ASP to be able to give general guidelines when moving from one to the other, but I can't help out there since I've never used ASP.

However, you might start by looking at a specific page where ASP is faster, and start profiling both of them. That is, in PHP you'd use $start = microtime(true) before a section of code, and after it you'd use $duration = microtime(true) - $start to time that section of code. You might specifically want to profile execution of SQL code separately so as to compare MySQL vs MS SQL separately from the rest of the PHP / ASP code.
Once you have a sufficiently small block of code and/or a specific query which is significantly slower in PHP / MySQL, post that here. Not sure, but for some people it might help to also see the corresponding ASP / MS SQL stuff.

Weedpacket

For that matter, how does PHP/MySQL compare with PHP/MSSQL?

Square1

johanafm;10992123 wrote:
As far as I know, there shouldn't be any significant difference.

As for general guidelines on how to improve speed with PHP / MySQL, the topic is rather big when not looking at a very specific issue, ranging from configuration setup, use of one or more caching techniques and writing efficient code/queries. Maybe someone else here knows about ASP to be able to give general guidelines when moving from one to the other, but I can't help out there since I've never used ASP.

However, you might start by looking at a specific page where ASP is faster, and start profiling both of them. That is, in PHP you'd use $start = microtime(true) before a section of code, and after it you'd use $duration = microtime(true) - $start to time that section of code. You might specifically want to profile execution of SQL code separately so as to compare MySQL vs MS SQL separately from the rest of the PHP / ASP code.
Once you have a sufficiently small block of code and/or a specific query which is significantly slower in PHP / MySQL, post that here. Not sure, but for some people it might help to also see the corresponding ASP / MS SQL stuff.

OK, made a bit of progress. I set up timers and saw that my banners ads were downloading slowly and fixed that. I also fixed a couple of slow queries. Probably shaved a good 1/2 second off the load time.

But a bigger problem still remains that I haven't yet sorted out. The FIRST time I go a page with a MySQL query it takes forever to load. When I refresh, it is fine. If I wait 3 minutes or so and then refresh, the problem returns and it takes forever (or sometimes the page doesn't even open!). It sounds like a cache issue. Where do I start to tackle this? I am on a shared MySQL server so I cannot do tweaking there. Is this a PHP cache issue? Is it a connectivity issue to MySQL? Any direction on this is appreciated!

johanafm

Square1;10992153 wrote:
The FIRST time I go a page with a MySQL query it takes forever to load. When I refresh, it is fine.

This does sound like a possible cache issue, but it might be something else as well. I would still run EXPLAIN on the queries involved and see if you are missing an index, could provide compound indices (multiple column indexes), if restructuring the query would help or if you could perform ordering on a result set with as few columns as possible before joining in all columns you to avoid file sort.

This is pure speculation, but I believe the same query is likely to produce the same execution plan in MS SQL and MySQL. Still it's entirely possible, perhaps even likely, that some queries will produce different execution plans since the DBMSs do not share the same optimization code. Thus, one query might be more efficient in one DBMS while another is more efficient in the other DBMS, or one DBMS might always be more efficient.

Square1;10992153 wrote:
If I wait 3 minutes or so and then refresh, the problem returns and it takes forever

If this happens without other intermittent page requests, thus without other queries being executed, by you or other users, a cached query would not have expired (afaik). Thus, the issue would not be caching, but something else.

Square1;10992153 wrote:
It sounds like a cache issue. Where do I start to tackle this

Not only do I lack the expertise to help out in this regard, but I've heard experts in the area say that giving general answers to configuration options from an optimization perspective is impossible since it all depends on what queries you execute, how often, how many differnet queries you execute, what the mix between selects and inserts/updates there are etc. In one case we actually improved performance by disabling the query cache entirely.

Square1;10992153 wrote:
Is this a PHP cache issue?

Not if it's related specifically to the DB. However, caching things outside the DB can still be a good idea and give you performance gains. Memcached does an excellent job for some stuff. Still, considering your problem description, I'd say you have issues with the DB that needs solving, regardless of wether using other caching techniques would help or not.

Square1;10992153 wrote:
Is it a connectivity issue to MySQL? Any direction on this is appreciated!

I'd give the MySQL forums a go. You are more likely to get the help you need there since this seems to be problems related directly to MySQL, and you are more likely to find people with expertise in MySQL there than you are here.

NogDog

One other option to consider with MySQL is which storage engine you are using on the table(s) in question, with MyISAM probably giving the best response time for reads (selects), but at the cost of not supporting some functionality that other engines such as INNODB support. However, I'd suspect that would be more of a fine-tuning issue in most cases over other optimizations mentioned above.

Or you could convert to MongoDB. 🙂

Square1

Well, as it turns out, the hosting co moved me to a different shared server and voila! problem gone.

Thank you!