openquery taking 1:30:00 plus

• TryingToLearn

  SSCrazy

  Points: 2645

  More actions

  October 2, 2014 at 12:03 pm

  #99032

  I have a MySql Database i need to extract data from based on matching info in a local SQL Server database,

  I will ultimately need to cycle through 20 of these MySql databases , but the query below is taking 1 minute and 20 plus seconds...Just for one..

  Is there a simple tweak that would speed it up?

  Thanks

  USE [CAS]

  GO

  DECLARE @PhoneDB varchar(max),

  @SQL NVARCHAR(MAX);

  set @PhoneDB = '[PHONEDB_PHI]';

  set @SQL = 'SELECT

  [calls_key] = oq.calls_key,

  [calldate] = oq.calldate,

  [calltime] = oq.time,

  [duration] = oq.duration,

  [duration_in_decimal] = oq.duration_in_decimal,

  [ext] = oq.ext,

  [trunk] = oq.trunk,

  [calltype] = oq.calltype,

  [io] = oq.io,

  [areacode] = oq.areacode,

  [phonenum] = oq.phonenum,

  [dept] = e.dept,

  [fname] = e.fname,

  [lname] = e.lname,

  [empnum] = e.empnum

  FROM

  [LinkedServer].[CAS].[dbo].[EMP] e

  inner join OPENQUERY(' + @PhoneDB + '' + ',''SELECT calls_key, calldate, time, cast(duration as char(8) charset utf8) as duration, duration_in_decimal, ext, trunk, calltype, io, areacode, phonenum FROM MySql.View'') oq

  on (e.ext = oq.ext)

  WHERE

  e.ext = oq.ext

  AND e.active = 1

  AND oq.calldate > getdate() -1

  AND oq.duration > ''00:00:20''';

  --print @SQL;

  EXEC sp_executesql @SQL;

• Ed Wagner

  SSC Guru

  Points: 287024

  More actions

  October 2, 2014 at 1:40 pm

  #1749798

  Duplicate post. Direct replies to http://www.sqlservercentral.com/Forums/Topic1622099-392-1.aspx.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions