Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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?
Posted
Comments
codejet 21-Aug-14 2:32am    
Please improve your question . You did not provide the structure of the tables.

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.
 
Share this answer
 
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.
 
Share this answer
 
Comments
Jörgen Andersson 29-Aug-14 2:53am    
If you want to comment on a solution you press the button "Have a Question or Comment?" instead of adding another solution.
If you do that the responder will get a notification email.
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.
 
Share this answer
 
Comments
thesarath 2-Sep-14 2:58am    
Yes. I have already added an index on date column using dataview. Still its slow. I have also added a column for Id and have set Autoincrement to true.

Datatable queried from SQL - 4 seconds
Datatable queried from CSV - 6 seconds

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900