How to tune the following query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to tune the following query

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-02-12 : 05:48:49

I have a table in which there are 100 of thousand of record.In on e of the stored procedure I am updating different columns in different go.
Now I want's to update them in a one go.
How can I do it

queries in my stored procedures are
UPDATE Job
SET CompanyName=c.Name
FROM Job j JOIN Company c ON j.CompanyID=c.CompanyID
WHERE CompanyName IS NULL AND NOT ((c.Name IS NULL) OR (c.Name='.'))

UPDATE Job
SET CompanyDescription=left(c.Description,1000)
FROM Job j JOIN Company c ON j.CompanyID=c.CompanyID
WHERE ((CompanyDescription IS NULL) OR (CompanyDescription=' ')) AND NOT ((c.Description IS NULL) OR (c.Description='.' OR (c.Description=' ')))

UPDATE Job
SET HomePageURL=c.URL
FROM Job j JOIN Company c ON j.CompanyID=c.CompanyID
WHERE HomePageURL IS NULL AND NOT ((c.URL IS NULL) OR (c.URL='.'))

-------------------------------
-- Do updates from the Branch Table
UPDATE Job
SET ContactAddr1=b.Address1
FROM Job j JOIN Branch b ON j.CompanyID=b.CompanyID AND j.BranchID=b.BranchID
WHERE ContactAddr1 IS NULL AND NOT ((b.Address1 IS NULL) OR (b.Address1='.'))

UPDATE Job
SET ContactAddr2=b.Address2
FROM Job j JOIN Branch b ON j.CompanyID=b.CompanyID AND j.BranchID=b.BranchID
WHERE ContactAddr2 IS NULL AND NOT ((b.Address2 IS NULL) OR (b.Address2='.'))

UPDATE Job
SET ContactCity=b.City
FROM Job j JOIN Branch b ON j.CompanyID=b.CompanyID AND j.BranchID=b.BranchID
WHERE ContactCity IS NULL AND NOT ((b.City IS NULL) OR (b.City='.'))

UPDATE Job
SET ContactState=b.State
FROM Job j JOIN Branch b ON j.CompanyID=b.CompanyID AND j.BranchID=b.BranchID
WHERE ContactState IS NULL AND NOT ((b.State IS NULL) OR (b.State='.'))

UPDATE Job
SET ContactCountry=b.Country
FROM Job j JOIN Branch b ON j.CompanyID=b.CompanyID AND j.BranchID=b.BranchID
WHERE ContactCountry IS NULL AND NOT ((b.Country IS NULL) OR (b.Country='.'))

UPDATE Job
SET ContactPostal=b.ZipCode
FROM Job j JOIN Branch b ON j.CompanyID=b.CompanyID AND j.BranchID=b.BranchID
WHERE ContactPostal IS NULL AND NOT ((b.ZipCode IS NULL) OR (b.ZipCode='.'))

UPDATE Job
SET ContactName=bc.Name
FROM Job j JOIN BranchContacts bc ON j.CompanyID=bc.CompanyID AND j.BranchID=bc.BranchID AND j.ContactID=bc.ContactID
WHERE ContactName IS NULL AND NOT ((bc.Name IS NULL) OR (bc.Name='.'))

UPDATE Job
SET ContactTitle=bc.Title
FROM Job j JOIN BranchContacts bc ON j.CompanyID=bc.CompanyID AND j.BranchID=bc.BranchID AND j.ContactID=bc.ContactID
WHERE ContactTitle IS NULL AND NOT ((bc.Title IS NULL) OR (bc.Title='.'))

UPDATE Job
SET ContactPhone=bc.Phone
FROM Job j JOIN BranchContacts bc ON j.CompanyID=bc.CompanyID AND j.BranchID=bc.BranchID AND j.ContactID=bc.ContactID
WHERE ContactPhone IS NULL AND NOT ((bc.Phone IS NULL) OR (bc.Phone='.'))

UPDATE Job
SET ContactFax=bc.Fax
FROM Job j JOIN BranchContacts bc ON j.CompanyID=bc.CompanyID AND j.BranchID=bc.BranchID AND j.ContactID=bc.ContactID
WHERE ContactFax IS NULL AND NOT ((bc.Fax IS NULL) OR (bc.Fax='.') OR (bc.Fax='0'))

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-12 : 06:19:16

UPDATE Job
SET CompanyName = case when WHERE CompanyName IS NULL AND NOT ((c.Name IS NULL) OR (c.Name='.')) then c.Name else j.CompanyName end ,
CompanyDescription = case when ((CompanyDescription IS NULL) OR (CompanyDescription=' ')) AND NOT ((c.Description IS NULL) OR (c.Description='.' OR (c.Description=' '))) then left(c.Description,1000) else j.CompanyDescription end
........

FROM Job j
JOIN Company c
ON j.CompanyID=c.CompanyID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 06:21:18

