Improve perfomance of SQL 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)

Improve perfomance of SQL Query

Author

Topic

sanjay_jadam
Yak Posting Veteran

53 Posts

Posted - 2006-01-25 : 04:04:47

I have some set of table.
Tables
Site
SiteID---------Name
1----------test
2----------test2
SiteAddress
SiteAddressID------SiteID--------AddressID---------EffectiveDate------TerminateDate
1---------------1----------------1----------------------------1/1/2004--------------1/1/2004
1---------------1----------------2----------------------------1/1/2004--------------1/1/2004
1---------------2----------------1---------------------------1/1/2004 --------------1/1/2004
1---------------2----------------2----------------------------1/1/2004--------------1/1/2004
Address
AddressID----Address1--------Address2
1------ABC---------------XYZ
2--------PQR--------------ZZZ
AddressType
AddressTypeID--------Description
1-----------Office
2-----------Home
AddressAddressType
AddressAddressTypeID---------AddressID-----------AddressTypeID
1------------------1--------------------------1
2------------------1--------------------------2
3------------------2--------------------------2
Scenario is like this one site having more then one addresses and one address can have more then one type
I want to retrieve a data like these
Location-----------HomeAddress--------------OfficeAddress
1--------------------PQR,ZZZ-------------------ABC,XYZ
If the Site having more then one home/Work address i have to check effective date from SiteAddress Table and Show only latest address from the list of Site Address

I got this result by using Subquery but it take 4 sec to execute
I want to improve performance.
How can I write a query without subquery ??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-25 : 04:07:42

post your current code

----------------------------------
'KH'
Happy Chinese New Year

sanjay_jadam
Yak Posting Veteran

53 Posts

Posted - 2006-01-25 : 04:16:46

SELECT (
SELECT dbo.tbl_Site.SiteID + ' ' + dbo.tbl_Address.AddressLine1 + ' ' + dbo.tbl_Address.AddressLine2 + ' ' + dbo.tbl_Address.AddressLine3
FROM dbo.tbl_Address INNER JOIN
dbo.tbl_SiteAddress ON dbo.tbl_Address.pk_AddressID = dbo.tbl_SiteAddress.fk_AddressID INNER JOIN
dbo.tbl_AddressAddressType ON dbo.tbl_Address.pk_AddressID = dbo.tbl_AddressAddressType.fk_AddressID INNER JOIN
dbo.tbl_AddressType ON dbo.tbl_AddressAddressType.fk_AddressTypeID = dbo.tbl_AddressType.pk_AddressTypeID RIGHT OUTER JOIN
dbo.tbl_Site ON dbo.tbl_SiteAddress.fk_SiteID = dbo.tbl_Site.pk_SiteID
WHERE (dbo.tbl_SiteAddress.TerminationDate IS NULL OR
dbo.tbl_SiteAddress.TerminationDate > GETDATE()) AND (dbo.tbl_AddressType.Description = 'Work')
AND tbl_SiteAddress.fk_SiteID = S.pk_SiteID
)
,(
SELECT dbo.tbl_Site.SiteID + ' ' + dbo.tbl_Address.AddressLine1 + ' ' + dbo.tbl_Address.AddressLine2 + ' ' + dbo.tbl_Address.AddressLine3
FROM dbo.tbl_Address INNER JOIN
dbo.tbl_SiteAddress ON dbo.tbl_Address.pk_AddressID = dbo.tbl_SiteAddress.fk_AddressID INNER JOIN
dbo.tbl_AddressAddressType ON dbo.tbl_Address.pk_AddressID = dbo.tbl_AddressAddressType.fk_AddressID INNER JOIN
dbo.tbl_AddressType ON dbo.tbl_AddressAddressType.fk_AddressTypeID = dbo.tbl_AddressType.pk_AddressTypeID RIGHT OUTER JOIN
dbo.tbl_Site ON dbo.tbl_SiteAddress.fk_SiteID = dbo.tbl_Site.pk_SiteID
WHERE (dbo.tbl_SiteAddress.TerminationDate IS NULL OR
dbo.tbl_SiteAddress.TerminationDate > GETDATE()) AND (dbo.tbl_AddressType.Description = 'Home')
AND tbl_SiteAddress.fk_SiteID = S.pk_SiteID
)
FROM tbl_Site S

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-25 : 05:00:47

can you post your tables DDL ? Is this a working query ? It looks like the subquery may return more than one records

----------------------------------
'KH'
Happy Chinese New Year

sanjay_jadam
Yak Posting Veteran

53 Posts

Posted - 2006-01-25 : 05:10:16

Sorry i forgot
I used top 1 and order by Effective Date DESC
But i am not very sure its right or wrong becoz my db doesn't contain perfect data but its enough for write a query

sanjay_jadam
Yak Posting Veteran

53 Posts

Posted - 2006-01-27 : 00:41:18

Is it any other way to write this query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources