Handling Huge Data insert and retrieval from MS SQL Server 2005
Posted on 2014-04-03
I have developed a Software for that inserts large data in to a SQL Server 2005 table. I am facing issue when extracting the data from the table using queries, it takes hours to retrieve data from the table. My table columns are
[INT_DATETIME] [bigint] NOT NULL,
[INT_POINTID] [int] NOT NULL,
[REAL_VALUE] [float] NOT NULL,
[INT_QUALITY] [smallint] NULL,
CONSTRAINT [PK_history_data] PRIMARY KEY CLUSTERED
I have 15000 Points changing values every second. So I am inserting these 15000 records every second. Hence there will be more than 1 GB data in 1 hour and hence every day more than 20GB data will be there in the table. Is there any issue in storing such huge data with 1 table ?
Please help me how I can retrieve data fast from this table. I tried BCP command which seems to be faster than the direct select sql query, but that also takes time in hours to get the values. What should be the way to fetch data in seconds ?