store procedure - with recompile

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

Transact-SQL (2000)

store procedure - with recompile

Author

Topic

ilimax
Posting Yak Master

164 Posts

Posted - 2007-03-17 : 00:28:18

Do I have to use WITH RECOMPILE if I have WHERE clouse in stored procedure ...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-17 : 00:55:13

take a look here http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?faqid=14

pootle_flump

1064 Posts

Posted - 2007-03-19 : 06:34:30

Other options:
http://www.sqljunkies.com/WebLog/amachanic/articles/StoredProcedureCaching.aspx
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

But to answer the question - no.

ilimax
Posting Yak Master

164 Posts

Posted - 2008-07-07 : 14:59:16

I am still confuesed about "WITH RECOMPILE" ....

This one of my procedure ... Procedure is called from our applications, built in VB6(ADO) and C# (ADO.net) ....

CREATE PROCEDURE Sales_One_Item
@Salco float OUTPUT,
@ItemNum nvarchar(30),
@fromdate datetime,
@todate datetime
AS
BEGIN
SET NOCOUNT ON
SELECT @Salco = SUM(Quantity)
FROM InvoiceItems
WHERE [Item Number] = @ItemNum AND ([Ship Date] BETWEEN @fromdate AND @todate)
GROUP BY [Item Number]
END
GO

New data is added to table InvoiceItems every second. Table has one clustered index, and non-clustered indexes on Item Number, and Ship Date ....

So, users always send similar parameters but never same. Or, very often application send same dates but differnet item number... By some of these articles I read Optimazer should handle this problem and I do not need WITH RECOMPILE ... But, I have noticed that sometimes result come slower ... I do not have idea why ... Do I have to use WITH RECOMPILE? ( I have SQL Server 2000)

This Dinamic SQL from link that "pootle flump" mentioned above (sqljunkies), looks good but I have never tried. What other people think about this? For me this solution looks smarter then using "WITH RECOMPILE" ...

ilimax
Posting Yak Master

164 Posts

Posted - 2008-07-11 : 08:46:47

?

Looks like nobody like my topic ...

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-11 : 22:29:58

Using it not not is not based on where clause, read books online.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-11 : 22:45:22

Read this:

http://www.devx.com/tips/Tip/13386