3

Like the title says, I have a stored procedure that suddenly started running very slowly. Previously it took about 5 seconds to execute, now it is taking seven minutes or more. This seems to have started when I altered the procedure, but the change I made should not have impacted the performance, as all I did was change the alias on two columns being returned. I'm not sure if altering the procedure actually caused this, however, because a similar procedure that also used to be very fast is having the same issues.

I tried updating statistics on the relevant tables and it did not help. I had this same problem last week and was able to fix it by doing that, but now it's a no go. I also tried using with recompile to no avail and deleted the procedure then re-added it.

This is the line that is taking much longer than before:

select PK_ShipDataSubsystemConfigID
,timestamp
,cast((
        select top 1 Longitude
        from [RemoteServer].[dbo].[Ships].LocationAttributes la
        inner join [RemoteServer].[dbo].[Ships].Locations l on l.ShipLocationId = la.ShipLocationId
        where l.SampleDateTime <= timestamp
            and l.SampleDateTime <= @ToDT
            and ShipId = @ShipId
        order by SampleDateTime desc
        ) as varchar(20)) as Longitude
,'Vessel Position Lng'
from FuelCalc
inner join ShipDataSubsystemConfig s on s.PK_ShipDataSubsystemConfigID =       FuelCalc.FK_ShipDataSubsystemConfigId
where timestamp > @FromDT
and timestamp < @ToDT
and FuelCalc.FK_ShipId = @ShipId
order by timestamp desc

Running the inner select by itself now takes at least 2 seconds, so I can understand why the whole procedure is taking so long. I am wondering if it has something to do with it being a call to a linked server. I tried creating a view joining the Locations and LocationAttributes tables to see if it made a difference, but it did not.

Any ideas? I have asked around with my coworkers and they are just saying I need to write a more efficient query (which may be true) but I really don't think that's causing my particular problem seeing as it was working fine until suddenly it wasn't. I looked at the execution plan but all it says is that the remote query is taking 100% of the time, but doesn't show the plan at more detail than that.

  • how fragmented are your indexes? have you tried EXEC sp_refreshsqlmodule proc – Julien Vavasseur Sep 9 '15 at 13:41
  • I just tried sp_refreshsqlmodule... doesn't seem to have helped. – eddie_cat Sep 9 '15 at 13:50
  • Can you post the query execution plan - use sql sentry's plan explorer (its free) ? – Kin Shah Sep 9 '15 at 13:54
  • Is the Linked Server under your control? Have you checked wait stats to see if it is a network related issue? – Mark Sinkinson Sep 9 '15 at 13:54
  • 1
    Wild guess: the remote query does not get executed in pass-through for some reason and both remote tables get downloaded to a temporary table. An inline table valued function on the remote server, accepting TIMESTAMP, @ToDt and @ShipId would probably hint SqlServer how to treat the remote query. – spaghettidba Sep 9 '15 at 13:58
3

I would suggest that you create a temp table or use common table expression to get the list of all longitudes you require from your remote Server and use the new temp table or common table expression to inner join with your FuelCalc table.

Something like below.

WITH CTE as 
(
select Longitude, MAX(SampleDateTime) as SampleDateTime, ShipLocationId, SHIPiD
        from [RemoteServer].[dbo].[Ships].LocationAttributes la
        inner join [RemoteServer].[dbo].[Ships].Locations l on l.ShipLocationId = la.ShipLocationId
        where l.SampleDateTime <= timestamp
            and l.SampleDateTime <= @ToDT
            and ShipId = @ShipId
        GROUP BY Longitude
)
select PK_ShipDataSubsystemConfigID
,timestamp
,cte.Longitude
,'Vessel Position Lng'
from FuelCalc
inner join ShipDataSubsystemConfig s on s.PK_ShipDataSubsystemConfigID =       FuelCalc.FK_ShipDataSubsystemConfigId
left outer join cte on cte.SHIPiD = FK_ShipId
where timestamp > @FromDT
and timestamp < @ToDT
and FuelCalc.FK_ShipId = @ShipId
order by timestamp desc
  • If That doesnt work you may wanna try parameter sniffing. simple-talk.com/sql/t-sql-programming/parameter-sniffing but its very unlikely in this sceanario – mouliin Sep 9 '15 at 14:03
  • I can't speak to the CTE workaround for this, but I've had a lot of success with grabbing Remote Server data into a local #TempTable to reduce the more crawling linked-server queries I've come across. Also allows you to re-index as needed locally for even more of a speed boost. – Peter Vandivier Sep 9 '15 at 14:10
  • Thanks y'all. I will try both of your suggestions, from what I'm looking at now the CTE select alone is very fast so it seems to already be some improvement! – eddie_cat Sep 9 '15 at 14:12
  • 1
    I ended up using a table variable where I just grabbed all the remote data needed before doing the rest of the stuff I needed to do. It's even faster than it was before now. :) – eddie_cat Sep 9 '15 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.