InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 11:21:36 GMT20http://www.sqlservercentral.com/Forums/Topic886593-146-1.aspxhttp://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices--Parameter-Sniffing.htmMJFri, 19 Mar 2010 14:51:08 GMTMANU-J.http://www.sqlservercentral.com/Forums/Topic886593-146-1.aspx[quote][b]balasach82 (3/19/2010)[/b][hr]I use a Stored Procedure (with parameter) which uses 3/4 joins to retrieve some fields.i.e., a select from 3/4 tables. The problem is whenever the SP is executed for a particular input for the first time, it takes 5 to 6 mins to retrieve records(1 to 100). But the same SP for the same parameter after that completes in a shorter duration. If the parameter is changed, then it takes similarly long time, but the second execution for the same parameter in shorter duration. Execution plan does not have any table scan.There are indexes present in all the tables.recompiling SP, Freeing cache does not have desired effect.SQL 2005Any recommendations?[/quote]Freeing cache will make it slower on the next run as well.Do you have appropriate indexes?Have you checked for parameter sniffing?how do the statistics appear for your tables?How is the index fragmentation level?Fri, 19 Mar 2010 12:01:45 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic886593-146-1.aspxI use a Stored Procedure (with parameter) which uses 3/4 joins to retrieve some fields.i.e., a select from 3/4 tables. The problem is whenever the SP is executed for a particular input for the first time, it takes 5 to 6 mins to retrieve records(1 to 100). But the same SP for the same parameter after that completes in a shorter duration. If the parameter is changed, then it takes similarly long time, but the second execution for the same parameter in shorter duration. Execution plan does not have any table scan.There are indexes present in all the tables.recompiling SP, Freeing cache does not have desired effect.SQL 2005Any recommendations?Fri, 19 Mar 2010 11:16:25 GMTbalasach82