-
12th May 2016, 07:34 PM
#1
Senior Member
Diamond Hubber
Thanks Kirukan, In my scenario "Partition by " works better
Om Namaste astu Bhagavan Vishveshvaraya Mahadevaya Triambakaya Tripurantakaya Trikalagni kalaya kalagnirudraya Neelakanthaya Mrutyunjayaya Sarveshvaraya Sadashivaya Shriman Mahadevaya Namah Om Namah Shivaye Om Om Namah Shivaye Om Om Namah Shivaye
-
12th May 2016 07:34 PM
# ADS
Circuit advertisement
-
22nd May 2016, 01:22 PM
#2
Senior Member
Senior Hubber
Originally Posted by
PARAMASHIVAN
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.
Bookmarks