SP is RecordSource of the form. When form is opened SP executed and after a time-out of query, connection is closing with nothing. If SP executed from SSMS it performed for about 2 seconds and returns a set of records. As I watched through the SSMS Profiler calls are identical, but count of Reads value (an execute from Access) > 28 million, and about 70 thousand from the SSMS. Help me, I'm confused. Screen with profiler http://take.ms/u7tTy
Asked
Active
Viewed 62 times
1
-
Just a guess here: Count of Reads != Count of Rows. Could be an issue with bad query plans for the SP causing this, such as parameter sniffing. – tobypls Apr 28 '16 at 11:49
-
@BIDeveloper, 28 million reads from Access – anatol Apr 28 '16 at 12:23
-
`Could be an issue with bad query plans for the SP causing this, such as parameter sniffing.` @tobypls, what does it mean? – anatol Apr 28 '16 at 12:26
-
@anatol have a look at this http://stackoverflow.com/questions/6585417/stored-procedure-slow-when-called-from-web-fast-from-management-studio (web in this case is similar to MS Access in your case) – tobypls Apr 28 '16 at 12:33
-
It would help if you post a screen picture of the SQL Server Profiler output. – RichardCL Apr 28 '16 at 12:55
-
@richard345, I have no reputation to posting images. Get link. – anatol Apr 29 '16 at 03:42
1 Answers
2
@tobypls,
thank you very much - your link was helpful.
Simple solution is rewrite (for example)
from
ALTER PROCEDURE [dbo].[sproc]
@param1 int,
AS
SELECT * FROM Table WHERE ID = @param1
to
ALTER PROCEDURE [dbo].[sproc]
@param1 int,
AS
DECLARE @param1a int
SET @param1a = @param1
SELECT * FROM Table WHERE ID = @param1a
I get it from this post.
But if you need full understanding of trouble then you must read really great article
Slow in the Application, Fast in SSMS?
Understanding Performance Mysteries
