2

I am continuously inserting value into 3 table having same structure

Table1 (col1 bigint primary key,col2 bigint,col3 int,col4 datetime,col5 varchar(400),col6 float,
    col7 numeric(18,6),col8 numeric(18,6),col9 float,col10 datetime,col11 int,col12 int,col13 bit,col14 varchar(20),col15 varchar(10))

Table2 (col1 bigint primary key,col2 bigint,col3 int,col4 datetime,col5 varchar(400),col6 float,
col7 numeric(18,6),col8 numeric(18,6),col9 float,col10 datetime,col11 int,col12 int,col13 bit,col14 varchar(20),col15 varchar(10))

Table3 (col1 bigint primary key,col2 bigint,col3 int,col4 datetime,col5 varchar(400),col6 float,
col7 numeric(18,6),col8 numeric(18,6),col9 float,col10 datetime,col11 int,col12 int,col13 bit,col14 varchar(20),col15 varchar(10))

I Store Data in Table1 for 1 day in Table2 for week and in Table3 for One Month On every day at midnight 1 am I delete yesterday data from Table1, 7 days ago data from Table2

when month change I change month table table3 (table3_jan,table3_feb etc)

I have set Non-index on all tables(Table1,Table2 etc.) (on column col2(bigint) and col4 (Datetime))

Currently I have 30 GB data in table3_sept and on daily basis it goes increases

data get inserted rapidly in above 3 table

Now when I fetch more than 7 days Data from Table3 and insert into #temp or other table all other insert operation get paused when query get execute then insert operation get resume.

select * into #temp from table3_aug where date between '1 aug 2017' and '17 aug 2017' order by id desc

or

select * into tablenew from table3_aug where date between '1 aug 2017' and '17 aug 2017' order by id desc

for fast result view I just created this structure. users only fetch one day data mostly and week day data then months data rarely. Previous I have Only one Table but for result view I just changed this

So my quetion is how to avoid that pause process Is their any wrong table structure I have created Kindly help me on high priority.Thanks

  • >>>I have set Non-index on all tables<<< Does it mean that you have no indexes at all? – sepupic Sep 19 '17 at 6:35
  • 1
    Not a direct answer to your question, but if you used partitioning you could ALTER TABLE SWITCH ... your data from one table to the next each day, avoiding the necessity of inserting into three tables at once. Then your users would not be blocked by reads from the monthly table. – mendosi Sep 19 '17 at 6:53
  • I have non index on table – Shridhar Sep 19 '17 at 7:55
  • I am using SQL web edition so not using partitioning – Shridhar Sep 19 '17 at 7:56
  • 1
    @Shridhar Table Partitioning is available for SQL 2016 Web Edition with SP1 CU1 and later. It may be worth patching your instance and reworking things to take advantage of formal table partitioning. – John Eisbrener Sep 19 '17 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.