大数据集的自动完成优化
我正在开发一个大型项目，我必须为用户提供将数据输入表单的有效方法。
该表单的三个字段需要来自公共数据源（SQL 表）子集的值。我使用 JQuery 和 JQuery UI 构建了一个自动完成功能，它将发布到通用 HttpHandler。
在内部，处理程序使用 Linq-to-sql 从该特定表中获取所需的数据。该表大约有 10 个不同的列，linq 表达式使用 SqlMethods.Like() 来匹配这 10 个字段中每个字段的单个搜索词。
问题是该表包含大约 20K 行。自动完成工作完美无缺，接受大量数据会带来延迟，在大约 6 秒左右（在我的本地计算机上调试时）才会显示。
JqueryUI 自动完成具有 0 延迟，对 3 键进行查询，并且发布的结果以 Facebook 风格的多行可选选项进行。 （我几乎不得不重写自动完成插件......）。
所以问题是数据与速度。关于如何加快速度有什么想法吗？我唯一的两个想法是缓存数据（如何/在哪里？）；或者使用直接的 sql 数据读取器进行数据访问？
任何想法将不胜感激！ 谢谢，
<bleepzter/>
I am working on a large project where I have to present efficient way for a user to enter data into a form.
Three of the fields of that form require a value from a subset of a common data source (SQL Table). I used JQuery and JQuery UI to build an autocomplete, which posts to a generic HttpHandler.
Internally the handler uses Linq-to-sql to grab the data required from that specific table. The table has about 10 different columns, and the linq expression uses the SqlMethods.Like() to match the single search term on each of those 10 fields.
The problem is that that table contains some 20K rows. The autocomplete works flawlessly, accept the sheer volume of data introduces deleays, in the vicinity of 6 seconds or so (when debugging on my local machine) before it shows up.
The JqueryUI autocomplete has 0 delay, queries on the 3 key, and the result of the post is made in a Facebook style multi-row selectable options. (I almost had to rewrite the autocomplete plugin...).
So the problem is data vs. speed. Any thoughts on how to speed this up? The only two thoughts I had were to cache the data (How/Where?); or use straight up sql data reader for data access?
Any ideas would be greatly appreciated!
Thanks,
<bleepzter/>
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我会考虑使用 .Take(10) linq 方法仅返回前 X 行。这应该转化为 sensbile sql 调用，这将大大减少数据库的负载。随着用户输入，他们会发现越来越少的匹配项，因此他们只会看到他们需要的数据。
我通常认为 10 个项目足以让用户了解正在发生的事情，并且仍然可以快速获取他们需要的数据（请参阅 amazon.com 搜索栏以获取示例）。
显然，如果您能够以有意义的方式对数据进行排序，那么这 10 个结果将更有可能快速为用户提供他们想要的内容。
I would look at only returning the first X number of rows using the
.Take(10)linq method. That should translate into a sensbile sql call, which will put much less load on your database. As the user types they will find less and less matches, so they will only see that data they require.I'm normally reckon 10 items is enough for the user to understand what is going on and still get to the data they need quickly (see the amazon.com search bar for an example).
Obviously if you can sort the data in a meaningful fashion then the 10 results will be much more likely to give the user what they are after quickly.
返回前 N 个结果肯定是个好主意。我们发现（查询 270K 的潜在列表）对于用户查找他们正在寻找的内容来说，返回前 30 条是更好的选择，但这完全取决于您正在查询的数据。
另外，您确实应该将延迟降低到 100-300 毫秒之类的合理值。当您将延迟设置为零时，一旦您按下 3 个字符的触发器，则每次都会有效。单身的。钥匙。中风。作为新查询发送到您的服务器。这很容易产生意想不到的和不受欢迎的效果，进一步减慢响应速度。
Returning the top N results is a good idea for sure. We found (querying a potential list of 270K) that returning the top 30 is a better bet for the user finding what they're looking for, but that COMPLETELY depends on the data you are querying.
Also, you REALLY should drop the delay to something sensible like 100-300 ms. When you set delay to ZERO, once you hit the 3-character trigger, effectively EVERY. SINGLE. KEY. STROKE. is sent as a new query to your server. This could easily have the unintended and unwelcome effect of slowing down the response even MORE.