数据表性能 - SQL到Datatable和CSV文件到Datatable性能问题 [英] Datatable perfomance - SQL to Datatable and CSV file to Datatable perfomance issue

问题描述

我有一个数据表，有11,00,000（11万卢比）的记录。我从SQL或CSV文件中获取数据。我将数据保存在数据表中。我通常根据条件从这个数据表查询到另一个数据表。



从CSV文件中获取的表格

................... .....................

如果我使用Datatable.Select（）从这个数据表查询到另一个数据表，它需要11秒。





从SQL Server获取的表格

........... ............................

如果我使用Datatable.Select从这个数据表查询另一个数据表（ ），它只用了3秒。我已经在ID栏上设置了主键，并且正在使用datatable.Select（DateCol>'+ dtDate +'））



为什么会这样？造成这种差异的原因是什么？

Hi,
I have a datatable that have 11,00,000(11 lakhs) records. I get the data either from SQL or CSV file. I keep the data in a datatable. I usually query from this datatable to another datatable based on condition.

Table which is fetched from CSV file
........................................
If I query from this datatable to another datatable using Datatable.Select(), its taking 11 seconds.


Table which is fetched from SQL Server
.......................................
If I query from this datatable to another datatable using Datatable.Select(), its taking only 3 seconds.I have already set Primary key on the column "ID" and am using datatable.Select("DateCol>'"+dtDate+"'"))

Why its so? What is the reason for this difference?

推荐答案

听起来你可能会错过桌子上的几个索引。



尝试使用show execution plan选项集在MSSQL Management Studio的查询编辑器中运行select查询。它会告诉你时间花在哪里并建议缺少索引。

http://technet.microsoft.com/en-us/library/ms178071（v = sql.105）.aspx [ ^ ]



基本上你可以右键点击缺少索引的行导致...文本并选择类似'复制定义到新查询编辑器'的功能只有几个选项。

Sounds like you could be missing a few indexes on your table.

Try running your select queries in a query editor of MSSQL Management Studio with the show execution plan option set. it will tell you where the time is spent and suggest missing indexes.
http://technet.microsoft.com/en-us/library/ms178071(v=sql.105).aspx[^]

Essentially you can right click on the line with missing index is causing ... text and select functionality something like 'copy definition to new query editor' there are only a few options.

见托马斯先生。问题不在于SQL Server。从SQL Server获取的数据表中的数据工作正常，性能良好。在某些情况下，我从CSV文件而不是SQL Server中选择数据。在这种情况下，即使我使用Dataview索引数据表，从Datatable过滤也很慢。

See Mr.Thomas. The issue is not with SQL Server. The data in datatable which is fetched from SQL server works fine with good performance. In some condition I select data from CSV file rather than SQL Server. In that case filtering from Datatable is slow even though I used Dataview for indexing datatable.

从文件中读取总是会慢一些，因为即使你的视图上有索引，你也要你的csv文件上有一个rowid，这意味着你每次读取它都必须完全解析csv文件。

这比从数据库中的表读取要慢得多。

如果您对日期列进行过滤，您可能还应在日期列中添加索引。

Reading from a file will always be slower, because even if you have an index on your view, you don't have a rowid on your csv file, which means that the csv file must be fully parsed every time you read from it.
This is a much slower process than reading from a table in the database.
You should probably also add an index on your date column if you filter on it.

这篇关于数据表性能 - SQL到Datatable和CSV文件到Datatable性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！