Click here to Skip to main content
15,867,835 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
How to ignore late response in sql on heavy data

What I have tried:

I have tried to execute the query in server, it takes 50 seconds to 1 minute time to response the full data.
Posted
Updated 9-Mar-16 2:29am
Comments
CHill60 9-Mar-16 7:16am    
What query?
And what do you mean by "ignore late response"?
itsathere 9-Mar-16 8:01am    
select ID, ErrorDescription, ErrorPage, IpAddress,
PageEvent, Status, CONVERT(varchar(50),CreatedDate,103) as CreatedDate,
(case when Status=1 then 'Open' when Status=0 then 'Resolved' end) as IsActive from dbo.ExceptionLog order by ID desc


this query takes above 60 seconds on execution


1 solution

Try removing the CONVERT. Do that conversion in your presentation layer.

Doing something similar with IsActive will probably help too.

As an aside, try to avoid using reserved words for column or table names. If you must use them then surround them with square brackets.
select ID, ErrorDescription, ErrorPage, IpAddress, 
 PageEvent, [Status], CreatedDate,
 [Status] as IsActive from dbo.ExceptionLog order by ID desc

Check to see if there are any indexes on this table - on ID at the very least.
 
Share this answer
 
Comments
itsathere 9-Mar-16 8:45am    
still takes 30 seconds and my query takes 23 seconds
CHill60 9-Mar-16 9:02am    
What takes 23 seconds? You said it took 60 ... a 50% improvement isn't bad!
Did you check the indexes?
There are several articles here on CodeProject with other suggestions
itsathere 9-Mar-16 9:12am    
Did you check the indexes?

explain please
CHill60 9-Mar-16 9:20am    
This article explains it better than I could - Understanding SQL Server Indexing[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900