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.

Thanks jacob for your reply. i will work on it.I know that how to check execution plan of query in sql server but how can i check excution plan of query in any application or for any application.

Apart from it i have few questions that, i have 2 database having same structure,procedure,function and other stuffs.Here when i execute a procedure then it takes all most same time in both database. but when i call them using my code/application(vb forms) then it take 5-10 sec in one database and near about 10 min in 2nd database.What could be reason for it.

my next question is that why i am facing such problem in .net forms with few procedure. though I have procedures runs smoothly,having lengthly and heavy calculations with huge data.

Alok,

One quick way to identify the execution plan of the queries being run on the server is to run the Who is active stored procedure created by Adam Machanic. You can run your .NET application and while the query is running, run the stored procedure. One of the columns returned by the procedure is the execution plan.

Some times the performance may vary between two databases even if they have the same data. A few factors that may affect is the capabilities of the disks where the data and log files are stored, where statistics are correctly updated or not, fragmentation of the data etc.

One point that I mentioned in the original post and you did not mention in your reply is about the status of various SET options. I would recommend you investigate that too.

Thanks again for your reply.Actually i have no idea that how to set status of SET options from my application/Code (.net).I am working on it.

The question is not about being able to set it. But to check what are the SET options enabled when your client executes the query. You can find it out with the Who-is-active procedure I mentioned in the previous post.

If you see a difference in the SET options used by SSMS and your client application, you can either change the SET options on SSMS from the options dialog or execute a 'SET #option# ON/OFF' on your connection before executing the stored procedure. This will make sure that both executions are done under identical environment and then you can compare the results again.

i used Who-is-active procedure and get difference in SET options of execution of my procedure from SSMS and from application. i am doing work with Who-is-active procedure and i will post the actual result of it.

but today i was Altering the same procedure ,after altering that procedure as i call it in my application it took only few seconds to show the data.I am sure that i did nothing with SET options in these 2-3 days. even before altering that procedure application was taking same time as it was taking previously. then what could be the cause behind it. Is it something related with query that i am using in procedure or something else.

one more detail about my procedure is that i am taking actually query in an variable and then executing it using EXEC and doing pivot and using Temp Table.

If the performance improved after you altered the stored procedure, it indicates that the problem could be with a BAD plan cache. When you alter the stored procedure it has to be recompiled and a new plan needs to be created in the next execution.