|
|
Posted by tg-php on 08/17/05 17:50
Without wading through all the code and queries it might be difficult to give you an exact answer, but maybe some of us can point out a 'gotcha' or two that you can check.
One that jumps to mind that I had issues with using ADODB (abstraction layer), and it was mostly with speed but I could see it causing other issues, is connecting and disconnecting from the database too much.
Here's some pseudo code:
Bad -
foreach $insertlist as $insertdata {
dbconnect();
dbinsert($insertdata);
dbdisconnect();
}
Good -
dbconnect();
foreach $insertlist as $insertdata {
dbinsert($insertdata);
}
dbdisconnect();
I was doing it inadvertantly because of the way I had things structured and my use of includes and such. Once I figured out that was causing a LOT of slowdown, I was able to do just one connect and disconnect in a script with lots of insert, update, select, etc statements in the middle.
It's possible for a database to reach max # connections and such. If you have a lot of people hammering at a system at once, you may not be closing the connection properly and/or creating too many connections. SQL Server should handle a lot, but even it has it's limits.
A random factoid about SQL Server.. and I don't remember the specifics, but if you use @IDENTITY (?) to grab the last inserted ID, you'll get the last inserted ID for any user, not just the the current user who just did an insert. I believe you use @@INDENTITY (double @ sign) to specify the current user's last insert.
Some of our developers at my last job had an issue with hitting the wrong ID (on a trigger I believe it was) and causing a mess. I could see where something like that could cause a script to go nuts. Just a (probably unrelated) 'gotcha' when it comes to SQL Server.
I'm sure someone else has some snakebites they can share :)
-TG
= = = Original message = = =
We are using PEAR as our database abstraction layer for connectivity to
MSSQL. It seems that, for some inexplicable reason, that our code is
losing it's connection to the sql server.
We have a section of our site where users can come in and fill out a
form to get an insurance quote. Once the form is submitted, there is quite a
lot of back end database processing, both read and write, which is
happening against the same set of tables for every submission. If just a few
people access the site at the same time and submit the form concurrently,
things are alright. But once more people start doing so, it seems to cause the
sql server to hang. In the middle of the process, one of the users will
run a query which causes a problem (and it's not the same every time) and
in the middle of the process for the other users, when it goes to try to
connect to the database again (via one of the class' that are getting
instantiated and executed as part of the overall process), PEAR returns
an error saying there is no database selected. The DSN has the correct
data and it works when there are fewer users, but for some reason it
says that the db has not been selected.
So it seems that the cause of that error is in the connect method of the
mssql class. We are using persistent connections but for some reason
when it goes to try to execute the mssql_pconnect it seems as if the
connection PHP has to MSSQL has been severed? But whatever has
happened, the only resolution to the problem is restarting apache. As
soon as we restart apache, everything starts working again (for the most
part). Though, if multiple users go in again and submit the form, PEAR
returns that same error (no database selected), the system then hangs
and an apache restart is again required.
I've gone through all of our code and for the life of me I cannot figure
out what might be causing the connection to sever in such a way. As far as
I can tell, we aren't leaving any open transactions (though, the
symptoms seem to indicate that may be the root cause?) and we really aren't doing
anything funky with regards to the queries we are running against the
database (other than the sheer number of queries - the application requires
*alot* of database processing). So I'm wondering if anyone else has run up
against a problem similar to this.
We've run this test against 3 different servers and the exact same thing
(as described above) happens on all 3.
Server 1 specs:
Windows 2000
Apache 2.0.53
PHP 4.3.8
MSSQL Server 2000 Enterprise SP3
Server 2 specs:
Windows 2003
Apache 2.0.53
PHP 4.3.11
MSSQL Server 2000 Enterprise SP4
Server 3 specs:
Windows 2003
Apache 2.0.53
PHP 4.3.11
MSSQL Server 2000 Enterprise SP4
Any help, suggestions or ideas at all would be very appreciated and most
welcome!
thnx,
Chris
___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
Navigation:
[Reply to this message]
|