Page 20 of 20 FirstFirst ... 10181920
Results 191 to 197 of 197

Thread: IT Professionals

  1. #191
    Senior Member Diamond Hubber PARAMASHIVAN's Avatar
    Join Date
    May 2009
    Location
    Kailash
    Posts
    8,329
    Post Thanks / Like
    Quote Originally Posted by kirukan View Post
    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 ?
    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

  2. # ADS
    Circuit advertisement
    Join Date
    Always
    Location
    Advertising world
    Posts
    Many
     

  3. #192
    Senior Member Senior Hubber kirukan's Avatar
    Join Date
    Nov 2004
    Location
    udal koodu
    Posts
    676
    Post Thanks / Like
    Quote Originally Posted by PARAMASHIVAN View Post
    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)

  4. Thanks PARAMASHIVAN thanked for this post
  5. #193
    Senior Member Diamond Hubber PARAMASHIVAN's Avatar
    Join Date
    May 2009
    Location
    Kailash
    Posts
    8,329
    Post Thanks / Like
    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

  6. #194
    Senior Member Senior Hubber kirukan's Avatar
    Join Date
    Nov 2004
    Location
    udal koodu
    Posts
    676
    Post Thanks / Like
    Quote Originally Posted by PARAMASHIVAN View Post
    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.

  7. #195
    Senior Member Diamond Hubber PARAMASHIVAN's Avatar
    Join Date
    May 2009
    Location
    Kailash
    Posts
    8,329
    Post Thanks / Like
    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 " ?
    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

  8. #196
    Senior Member Senior Hubber kirukan's Avatar
    Join Date
    Nov 2004
    Location
    udal koodu
    Posts
    676
    Post Thanks / Like
    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

  9. #197
    Senior Member Diamond Hubber PARAMASHIVAN's Avatar
    Join Date
    May 2009
    Location
    Kailash
    Posts
    8,329
    Post Thanks / Like
    Quote Originally Posted by kirukan View Post
    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 ?
    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

Page 20 of 20 FirstFirst ... 10181920

Similar Threads

  1. IT Professionals
    By Raghu in forum Miscellaneous Topics
    Replies: 206
    Last Post: 19th July 2007, 09:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •