CODEWITHSUNDEEP
asp.netsql
Asha
Asha

Reputation: 134

Throwing time out expired exception while executing Query from ASP.NET

I am having a table with 1000000 values. From that I need to retrieve some values which are fulfilling my conditions. But when I executing the below mentioned query from ASP.NET it throws an TimeOut expired Exception. How to avoid this one

"SELECT ID,Intime,OutTime from [dbo].MasterLog 
WHERE CardId=(SELECT ID from User WHERE Name like 'ki%')"

It will be very useful if I got the answer. Thanks in advance

Upvotes: 4

Views: 676

Answers (4)

kaeparksal
kaeparksal

Reputation: 11

  1. WEB.CONFIG TIMEOUT option is long time

  2. IIS Application Pool TIMEOUT option is long time

  3. (Current Page) Request Header KeepAlive = true setting

Upvotes: 1

ljh
ljh

Reputation: 2594

Besides update your connection and command timeout, if you add a non-cluster index on CardID and include column ID, Intime, OutTime, which is a covering index, your query will be faster, that will help. 


CREATE NONCLUSTERED INDEX [IX_NCI_MasterLog_CardID] ON [dbo].[MasterLog]
(
    [CardID] ASC
)
INCLUDE (   [ID],
            [InTime],
            [OutTime[) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Upvotes: 2

Karthik AMR
Karthik AMR

Reputation: 1714

It sounds like you need to add an index to the column in the where clause of your query.

check this out

this also

  • And also think about Stored Procedures
  • Coz stored procedures will help a lot to retrieve data in a short time from DB.
  • Always avoid writing query in front end.

But first and most important thing is indexing, If you having more data means index it.

Upvotes: 4

vinodpthmn
vinodpthmn

Reputation: 1062

Try these,

  1. Specify SQL Connection Timeout and Command Timeout properly.
  2. Add index to the table on CardId
  3. Selecting TOP 100 / 1000 records first and try to get the rest as and when required to improve the performance of the query

Upvotes: 0

Related Questions