Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL
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 20-Aug-14 21:07pm
Comments
codejet at 21-Aug-14 2:32am
   
Please improve your question . You did not provide the structure of the tables.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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.
  Permalink  
Comments
Jörgen Andersson at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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.
  Permalink  
Comments
thesarath at 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)



Advertise | Privacy | Mobile
Web02 | 2.8.150129.1 | Last Updated 29 Aug 2014
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100