First three UPDATEs can be replaced with this one.

UPDATE		j 
SET		j.CompanyName =	CASE
					WHEN c.Name IS NULL OR c.Name = '.' THEN j.CompanyName
					ELSE c.Name
				END,
		j.CompanyDescription =	CASE
						WHEN  AND c.Description IS NULL OR c.Description  IN ('.', ' ') THEN j.CompanyDescription
						ELSE LEFT(c.Description, 1000)
					END,
		j.HomePageURL =	CASE
					WHEN c.URL IS NULL OR c.URL = '.' THEN j.HomePageURL
					ELSE c.URL
				END
FROM		Job AS j
INNER JOIN	Company AS c ON j.CompanyID = c.CompanyID
WHERE		j.CompanyName IS NULL
		OR j.CompanyDescription IS NULL
		OR j.CompanyDescription = ' '
		OR j.HomePageURL IS NULL

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 06:32:28

Next six UPDATEs can be replaced with this

UPDATE		j
SET		j.ContactAddr1 =	CASE
						WHEN b.Address1 IS NULL OR b.Address1 = '.' THEN j.ContactAddr1
						ELSE b.Address1
					END,
		j.ContactAddr2 =	CASE
						WHEN b.Address2 IS NULL OR b.Address2 = '.' THEN j.ContactAddr2
						ELSE b.Address2
					END,
		j.ContactCity =	CASE
					WHEN b.City IS NULL OR b.City = '.' THEN j.ContactCity
					ELSE b.City
				END,
		j.ContactState =	CASE
						WHEN b.State IS NULL OR b.State '.' THEN j.ContactState
						ELSE b.State
					END,
		j.ContactCountry =	CASE
						WHEN b.Country IS NULL OR b.Country = '.' THEN j.ContactCountry
						ELSE b.Country
					END,
		j.ContactPostal =	CASE
						WHEN b.ZipCode IS NULL OR b.ZipCode = '.' THEN j.ContactPostal
						ELSE b.ZipCode
					END
FROM		Job AS j
INNER JOIN	Branch AS b ON b.CompanyID = j.CompanyID AND b.BranchID = j.BranchID
WHERE		j.ContactAddr1 IS NULL
		OR j.ContactAddr2 IS NULL
		OR j.ContactCity IS NULL
		OR j.ContactState IS NULL
		OR j.ContactCountry IS NULL
		OR j.ContactPostal IS NULL

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 07:05:25

Last four with this

UPDATE		j
SET		j.ContactName =	CASE
					WHEN b.Name IS NULL OR b.Name = '.' THEN j.ContactName
					ELSE b.Name
				END,
		j.ContactTitle =	CASE
					WHEN b.Title IS NULL OR b.Title = '.' THEN j.ContactTitle
					ELSE b.Name
				END,
		j.ContactPhone =	CASE
					WHEN b.Phone IS NULL OR b.Phone = '.' THEN j.ContactPhone
					ELSE b.Phone
				END,
		j.ContactFax =	CASE
					WHEN b.Fax IS NULL OR b.Fax IN ('.', '0') THEN j.ContactFax
					ELSE b.Fax
				END
FROM		Job AS j
INNER JOIN	Branch AS b ON b.CompanyID = j.CompanyID AND b.BranchID = j.BranchID AND j.ContactID = b.ContactID
WHERE		j.ContactName IS NULL
		OR j.ContactTitle IS NULL
		OR j.ContactPhone IS NULL
		OR j.ContactFax IS NULL

Peter Larsson
Helsingborg, Sweden

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-02-12 : 07:15:28

Do i really need to override the value again if the value for case is false.
Like for follwing statement
j.ContactName = CASE
WHEN b.Name IS NULL OR b.Name = '.'
THEN j.ContactName
ELSE b.Name
END,
j.ContactTitle =
CASE
WHEN b.Title IS NULL OR b.Title = '.'
THEN j.ContactTitle
ELSE b.Name
END,
Could Below statement without else clause be legal
[I doesn't want's to update the value which is already correct]

j.ContactName = CASE
WHEN b.Name IS NULL OR b.Name = '.'
THEN j.ContactName
END,
j.ContactTitle =
CASE
WHEN b.Title IS NULL OR b.Title = '.'
THEN j.ContactTitle
END,

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-12 : 07:18:55

"Do i really need to override the value again if the value for case is false."

Yes.

"Could Below statement without else clause be legal"

Without ELSE clause it will replace column value as NULL when case condition results in false.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-02-12 : 07:31:16

Also for
UPDATE Job
SET CompanyDescription=left(c.Description,1000)
FROM Job j JOIN Company c ON j.CompanyID=c.CompanyID
WHERE ((CompanyDescription IS NULL) OR (CompanyDescription=' ')) AND NOT ((c.Description IS NULL) OR (c.Description='.' OR (c.Description=' ')))

is the Following query is equvalent ? [I am not sure]

