Decided to uninstall SQL Express 2008 and install SQL Express 2005. Restored the database (same one I had originally backed up for the migration), load time for the one page was down to ~6 seconds from ~13 seconds.
Reindexed the database for fun and profit and checked consistency. Seemed fine (return code 0).
Try commenting out the echoing of the uid
create procedure TigAddUser(_user_id varchar(2049) CHARSET utf8, _user_pw varchar(255) CHARSET utf8)
begin
declare res_uid bigint unsigned;
insert into tig_users (user_id, sha1_user_id, user_pw)
values (_user_id, sha1(lower(_user_id)), _user_pw);
select LAST_INSERT_ID() into res_uid;
insert into tig_nodes (parent_nid, uid, node)
values (NULL, res_uid, 'root');
if _user_pw is NULL then
update tig_users set account_status = -1 where uid = res_uid;
end if;
-- select res_uid as uid;
end //
Try throttling the output of the ids in groups of 1250
create procedure TigUserGen(usr_count INT)
begin
DECLARE cnt,len,echo_count,echo_limit INT;
DECLARE usr CHAR(6);
SET cnt = 0;
SET len = 0;
SET usr = '';
SET echo_limit = 1250;
SET echo_count = 0;
WHILE cnt < usr_count DO
SET cnt = cnt + 1;
SET echo_count = echo_count + 1;
SET usr = CAST(cnt AS CHAR(6));
SET len = CHAR_LENGTH(usr);
CASE len
WHEN 1 THEN
call TigTestAddUser(CONCAT('+39100000', usr), CONCAT('pwd_00000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
WHEN 2 THEN
call TigTestAddUser(CONCAT('+3910000', usr), CONCAT('pwd_0000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
WHEN 3 THEN
call TigTestAddUser(CONCAT('+391000', usr), CONCAT('pwd_000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
WHEN 4 THEN
call TigTestAddUser(CONCAT('+39100', usr), CONCAT('pwd_00', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
WHEN 5 THEN
call TigTestAddUser(CONCAT('+3910', usr), CONCAT('pwd_0', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
ELSE
call TigTestAddUser(CONCAT('+391', usr), CONCAT('pwd_', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
END CASE;
IF echo_count >= echo_limit THEN
SELECT CONCAT('Users Generated : ',cnt) TigUserGenStatus;
SET echo_count = 0;
END IF;
END WHILE;
IF echo_count >= 0 THEN
SELECT CONCAT('Users Generated : ',cnt) TigUserGenStatus;
END IF;
end //
delimiter ;
call TigUserGen(200000);
Best Answer
Just a guess as there isn't enough information to really figure this out but it could be network latency. You're saying that local connection to local DB is fast but local to remote DB is slow. All other things being equal the only difference is the network connection between you (local) and the remote DB.
Generally AWS is pretty fast and has a stable network but you might notice latency issues for more chatty protocols. I'm not too familiar with the wire protocol for SQL Server but I know that Oracle for example is a relatively chatty protocol so it could be similar. As such you'll see much better performance when the network connection between the client and the server (the database) are on a low latency network with a minimal amount of hops.
Another issue could be if your local internet connection doesn't have much bandwidth (say a 1 Mb/s DSL) and you try streaming back a lot of rows from the remote database.
You should repeat your test from a Windows instance that's close to the RDS instance. I don't think you're allowed to remote into the RDS instance itself but you should be able to spin up a Windows instance that's in the same availability zone. That should be close enough to what it's like in a real world deployment.
Also, if you're planning on using the remote Amazon RDS instance as the database for an application that's not running on Amazon then you really need to test out the performance in advance. The intended use case is having your application running on EC2 so that you're on a low latency network with the DB. It would still work remotely but the performance will we wildly different.