Level 102
Try using debugbar to see what is taking so long. How many records does it get?
Oct 6, 2022
19
Level 9
Simple database query takes forever
Hello,
my app is connected to oracle database. everything was fine until suddenly every query start to take ages to run. even simplest queries.
for example
select sysdate from dual
this query takes 0 seconds when I run it in sqldeveloper but this route takes about 10 seconds
Route::get('test', function () {
$date = DB::select('select sysdate from dual');
return $date;
});
what could be the issue!
Level 9
@Sinnbeck even if the query returns one record like the one above.
Level 102
@uniqueginun what if it does not do a query at all?
Level 9
@Sinnbeck the route response would take milliseconds
Level 102
@uniqueginun ok so it has to do with the connection to the database. What is you dev set up?
Level 9
@Sinnbeck you mean the database? I am connecting to Oracle 11g database using yajra/laravel-oci8 package.
Level 102
@uniqueginun ah ok. Do you have a mysql database that you can try using for comparison?
Level 73
@uniqueginun Is it a local Oracle DB? or a remote one?
Level 9
@Tray2 remote one
Level 9
@Sinnbeck I can try and do that
Level 102
@uniqueginun well if it's remote then maybe just try and set up a local one
Level 73
@uniqueginun This sounds more like it takes time to connect to the database rather than the query it self. Are you using ldap, tns or something else for the database name lookup?
Level 9
@Tray2 I am using TNS
Level 73
@uniqueginun What happens if you run tnsping on it?
Level 9
@Tray2 how can I do that from laravel?
Level 9
@Tray2 btw I tried to test the database connection process by this code
Route::get('test', function () {
try {
DB::connection()->getPdo();
if(DB::connection()->getDatabaseName()){
dd("Yes! Successfully connected to the DB: " . DB::connection()->getDatabaseName());
}else{
dd("Could not find the database. Please check your configuration.");
}
} catch (\Exception $e) {
dd("Could not connect to the database. Please check your configuration. error:" . $e );
}
});
this route took milliseconds to respond and it displayed my connection info
Level 73
@uniqueginun You need to run that from the OS, in a terminal
Level 51
Is that a valid syntax?
shouldn't it be like this?
$date = DB::table('dual')->select('sysdate')->get();
And what happen if you run the query directly in the database, does it take long time?
Try to run the query against Explain to see what's happening.
Level 9
@MohamedTammam yes it is a valid syntax you can run raw queries. when I run queries directly from database it is super fast. the app itself is fast when no database query is involved
Please or to participate in this conversation.