UPDATE j
SET j.CompanyDescription = CASE
WHEN c.Description IS NULL OR c.Description IN ('.', ' ') THEN j.CompanyDescription
ELSE LEFT(c.Description, 1000)
END
FROM Job AS j
INNER JOIN Company AS c ON j.CompanyID = c.CompanyID
WHERE j.CompanyDescription IS NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 07:34:26

In my suggestion I also added
OR j.CompanyDescription = ' '

Did you miss that?

Peter Larsson
Helsingborg, Sweden

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-02-12 : 07:38:07

quote:
Originally posted by harsh_athalye

"Do i really need to override the value again if the value for case is false."

Yes.

"Could Below statement without else clause be legal"

Without ELSE clause it will replace column value as NULL when case condition results in false.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Then I think this approach will not be well suited as I have 100 of thousand of records so so many insertion again.[Is n't it ?]
Can any body suggest me any other work around ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-12 : 07:41:52

I don't think this is added performance penalty. Insert/Updates works in chunks of pages, so whether you insert/update a single column or 50 columns, it hardly matters unless you have clustered index set up on those columns.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-02-12 : 12:53:55

I am not saying about number of column.
I am asking about overhead of updating the table again with the same value[value mihgt be too large]
Could some how I can escape from this ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 13:07:47

You filter out the records to update.
You will get some more records, correct, but you only have to JOIN 3 times in total instead of 10 times! There is the performance gain!

Peter Larsson
Helsingborg, Sweden

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-02-13 : 05:41:17

Is the Following is more correct

---------------------Company-----------------------------

UPDATE j
SET j.CompanyName = CASE
WHEN CompanyName IS NULL AND NOT ((c.Name IS NULL) OR (c.Name='.'))
Then c.Name
Else j.CompanyName
END,
j.CompanyDescription = CASE
WHEN ((CompanyDescription IS NULL) OR (CompanyDescription=' ')) AND NOT ((c.Description IS NULL) OR (c.Description='.' OR (c.Description=' ')))
Then LEFT(c.Description, 1000)
Else j.CompanyDescription
END,
j.HomePageURL = CASE
WHEN HomePageURL IS NULL AND NOT ((c.URL IS NULL) OR (c.URL='.'))
THEN c.URL
ELSE j.HomePageURL
END
FROM Job AS j
INNER JOIN Company AS c ON j.CompanyID = c.CompanyID

-----------------------------------Contact-------------------------------------

UPDATE j
SET j.ContactName = CASE
WHEN j.ContactName IS NULL AND NOT ((b.Name IS NULL) OR (b.Name='.'))
Then b.Name
Else j.ContactName
END,
j.ContactTitle = CASE
WHEN j.ContactTitle IS NULL AND NOT ((b.Title IS NULL) OR (b.Title='.'))
Then b.Name
Else j.ContactTitle
END,
j.ContactPhone = CASE
WHEN j.ContactPhone IS NULL AND NOT ((b.Phone IS NULL) OR (b.Phone='.'))
Then b.Name
Else j.ContactPhone
END,
j.ContactFax = CASE
WHEN j.ContactFax IS NULL AND NOT ((b.Fax IS NULL) OR (b.Fax='.'))
Then b.Name
Else j.ContactFax
END
FROM Job AS j
INNER JOIN BranchContacts AS b ON b.CompanyID = j.CompanyID AND b.BranchID = j.BranchID AND j.ContactID = b.ContactID

---------------------------Branch--------------

UPDATE j
SET j.ContactAddr1 = CASE
WHEN ContactAddr1 IS NULL AND NOT ((b.Address1 IS NULL) OR (b.Address1='.'))
THEN b.Address1
ELSE j.ContactAddr1
END,
j.ContactAddr2 = CASE
WHEN ContactAddr2 IS NULL AND NOT ((b.Address2 IS NULL) OR (b.Address2='.'))
THEN b.Address2
ELSE j.ContactAddr2
END,
j.ContactCity = CASE
WHEN ContactCity IS NULL AND NOT ((b.City IS NULL) OR (b.City='.'))
THEN b.City
ELSE j.ContactCity
END,
j.ContactState = CASE
WHEN ContactState IS NULL AND NOT ((b.State IS NULL) OR (b.State='.'))
THEN b.State
ELSE j.ContactState
END,
j.ContactCountry = CASE
WHEN ContactCountry IS NULL AND NOT ((b.Country IS NULL) OR (b.Country='.'))
THEN b.Country
ELSE j.ContactCountry
END,
j.ContactPostal = CASE
WHEN ContactPostal IS NULL AND NOT ((b.ZipCode IS NULL) OR (b.ZipCode='.'))
THEN b.ZipCode
ELSE j.ContactPostal
END
FROM Job AS j
INNER JOIN Branch AS b ON b.CompanyID = j.CompanyID AND b.BranchID = j.BranchID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources