Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

Select on the basis of DAY component in datatime column. How can I improve the performance?

I have datatime column in one table with huge data and want to execute select on the basis of DAY component in datatime column. Due to huge data having issues in performance, how can I improve the performance? select * from tblErrorLog where DAY(errorDate) = 20

user-image
Question added by Muhammad Hammad Javaid , IT Project Manager , Dubai Islamic Bank
Date Posted: 2013/04/06
Panneer selvam xavier
by Panneer selvam xavier , Supply Chain Analyst / Trainer , Infonet

First of all, I'd recommend using the ISO-8601 standard format for date/time - it works regardless of the language and regional settings on your SQL Server.
ISO-8601 is the YYYYMMDD format - no spaces, no dashes - just the data: select * from tblErrorLog where errorDate = '20081220' Second of all, you need to be aware that SQL Server 2005 DATETIME always includes a time.
If you check for exact match with just the date part, you'll get only rows that match with a time of 0:00:00 - nothing else.
You can either use any of the recommend range queries mentioned, or in SQL Server 2008, you could use the DATE only date time - or you could do a check something like: select * from tblErrorLog where DAY(errorDate) = 20 AND MONTH(errorDate) = 12 AND YEAR(errorDate) = 2008 Whichever works best for you.
If you need to do this query often, you could either try to normalize the DATETIME to include only the date, or you could add computed columns for DAY, MONTH and YEAR: ALTER TABLE tblErrorLog ADD ErrorDay AS DAY(ErrorDate) PERSISTED ALTER TABLE tblErrorLog ADD ErrorMonth AS MONTH(ErrorDate) PERSISTED ALTER TABLE tblErrorLog ADD ErrorYear AS YEAR(ErrorDate) PERSISTED and then you could query more easily: select * from tblErrorLog where ErrorMonth = 5 AND ErrorYear = 2009 and so forth.
Since those fields are computed and PERSISTED, they're always up to date and always current, and since they're peristed, you can even index them if needed.

Better we can use hour component in datatime column.

Riaz Ghafoor
by Riaz Ghafoor , general manager hr administration , Decision HR Consultancy

How can I improve the performance?

  To improve my performance I must have the ability to set priorities and segregate the relevant from the irrelevant when facing the many tasks to do in a day.  I should possess a sense of urgency, the ability to get the job done fast.

Secondly which improve my performance at work is to develop a positive mental attitude. My seniors and colleagues quickly notice a consistent, persistent attitude of cheerfulness and optimism. When we make efforts to cultivate an attitude of friendliness toward people, they will make extraordinary efforts to open doors for you.

 I must upgrade work-related skills always making sure ‘and keeping seniors in knowledge, I should get additional courses to improve myself at my job and discuss and explain about benefits courses with them.  I may request for reimbursing the course fee if you pass saying these courses will improve and update my work activates, but make it clear that I go to take them anyway. Bosses are very impressed with people who are constantly striving to learn more in order to increase their value to their companies.

I agree that over 85 %of ones success in work will come from his/her personality and y ability to communicate effectively with others.

I can greatly improve how other people perceive me by continually looking for ways to boost their self-esteem throughout the workday. A little genuine praise and appreciation, on a regular basis, will cause people to like me and want to help. A likeable person is often perceived as being better at what they do than a person with a negative personality. When I am with my seniors , make it a practice to listen with respect, attentiveness and interest.

Honestly and sincerely listen to another person, the more that other person will like and trust one and want to give him additional help and responsibilities. All leaders are excellent listeners. It is a key method of influence. If one wants to get people on your side, practice asking interested questions and then listening intently to the answers.

More Questions Like This