SQL VS Temps de réponse
Bonjour à tous !
Voici ma question, c'est au sujet de la performance.
J'utilise cakephp qui gère les "transactions".
Je dois enregistrer en une traite + de 100 records dans une table.
J'ai effectué 2 Tests.
1) Premier test avec la requête de cakephp.
Elle gère les transactions:
Nombre de INSERT TO: 100
Temps de réponse: +- 30-60 MS
2) J'ai généré mon propre INSERT TO
Nombre de INSERT TO: 1
Temps de réponse: +- 60-110 MS
Le temps de réponse est plus bas via la requête de cake, mais il effectue + de 100 queries sur la base.
Par contre en query direct, je n'est qu'une seuls Query mais un MS + élever.
Alors, il vaut mieux réduire le nombre de query sur le serveur au risque d'un MS + élever ou alors bcp de query mais un MS bas ?
D'avance MERCI
2 réponses
Bien sur :
Voici ma requête perso, seulement 6 requete mais 84ms :
(default) 6 queries took 84 ms
Nr Query Error Affected Num. rows Took (ms)
1 INSERT INTO `queries` (`query`, `user_id`, `public`, `created`) VALUES ('Hello !', 1, '1', '2012-01-07 19:25:24') 41
2 SELECT `Contact`.`id`, `Contact`.`user_id` FROM `contacts` AS `Contact` WHERE `Contact`.`contact` = 1 99 99 0
3 SELECT `User`.`id`, `User`.`mail`, `User`.`username`, `User`.`query_mail` FROM `users` AS `User` WHERE `User`.`id` IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 1) 100 100 1
4 INSERT INTO answers (reply, query_id,user_id) VALUES (3,15,1), (3,15,2), (3,15,3), (3,15,4), (3,15,5), (3,15,6), (3,15,7), (3,15,8), (3,15,9), (3,15,10), (3,15,11), (3,15,12), (3,15,13), (3,15,14), (3,15,15), (3,15,16), (3,15,17), (3,15,18), (3,15,19), (3,15,20), (3,15,21), (3,15,22), (3,15,23), (3,15,24), (3,15,25), (3,15,26), (3,15,27), (3,15,28), (3,15,29), (3,15,30), (3,15,31), (3,15,32), (3,15,33), (3,15,34), (3,15,35), (3,15,36), (3,15,37), (3,15,38), (3,15,39), (3,15,40), (3,15,41), (3,15,42), (3,15,43), (3,15,44), (3,15,45), (3,15,46), (3,15,47), (3,15,48), (3,15,49), (3,15,50), (3,15,51), (3,15,52), (3,15,53), (3,15,54), (3,15,55), (3,15,56), (3,15,57), (3,15,58), (3,15,59), (3,15,60), (3,15,61), (3,15,62), (3,15,63), (3,15,64), (3,15,65), (3,15,66), (3,15,67), (3,15,68), (3,15,69), (3,15,70), (3,15,71), (3,15,72), (3,15,73), (3,15,74), (3,15,75), (3,15,76), (3,15,77), (3,15,78), (3,15,79), (3,15,80), (3,15,81), (3,15,82), (3,15,83), (3,15,84), (3,15,85), (3,15,86), (3,15,87), (3,15,88), (3,15,89), (3,15,90), (3,15,91), (3,15,92), (3,15,93), (3,15,94), (3,15,95), (3,15,96), (3,15,97), (3,15,98), (3,15,99), (3,15,100) 40
5 SELECT `Contact`.`id`, `Contact`.`user_id` FROM `contacts` AS `Contact` WHERE NOT (`Contact`.`user_id` IS NULL) AND `Contact`.`contact` = 1 99 99 1
6 SELECT `User`.`id`, `User`.`username` FROM `users` AS `User` WHERE `User`.`id` IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100)Code du sql perso :
$q = "INSERT INTO answers (reply, query_id,user_id) VALUES ";
foreach( $users as $user )
{
if($index != 0) $q .= ", ";
$q .= "($reply,".$d'Query']'id'].",".$user'User']'id'].")";
$index++;
}
$this->Answer->query($q);voici le résultat avec Cake, 105 queries mais a peine 38ms :
(default) 105 queries took 38 ms
Nr Query Error Affected Num. rows Took (ms)
1 INSERT INTO `queries` (`query`, `user_id`, `public`, `created`) VALUES ('Hello !', 1, '1', '2012-01-07 19:28:16') 34
2 SELECT `Contact`.`id`, `Contact`.`user_id` FROM `contacts` AS `Contact` WHERE `Contact`.`contact` = 1 99 99 1
3 SELECT `User`.`id`, `User`.`mail`, `User`.`username`, `User`.`query_mail` FROM `users` AS `User` WHERE `User`.`id` IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 1) 100 100 1
4 INSERT INTO `answers` (`query_id`, `user_id`, `reply`, `created`) VALUES (1, 300, 1, '2012-01-07 19:28:17') 0
5 INSERT INTO `answers` (`query_id`, `user_id`, `reply`, `created`) VALUES (1, 500, 0, '2012-01-07 19:28:17') 0
6 INSERT INTO `answers` (`query_id`, `user_id`, `reply`, `created`) VALUES (19, 3, 3, '2012-01-07 19:28:17')
******Executé 100 fois** /
104 SELECT `Contact`.`id`, `Contact`.`user_id` FROM `contacts` AS `Contact` WHERE NOT (`Contact`.`user_id` IS NULL) AND `Contact`.`contact` = 1 99 99 1
105 SELECT `User`.`id`, `User`.`username` FROM `users` AS `User` WHERE `User`.`id` IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100)Le code pour avec cake :
foreach( $users as $user )
{
$answers_list$index]'query_id'] = $this->Query->id;
$answers_list$index]'user_id'] = $user'User']'id'];
$answers_list$index]'reply'] = $reply;
/*if($index != 0) $q .= ", ";
$q .= "($reply,".$d'Query']'id'].",".$user'User']'id'].")";*/
$index++;
}
//$this->Answer->query($q);
if($this->Answer->saveMany($answers_list, array( 'fieldList' => array('query_id','user_id', 'reply'))))
{
}else
{
$this->Session->setFlash(__("Error: We can't save one or many answers"),"notif", array('stype' => 'error-message'));
}