Hi,
I am running a service which will read a directory and gets the files in that directory, all these files are notepad files only, for every 1 hour time more and more text files will come and sit into that directory(I mean per day 500 mb notepad files will come and sit in that directory).
The service which I am running will read all notepad files and puts the content in the SQL server database in one table only, all the content in all notepad files will be the same(What i mean is content in the notepad will be seperated with comma(,), every row in the notepad is one row for me).
The problem is:
I have created only one table in the database, why i have created only one table is, it(Table) is enough for me to store all the content in the text files, the content in the notepad files is equal.
I have created indexes too in the table, for 3 fields, in query where condition is based on those 3 fields only.
As I have only one table, all the contents in the notepad files will get stored in the same table, the size of the content has tremendously increased to 10 gb of data and may go increase.
Now whenever I query the database it is taking too much of time.
For retrieving one day data( one day it will have 100000 rows in it) it is taking 30 minutes some times it is taking 1 hour to fetch the records.
I have developed one web application in asp.net which will query the databse for reports, the query is not getting executed, because it is getting time out error.
Please help me in solving this issue.
You suggest me the design of the database
or any other plan to implement the requirement.