Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is first time i am working with huge database ( 100 million rows in table Events).

Use case here is to quickly select and insert x and y for selected Organisation and datetime.

My database knowledge is very basic, so could you please provide me some tips/articles/best practices for situations like my ( huge table, about 500 inserts per second, required fast selects (maximum delay from inserting data to selecting it is 3 seconds).

Also - is there any mistakes in my schema ? ( http://sqlfiddle.com/#!6/24bc5 ).

CREATE TABLE [dbo].[Organisations](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nchar](100) NOT NULL,
 CONSTRAINT [PK_Organisation] PRIMARY KEY (Id) ) ;


CREATE TABLE [dbo].[Events](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [OrganisationId] [int] NOT NULL,
    [x] [decimal](18, 10) NOT NULL,
    [y] [decimal](18, 10) NOT NULL,
    [datetime] [datetime] NOT NULL,
 CONSTRAINT [PK_Events] PRIMARY KEY (Id)) ;

CREATE NONCLUSTERED INDEX [EVENTS_INDEX] ON [dbo].[Events]
(
    [OrganisationId] ASC,
    [datetime] ASC
)
INCLUDE (   [Id],
    [x],
    [y]);


ALTER TABLE [dbo].[Events]  WITH CHECK ADD  CONSTRAINT [fk_events_organisations] FOREIGN KEY([OrganisationId])
REFERENCES [dbo].[Organisations] ([Id])
GO
share|improve this question

migrated from stackoverflow.com Dec 9 '13 at 20:58

This question came from our site for professional and enthusiast programmers.

closed as too broad by Mark Storey-Smith, RolandoMySQLDBA, Max Vernon, bluefeet, Paul White Dec 10 '13 at 3:05

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Hardware is going to play a role -- what are you using on the hardware side. eg, do you expect to achieve this with 2 GB of memory on the server? –  Hogan Dec 9 '13 at 20:39
1  
"required fast selects" --- how are we supposed to give any advices if we don't see the corresponding queries? –  zerkms Dec 9 '13 at 20:40
1  
You can remove the ID column from the INCLUDE list for the NC index since it's already included as a pointer to the CI record –  JNK Dec 9 '13 at 20:59
    
Lets start saying you have a SMALL table - nothing large here. Second - how are you going to query? The select speed is determined by the RIGHT index, and the right index by the queries. Your current index will help if you look by DATETIME and THEN (!) filter by x and y. Only. 3 seconds is plenty, btw.... on decent hardware I Would expect way way way below 1 second responses. And 500 inserts per second - I think I do 70.000 per second on my main insert application. Small. Really. THis is 2013, not 1990. –  TomTom Dec 9 '13 at 21:13
add comment