什么导致我的Laravel应用程序放慢速度？

Question

我有一个使用Laravel 5.2和Eloquent ORM开发的应用程序。该应用程序连接到MS SQL Server以读取/写入数据。

我遇到了应用程序有时减慢性能问题“的页面需要至少5秒钟响应”

我安装了“发条”试试，看看有什么是需要长时间来执行。在深入了解多个请求之后，我注意到有些查询需要很长时间才能完成。

这是查询耗时约1600ms完成的一个示例。 （1600ms之间包括犯下和网络延迟。）

INSERT into [items] ([control_id], [created_at], [interview_id], [is_default], [item_id], [item_title], [item_type], [reporting_value], [updated_at]) 
values ('328', '2016-06-23 23:12:02', '6278', 'No', '1105', '1 (Poor)', 'Answer', '1', '2016-06-23 23:12:02'), 
('328', '2016-06-23 23:12:02', '6278', 'No', '1106', '2', 'Answer', '2', '2016-06-23 23:12:02'), 
('328', '2016-06-23 23:12:02', '6278', 'No', '1107', '3', 'Answer', '3', '2016-06-23 23:12:02'), 
('328', '2016-06-23 23:12:02', '6278', 'No', '1108', '4', 'Answer', '4', '2016-06-23 23:12:02'), 
('328', '2016-06-23 23:12:02', '6278', 'No', '1109', '5', 'Answer', '5', '2016-06-23 23:12:02'), 
('328', '2016-06-23 23:12:02', '6278', 'No', '1110', '6', 'Answer', '6', '2016-06-23 23:12:02'), 
('328', '2016-06-23 23:12:02', '6278', 'No', '1111', '7', 'Answer', '7', '2016-06-23 23:12:02'), 
('328', '2016-06-23 23:12:02', '6278', 'No', '1112', '8', 'Answer', '8', '2016-06-23 23:12:02'), 
('328', '2016-06-23 23:12:02', '6278', 'No', '1113', '9', 'Answer', '9', '2016-06-23 23:12:02'), 
('328', '2016-06-23 23:12:02', '6278', 'No', '1114', '10 (Excellent)', 'Answer', '10', '2016-06-23 23:12:02'), 
('328', '2016-06-23 23:12:02', '6278', 'No', '1608', 'Don''t Know', 'Others', '98', '2016-06-23 23:12:02'), 
('328', '2016-06-23 23:12:02', '6278', 'No', '1708', 'Does not have item/department', 'Answer', '99', '2016-06-23 23:12:02'), 
('338', '2016-06-23 23:12:02', '6278', 'No', '1205', '1 (Poor)', 'Answer', '1', '2016-06-23 23:12:02'), 
('338', '2016-06-23 23:12:02', '6278', 'No', '1206', '2', 'Answer', '2', '2016-06-23 23:12:02'), 
('338', '2016-06-23 23:12:02', '6278', 'No', '1207', '3', 'Answer', '3', '2016-06-23 23:12:02'), 
('338', '2016-06-23 23:12:02', '6278', 'No', '1208', '4', 'Answer', '4', '2016-06-23 23:12:02'), 
('338', '2016-06-23 23:12:02', '6278', 'No', '1209', '5', 'Answer', '5', '2016-06-23 23:12:02'), 
('338', '2016-06-23 23:12:02', '6278', 'No', '1210', '6', 'Answer', '6', '2016-06-23 23:12:02'), 
('338', '2016-06-23 23:12:02', '6278', 'No', '1211', '7', 'Answer', '7', '2016-06-23 23:12:02'), 
('338', '2016-06-23 23:12:02', '6278', 'No', '1212', '8', 'Answer', '8', '2016-06-23 23:12:02'), 
('338', '2016-06-23 23:12:02', '6278', 'No', '1213', '9', 'Answer', '9', '2016-06-23 23:12:02'), 
('338', '2016-06-23 23:12:02', '6278', 'No', '1214', '10 (Excellent)', 'Answer', '10', '2016-06-23 23:12:02'), 
('338', '2016-06-23 23:12:02', '6278', 'No', '1618', 'Don''t Know', 'Others', '98', '2016-06-23 23:12:02'), 
('338', '2016-06-23 23:12:02', '6278', 'No', '1721', 'Does not have item/department', 'Answer', '99', '2016-06-23 23:12:02'), 
('408', '2016-06-23 23:12:02', '6278', 'No', '1472', '1 (Poor)', 'Answer', '1', '2016-06-23 23:12:02'), 
('408', '2016-06-23 23:12:02', '6278', 'No', '1473', '2', 'Answer', '2', '2016-06-23 23:12:02'), 
('408', '2016-06-23 23:12:02', '6278', 'No', '1474', '3', 'Answer', '3', '2016-06-23 23:12:02'), 
('408', '2016-06-23 23:12:02', '6278', 'No', '1475', '4', 'Answer', '4', '2016-06-23 23:12:02'), 
('408', '2016-06-23 23:12:02', '6278', 'No', '1476', '5', 'Answer', '5', '2016-06-23 23:12:02'), 
('408', '2016-06-23 23:12:02', '6278', 'No', '1477', '6', 'Answer', '6', '2016-06-23 23:12:02'), 
('408', '2016-06-23 23:12:02', '6278', 'No', '1478', '7', 'Answer', '7', '2016-06-23 23:12:02'), 
('408', '2016-06-23 23:12:02', '6278', 'No', '1479', '8', 'Answer', '8', '2016-06-23 23:12:02'), 
('408', '2016-06-23 23:12:02', '6278', 'No', '1480', '9', 'Answer', '9', '2016-06-23 23:12:02'), 
('408', '2016-06-23 23:12:02', '6278', 'No', '1481', '10 (Excellent)', 'Answer', '10', '2016-06-23 23:12:02'), 
('408', '2016-06-23 23:12:02', '6278', 'No', '1648', 'Don''t Know', 'Others', '98', '2016-06-23 23:12:02'), 
('408', '2016-06-23 23:12:02', '6278', 'No', '1733', 'Does not have item/department', 'Answer', '99', '2016-06-23 23:12:02') 

当我直接用MS SQL Server Management Studio中它完成于00:00:00告诉我，数据库服务器运行正常执行这个查询，也没有数据服务器端的延迟。

由于插入了大量数据，我在考虑PDO花费很长时间来准备声明，这会导致延迟，因为这里有36行正在准备中。但是，问题也可能是PDO需要一段时间才能连接数据库，这就是为什么我看到延迟是一个会延迟整个应用程序的查询。

这是另一个简单的查询，它在1300 ms后返回1行，在MS SQL Management Studio中执行它时在00:00:01完成。

SELECT * FROM [stext] WHERE [interview_id] = '6278' and [control_id] in ('306') ORDER BY [id] ASC 

此查询没有太多的准备，但仍需要1300多万年才能完成。

Web服务器和数据服务器都在同一个网络和主机上。两者都位于同一个实体房间，所以不应该有任何网络问题。我甚至试图ping服务器，ping看起来很正常。

我如何知道是什么导致我的应用程序变慢？是PDO绩效问题，还是花言巧语需要很长时间才能做好准备？

我该如何解决这个问题？

为上面的查询，这是雄辩的代码，我使用

$records = array(array(...), array(...)); 
Item::insert($records); 

Answer 1

以我的经验，最好不要使用PDO对于批量插入与记录thundreds。这是缓慢的大量准备好的数据。 最好为此目的创建简单的查询。