OPTIMIZING QUERY

I query two fields in my string.. Those are myHour and myCounty. There are
about 5 million records.
myHour , myCountry and cpm fields are indexed. It returns too late..
SELECT SUM(cpm) from dbilgi where myHour >= '2006-02-05 00:00:00' and
myHour <= '2006-02-05 23:59:59' and myCountry = 'TR'
when i add myCountry = 'TR' it is getting slower.It takes 45 seconds to
return datas after i run my query.
How can optimize my query...you mean you have three separate indexes - one on myHour, one on myCountry,
and one on cpm column? in that case try with covered index on all three
columns, this should speed things up.
dean
"Savas Ates" <in da club> wrote in message
news:%236XzUo2KGHA.1088@.tk2msftngp13.phx.gbl...
>I query two fields in my string.. Those are myHour and myCounty. There are
> about 5 million records.
> myHour , myCountry and cpm fields are indexed. It returns too late..
> SELECT SUM(cpm) from dbilgi where myHour >= '2006-02-05 00:00:00' and
> myHour <= '2006-02-05 23:59:59' and myCountry = 'TR'
> when i add myCountry = 'TR' it is getting slower.It takes 45 seconds
> to
> return datas after i run my query.
>
> How can optimize my query...
>
>|||Without knowing too much about the table structure, and if there are any
clustered indexes, here is what is (probably) happening.
SELECT SUM(cpm) from dbilgi where myHour >= '2006-02-05 00:00:00' and
myHour <= '2006-02-05 23:59:59'
would (probably) result in an index s on the nonclustered index for
myHour, with a bookmark lookup to either the clustered index or table. Resul
t
is returned fairly quick.
Adding the condition for myCountry
SELECT SUM(cpm) from dbilgi where myHour >= '2006-02-05 00:00:00' and
myHour <= '2006-02-05 23:59:59' and myCountry = 'TR'
The optimizer sees that either
-the majority of rows in dbilgi have myCoutry = 'TR' , or
-the majority of rows in dbilgi, where myHour is between '2006-02-05
00:00:00' and
'2006-02-05 23:59:59', have a myCountry = 'TR'
and chooses to perform a table scan (or clustered index scan) instead of
using the indexes. Here the optimizer decides that the cost of the bookmark
lookups will be more expensive than just scanning the whole table (or
clustered index).
As Dean mentioned in an earlier reply, you could create a composite index on
myCountry, myHour, and cpm to make the above query faster, but building that
index may take quite a long time on a table with 5 million+ rows.
You could also try using the WITH (INDEX(index_name)) table hint to force
the use of your nonclustered indexes on myCountry and myHour.
"Savas Ates" wrote:

> I query two fields in my string.. Those are myHour and myCounty. There ar
e
> about 5 million records.
> myHour , myCountry and cpm fields are indexed. It returns too late..
> SELECT SUM(cpm) from dbilgi where myHour >= '2006-02-05 00:00:00' and
> myHour <= '2006-02-05 23:59:59' and myCountry = 'TR'
> when i add myCountry = 'TR' it is getting slower.It takes 45 seconds
to
> return datas after i run my query.
>
> How can optimize my query...
>
>|||Savas Ates (in da club) writes:
> I query two fields in my string.. Those are myHour and myCounty. There
> are about 5 million records.
> myHour , myCountry and cpm fields are indexed. It returns too late..
> SELECT SUM(cpm) from dbilgi where myHour >= '2006-02-05 00:00:00' and
> myHour <= '2006-02-05 23:59:59' and myCountry = 'TR'
> when i add myCountry = 'TR' it is getting slower.It takes 45
> seconds to return datas after i run my query.
Judging from this query alone, a clustered index on myHour could be a
good bet. Or a non-clustered index on (myHour, myCountry, cpm) or
even (myCountry, myHour, cpm). But the latter index would not be
use for the query without myContry.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In addition to what everyone else says. Check out the plan and see what is
happening and what is most costly in each. Do this again after adding
indexes. Figuring out what goes on internally will make this kind of stuff
easier.
One other little point. This value: '2006-02-05 23:59:59' for a date has
two problems.
For smalldatetime:
declare @.date smalldatetime
set @.date = '2006-02-05 23:59:59'
select @.date
Returns:
2006-02-06 00:00:00
declare @.date datetime
set @.date = '2006-02-05 23:59:59.003'
select @.date
select case when @.date <= '2006-02-05 23:59:59' then 1 else 0 end
0
Because of this, your where clause leaves a one second gap. Use '2006-02-05
23:59:59.997' instead (it is only 1 second, but it can make a difference)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Savas Ates" <in da club> wrote in message
news:%236XzUo2KGHA.1088@.tk2msftngp13.phx.gbl...
>I query two fields in my string.. Those are myHour and myCounty. There are
> about 5 million records.
> myHour , myCountry and cpm fields are indexed. It returns too late..
> SELECT SUM(cpm) from dbilgi where myHour >= '2006-02-05 00:00:00' and
> myHour <= '2006-02-05 23:59:59' and myCountry = 'TR'
> when i add myCountry = 'TR' it is getting slower.It takes 45 seconds
> to
> return datas after i run my query.
>
> How can optimize my query...
>
>