0

Moving some complicated reporting sprocs to a centralized server and time went from 5 seconds to 30+ seconds.

validating what take so long via:

print '04 NWA Raw data Numeric'
print datediff(ss, @now, getdate())
set @now = GETDATE()

I am attempting to only pull local what this report needs with these queries:

1355 rows in 10 seconds----

select * 
into #nwaDump
from [Phoenix].[NWA].dbo.QISDataNumeric
where rowguid in (
select rowguid from [Phoenix].[NWA].[dbo].[QISDataText] nd
where nd.DataValue in ( '41310291            ' ) 
)

249 rows in 28 seconds

select * 
into #nwaText 
from [Phoenix].[NWA].[dbo].[QISDataText] td
where td.DataValue in ( '41310291            ' )

Same two queries run on other server < 1 second return time.

Any ideas?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SteveO
  • 477
  • 2
  • 9
  • 17

1 Answers1

1

You can try to use OPENQUERY for this, since it should make the filters on the linked server and then pull them to your other server:

SELECT *
INTO #nwaText 
FROM OPENQUERY(Phoenix,'SELECT * FROM [NWA].[dbo].[QISDataText] 
                        WHERE DataValue in ( ''41310291            '' )')
Lamak
  • 69,480
  • 12
  • 108
  • 116
  • Found that I didn't need the OpenQuery when I placed the real values into the in clause. It was the Into # that was killing me. – SteveO Jan 07 '14 at 17:32