1

I'm developing a project where I need to retrieve HUGE amounts of data from an MsSQL database and treat that data. The data retrieval comes from 4 tables, 2 of them with 800-1000 rows, but the other two with 55000-65000 rows each one.

The execution time wasn't tollerable, so I started to rewrite the code, but I'm quite inexperienced with PHP and MsSQL. My execution of PHP atm is in localhost:8000. I'm generating the server using "php -S localhost:8000".

I think that this is one of my problems, the poor server for a huge ammount of data. I thought about XAMPP, but I need a server where I can put without problems the MsSQL Drivers to use the functions.

I cannot change the MsSQL for MySQL or some other changes like that, the company wants it that way...

Can you give me some advices about how to improve the performance? Any server that I can use to improve the PHP execution? Thank you really much in advance.

Improve this question
1
  • 2
    65000 is really not a huge amount of data, it's not even in the same zip code as huge. I'm taking a wild guess here but I assume the problem lies in how the queries are built, that's assuming the server is not a $5 VPS. Commented Jul 23, 2015 at 12:01

3 Answers 3

Reset to default
1

The PHP execution should least of your concerns. If it is, most likely you are going about things in the wrong way. All the PHP should be doing is running the SQL query against the database. If you are not using PDO, consider it: http://php.net/manual/en/book.pdo.php

First look to the way your SQL query is structured, and how it can be optimised. If in complete doubt, you could try posting the query here. Be aware that if you can't post a single SQL query that encapsulates your problem you're probably approaching the problem from the wrong angle.

I am assuming from your post that you do not have recourse to alter the database schema, but if so that would be the second course of action.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

In fact, the retrieval from the Database isn't the main problem, since I can store the information retrieved from 4 tables in 4 arrays that sqlsrv functions gives you. I mainly think that my real problem is the usage of "localhost" and the treatment of some of the data retrieved...
You're pulling the data from each table into four separate arrays in PHP? If so, why is this necessary? When you speak of "treatment" of the data, what are you actually doing?
0

Try to do as much data processing in SQL Server as possible. Don't do data joining or other type of data processing that can be done in the RDBMS.

I've seen PHP code that retrieved data from multiple tables and matched lines based on several conditions. This is just an example of a misuse.

Also try to handle data in sets in SQL (be it MS* or My*) and avoid, if possible, line-by-line processing. The optimizer will output a much more performant plan.

Improve this answer

Comments

0

This is small database. Really. My advices:
- Use paging for the tables and get data by portions (by parts)
- Use indexes for tables
- Try to find more powerful server. Often hosters companies uses one database server for thousands user's databases and speed is very slow. I suffered from this and bought dedicated server finally.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.