Hii,
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
TABLE [history_data](
[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
(
[INT_DATETIME] ASC,
[INT_POINTID] ASC
)
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 ?
Also, make sure you have a large auto-grow amount on the data files. (Make sure you have IFI turned on, although that affects only INSERT speed).
If possible, make sure the log never needs to dynamically grow but has already been pre-allocated.