[PHP-DB] SQL very fast here very slow there

[Paolo Bonavoglia]

I have realized a php web page with this heavy SQL query, necessary to 
show a list of students:

$sqlcom = SELECT Alunni.Cod_Alunno, Nome, Cognome, Scritto, Orale, Assenze
 FROM Alunni, Scrutini, Iscritti
 WHERE Alunni.Cod_Alunno = Scrutini.Alunno AND Scrutini.Alunno = 
Iscritti.Cod_Alunno
 AND Scrutini.Anno = Iscritti.Anno AND Iscritti.Anno = $Anno
 AND Iscritti.Classe = $Classe AND Scrutini.Materia = '$Materia'
ORDER BY Cognome, Nome;
$alunni  = odbc_connect(Orario, admin,  );
$cur = odbc_exec($alunni, $sqlcom);
...

The three tables are rather big (about 9000 records); they are Paradox 
tables which php sees through the Windows ODBC interface.

I have two copies of this DB one on my home PC (Athlon XP 1600, with 
Win-XP, Apache 1.3.27 and Php 4.3), the other on the server of my school 
(HP e200 Pentium 1000 with Windows 2000) which works as a web server with 
Apache 1.3.24 and Php 4.23.

Now something very strange happens: on my home PC the Select works 
perfectly and is executed in a flash, the web page appearing in less than 
one second.
On the Win2000 server the same SQL command behaves extremely slow and takes 
up to 60 seconds to execute and show, and is very often timed out. There is 
no difference executing the command directly on the server or from a PC 
connected to the server in the LAN.

I understand that a server has to share his time and resources ... but such 
a huge difference is really surprising for me. And I have many other SQL 
queries working decently on the server.

I've lookeed for differences between php.ini files, but found nothing of 
meaning.

Any ideas? Is there a way to speed up this command on the server?

Thanks to anybody which can help me!


Paolo Bonavoglia
---
Docente di Matematica e Fisica
Responsabile Lab.Informatica e sito Web

Liceo Classico Marco Foscarini
Cannaregio 4942
I 30121   VENEZIA

Tel. 041,5224845
Fax 041,5201657

E-Mail [EMAIL PROTECTED]

--
Pagine Web   http://www.liceofoscarini.it/



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] join query/relational database question

[Paolo Bonavoglia]

At 16:09 06/01/2003 06/01/2003, Doug Parker wrote:

I have a relational database issue that I'll simplify for the sake of this 
post.  [...] So it would be like this:

id | company_id | status|   company

1   46  Active  Jones Inc.
2   54  InactiveBaker 
Inc.


Using SQL the following SELECT should be the solution:

SELECT projects.id, company_id, status, company
FROM projects, clients
WHERE projects.company_id = clients.id

You can of course add any other filter in the WHERE clause using the usual 
AND OR NOT operators.

Of course you have to embed this command in an appropriate PhP statement, 
e.g. using the odbc library, and having an open connection $conn to the DB:

odbc_exec($conn, 'SELECT ... ');

Hope this will be enough to solve your problem.



Paolo Bonavoglia

Cannaregio 3027/R
30121 V E N E Z I A

Sito del Liceo Foscarinihttp://www.liceofoscarini.it/
Astronomia e 
Calendari  http://digilander.iol.it/paolobona/astro/home.html


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php