0

I have an C# winform application with sql express db,

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

in 5 index 3 columns are varchar datatype and 1 column is bit and 1 is 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 store 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 millisecond 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 access 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.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
Sagar Kerlekar
  • 9
  • 2
  • Possible duplicate of [SQL Server stored procedure runs fast in SSMS and slow in application](https://stackoverflow.com/questions/54400516/sql-server-stored-procedure-runs-fast-in-ssms-and-slow-in-application). Please don't just re-post questions when they're closed; explain in the question itself why it's not a duplicate. – Jeroen Mostert Jan 29 '19 at 10:29
  • Jeron, please try to understand my question, have deeply study my question, – Sagar Kerlekar Jan 29 '19 at 10:39
  • I am facing problem sometimes in while continue running my application, AS per my understanding you link is solved my problem, in that case user face problem every time while they hit sp on db , also they are expected results in seconds, In my scenario I am expecting result in milliseconds also My SP not take time everytime when it hit on db. it was taken a time randomly after 100 times, 200 Most of the time SP given me result in 1 milliseconds. Please try to understand my scenario. Your help is appreciable – Sagar Kerlekar Jan 29 '19 at 10:48
  • Is it possible you are waiting for a lock over an index updating while you perform a SELECT or other UPDATE statements? Try to balance your indexes, that while they MAY increase SELECT performance can adversely impact UPDATE timings – LittleSweetSeas Jan 29 '19 at 11:00
  • How to balance the index , can you please explain, Thanks – Sagar Kerlekar Jan 29 '19 at 11:42

1 Answers1

0

If it's happening randomly, check if the table updates are being fired during that time when the queries are slow. This could be one of the possibility. Other possibility is that your indexes are not correctly created, i.e., there are indexes which doesn't have correct order of fields, which are being used in the query. (i.e., you have non clustered index with column order as col1, col3, col2 and that is being used in the query execution). For this you can check the execution plan of the query and check if it's doing a index seek/scan.

If the problem is during update, you can try using set transaction isolation level read uncommitted to fetch result faster.

mukesh joshi
  • 584
  • 5
  • 19