英文:
Laravel raw select query takes 15 times longer when selecting VARBINARY(MAX) column
问题
I noticed a weird behavior with my code.
我注意到我的代码出现了奇怪的行为。
I have a table that stores users' images in a VARBINARY(MAX) format (Not the best, I know, needs to be changed)
我有一个表，以 VARBINARY(MAX) 格式存储用户的图像（我知道不是最佳选择，需要更改）。
Now, I am trying to select the top 5000 users images.
现在，我正在尝试选择前 5000 个用户的图像。
When I type the following query in MS SQL, it finishes in 7 seconds:
当我在 MS SQL 中输入以下查询时，它在 7 秒内完成：
select top(5000) user_id, user_photo from user_photos
When I do the same query with Laravel's raw query, it takes 110+ seconds on average:
当我使用 Laravel 的原始查询执行相同的查询时，平均需要 110 秒以上：
$photos = DB::select("select top(5000) user_id, user_photo from user_photos");
Now, if I remove the user_photo from the select, it takes less than 0.3 of a second:
现在，如果我从选择中删除 user_photo，则不到 0.3 秒：
$photos = DB::select("select top(5000) user_id from user_photos");
So it has to do something with the VARBINARY(MAX) column that holds really long strings, which doesn't play well with PHP or Laravel
因此，这与存储非常长字符串的 VARBINARY(MAX) 列有关，这与 PHP 或 Laravel 不兼容。
But why?
但为什么呢？
英文:
I noticed a weird behavior with my code.
I have a table that stores users' images in a VARBINARY(MAX) format (Not the best, I know, needs to be changed)
Now, I am trying to select the top 5000 users images.
When I type the following query in MS SQL, it finishes in 7 seconds:
select top(5000) user_id, user_photo from user_photos
When I do the same query with Laravel's raw query, it takes 110+ seconds in average:
$photos = DB::select("select top(5000) user_id, user_photo from user_photos");
Now, if I remove the user_photo from the select, it takes less than 0.3 of a second:
$photos = DB::select("select top(5000) user_id from user_photos");
So it has to do something with the VARBINARY(MAX) column that holds really long strings which doesn't play well with PHP or Laravel
But why?
答案1
得分: 3
SSMS默认不在内存中存储或显示blob列中的所有数据，因此在通过网络获取所有数据时，它不会将其全部加载到内存中或呈现出来。而且SSMS中的数据获取代码路径经过了良好的优化。关于Laravel的做法我不清楚。
在您的应用程序中，应该避免从数据库中检索5000张照片。而是按实际需要逐个检索它们。
英文:
SSMS does not store in memory or display all data in blob columns by default, so while it's fetching all the data over the network it's not loading it all into memory or rendering it. And the data fetching code path in SSMS is pretty well optimized. No idea about Laravel is doing.
In your application you should avoid retrieving 5000 photos from the database. Instead retrieve them one-by-one as actually needed.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论