October 2, 2014 at 1:36 pm
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;
October 2, 2014 at 1:47 pm
First let me tell you that I don't know MySql; in fact I've never touched it. I know that linked servers are not known for their stellar performance. Given that you're using one, why would you use dynamic SQL for the pull? If you want to loop through a list of 20 of them, I can understand it, but not for just one.
My question is about the MySql part. How long does it take the same query to run against the MySql data source? Pulling data in SQL will never top that speed, so if there's a performance problem on the MySql side of the world, then you'll need to address it there. If that's the case, I'm not the guy to help.
October 2, 2014 at 2:34 pm
SQL will have to pull back all rows, then apply the WHERE conditions.
You need to add the relevant WHERE conditions:
WHERE oq.calldate > getdate() -1
AND oq.duration > ''00:00:20'''
to the remote query to limit the number of rows that have to be sent back from MySQL.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 2, 2014 at 3:56 pm
Thank you kindly...down to 14 seconds....
i will be looping through 20 times based on locations in a table.
October 3, 2014 at 5:03 am
Nice catch, Scott. I was focused on the part I didn't know and missed the obvious. Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply