I have problem with SQL update. Sometimes it can take 2-10 seconds. I need
to make this update every time page is opened - ASYNCHRONOUSLY.
I have read forums, php.net etc about running php code 'in background' after
all data generated is received by user browser. However i cant simply use
it. Nothing works for me, im on a paid server.
I have checked the headers and all content my PHP outputs is zipped and send
compressed by apache (content-length header filled automatically). Is there
any way of running SQL query asynchronously to PHP script (so send query to
SQL, query is placed in some buffer and function returns immediatelly?)
TIA for help. 6 2007
'~=_Slawek_=~' wrote: I have problem with SQL update. Sometimes it can take 2-10 seconds. I need to make this update every time page is opened - ASYNCHRONOUSLY.
I have read forums, php.net etc about running php code 'in background' after all data generated is received by user browser. However i cant simply use it. Nothing works for me, im on a paid server.
I have checked the headers and all content my PHP outputs is zipped and send compressed by apache (content-length header filled automatically). Is there any way of running SQL query asynchronously to PHP script (so send query to SQL, query is placed in some buffer and function returns immediatelly?)
TIA for help.
There are many places that an SQL update can get bogged down... to name a few:
000) poorly designed logical and/or physical database
001) poorly written update statements
make sure the column you are updating is NOT the column in an index.
010) Number of rows in a non-indexed lookup to do the update
011) Number of users concurrently updating the same site
(locking issues)
100) System/Disk/Network latencies
since it is a "paid" service you have no control over this.
And since you have not provided us with any code segments to look at everything
is just speculation.
--
Michael Austin.
DBA Consultant
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)
Hello Michael.
Thanks for thinking over my problem. Your 4. assumption is right i cant tell
all other are wrong but I think they are. For my code:
mysql_query("UPDATE liczniki SET value=value+1 WHERE
owner_id='".$this->get_user_id()."' AND
owner_licznik_number='$owner_licznik_number'");
It takes 80ms to a couple of seconds, on shared hosting with DB overloaded
by other users (I assume some kind of write behind caching is responsible
for that). Table size is for now 100 records.
For table structure:
FIELD::TYPE::NULL::KEY::DEFAULT
id::int(10) unsigned::::PRI::NULL::auto_increment
owner_id::int(10) unsigned::YES::MUL::NULL::
owner_user::varchar(255)::YES::MUL::NULL::
owner_licznik_number::int(11) unsigned::::MUL::0::
value::int(10) unsigned::::::0::
type::enum('inny','online','wyswietlen','odwiedzin ')::::::inny::
sub_type::enum('tekstowy','graficzny')::::::teksto wy::
style::blob::YES::::NULL::
settings::blob::YES::::NULL::
last_updated::timestamp::YES::::0000-00-00 00
TIA.
I think even if your server is overloaded, is this happening every
time, every day, every minute? is your site getting a massive number
of hits?
I have seen before when you leave connections open, result sets not
freed, etc. this memory is not necessarily reallocated or destroyed and
thus everytime the system needs to allocate more memory for your PHP
script it may need to swap some to the disk, and this would cause your
latency. Remember to close all your connects and free all of your
result sets.
Alex http://prepared-statement.blogspot.com
'~=_Slawek_=~' wrote: Hello Michael.
Thanks for thinking over my problem. Your 4. assumption is right i cant tell all other are wrong but I think they are. For my code:
mysql_query("UPDATE liczniki SET value=value+1 WHERE owner_id='".$this->get_user_id()."' AND owner_licznik_number='$owner_licznik_number'");
It takes 80ms to a couple of seconds, on shared hosting with DB overloaded by other users (I assume some kind of write behind caching is responsible for that). Table size is for now 100 records.
For table structure: FIELD::TYPE::NULL::KEY::DEFAULT id::int(10) unsigned::::PRI::NULL::auto_increment owner_id::int(10) unsigned::YES::MUL::NULL:: owner_user::varchar(255)::YES::MUL::NULL:: owner_licznik_number::int(11) unsigned::::MUL::0:: value::int(10) unsigned::::::0:: type::enum('inny','online','wyswietlen','odwiedzin ')::::::inny:: sub_type::enum('tekstowy','graficzny')::::::teksto wy:: style::blob::YES::::NULL:: settings::blob::YES::::NULL:: last_updated::timestamp::YES::::0000-00-00 00
TIA.
Sounds like it may be time to find another host which doesn't overload
their servers like this. A simple update for 100 records shouldn't take
anywhere near 2 seconds.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp. js*******@attglobal.net
==================
Maybe you have some suggestions what host should I try? My hosting is very
good (cpanel, unlimited ftp, mail, domains, awstat, server logs, custom
error pages, directory protection, installed nearly all PHP extensions, 6
postgre + mysql database without limits, 10 GIG month transfer, 2GIG on disk
for only $35 yearly).
@Alex:
Yes i wrote its overloaded. Sometimes there is 10MBytes of data/sec taken
from DB (probably some site written badly).
I just taken some stats, the question is: should i change hosting. I mean I
only updated 25 X 4-byte fields that are not primary indexes and delay is
IMO huge (but im new to DB optimisation so I can be wrong). How slow itll
get when i finally have 2000 records table (its my destination, not much
IMO).
I guess that after 4'th update my table finally all go to cache the >WHOLE<
25 rows >LOL<.
/*[23:22:21][ 344 ms]*/ UPDATE liczniki SET value=1
/*(25 row(s) affected)*/
/*[23:22:21][ 406 ms]*/ UPDATE liczniki SET value=1
/*[23:22:22][ 609 ms]*/ UPDATE liczniki SET value=1
/*[23:22:22][ 219 ms]*/ UPDATE liczniki SET value=1
/*[23:22:22][ 63 ms]*/ UPDATE liczniki SET value=1
/*[23:22:22][ 62 ms]*/ UPDATE liczniki SET value=1
/*[23:22:22][ 63 ms]*/ UPDATE liczniki SET value=1
/*[23:22:22][ 47 ms]*/ UPDATE liczniki SET value=1
/*[23:22:22][ 47 ms]*/ UPDATE liczniki SET value=1
/*[23:22:22][ 63 ms]*/ UPDATE liczniki SET value=1
'~=_Slawek_=~' wrote: Maybe you have some suggestions what host should I try? My hosting is very good (cpanel, unlimited ftp, mail, domains, awstat, server logs, custom error pages, directory protection, installed nearly all PHP extensions, 6 postgre + mysql database without limits, 10 GIG month transfer, 2GIG on disk for only $35 yearly).
@Alex: Yes i wrote its overloaded. Sometimes there is 10MBytes of data/sec taken from DB (probably some site written badly).
I just taken some stats, the question is: should i change hosting. I mean I only updated 25 X 4-byte fields that are not primary indexes and delay is IMO huge (but im new to DB optimisation so I can be wrong). How slow itll get when i finally have 2000 records table (its my destination, not much IMO).
I guess that after 4'th update my table finally all go to cache the >WHOLE< 25 rows >LOL<.
/*[23:22:21][ 344 ms]*/ UPDATE liczniki SET value=1 /*(25 row(s) affected)*/ /*[23:22:21][ 406 ms]*/ UPDATE liczniki SET value=1 /*[23:22:22][ 609 ms]*/ UPDATE liczniki SET value=1 /*[23:22:22][ 219 ms]*/ UPDATE liczniki SET value=1 /*[23:22:22][ 63 ms]*/ UPDATE liczniki SET value=1 /*[23:22:22][ 62 ms]*/ UPDATE liczniki SET value=1 /*[23:22:22][ 63 ms]*/ UPDATE liczniki SET value=1 /*[23:22:22][ 47 ms]*/ UPDATE liczniki SET value=1 /*[23:22:22][ 47 ms]*/ UPDATE liczniki SET value=1 /*[23:22:22][ 63 ms]*/ UPDATE liczniki SET value=1
Well, you also get what you pay for. Hosting companies have to pay for
the servers, connections to the world, electricity, personnel, etc.
If they're going to charge a cheap price, they have to put a lot of
people on a server. And if your server is being overloaded by another
user (or multiple users), there's not much you can do about it.
You can request a different server from your host. If they move you,
you might or might not get better response. Or you can find another
hosting company. Again, if you go with a $35/yr hosting company you may
or may not get better response.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp. js*******@attglobal.net
================== This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: al |
last post by:
Greetings,
In the customers table in Northwind db, one can update PK
(customerid) and all other fields in the same table. My question is
how can you do this in the udpate stat. That is, if one...
|
by: Gent |
last post by:
am using FOR UPDATE triggers to audit a table that has 67 fields. My
problem is that this slows down the system significantly. I have
narrowed down the problem to the size (Lines of code) that need...
|
by: serge |
last post by:
/*
This is a long post. You can paste the whole message
in the SQL Query Analyzer.
I have a scenario where there are records
with values pointing to wrong records and I need to fix them
using an...
|
by: Niyazi |
last post by:
Hi,
I have not understand the problem. Before all the coding with few
application everything worked perfectly. Now I am developing Cheque Writing
application and when the cheque is clear the...
|
by: Maxi |
last post by:
There is a lotto system which picks 21 numbers every day out of 80
numbers.
I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21)
Here is the structure and sample data:
...
| |
by: abdoly |
last post by:
i wrote a code to update datagrid with the datagrid updatecommand but i cant get the updated values after being update
that is the code
private void DataGrid1_UpdateCommand(object source,...
|
by: Zorpiedoman |
last post by:
I keep getting a concurrency exception the second time I make a change and
attempt to update a dataadapter. It appears this is by design, so there must
be something I can do to avoid it.
...
|
by: cindy |
last post by:
this is the call
private void Page_Load(object sender, System.EventArgs e)
{
OdbcConnection connection = new OdbcConnection ("DSN=PFW52");
CreateDataAdapter(connection);
}
this is the code,...
|
by: =?Utf-8?B?UlBhcmtlcg==?= |
last post by:
I used the wizard to generate a typed dataset for my table and let it create
my SPROCs. It created everything, and the GetData() method and the custom
GetByUserName query works great, but when I...
|
by: SAL |
last post by:
Hello,
I have a Gridview control (.net 2.0) that I'm having trouble getting the
Update button to fire any kind of event or preforming the update. The
datatable is based on a join so I don't know...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |