Solved

PHP 4.4.4, Apache 2.0.x and MS SQL 2005 SLOW/Performance Issues

Posted on 2006-10-27
5
718 Views
Last Modified: 2013-12-13
We just moved from MySQL to MS SQL ... It was a group decision based on our dept. needs.  

When we hosted MySQL previously it was on the same machine as Apache and was lightning fast.

Now that we've moved to MsSQL 2005 on a remote machine (still local network) with Apache our site are ALL super slow.

I have run any checks, but I have done some reading and it seems these is a common problem.

Pages w/o SQL are still quick and without delay.  I'm not too found of the soon to become extinct DLL that allows for connectivity between PHP in Windows w/ Apache and MS SQL.  But I don't think that's the problem (or could it be?).

Our PHP queries are simple... Select * which returns maybe 10 lines.  I'd say it's at least twice as slow, if now worse.

Any ideas?

Thanks!
0
Comment
Question by:jgantes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17825270
listening...

I have a similar problem with having mysql on another domain than the web server with asp.net + c# + mysql 4...


0
 

Author Comment

by:jgantes
ID: 17827109
I can't figure it out for the life of me...  It's slow as heck.  It can't JUST be that it's remote.  The servres are each Core DUO w/ 3GB RAM on their own Gigabit switch.  

And if you're having issues with MY SQL and not MS SQL, that says that maybe it's a PHP windows or Apache issue?
0
 

Author Comment

by:jgantes
ID: 17830403
Interesting... We rebooted and it was still slow.  But now, a day later, it's quick agian.  I can't figure this out!!  It must be something to do with Apache or PHP
0
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 190 total points
ID: 17848431
I'm been using PHP5 with Sambar Server and MS SQL 2000 (Developer Edition locally and Enterprise Edition in production).

I used to use the php_mssql.dll extension, but this was just too flawed.

Instead, I use the ODBC mechanism.

I have a simple OOP factory to wrap MS SQL, MS Access and Sage Retrieve 4GL databases.

Whilst ODBC is NOT the best way to go, it does provide a simple route for me.

In terms of performance, I'm very happy with it.

I DO use persistent connections.

I ALWAYS put the full table names in the SQL statements (db.owner.table) rather than just the table name as I have multiple SQL servers with multiple databases but using the same connection credentials.

I make use of views and stored procedures where appropriate. I also run the Index Optimizer on the queries on a semi regular basis to see if I can increase performance of the server.

Persistent connections are probably going to help and not using plain CGI (I think FastCGI for Apache supports persistent database connections).


Erm.

Other than that, just the usual stuff.

1 - Always make sure you name ALL the columns you NEED. Don't use SELECT * as this is wasteful.
2 - Take care when making joins to make sure you select the smallest amount of data on the left before you start joining/filtering. Not 100% sure that this makes any real difference any more for MSSQL, but certainly does for Access and Sage DBs. I can restructure the joins on a query and make it VERY VERY slow with Access - even though the results are 100% identical.
3 - Run the optimizer to make sure you have appropriate indexes available to speed the query.
4 - Sort the data only if needed - I was surprised to see how often I did not need the results sorted.
5 - Make sure the SQL server has access to as much RAM and Processors as possible.
6 - Make sure you have the right licenses for SQL server - too many connections may either result in a slow down on the server or no access!
7 - Do not run the development dbs on the same server as production as the memory will be effectively shared between the DBs. Sure, LRU and MRU mechanisms are in place but for the same of £35 for the 5 user Developer Edition, it sure isn't worth the hassle and slow down.
8 - Don't have compression enabled on the SQL data storage! You'd be surprised the number of times I've seen customers being cheap on upgrading disk space and have enabled compression and just how nicely SQL MDF/LDFs compress! Joy!
9 - Something I read, but have not been able to adequately test ... If your DB is read only with regard to a specific application, then use a db_readonly/db_denywrite role. I've read that this makes certain improvements, but I'm not sure (and I can't find the link - sorry).

Hope that helps some.
0
 

Author Comment

by:jgantes
ID: 17851873
There doesn't seem to be a concrete answer (so far) on this issue.  So thanks for the help RQ!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question