I think the first thing to check is the parameters you pass in both cases. You need to verify that you pass the same parameters when executing from SSMS and from the application to have a performance comparison.
The next step could be compare the execution plan of both cases. If you find that SQL Server is using a different execution plan when executing from the application, that explains the reason for the performance difference.
Another point to consider is the environment settings. When you execute a query from SSMS, it runs within a certain environment created using the various SET statements: such as
ANSI_WARNINGS, ANSI_NULLS etc. You can see these settings from the tools>options page.
Your client component will be setting some of the settings ON or OFF when a connection is established. If the SET options turned ON or OFF at the time of running the query from SSMS and from the application are different, the performance can vary.
commented on Jan 19 2011 8:52AM