EXECUTE & RETURN - SQL Server Forums

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)

EXECUTE & RETURN

Author

Topic

Mak
Starting Member

7 Posts

Posted - 2006-02-17 : 05:30:43

Hi,

Please take a look:

(This Procedure is located in PrimaryDB)
CREATE PROCEDURE dbo.ReportClients
(
@User int,
@From SmallDateTime,
@To SmallDateTime,
)
AS

IF ( (SELECT SecondayDB.dbo.HasClients ('ReportClient', @DateFrom, @DateTo) )) = 1 )
BEGIN
EXEC SecondayDB..ReportClients @UserID, @DateFrom, @DateTo,
RETURN
END
[And rest of the procedure]

Assuming dbo.HasClients returns 1 and SecondayDB..ReportClients is executed it takes quite a long time to return results, although its not suppose to execute rest of the main procedure and I tested it with few print statements and it does not (in theory).

As soon as I comment out all the code after the condition (in main procedure) it becomes much quicker. It should take approx. 3 seconds and currently its taking 25 seconds to run.

This is what I have already tried:
All the necessary columns are indexed (Re-Indexed)
Dropped & re-created procedure/table(s) in both databases
Updated statistics
Stop/start the sql server

But nothing seems to be working and I got other procedures working in same way and they are really quick.

Anyone have any ideas to get around this problem?

Many Thanks

Mak

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-02-17 : 06:06:09

Why the missing db-owner in the EXEC statement?
Are you missing out on cache with this statement? Is the SP being re-compiled?

Mak
Starting Member

7 Posts

Posted - 2006-02-17 : 06:36:44

I did try with dbo as well, still the same
EXEC SecondayDB.dbo.ReportClients @UserID, @DateFrom, @DateTo

Cache: I run the following before executing (in query analyser) to clear cache/buffers
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Tried WITH RECOMPILE and without, no difference

I did get error for this proc once (which I didn’t get for any other)
“……Execute sp_addlinkedserver to add the server to sysservers.”
Which was fixed by running
EXEC sp_addlinkedserver
'MYSERVER',
N'SQL Server'
GO

I also tried running the same proc on different servers, but nothing seems to make any difference.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-02-17 : 10:51:45

what's the performance of the 'exec' like when run direct on the other server?
can you post execution plans from the 2nd server?
and also from this server?

you may have 'network traffic' issues.

Mak
Starting Member

7 Posts

Posted - 2006-02-21 : 04:57:13

Many thanks for your reply again,
the problem is getting even bit strange now, if i run the procedure from query analyser it takes quite a long time, however when i call it from the application with same parameters its take long on first execution and later its pretty quick.

the only difference in query analyser & application is that i was running it with recompile and clearing cache in q. a.

Regards

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-02-21 : 05:20:34

Well - that seems to indicate that the application is using a cached version after the 1st execution - which is what you want. Because you keep freeing the proc cache, your query analyzer never gets that opportunity.

Can I recommend you generate the execution plan, and then try the sp a few times WITHOUT clearing the cache, and see what you execution times become.

Bottom line - is the performance, once cached, acceptable or not?

CiaO

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources