0

I have a C# WinForms application with SQL express DB,

I have a table contains 20 columns and 2 lacks records, the table having 6 indexes 1 is clustered index and 5 are a non clustered index.

in 5 indexes 3 columns are varchar datatype and 1 column is bit and 1 is a numeric datatype.

My Application scenario is

we have 2 Application and PC both the pc running different applications and accessed single DB located at PC 1-

both the applications running continuously and hit on DB for "UPDATE" records and sometimes "SELECT" some record.

We have no concern about update statement, but whenever application hit select query then sometimes application takes more time than normal, I have very much concern about the select statement result. below is my query which is called from a stored procedure.

SELECT Count(PrimaryKey) as CNT From Tabel where Col1 ="AAAAAAAA" 
and (Col2 =0 OR Col2 is null) and Col3 =0

Most of the times the SP given result in 1 or 2 milliseconds but sometimes it takes 45,60, 90,189, 260 e.t.c millisecond, which not useful for my applications

I am facing this issue at second PC which is remotely accessed DB though LAN.

The execution time has been confirmed by application logs which maintained by my application and through SQL Profiler.

My expected result is in 1 to 30 millisecond not more than 30,

Please help me on this for getting consistent time result.

Thank you so much in advance.

Thanks,

Sagar Kerlekar.

Sagar Kerlekar
  • 9
  • 2
  • please google for "SQL Server stored procedure runs fast in SSMS and slow in application" it is well explained in many, many places – Selvin Jan 28 '19 at 11:04
  • Dear Selvin,I have already checked all the options which posted on stack overflow and other forums which is not resolved my problem, – Sagar Kerlekar Jan 28 '19 at 11:11
  • 1
    There is a huge amount of resource in the 3 topics which this question is currently marked as a duplicate of. If you feel that none of these (or other) questions answer your question, then please post a new question. When you do so, however, please ensure you include everything you have tried (the SQL/asp.net code you've changed, settings you've adjusted, etc) which is outlined in the questions above; that includes the parts from the linked articles such as the article by [Erland Sommarsko](http://www.sommarskog.se/query-plan-mysteries.html), which is a very informative on the behaviour. – Thom A Jan 28 '19 at 11:19
  • Dear Larnu, I have tried most of the options which is posted by you and other too, but not resolved my issue, Regarding the Erland Sommarsko post mentioned issue is taken results in seconds, I expecting in milliseconds which is not coming consistent. I have also expained my whole application and there execution also. please go through it. – Sagar Kerlekar Jan 28 '19 at 11:44

1 Answers1

1

Slow in the Application, Fast in SSMS? Understanding Performance Mysteries

In general, it often related to SET options.

SSMS has own SET values, which your app perhaps missing.

Just run DBCC USEROPTIONS in both: SSMS and your app and compare them

Alexander Volok
  • 5,630
  • 3
  • 17
  • 33
  • Dear Alexander, I have set ANSI_NULLS ,QUOTED_IDENTIFIER ,NOCOUNT and arithabort, Please suggest do I require to set more, Please Note : - As per suggestions by other post , I faced problem once or twice in each 100 or 200 hits on db., Also I am expected result in Millisecond. – Sagar Kerlekar Jan 28 '19 at 11:34