Extract required data from huge DB about 40 GB: Taking too much time to filter data and get required results
| Recommend this page to a friend! |
All requests  |
> | Extract required data from huge DB ab... | > | Request new recommendation | > |  Featured requests |
> |  No recommendations |
Extract required data from huge DB about 40 GB #fast database retrieval
by Siva Kumar - 9 years ago (2015-06-19)
Taking too much time to filter data and get required results
  +3 | I am working in system which connects to multiple channels like booking.com, hrs.com, centralR, etc. nearly about to 50 channels. The working system takes input data from hotels and send them to these channels for online reservation. There is page which shows the each update sent to channel received from hotels base for selection of hotel, room type, etc.. We maintain the last 3 months data which is nearly about 40 GB. This DB is well designed and indexed. Can any one please suggest a package which can improve retrieving from this data. |
- 1 Clarification request
1.
by Manuel Lemos - 9 years ago (2015-06-19) ReplyI suspect there is not PHP class for that problem, if this is a matter of speed of queries to the database.
I think you need to figure which database queries are taking too long.
If you are using MySQL, then you can use the EXPLAIN command followed by the slow query and it will tell you what clauses are using indexes or not.
Try to do that for your slowest query and reply here showing the query and the output of the EXPLAIN command, so we can advise.
2.
by Dave Smith - 9 years ago (2015-06-19) in reply to comment 1 by Manuel Lemos CommentI have to agree, that it is most likely a poorly designed query, assuming that the database is truly well designed and indexed.
Another possibility is scalability issues, how much traffic is your site getting?
1 Recommendation
QDataObject: Access MySQL query results as objects
It can take a MySQL query result set resource handle as parameter and provides several types of functions to access the data from the result set.
It provides check or retrieve the result set data positions, as well retrieve the type and length of the respective columns.
The package provides implementations that use the regular MySQL and MySQLi extensions to access MySQL query results.
New:
DataObjectFactory
=== QMysqlDataObject ===
Test => SELECT * FROM geodb_textdata
TEST ENV:
Windows Vista Xampp, AMD Turion 64x2 Mobile 1.80 GHz
2 MB RAM (average performance)
1.a) object building runtime 0.176911115646s
on a db table with 337.925 records
(building uncached dataobject from uncached query at page offset 22210 by 10 rows/page)
1.b) object building runtime 0.275130033493s
on a db table with 337.925 records
(building uncached dataobject from uncached query at page offset 3000 by 100 rows/page)
2.a) object building runtime 0.0171821117401s
on a db table with 337.925 records
(building uncached dataobject from uncached query at page offset 10 by 10 rows per page)
2.b) object building runtime 0.0609588623047s
on a db table with 337.925 records
(building uncached dataobject from uncached query at page offset 10 by 100 rows per page)
3)
SELECT * FROM orders
LEFT JOIN customers on customers.CustomerID=orders.CustomerID
(northwind demo database)
object building runtime 0.055801153183s
(building uncached dataobject from uncached query at page offset 1 by 10 rows per page)
Mysql DB times:
Status runtime
(initialization) 0.0000377
Opening tables 0.0002247
System lock 0.0000032
Table lock 0.0000067
init 0.0000107
optimizing 0.0000037
statistics 0.0000107
preparing 0.00001
executing 0.0000467
Sending data 0.0002505
end 0.0000042
query end 0.0000035
freeing items 0.0000077
closing tables 0.000002
removing tmp table 0.0001105
closing tables 0.0000042
logging slow query 0.0000025
Conclusion:
Object building becomes much quicker if you operate with sql conditions.
The test above shows, that a cut on a deep offset is slow. So you have to reduce the data cloud by conditioning and so on.
(On a Linux Web server it will run much faster.)
Regards Tom Schaefer
+2 |
Looking again there is this MySQL database access wrapper that logs slow queries. This may not optimize your application automatically but it may help detecting which queries are taking too long and probably need to have the be rethought or added adequate indexes. |
| Recommend package | |
|
|
