i have one query Where When i m filtering data on the basis of 2 dates it will take 2 min to execute
but when i remove that condition it takes 1 sec, only
5 2068
No. Using Dates in the Filter isn't affect the Performance. For Example the Following query take less than one minute to run. The Performance of the query is based on index then no of Rows in that table like this. -
select * from AccountEmployee
-
where convert(varchar,convert(datetime,CreatedOn,101),101) >= '05/04/2011'and
-
convert(varchar,convert(datetime,CreatedOn,101),101) <= '05/07/2011'
-
The short answer is yes, obviously your filter is affecting performance.
The long answer is no, using dates compared to any other data type does not result in longer query times.
The reason you're experiencing poor performance is most likely a result of poor design.
The next obvious question is, how do I fix the design? The answer is, no one knows, you've shared nothing with us about the design. We don't know what the query looks like, we don't know what the table structure looks like, we don't know what the data looks like, and we don't know what indexes you have.
Actually, it will affect your performance.
In general, using a function to a column in a WHERE clause will, most of the time, will tell the SQL Server not to use an index seek but will use an index scan. This is essentially a table scan. You will feel this in a large table. Always try not to use a function in your WHERE clause.
Check this out.
Good Luck!!!
~~ CK
@ck, The poster never said they used a function on the date. The function you're seeing is a reply from someone else.
Aahh yes, I miss that. Thanks Rabbit.
~~ CK
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Michael DeLawter |
last post by:
Using Access 2002.
I have a chart in a report that is currently based on a query in which
the user enters the start and end date for the chart to display. Both
the start and end dates have been...
|
by: Stewart Allen |
last post by:
Hi There
I have a function that does some calculations depending on the 2 date
arguments passed into it:
Function GetPeriods(dteStart As Date, dteEnd As Date) As Integer
The function has 2...
|
by: Guido |
last post by:
How to cope with query output which provides all possible values based
on a one to many relationship between two tables. I'm only interested
in one answer per primary key in the 'one' table. I've...
|
by: Bill R via AccessMonster.com |
last post by:
I have a query:
SELECT tblCalendar.CalendarDay AS LastSunday
FROM tblCalendar
WHERE (((tblCalendar.CalendarDay)>=(Now()-7) And (tblCalendar.CalendarDay)...
|
by: Kenneth Keeley |
last post by:
Hi,
I am trying to get the results out of an SQL database where the date is
greater than todays date.
this is thecode that I am trying to use.
SQLQuery = new SqlDataAdapter("SELECT Id, Title,...
|
by: ode |
last post by:
I am an infrequent user and my version is Access 2000. I have a list of 122k lines which consits of 3 fields/data points;a product code, a date, and a price code. I want to run a query to select...
|
by: panjap |
last post by:
hi, i was wondering could anyone help me with a query i have created. i have sucessfully worked out the total amount od prodcuts sold in a shop.
However i want a query to show the total sold of one...
|
by: Dan2kx |
last post by:
Good aft peeps,
i have a a working solution for this already but i suppose you could say it is a bit lazy,
I have used ADezii's calendar to display dates in my own unique way using the...
|
by: Michael Adams |
last post by:
I have a form that has two (2) calendar dates 1. fromdatetext, 2. todatetext. I would like to be able to search the records from query "Emp_Lst_Frm_Query" and give the sum results from the dates. I...
|
by: neelsfer |
last post by:
I have a table called RaceEntry2 and need to use a query to change the layout of the data.
Its currently in this format
RaceName =numberfield; racedate=datefield;lap1-8=datefields;...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |