your are correct ,
i have put an index on both send time and msgid and the query took 0.00001 sec >> before it took 0.28 sec
The SQL Connector in Diafaan SMS Server uses the separate SELECT statement because for GSM modems the number of unique Message IDs is limited and a general UPDATE status would also update older messages with the same message ID.
If you are using an SMPP server with unique message IDs to send the messages or if you don't mind that there is a small chance that older records are falsely updated, you can remove the 'Index' value in the 'SMS log' settings of the SQL Connector properties. When this value is empty, Diafaan SMS Server skips the SELECT statement and directly uses the Message ID value in the UPDATE statement.
If you have not already done so, it is also a good idea to add a database index to the 'MessageId' field (plus optionally the 'SendTime' field) of the 'MessageLog' database table. This will greatly improve the speed of the status update procedure.
yes you are correct ,, but actully we need the data on sms log ,,
is it possible to add a new feature that do the folowing :
1- take msg from msg out table
2- update the table using msgid not id ( this will improve the performance ) ,, so no need to do select statement before update
UPDATE message_smpp_log SET statusCode=201,errorCode='',errorText='',receiveTime=_binary'2013-11-28 20:23:00' WHERE id=322997;
SELECT id FROM message_smpp_log WHERE messageId='1385659344662107240' ORDER BY sendTime DESC LIMIT 1;
UPDATE message_smpp_log SET statusCode=201,errorCode='',errorText='',receiveTime=_binary'2013-11-28 20:23:00' WHERE id=323059;
SELECT id FROM message_smpp_log WHERE messageId='1385659344615107185' ORDER BY sendTime DESC LIMIT 1;
UPDATE message_smpp_log SET statusCode=201,errorCode='',errorText='',receiveTime=_binary'2013-11-28 20:23:00' WHERE id=323249;
SELECT id FROM message_smpp_log WHERE messageId='1385659412381107845' ORDER BY sendTime DESC LIMIT 1;
UPDATE message_smpp_log SET statusCode=201,errorCode='',errorText='',receiveTime=_binary'2013-11-28 20:23:00' WHERE id=323345;
If the MessageLog table is very large it might take some time to update the status of the messages and this could explain the delay in the uptake of new messages from the MessageOut table.
It is difficulty to say what the maximum number of log records in the database should be because that depends on the speed of the database but if you send large amount of messages it is probably best to clear the MessageLog table before adding a new batch of messages to the MessageOut table.
I am using local mysql db ,, I have noticed that diafaan trying to update the log table with delivery report ,, so may this cause the latency ..
consider send 10,000 and after that you try to send 10 msg and you isnert the records into out table ,, diafaan is busy with updating incoming dlr report
is this caused latency ?
Sending 5000-10000 messages with the SQL Connector should not result in large delays if the database connection is fast and reliable enough. Do you use a local database or is the database server installed on another server?
Do you see any error messages in the event log of the SQL Connector?
I have a Diafaan SMS Server full edition 184.108.40.206 , I have noticed when i send large amount of sms ( example 5000 sms or 10,000 ) the db connector take long time to take a new msgs from sms out table . even if the all 5,000 are sent , the connector hang for some times and then start to take the pending msgs from the out table ..
db is Mysql .
maximum batch size is 1000
check interval 5 sec
Most Users Ever Online: 494
Currently Browsing this Page:
Guest Posters: 405
Newest Members:, Henk Helmantel
Administrators: Henk Helmantel: 641