one column in SELECT list increases response time

Question

one column in SELECT list increases response time

Kaushik Majumder

Ten Centuries

Points: 1009
More actions
December 24, 2008 at 5:48 am

#125302

Hi,
The following query involves a few INNER and LEFT joins. The last join (joing ROE table) is a bit different as it involves a function as well.
The issue is when any column from the ROE table is included in the SELECT list, the query response time goes up at least to 3 minutes. However, if the same is tried without any column from ROE table (in the SELECT list) the same responds within a minute. The number of rows in the ROE table merely touches 2000 and the query results does go upto 2.5 L, at the most. ROE table does have a composite index of CurrencyCode/EffectiveDate/ROEBP.
Any advice please?
Kind Regards.
[font="Verdana"]SELECT DISTINCTrsk.riskid,
dc.CertificateNo,
rs.Status,
(lu.FirstName + ' ' + lu.LastName) [Bound By],
cm.CompanyName [Division],
sd.InsuredName [Assured],
dc.CustomerName,
dc.ClientReference,
cd.Description [Commodity Category],
dc.DateBound [Departure Date],
si.SumInsuredCcy [Sum Insured Currency],
si.SumInsuredValue [Sum Insured],
rp.PremiumCurrency [Original Premium Currency],
rp.PremiumAmount [Original Premium Amount],
ro.ROEtoGBP [Rate of Exchange],
dc.Vessel,
cn.Name [Conveyance Method],
dc.Voyage,
sh.CityFrom [Commencement of Transit],
ct1.CountryName [Country From],
sh.CityTo [Place of Final Destination],
ct2.CountryName [Country To],
dc.PolicyNo [Policy No],
rpl.InceptionDate [Inception Date],
rpl.ExpiryDate [Expiry Date],
dc.SurveyAgentId [Settling Agent/Claims Handling]
FROM dbo.Declaration dc
INNER JOIN dbo.Risk rsk ON dc.RiskId = rsk.RiskId
INNER JOIN dbo.RiskStatus rs ON rsk.RiskStatusId = rs.RiskStatusId
INNER JOIN dbo.LogonUser lu ON dc.UpdatedByUserId = lu.LogonUserId
INNER JOIN dbo.RiskCompany rc ON dc.RiskId = rc.RiskId
INNER JOIN dbo.Company cm ON rc.CompanyId = cm.CompanyId
INNER JOIN dbo.SumInsured si ON dc.RiskId = si.RiskId
INNER JOIN dbo.Shipment sh ON dc.RiskId = sh.RiskId
INNER JOIN dbo.RiskParent rsp ON dc.RiskId = rsp.RiskId
INNER JOIN dbo.RiskPolicy rpl ON rsp.ParentRiskId = rpl.RiskId
LEFT JOIN dbo.StorageDeclaration sd ON dc.RiskId = sd.RiskId
LEFT JOIN dbo.RiskPremiumComponent rp ON dc.RiskId = RP.RiskId
LEFT JOIN dbo.Commodity cd ON dc.CommodityId = cd.CommodityId
LEFT JOIN dbo.Conveyance cn ON cn.ConveyanceId = sh.Method
LEFT JOIN dbo.Country ct1 ON sh.CountryIdFrom = ct1.CountryId
LEFT JOIN dbo.Country ct2 ON sh.CountryIdTo = ct2.CountryId
LEFT JOIN dbo.ROE ro ON si.SumInsuredCcy = ro.CurrencyCode AND
ro.EffectiveDate = (SELECT Max(EffectiveDate) FROM ROE WHERE CurrencyCode=si.SumInsuredCcy AND EffectiveDate<=dc.DateBound)[/font]

Viewing 4 posts - 1 through 4 (of 4 total)

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

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 1

it may not only be the left join, but I guess most of the waste time will be the correlated join predicate !

apparently this can be an indexed item, but retrieving a non indexed column from ROE changes the execution plan drastically.

use

set statistics io on

set statistics time on

to figure out what it does, and how it does it.

you can also use "show actual execution plan" (query analyser) to find your way around.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Kaushik Majumder Ten Centuries Points: 1009 More actions · Answer 2

Thanks.....

Sorry, I am a bit late in replying, Christmas leave...:)

Will try and update.

Regards,

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 3

No problem.

Keep us updated 😉

Happy 2009