1

I am having an issue where I can run a query in SQL Sever management studio but the same query hangs when I run it from Mybatis. The activity monitor shows many processes started by the query that seem to be clocking each other with many cxpacket and pageiolatch_sh waits.

The audit_player table has 267 million rows, player and playerInfo 350,000 and currency 20.

Any ideas on what could cause this?

select 'INS' as typeOfChange,
   p.playerId as "model.id"
   from Player p       
INNER join         
   (select DISTINCT(playerId) as playerId          
     from Audit_Player          
     where modifiedDate > '2012-11-14 14:35:47' and action in ('INS', 'UPD') and tester = 0) ap 
on p.playerId = ap.playerId       
left outer join PlayerInfo i on i.playerId = p.playerId       
left outer join Currency c on c.curName = i.curName where c.curCode <> 'XXX'       
UNION       
 select 'DEL' as typeOfChange,
   playerId as "model.id"
 from Audit_Player       
 where modifiedDate > '2012-11-14 14:35:47' and action = 'DEL'
Jbbx
  • 31
  • 3
  • 3
    Please read : http://www.sommarskog.se/query-plan-mysteries.html Also possible duplicate of http://stackoverflow.com/questions/9177343/fast-in-ssms-and-slow-in-the-application-why-does-it-take-this-dataset-so-long http://stackoverflow.com/questions/2736638/sql-query-slow-in-net-application-but-instantaneous-in-sql-server-management-st http://stackoverflow.com/questions/7637907/query-extremely-slow-in-code-but-fast-in-ssms take your pick... – Aaron Bertrand Nov 19 '12 at 19:03
  • Thanks, those look promising – Jbbx Nov 19 '12 at 23:13

0 Answers0