IT Professionals

[PARAMASHIVAN]

Yes I have used but in 2008.Hope its same in 2014 as well.

kirukan

Thanks, I think it is better to identify duplicates using this than the traditional "having count (0) > 1 " , right ?

[kirukan]

Thanks, I think it is better to identify duplicates using this than the traditional "having count (0) > 1 " , right ?

To me it depends if you just need a list traditional itself will work better if you are going to do further analysis then partition will help.Below is an example from supplier master to identify two different suppliers given same beneficiary name for payment.

--With Partition
WITH party as(
SELECT ROW_NUMBER() OVER (PARTITION BY party_benfname
ORDER BY ptyh_id DESC ) RN,party_benfname,ptyh_id
FROM party_master)

select * from party where party_benfname in(select party_benfname from party where RN>1)

--Old style
select party_benfname,Ptyh_id from party_master where party_benfname in(
select party_benfname from party_master group by party_benfname having count(party_benfname)>1)

[PARAMASHIVAN]

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

[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.

[PARAMASHIVAN]

Hello Kirukan

What version of SQL server are you using ? 2012/2014/2106 ? Did you put the query into "Query execution planner" or "Database tuning adviser " ?

[kirukan]

Its running in 2008.

Yes checked the execution planner and Table T take 71% cost.Is it normal to scan entire table even though there will be no matching records based on Table G and I.
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

[PARAMASHIVAN]

Its running in 2008.

Yes checked the execution planner and Table T take 71% cost.Is it normal to scan entire table even though there will be no matching records based on Table G and I.
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

Have you tried partitioning the table ? is it a heap table ?