simple select query performance poor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

simple select query performance poor

Author

Topic

kjk_kjp
Starting Member

18 Posts

Posted - 2010-05-11 : 12:29:08

I need help trying to figure out where my performance isssue is - is it network related, server configuration related, etc...

Background info...
I'm connecting to a remote SQL 2008 Server (via Linked Server) - I have setup a SQL 2008 server on my network that is used to query against a sql database at a remote location on a different network (Linked Server). I'm only doing Select statements against this remote database for reporting purposes. Once I have the select statement working how I need it, I then convert it to a Stored Procedure to use in SSRS 2008.
If I run the query below on my server it returns roughly 3400 rows and takes 00:01:35 to execute. If I connect to the remote database and run this same query it takes less than 1 second to execute.

Query:
SELECT ID
,TR_DATE
,SOURCE
,REF_NO
,DESCRIPTION
,FISCAL_YEAR_MONTH
,CREDIT
,DEBIT
FROM [remote server IP address].[database].[dbo].[table123]
WHERE FISCAL_YEAR = '2010'
and LEFT(ID,2) = '04'

I have noticed that if I run the following query from database it will return 3400 rows in 00:00:26

SELECT ID
-- ,TR_DATE
-- ,SOURCE
-- ,REF_NO
-- ,DESCRIPTION
-- ,FISCAL_YEAR_MONTH
-- ,CREDIT
-- ,DEBIT
FROM [remote server IP address].[database].[dbo].[table123]
WHERE FISCAL_YEAR = '2010'
and LEFT(ID,2) = '04'

For each field I uncomment above and re-run query, it adds roughly 10 seconds to the query.

Any ideas on what I can run to see where things are getting hung up would be greatly appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-11 : 12:31:44

If it works on the remote server fine, then you should consider calling a stored procedure instead.

EXEC [remote server IP address].[database].[dbo].StoredProcName

You could also try OPENROWSET.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kjk_kjp
Starting Member

18 Posts

Posted - 2010-05-11 : 13:14:14

I'm not able to create this as a stored procedure on the remote server. This is the main reason I needed to setup a SQL database on my server so that I could create stored procedure to execute.

kjk_kjp
Starting Member

18 Posts

Posted - 2010-05-18 : 10:52:08

I think I have it figured out - running EXEC() AT LinkedServer is working great.
Example:

EXEC('SELECT ID
,TR_DATE
FROM [database].[dbo].[table123]
WHERE FISCAL_YEAR = ''2010''
and LEFT(ID,2) = ''04''') AT LinkedServer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources