insert

Question

Post reply

insert

Tara-1044200

SSCoach

Points: 15951
More actions
November 17, 2010 at 2:19 am

#143551

The following insert statement is taking too long, more than 4 hrs which deals with 4 milliom records and during the process the log file is increasing rapidaly, any trick to tune this?
Insert Into Emp_COT Select K.ID, A.startdate, A.enddate
From OBBEAVER.RevenueCA.dbo.Empdates AS A Inner Join IDMember AS K
On IsNull(A.empid,'') = IsNull(A.empid,'')

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

One obvious problem is that you are not joining K to A at all , so that will be a Cartesian join

Insert Into Emp_COT Select K.ID, A.startdate, A.enddate

From OBBEAVER.RevenueCA.dbo.Empdates AS A Inner Join IDMember AS K

On IsNull(A.empid,'') = IsNull(K.empid,'')

Why the isnull 's too ?

Presumably this is a primary key to foreign key relationship , it makes no sense to me.

Clear Sky SQL
My Blog[/url]

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

does this mean the query will never end in such scenario?

actuallly empid is not a PK but it is pulling from a view (select distinct empid) which allows null ans so checking for isnull.

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

It will 'end' at some point.

But consider this ,

every row in OBBEAVER.RevenueCA.dbo.Empdates will be joined to every row in IDMember.

Is this really what you want ?

I doubt it somehow.

Clear Sky SQL
My Blog[/url]

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

I have updated my comment above.

Also is it possible that there is a NULL when you select distinct values of a columun which allows NULL ?

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

Hi ,

i believe you may of missed the more relevant point here.

Your join condition is

On IsNull(A.empid,'') = IsNull(A.empid,'')

You are join table A back to table A , this means that for every row in Table A it will join to every row in table K. This is a cartesian join. If both rows have 1000 rows , then the resulting output will be 1Million rows!!. Is this really what you want?

I think your join condition , in the first instance, lets leave the isnull out of it for the moment, should be

On IsNull(A.empid,'') = IsNull(K.empid,'')

Clear Sky SQL
My Blog[/url]

Tara-1044200 SSCoach Points: 15951 More actions · Answer 6

i understand the 1st part, can you please explain the part that deals with NULL ?

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

What are you expecting to happen when A.empid is null , presently it will join to every row in K where Empid is null. Is that what you want , are you sure this shouldnt be an outer join ?

Clear Sky SQL
My Blog[/url]