Posted by Orly Junior on 06/16/05 12:37

By using the profiler, I found that while executing the first query (20 days
span), the system don't use the index. How it possible?

A simpler version of the query that causes the same problem is:

select checksum_agg(binary_checksum([dc])) from [table1] where [dc] between
'2003-01-01' and '2003-01-20'

The profiler reports it will be using a clustered index scan wich is
unacceptable since the table have a lot of records.

Why the hell it is not using the [dc] index ?? If a tight the criteria to
between a 10-day span it uses the index correctly.

Do you have any idea why is that happening?

Thanks in advance and best regards,

Orly Junior
IT Professional


"Orly Junior" <nomail@nomail.com> wrote in message
news:42b0c9e6$0$32014$a729d347@news.telepac.pt...
>
> Gentlemen,
>
> I am using the following query to get a list of grouped checksum data.
>
> SELECT CAST(Field0_datetime AS INT),
> CHECKSUM_AGG(BINARY_CHECKSUM(Field1_bigint, Field2_datetime,
> Field3_datetime, Field4_bigint, Field5_bigint, CAST(Field6_float
> Decimal(38,6)), Field7_datetime))
> FROM Table1
> WHERE Field0_datetime BETWEEN '2003-01-01' AND '2003-01-20'
> GROUP BY CAST(Field0_datetime AS INT)
>
> Please notice the used filter: from January 1 to January 20.
> That query takes about 6 minutes do return the data. The result is 18
> records.
>
> However, when I execute the same query filtering BETWEEN '2003-01-01' and
> '2003-01-10', this time it takes only 1 second to return data.
> When I execute the query filtering BETWEEN '2003-01-10' and '2003-01-20'
> the query takes another 1 second to return data.
>
> So why 6 minutes to process them together??
>
> The table have an index by Field0_datetime.
>
> It contains about 1.5 millions records total, using around 1.7Gb of
> diskspace, indexes included.
>
> From 2003-01-01 and 2003-01-20, there are 11401 records selected. Don't
> look like that much.
>
> The situation is repeatable, I mean, if I execute the queries back and
> again, they takes the about the same ammount of time to execute, so I
> don't think this problem is related to cache or something like that.
>
> I would appreciate any advice about what might be wrong with my situation.
>
> Thanks a lot and kind regards,
>
> Orly Junior
> IT Professional
>
>
>

[Back to original message]