Text Query Faster than Stored Procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Text Query Faster than Stored Procedure?

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2008-09-15 : 14:24:27

I've got a Text Query that I wrote in Query Analyzer. I got it tweaked enough that it runs (though it still needs work) and takes about 2 seconds per employee. I created a stored procedure for it so that I could begin with a little testing, and I am finding the it takes the stored procedure about 40 seconds per employee.

How could this be? Aren't stored procedures supposed to be faster?

Here is my text in Query Analyser:

Declare @Date1 DateTime
Declare @Date2 DateTime
Declare @Name nvarchar(100)
set @Date1='08/15/2008'
set @Date2='09/15/2008'
set @Name='Dixie Wright'
SELECT A.Serial_Number, CoilType, A.Date_Time, B.OP_ID, A.Test_Result AS 'Chamb', B.Test_Result AS 'Decay'
FROM Test_Results A
  INNER JOIN (SELECT OP_ID, Serial_Number, System_ID, Test_Result FROM Test_Results WHERE (OP_ID=@Name) AND (System_ID Like '%Decay%') AND (Test_Result Not Like '%Abort%')) B ON (A.Serial_Number=B.Serial_Number)
  INNER JOIN (SELECT CoilType, Serial_Number FROM ACP_Parts WHERE (System_ID Like '%Label%')) C ON (A.Serial_Number=C.Serial_Number)
WHERE (A.System_ID Like '%Chamber%') AND (A.Date_Time BETWEEN @Date1 AND @Date2)
ORDER BY A.Date_Time, A.Serial_Number

Here is my stored procedure:

CREATE PROCEDURE sp_BrazerAtChamb(@Name nvarchar(100), @Date1 DateTime, @Date2 DateTime) AS
SELECT A.Serial_Number, CoilType, A.Date_Time, B.OP_ID, A.Test_Result AS 'Chamb', B.Test_Result AS 'Decay'
FROM Test_Results A
  INNER JOIN (SELECT OP_ID, Serial_Number, System_ID, Test_Result FROM Test_Results WHERE (OP_ID=@Name) AND (System_ID Like '%Decay%') AND (Test_Result Not Like '%Abort%')) B ON (A.Serial_Number=B.Serial_Number)
  INNER JOIN (SELECT CoilType, Serial_Number FROM ACP_Parts WHERE (System_ID Like '%Label%')) C ON (A.Serial_Number=C.Serial_Number)
WHERE (A.System_ID Like '%Chamber%') AND (A.Date_Time BETWEEN @Date1 AND @Date2)
ORDER BY A.Date_Time, A.Serial_Number
GO

I called the stored procedure in Query Analyzer using this:

exec sp_BrazerAtChamb 'Dixie Wright', '08/15/2008', '09/15/2008'

Does anyone see why my stored procedure would take longer to run than the text only version in query analyzer?

Also, a minor annoyance that I have not been able to figure out yet: The stored procedure returns the same serial_number/date_time once for a Chamber Test and again for the Decay Test. I'd really like to reduce this to only seeing one for each date_time record - these serial_numbers can not physically be tested at both system_ids at the same time.

Avoid Sears Home Improvement

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-15 : 14:27:42

This is probably due to parameter sniffing.

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

Subscribe to my blog

jp2code
Posting Yak Master

175 Posts

Posted - 2008-09-15 : 14:56:43

Thanks tkizer! I'd never heard of "Parameter Sniffing". A google search fixed the timing issue.

Any idea how to prevent duplicates? Only one record should display for a given Date_Time:

C710154 1036 08     	Condenser                	2008-08-15 00:03:19.347	Dixie Wright	Pass.	PASSED PD
C036049 1082 08     	Evaporator               	2008-08-15 10:50:12.330	Dixie Wright	FAIL: FINE TEST ABORT.	PASSED PD
C036049 1081 08     	Evaporator               	2008-08-15 11:39:43.403	Dixie Wright	Pass.	PASSED PD
C710225 1093 08     	Condenser                	2008-08-18 07:58:33.040	Dixie Wright	Pass.	PASSED PD
C710224 1081 08     	Condenser                	2008-08-18 09:42:45.590	Dixie Wright	Pass.	FAILED PD, FAIL, Final Pressure Decay, 
C710224 1081 08     	Condenser                	2008-08-18 09:42:45.590	Dixie Wright	Pass.	PASSED PD
C710224 1088 08     	Condenser                	2008-08-18 10:01:28.157	Dixie Wright	Pass.	PASSED PD
C710224 1086 08     	Condenser                	2008-08-18 12:34:15.607	Dixie Wright	Pass.	PASSED PD
C710224 1100 08     	Condenser                	2008-08-18 12:43:39.113	Dixie Wright	Pass.	PASSED PD
C710142 1092 08     	Condenser                	2008-08-18 16:26:16.730	Dixie Wright	FAIL: Fine Test.	PASSED PD
C710142 1097 08     	Condenser                	2008-08-18 16:42:56.933	Dixie Wright	FAIL: FINE TEST ABORT.	PASSED PD
CP19013 1175 08     	Evaporator               	2008-08-18 18:26:49.657	Dixie Wright	Pass.	PASSED PD
CP19013 1176 08     	Evaporator               	2008-08-18 18:49:18.320	Dixie Wright	Pass.	PASSED PD
C035123 1039 08     	Evaporator               	2008-08-18 20:40:03.430	Dixie Wright	FAIL: FINE TEST ABORT.	PASSED PD
C035123 1038 08     	Evaporator               	2008-08-18 21:09:47.020	Dixie Wright	Pass.	PASSED PD

Avoid Sears Home Improvement

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-15 : 15:03:05

You can handle that through a GROUP BY.

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

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 15:21:17

GROUP BY duplicated columns and apply MIN() or MAX() on others

jp2code
Posting Yak Master

175 Posts

Posted - 2008-09-15 : 15:36:45

visakh16:

How could I use GROUP BY with ORDER BY? I saw several questions about this online, but most of those sites were blocked by our proxy.

I tried replacing the single "ORDER BY" line above with this:

GROUP BY A.Date_Time
ORDER BY A.Serial_Number

But, I got a Huge SQL-DMO (ODBC SQLState: 42000) error! (5 lines in my messagebox!)

Avoid Sears Home Improvement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources