Why is this query faster without an index?

Question

I have inherited a new system and I am trying to make some improvements to the data. I am trying to improve this table and seem to be unable to understand my findings.

I have the following table structure:

CREATE TABLE [dbo].[Calls]( [CallID] [varchar](8) NOT NULL PRIMARY KEY, [RecvdDate] [varchar](10) NOT NULL, [yr] [int] NOT NULL, [Mnth] [int] NOT NULL, [CallStatus] [varchar](50) NOT NULL, [Category] [varchar](100) NOT NULL, [QCall] [varchar](15) NOT NULL, [KOUNT] [int] NOT NULL) 

This table contains about 220 thousand records. I need to return all records whose date is greater than a certain date. In this case, 12/1/2009. This query will return about 66 thousand records, and it will take about 4 seconds to start. From past systems that I worked on, this seems high. Especially considering how few records there are in the table. Therefore, I would like to bring this time.

So, I wonder what would be good ways to bring this to an end? I tried to add a date column to the table and convert the row date to the actual date column. Then I added an index to this column, but the time remained the same. Given that there are not many records, I can see how a table scan can be fast, but I would think that an index could shorten the time.

I also considered simply querying the columns of the month and year. But I have not tried it yet. And I would like, if possible, to leave it in the "Date" field. But if not, I can change it.

Any help is appreciated.

EDIT: here is the query I'm trying to run and check the speed of the table. I usually expose the columns, but for simplicity I used *:

 SELECT * FROM _FirstSlaLevel_Tickets_New WHERE TicketRecvdDateTime >= '12/01/2009' 

EDIT 2: So I mentioned that I was trying to create a table with a date column containing the recvddate data, but as a date, not varchar. This is what the TicketRecvdDateTime column is in the query above. The original query that I run against this table is:

 SELECT * FROM Calls WHERE CAST(RecvdDate AS DATE) >= '12/01/2009' 

Answer 1

You may have come across what is called the Tipping Point in SQL Server. Although you have the appropriate index in the column, SQL Server may decide to scan the table anyway if the expected number of rows returned exceeds a certain threshold (the “polling point”).

In your example, this seems likely, since you rotate 1/4 of the number of rows in the database. The following is a good article that explains this: http://www.sqlskills.com/BLOGS/KIMBERLY/category/The-Tipping-Point.aspx

Answer 2

SELECT * will usually work poorly.

Either the index will be ignored, or you will receive a key / bookmark in the clustered index. It doesn't matter: both can work poorly.

For example, if you had this request and a pointer to TicketRecvdDateTime INCLUDEd CallStatus, then it most likely will work as expected. It will be covering

 SELECT CallStatus FROM _FirstSlaLevel_Tickets_New WHERE TicketRecvdDateTime >= '12/01/2009' 

This is in addition to Randy Minder's answer: finding a key / bookmark can be cheap enough for a few rows, but not for a large piece of table data.

Answer 3

Your query is faster with an index (or, more precisely, with the same speed w / or without indeX), because the index in RecvdDate will always be ignored in an expression like CAST(RecvdDate AS DATE) >= '12/01/2009' . This is a non-SARG expression because it requires the column to be converted through a function. In order for this event to be considered, you must accurately express the filter criteria on an indexed column, and not on an expression based on it. This will be the first step.

There are several steps:

• Get rid of the VARCHAR (10) column for dates and replace it with the corresponding DATE or DATETIME column. Storing the date and / or time when lines are riddled with problems. Not only for indexing, but also for correctness.
• A table that is often scanned in a column-based range (since most such call log tables) must be clustered by this column.
• It is unlikely that you really need the yr and mnth . If you really need them, then you probably need them as computed columns.

.

 CREATE TABLE [dbo].[Calls]( [CallID] [varchar](8) NOT NULL, [RecvdDate] [datetime](10) NOT NULL, [CallStatus] [varchar](50) NOT NULL, [Category] [varchar](100) NOT NULL, [QCall] [varchar](15) NOT NULL, [KOUNT] [int] NOT NULL, CONSTRAINT [PK_Calls_CallId] PRIMARY KEY NONCLUSTERED ([CallID])); CREATE CLUSTERED INDEX cdxCalls ON Calls(RecvDate); SELECT * FROM Calls WHERE RecvDate >= '12/01/2009'; 

Of course, the correct structure of the table and indexes should be the result of a thorough analysis, taking into account all factors, including update efficiency, other queries, etc. I recommend that you start by looking at all the topics included in Index Design .

Answer 4

Can you change your request? If multiple columns are required, you can modify the SELECT clause to return fewer columns. And then you can create a coverage index that includes all referenced columns, including TicketRecvdDateTime .

You can create an index on TicketRecvdDateTime , but you cannot avoid the tipping point that @Randy Minder talks about. However, scanning a lower index (less than scanning a table) will return fewer pages.

Answer 5

Assuming RecvdDate is the TicketRecvdDateTime you are talking about:

SQL Server only compares dates in single quotes if the field type is DATE. Your query probably compares them as VARCHAR. try adding a line with '99 / 99/0001 'and see if it is displayed below.

If so, your query results are incorrect. Change the type to DATE.

Note that VARCHAR is not indexed well, DATETIME does.

Check the query plan to see if its indexes use it. If the database is small compared to the available RAM, it can simply scan the table and store everything in memory.

EDIT: Having seen the CAST / DATETIME changes, let me point out that parsing a date from VARCHAR is a very expensive operation. You do it 220k times. This will kill performance.

Also, you no longer check the indexed field. comparison with an expression that includes an index field does not use an index.