query optimization help needed.

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 2000 Forums

SQL Server Development (2000)

query optimization help needed.

Author

Topic

abhishekmadas
Starting Member

19 Posts

Posted - 2007-02-05 : 12:28:59

I need help in optimizing this query. The major time takes in calling a remote database. Thanks in advance.

ALTER PROCEDURE dbo.myAccountGetCallLogsTest
@directorynumber as varchar(10),
@CallType as tinyint
AS

declare @dt as int
SELECT TOP 1 @dt=datediff(day,C.EstablishDate,getdate())
FROM ALBHM01CGSERVER.Core.dbo.Customer C INNER JOIN
ALBHM01CGSERVER.Core.dbo.UsgSvc U ON C.CustID = U.CustID
WHERE (U.ServiceNumber = @directoryNumber)
ORDER BY C.EstablishDate DESC

IF @dt>90
select DN as Number, Remote_DN as [Remote Number], City, StartTime as [Start Time], EndTime as [End Time]
from vw_Call_Logs
where DN = '1' + @directoryNumber and call_type = @CallType and datediff(day,starttime,getdate())<90
order by starttime desc
ELSE
select DN as Number, Remote_DN as [Remote Number], City, StartTime as [Start Time], EndTime as [End Time]
from vw_Call_Logs
where DN = '1' + @directoryNumber and call_type = @CallType and datediff(day,starttime,getdate())< @dt
order by starttime desc

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-05 : 12:36:51

Have a look at the query placn and see what it is sending to the remote database.
maybe something like

declare @sql nvarchar(4000)
select @sql = 'SELECT TOP 1 @dt=datediff(day,C.EstablishDate,getdate())
FROM Customer C INNER JOIN
UsgSvc U ON C.CustID = U.CustID
WHERE (U.ServiceNumber = ''' + @directoryNumber + ''')
ORDER BY C.EstablishDate DESC'

exec ALBHM01CGSERVER.Core.dbo.sp_executesql @sql, N'@dt int out', @dt out

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

abhishekmadas
Starting Member

19 Posts

Posted - 2007-02-05 : 13:00:33

I get Remote scan cost = 100%

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-05 : 13:15:41

It should show you the query that is being sent to the remote server. It may be transfering all the data locally. The query I gave above should cure that if you can run it.

If not check the link server compatibility settings.

Better is to call a stored procedure on the remote server to return the data.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

abhishekmadas
Starting Member

19 Posts

Posted - 2007-02-05 : 16:42:13

Thanks, I created a stored procedure on the remote server and it works in a split second.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources