Speeding up cross server queries

[Phil.]

Hi

I am having some troubles with the performance of a report which pulls data from a few servers which I hope someone can give me a pointer or 2 with.

The way I'm doing it at the moment is connecting to each server's database in turn, running the queries I need to and storing the data in one big array before doing some calculations on and outputting.

It works well for smaller amounts of data but when large date ranges are pulled it pretty much grinds to a halt. It's not the query themselves as they run fast enough on the local host.

If anyone has any experience with this or could suggest some things which may help speed up cross server querying, i'd appreciate it.

Thanks

[Frost]

SQL is the shit. Need to pull some complex data, it's much faster to use a huge SQL query than it is to pull data you don't need - transfer it over a connection - then process it with a slow scripting language. I've seen 100+ line SQL queries run in milliseconds. You can be fancy and use multi threading to pull data from both servers at once which will make the time taken to execute the queries less noticeable, but won't help much with the transfer times.

[Jayy]

Large SQL databases seem to fuck servers up and grind them to a halt as you say. I've spent hours on 2gb + databases trying to back them up and then restore them and had to break them down into small chunks in order to do it.

I don't know of any way to do what you're saying though unfortunately.

[ajag]

which database server are you using? SQL server, oracle, mySQL?
____________________
Street Triple

[panrider_uk]

[ajag]

[ajag]

[Phil.]

Hi

I'm running PHP & MySQL.

Here's part of the cross database connection class

[ajag]

hmmm, I am not good with PHP so can't really comment on your code. However, if the query returns ok for one server and not hte other, I would probably start looking at the differences between the servers and the infrastructure around them.

How many rows is your query returning? I know for a fact that in MS SQL Server if your SQL returns more than 75k rows (I believe) it locks down the whole table for you and no one can read or write to it until you are done. Not sure if the same is true for MySQL but maybe another system is doing that for you?
____________________
Street Triple

[Kai.Wilson]

could be your server blocking multiple conections,

I know some dont allow for then 10 conections at a time from anyone IP to stop DDoS attacks.

[TQ]

It might be unrelated and might not even be right but it looks like the "mysql_close($this->link($site)); " line is not being executed because it is after the "return" statement.

In other languages once a function returns no code after that line will be executed, don't know if it's the same in php.
____________________
First Bike :Gilera DNA 50 | Second: Imported 1989 NSR125
Third: 1998 Yamaha XJ600N | Currently: 1999 Yamaha FZS600

[Frost]

He's right it's not closing the connection. Also make sure your sanitising your database inputs.

What is the actual query that's running?

[Phil.]

Hi

Thanks for pointing out that bug and all the suggestions that have been made.

More calculations are being done in PHP than are necessary so I'm going to move as much to sql as possible. Also going to try experimenting with table views to see if that has much impact on the speed.

Thanks

[Kickstart]

Hi

Views in MySQL are just for convenience. They have no performance benefits.

While keeping it in an SQL query will give the best performance, if you need multiple queries on the same database to get a final amount of info (which would suggest a poor database design, but sometimes necessary) then it might be best to have a php script running on the other database servers to do the intermediate processing, and only return the final info required.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing

[Frost]

If it's an unavoidable mega query that takes minutes to perform even when optimised, you can run it as a scheduled task. E.g Pull all data from everywhere and do loads of processing every hour, store result in table, pull from that table when needed. Obviously some data will be up to an hour out of date, but sometimes that's acceptable.