question

dipakborkar avatar image
dipakborkar asked

Insert operation pause while fetch data.

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 (column col2 and col4) Currently I have 30 GB data in table3_sept and on daily basis it goes increases data inserted rapidly in above 3 table Now when I fetch 15 days Data from Table3. all insert operation get pasued when query get execute then insert operation get resume. So my question is how to avoid that pause process Is their any wrong table structure I have created Kindly help me on high priority.Thanks
sql-server-2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
jasmy avatar image
jasmy answered
You can use With(nolock) Statement while selection. EG SELECT * FROM table1 WITH(NOLOCK)
8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dipakborkar avatar image dipakborkar commented ·
currently I am using same.
0 Likes 0 ·
jasmy avatar image jasmy commented ·
Can you index the where clause? Can you please check the query plan?
0 Likes 0 ·
dipakborkar avatar image dipakborkar commented ·
yes i have non index on two columns.
0 Likes 0 ·
jasmy avatar image jasmy commented ·
what are the columns? Can you please check the execution plan for the select query? Is there any index missing?
0 Likes 0 ·
dipakborkar avatar image dipakborkar commented ·
I have one non-index on bigint and datetime column and one bigint column is primary key Yes I check execution plan their is no any missing index
0 Likes 0 ·
jasmy avatar image jasmy commented ·
Can you please share the plan
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
I think you also need to share the table structure (including indexes) and the query you are using to get 15 days worth of data
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
...and you also need to be aware that (NOLOCK) can generate incorrect results. And, despite its name, can also generate locks...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.