RE: [PHP-DB] SQL very fast here & very slow there
Is it possible that the tables at school are not indexed?
berber
-Original Message-
From: Paolo Bonavoglia [mailto:[EMAIL PROTECTED]]
Sent: Saturday, January 25, 2003 7:55 PM
To: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] SQL very fast here & very slow there
At 16:43 24/01/2003 24/01/2003, John A DAVIS wrote:
>We had a very sluggish SQL Server 7.0 and we tested the RAM chips and
>found one to be bad. It was throwing off a whole bank of chips (1gb).
>Stuff is a lot faster now.
>Also, your indexes might be different on your tables. Indexing the
correct
>fields, especially the joined fields and ID fields used in searches,
>really helps big time.
>I'm no expert.
At 16:32 24/01/2003 24/01/2003, Brent Baisley wrote:
>I'm not sure how Paradox works, but it may be locking problems you are
>running into. I know Paradox used to have issues with data changing
while
>a query was executing. On your home machine you are the only one
accessing
>the DB, so there wouldn't be any contention. Your home DB probably hits
>the cache since the data isn't changing.
>How busy is the DB at school?
Thanks a lot for your suggestions; sometimes Paradox does have
locking problem ... but the DB at school is not very busy, and the
tables
and web pages I am using are still private, in a password protected
area.
Indexes are identical So I'm still in doubt. I have checked RAM and
indexes
at school but everything seems Ok.
And I've found something even more strange: I've splitted the
SQL
query in two: the first uses just the first two tables, and the output
table is then visited with a PhP while {} loop which at every step
execuste
a simple SQL SELECT on the third table; I thought this a much less
efficient method to make the web page, and so it is on my home PC, where
it
takes some 3 or 4 seconds to complete (instead of 1 sec or less); but on
the school server the web page takes "only" 15-20 sec, a lot less than
before (60sec and even more)! A 3 tables join is too much for the
server??
So I've found a (not so good) solution to the problem, but I
still
do not understand it's nature and this is quite disappointing.
>There is really nothing in your question applicable to PHP. Have you
>tried
>a Paradox list?
I'll try, but using the Paradox 9 program the SQL Select
executes
in a flash at home and at school, there is no measurable difference;
maybe
the problem is with the ODBC Paradox driver?
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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL very fast here & very slow there
At 16:43 24/01/2003 24/01/2003, John A DAVIS wrote:
We had a very sluggish SQL Server 7.0 and we tested the RAM chips and
found one to be bad. It was throwing off a whole bank of chips (1gb).
Stuff is a lot faster now.
Also, your indexes might be different on your tables. Indexing the correct
fields, especially the joined fields and ID fields used in searches,
really helps big time.
I'm no expert.
At 16:32 24/01/2003 24/01/2003, Brent Baisley wrote:
I'm not sure how Paradox works, but it may be locking problems you are
running into. I know Paradox used to have issues with data changing while
a query was executing. On your home machine you are the only one accessing
the DB, so there wouldn't be any contention. Your home DB probably hits
the cache since the data isn't changing.
How busy is the DB at school?
Thanks a lot for your suggestions; sometimes Paradox does have
locking problem ... but the DB at school is not very busy, and the tables
and web pages I am using are still private, in a password protected area.
Indexes are identical So I'm still in doubt. I have checked RAM and indexes
at school but everything seems Ok.
And I've found something even more strange: I've splitted the SQL
query in two: the first uses just the first two tables, and the output
table is then visited with a PhP while {} loop which at every step execuste
a simple SQL SELECT on the third table; I thought this a much less
efficient method to make the web page, and so it is on my home PC, where it
takes some 3 or 4 seconds to complete (instead of 1 sec or less); but on
the school server the web page takes "only" 15-20 sec, a lot less than
before (60sec and even more)! A 3 tables join is too much for the server??
So I've found a (not so good) solution to the problem, but I still
do not understand it's nature and this is quite disappointing.
There is really nothing in your question applicable to PHP. Have you tried
a Paradox list?
I'll try, but using the Paradox 9 program the SQL Select executes
in a flash at home and at school, there is no measurable difference; maybe
the problem is with the ODBC Paradox driver?
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] SQL very fast here & very slow there
I'm not sure how Paradox works, but it may be locking problems you are running into. I know Paradox used to have issues with data changing while a query was executing. On your home machine you are the only one accessing the DB, so there wouldn't be any contention. Your home DB probably hits the cache since the data isn't changing. How busy is the DB at school? There is really nothing in your question applicable to PHP. Have you tried a Paradox list? On Friday, January 24, 2003, at 04:16 PM, Paolo Bonavoglia wrote: 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. -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SQL very fast here & very slow there
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
