IT Professionals

[kirukan]

Thanks Kirukan, In my scenario "Partition by " works better

I am looking for a logical reason behind sql performance.It would be gr8 if you could throw some light on this.

Below is a query which was having performance issue. Removing the function fn_fraction has improved the performance from 20 sec to 2 sec. But My query is why the Table T is fully scanned when records are filtered in the inner join. In the profiler fn_fraction method is called for the number of rows in T table.
Output of this select is 0 rows. But T has 100k rows

G primary key = intCmpcd, intGlobalId, intInvId
I Index Key= intCmpcd, intAdjId
I Primary key= intCmpcd, intAssetId, intAssetTag
T Primary key= intCmpcd, intAssetId, intAssetTag

select *
FROM FI_Transaction_Inv_Dtl G
INNER JOIN FA_Asset_TagDtl I ON (I.intCmpCd=G.intCmpCd AND I.intAdjId=G.intInvId )
INNER JOIN FA_Asset_TagHdr T ON (T.intCmpCd=I.intCmpCd AND T.intAssetId=I.intAssetId AND T.intAssetTag=I.intAssetTag)
WHERE G.intCmpCd=1 AND G.intGlobalId=3463
AND ABS(dbo.fn_fraction(T.decAcqCostPO*1,1)-T.decAcqCost)<=1
As I could not get a logical answer for this its bothering me for the past 2,3 days.