Problem during inserting row in a table ( sql server 2005)

Question

Post reply

Problem during inserting row in a table ( sql server 2005)

dsachu

SSCrazy

Points: 2242
More actions
January 4, 2012 at 3:17 am

#262352

Hi all,
I have 1 TB data in my database & I have only one table in my database
where more than 10 lacs data are available.
When i am going to insert single row in table then it's taking lot of time approx 7 minutes. How i can resolve this issue.
Please guide me with step by step.
Regards,
Sachin.

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Sounds to me like an unindexed foreign key lookup, trigger activity, blocking or indexed views.

Take a look at the plan and the active processes to see if you are being blocked.

I think most of these can be discounted as you only have one table, but that in itself sounds as if the design is wrong.

Dave

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 2

kumar.sachu08 (1/4/2012)
Hi all,
I have 1 TB data in my database & I have only one table in my database
where more than 10 lacs data are available.
When i am going to insert single row in table then it's taking lot of time approx 7 minutes. How i can resolve this issue.
Please guide me with step by step.
Regards,
Sachin.

I agree with Dave. It sounds like it could be a bad design. Heh... either that or it's CDRs (Call Detail Records) from a telephone company. 😉

Chances are, if you have such a table, you also have a pot-wad of indexes. Inserts will affect ALL indexes and could cause many of them to split either at the page level or the extent level. Updating all of those indexes (especially when the splits occur) can take a huge amount of time.

My recommendation, if you can't redesign the table, is to do a serious reevalution of the indexes and see if some of them can be combined to support a core set of queries and removed, if not. Part of your investigation should include a FILL FACTOR study to help keep the splits down to a reasonable level.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

Them's big rows. Can you post the CREATE TABLE script?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 4

ChrisM@home (1/4/2012)
Them's big rows. Can you post the CREATE TABLE script?

+1

I am expecting few blob columns. It’s the only thing I can think of at the moment that can justify 10 lacs (1,000,000) records with 1 TB data reasonably.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

Dev (1/4/2012)
I am expecting few blob columns. It’s the only thing I can think of at the moment that can justify 10 lacs (1,000,000) records with 1 TB data reasonably.

Im hoping for

ID integer PK identity

Data XML not null

🙂

Clear Sky SQL
My Blog[/url]

dsachu SSCrazy Points: 2242 More actions · Answer 6

Actually In interview someone ask me, Suppose you have 6 TB Database in which you have so many tables (approx 70) but one table like "abc" contain more than 15 Lacs records.

When we are going to insert a record in this "abc" table then it's taking lot of time approx 10 minutes.

Then what's step you should take to minimize the record insertion time.

Please guide me step by step.

Regards,

Sachin.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 7

TBH , the problem solving skills that you would have to demonstrate would be an integral part of a DBA role.

If you are going for a DBA role , then you *should* know this.

If you dont , and your prospective employer needs your to, its the wrong job for you right now , sorry.

Clear Sky SQL
My Blog[/url]

dsachu SSCrazy Points: 2242 More actions · Answer 8

I know i am fresher....and i am giving interview......i have only 1 years of experience.

then what should i do....when any interviewer asking like such type of question ?

Should i left the interview without attempting or i am not right person to know all the question answer ?

I am getting the problem to find out answer from itself. That's why I raging question.

If you understand fresher should not required such type of question answer then who will suggest interview don't ask such type of question with fresher.

Thanks.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 9

Then the best answer is :

"I dont know, but i would start to find out how to by........"

I dont know , is valid answer to any question. The 'but' follow on is valuable to an employer to see what you problem solving potential would be. Nobody knows it all. Perhaps the only bad answer would be : but i would post a question on SSC and wait for an answer.

Where would you go to find information ?

Clear Sky SQL
My Blog[/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 10

kumar.sachu08 (1/5/2012)
I know i am fresher....and i am giving interview......i have only 1 years of experience.
then what should i do....when any interviewer asking like such type of question ?
Should i left the interview without attempting or i am not right person to know all the question answer ?
I am getting the problem to find out answer from itself. That's why I raging question.
If you understand fresher should not required such type of question answer then who will suggest interview don't ask such type of question with fresher.
Thanks.

kumar.sachu08 (1/4/2012)
Hi,
Can we implement Clustered Index on Unique key ? If no... why ?
Regards,
Sachin.

Stay in your current position and consolidate your knowledge for a year or two.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 11

kumar.sachu08 (1/4/2012)
Hi all,
When i am going to insert single row in table then it's taking lot of time approx 7 minutes.

Has this always been the case or has it just started happening? Have you tried to Profiler the insert?

_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

dsachu SSCrazy Points: 2242 More actions · Answer 12

Hi Gail Shaw ,

Expect answer from your side.

Thanks in advance.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 13

Gail is one of the best, but what makes you think that her advice would be different from ours ?

Is you interview answer now going to be 'ill ask Gail Shaw' ?

We are all unpaid volunteers here, we have tried our best to help you but since you dont want that ...bye.....

Clear Sky SQL
My Blog[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 14

Dave Ballantyne (1/4/2012)
Sounds to me like an unindexed foreign key lookup, trigger activity, blocking or indexed views.
Take a look at the plan and the active processes to see if you are being blocked.
I think most of these can be discounted as you only have one table, but that in itself sounds as if the design is wrong.
Dave

Actually, Dave Gave you the answer in his very first post. you should have a mental checklist for slow inserts that includes that list, because there's no one answer.

it would require an investigation as to WHY it was slow. Dave mentioned unindexed foreign key lookup, trigger activity, blocking or indexed views; all those things could be reviewed, one item at a time, to determine if it is having the impact.

you would probably want to start by looking at the execution plan of the slow insert first.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!