使用SPQuery从某些列表中获取项目时，性能非常非常慢 [英] Very very slow performance when using SPQuery to get items from a certain lists

问题描述

我们有一个运行中的系统，最近发现某些图表开始需要很长时间才能加载，最终超时.

We have a live system and recently noticed certain charts started to take a long time to load and eventually timeout.

有问题的列表有9k个项目.经过一些调查，我们发现通过SPList.Items获取项目需要6到8秒的时间.使用空的SPQuery对象获取项目可能需要3分钟以上的时间.设置 spquery对象上的viewfields属性，以便我只获得标题即可在1-2秒内获得结果.然后，当我添加更多视域时，性能开始变得越来越差.似乎向视场中添加某些查找确实有害 对性能的影响.例如，如果我将视场对象设置为检索名为Brands的查找字段，则将花费一分钟以上的时间来检索项目.该查找指向的列表只有215个项目.

The list in question has 9k items. After carrying out some investigation we have found out that getting the items by SPList.Items takes between 6-8 seconds. Getting the items by using an empty SPQuery object can end up taking over 3 minutes. Setting the viewfields property on the spquery object so that I only get the title gets the results in 1-2 seconds. As I then add more viewfields the performance starts getting worse and worse. It seems as if adding certain lookups to the viewfields has a really detrimental effect on performance. For example if I setup the viewfield object to retrieve a lookup field called Brands it takes over a minute for the items to be retrieved. The list this lookup points to only has 215 items.

除此以外，问题似乎不仅限于此列表，还不影响所有列表.我们有包含12k项(但字段很少)的列表，这些列表将使用spquery方法在1-2秒内检索数据.

In addition to this the problem doesnt just seem to be restricted to this list alone but it does not affect all lists. We have lists with 12k items (but with very few fields) that will retrieve the data in 1-2 seconds by using the spquery approach.

我认为数据库级别出现了问题.当然，GetItems所花费的时间不应该超过SPList.Items吗?

I think something has gone wrong at the DB level. Surely GetItems should not be taking longer than SPList.Items?

我想了解如何进一步解决此问题?也许是在数据库级别找出什么查询正在减慢速度等等?

I wanted to find out how do I go about troubleshooting this issue further? Perhaps to find out at the DB level what query is slowing things down etc?

推荐答案

您好，DaniyaalRaza，

Hi DaniyaalRaza, 

包含5000多个项目的列表中是否存在此问题? SharePoint有

Does the issue exists in the lists which contain more than 5000 items? SharePoint has resource throttles and limits that govern the amount of data and throughput that can be managed. The List View Threshold is by default, approximately 5000 items. 

CPU利用率高吗?如果是，请检查SQL Server CPU使用率，并检查CPU使用率最高.

是否有任何阻塞导致查询花费更多时间?

最好的问候，

丽莎·陈(Lisa Chen)

Is the CPU utilization high? If yes check the SQL Server CPU usage and check for top CPU consuming.

Is there any blocking causing the query to take more time?

Best Regards, 

Lisa Chen 

这篇关于使用SPQuery从某些列表中获取项目时，性能非常非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！