Optimizing SQL Filtering - Conceptual question

I expose a simple example of what I'm trying to figure it out:
table1:
==== field1: PK, indexed
field2: FK, indexed
field3: numeric field, non indexed
field4: date field, non indexed
I want to know if this SQL is slower than the second:
select ... from table
where field3 = value // non indexed field first
and field1 = value // indexed field last
select ... from table
where field1 = value // indexed field first
and field3 = value // non indexed field last
Which is faster? Does it matter which field is before? Or the server
organize the fields automatically?
Is the order of fields in the 'where' part important to the query execution
speed?
If I put indexed fields first in the list, the query is executing faster
than if I put an non indexed field first?
I'm using SQL Server 2000.
Thank you very much
--
Daniel E. Alvarez
IMS Soluciones Tecnológicas S.A.
quilate@.kropol.com.arDaniel,
The order of fields in the WHERE clause should not affect the order that the
optimizer uses.
Are you seeing that behavior? (I was unclear from your text below.) I
would suggest rerunning your tests several times and take timings from each
test cycle.
Russell Fields
(For a very complex set of joins the optimizer must eventually stop
optimizing and execute. In a case like that, the order of the FROM and
WHERE clause MAY have the side-effect of changing where the optimizer
decides to move on.)
"Daniel Alvarez" <dalvarez@.flashmail.com> wrote in message
news:#NluJkTKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I expose a simple example of what I'm trying to figure it out:
> table1:
> ====> field1: PK, indexed
> field2: FK, indexed
> field3: numeric field, non indexed
> field4: date field, non indexed
> I want to know if this SQL is slower than the second:
> select ... from table
> where field3 = value // non indexed field first
> and field1 = value // indexed field last
> select ... from table
> where field1 = value // indexed field first
> and field3 = value // non indexed field last
> Which is faster? Does it matter which field is before? Or the server
> organize the fields automatically?
> Is the order of fields in the 'where' part important to the query
execution
> speed?
> If I put indexed fields first in the list, the query is executing faster
> than if I put an non indexed field first?
> I'm using SQL Server 2000.
> Thank you very much
> --
> Daniel E. Alvarez
> IMS Soluciones Tecnológicas S.A.
> quilate@.kropol.com.ar
>

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...
>
>

Optimizer not using Indexed View

Hi All
We are having problem on indexed views when we try to join to more than one
table that is not part of the original indexed view in a query. Has anybody
seen a similar problem?
Thanks
Atholl
See SQL examples below:
--New Indexed View - fact table grouped by three columns, no joins drop view
dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
CREATE VIEW dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
WITH SCHEMABINDING
AS
SELECT COUNT_BIG (*) AS Expr1
,a.business_unit_id
,a.date_id
,a.test_id
,sum(a.RECORD_COUNT_FAILED) record_count_failed
from dbo.fact_diagnostic_results a
group by a.business_unit_id, a.date_id, a.test_id
CREATE UNIQUE CLUSTERED INDEX [ivx_FACT_DIAGNOSTIC_RESULTS_1] ON
[dbo].[IV_FACT_DIAGNOSTIC_RESULTS_1]
(business_unit_id, date_id, test_id) ON [Indexes]
--WORKING - joined to 2 dimension tables, uses dimension table id columns in
group by select bu.business_unit_id
,d.date_id
,sum(a.record_count_failed) record_count_1
from dbo.fact_diagnostic_results a
inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_id
,d.date_id
--NOT WORKING - joined to 2 dimension tables, uses dimension table columns
in group by select bu.business_unit_srccd
,d.calendar_date_desc
,sum(a.record_count_failed) record_count_1
from dbo.fact_diagnostic_results a
inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_srccd
,d.calendar_date_desc
--WORKING - joined to 1 dimension table, uses dimension table columns in
group by select bu.business_unit_srccd
-- ,d.calendar_date_desc
,sum(a.record_count_failed) record_count_1
from dbo.fact_diagnostic_results a
inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
--inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_srccd
-- ,d.calendar_date_desc
I'm surprised that third query is using the indexed view -- what is the
execution plan for that?
Can you add business_unit_srccd to the view?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Atholl" <Atholl@.discussions.microsoft.com> wrote in message
news:6E3F6D63-6A57-4D9F-95B8-A6774E2D858B@.microsoft.com...
> Hi All
> We are having problem on indexed views when we try to join to more than
> one
> table that is not part of the original indexed view in a query. Has
> anybody
> seen a similar problem?
> Thanks
> Atholl
> See SQL examples below:
> --New Indexed View - fact table grouped by three columns, no joins drop
> view
> dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
> CREATE VIEW dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
> WITH SCHEMABINDING
> AS
> SELECT COUNT_BIG (*) AS Expr1
> ,a.business_unit_id
> ,a.date_id
> ,a.test_id
> ,sum(a.RECORD_COUNT_FAILED) record_count_failed
> from dbo.fact_diagnostic_results a
> group by a.business_unit_id, a.date_id, a.test_id
> CREATE UNIQUE CLUSTERED INDEX [ivx_FACT_DIAGNOSTIC_RESULTS_1] ON
> [dbo].[IV_FACT_DIAGNOSTIC_RESULTS_1]
> (business_unit_id, date_id, test_id) ON [Indexes]
> --WORKING - joined to 2 dimension tables, uses dimension table id columns
> in
> group by select bu.business_unit_id
> ,d.date_id
> ,sum(a.record_count_failed) record_count_1
> from dbo.fact_diagnostic_results a
> inner join dbo.lu_business_unit bu on a.business_unit_id =
> bu.business_unit_id
> inner join dbo.lu_date d on a.date_id=d.date_id
> group by bu.business_unit_id
> ,d.date_id
> --NOT WORKING - joined to 2 dimension tables, uses dimension table columns
> in group by select bu.business_unit_srccd
> ,d.calendar_date_desc
> ,sum(a.record_count_failed) record_count_1
> from dbo.fact_diagnostic_results a
> inner join dbo.lu_business_unit bu on a.business_unit_id =
> bu.business_unit_id
> inner join dbo.lu_date d on a.date_id=d.date_id
> group by bu.business_unit_srccd
> ,d.calendar_date_desc
> --WORKING - joined to 1 dimension table, uses dimension table columns in
> group by select bu.business_unit_srccd
> -- ,d.calendar_date_desc
> ,sum(a.record_count_failed) record_count_1
> from dbo.fact_diagnostic_results a
> inner join dbo.lu_business_unit bu on a.business_unit_id =
> bu.business_unit_id
> -- inner join dbo.lu_date d on a.date_id=d.date_id
> group by bu.business_unit_srccd
> -- ,d.calendar_date_desc
>
|||Hi Adam
The execution plan does a Clustered Index Scan on the view; why are you
surprised that it uses the view?
Yes I could add business_unit_srccd to the view but this is just one of
about 30 columns on the business_unit table, I don't want to add them all to
the view.
The interesting thing is that it seems to work as long as I have the ID
columns in the Group By on the query. This is strange because the ID columns
come from the dimension tables and aren't even part of the view so why are
they any different from the other columns on the dimension tables?
Atholl
"Adam Machanic" wrote:

> I'm surprised that third query is using the indexed view -- what is the
> execution plan for that?
> Can you add business_unit_srccd to the view?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Atholl" <Atholl@.discussions.microsoft.com> wrote in message
> news:6E3F6D63-6A57-4D9F-95B8-A6774E2D858B@.microsoft.com...
>
>
|||"Atholl" <Atholl@.discussions.microsoft.com> wrote in message
news:3E604176-E361-41C9-9FC6-4677B7DAE514@.microsoft.com...
> Hi Adam
> The execution plan does a Clustered Index Scan on the view; why are you
> surprised that it uses the view?
Even though it's doing a scan on the view, it still needs to do some
sort of lookup operation to get the values for that missing column -- how
expensive is that? I was assuming it would be a fairly expensive operation,
but perhaps the dimension is not large?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457

Optimizer not using Indexed View

Hi All
We are having problem on indexed views when we try to join to more than one
table that is not part of the original indexed view in a query. Has anybody
seen a similar problem?
Thanks
Atholl
See SQL examples below:
--New Indexed View - fact table grouped by three columns, no joins drop view
dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
CREATE VIEW dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
WITH SCHEMABINDING
AS
SELECT COUNT_BIG (*) AS Expr1
,a.business_unit_id
,a.date_id
,a.test_id
,sum(a.RECORD_COUNT_FAILED) record_count_failed
from dbo.fact_diagnostic_results a
group by a.business_unit_id, a.date_id, a.test_id
CREATE UNIQUE CLUSTERED INDEX [ivx_FACT_DIAGNOSTIC_RESULTS_1] ON
[dbo].[IV_FACT_DIAGNOSTIC_RESULTS_1]
(business_unit_id, date_id, test_id) ON [Indexes]
--WORKING - joined to 2 dimension tables, uses dimension table id columns in
group by select bu.business_unit_id
,d.date_id
,sum(a.record_count_failed) record_count_1
from dbo.fact_diagnostic_results a
inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_id
,d.date_id
--NOT WORKING - joined to 2 dimension tables, uses dimension table columns
in group by select bu.business_unit_srccd
,d.calendar_date_desc
,sum(a.record_count_failed) record_count_1
from dbo.fact_diagnostic_results a
inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_srccd
,d.calendar_date_desc
--WORKING - joined to 1 dimension table, uses dimension table columns in
group by select bu.business_unit_srccd
-- ,d.calendar_date_desc
,sum(a.record_count_failed) record_count_1
from dbo.fact_diagnostic_results a
inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
-- inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_srccd
-- ,d.calendar_date_descI'm surprised that third query is using the indexed view -- what is the
execution plan for that?
Can you add business_unit_srccd to the view?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Atholl" <Atholl@.discussions.microsoft.com> wrote in message
news:6E3F6D63-6A57-4D9F-95B8-A6774E2D858B@.microsoft.com...
> Hi All
> We are having problem on indexed views when we try to join to more than
> one
> table that is not part of the original indexed view in a query. Has
> anybody
> seen a similar problem?
> Thanks
> Atholl
> See SQL examples below:
> --New Indexed View - fact table grouped by three columns, no joins drop
> view
> dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
> CREATE VIEW dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
> WITH SCHEMABINDING
> AS
> SELECT COUNT_BIG (*) AS Expr1
> ,a.business_unit_id
> ,a.date_id
> ,a.test_id
> ,sum(a.RECORD_COUNT_FAILED) record_count_failed
> from dbo.fact_diagnostic_results a
> group by a.business_unit_id, a.date_id, a.test_id
> CREATE UNIQUE CLUSTERED INDEX [ivx_FACT_DIAGNOSTIC_RESULTS_1] ON
> [dbo].[IV_FACT_DIAGNOSTIC_RESULTS_1]
> (business_unit_id, date_id, test_id) ON [Indexes]
> --WORKING - joined to 2 dimension tables, uses dimension table id columns
> in
> group by select bu.business_unit_id
> ,d.date_id
> ,sum(a.record_count_failed) record_count_1
> from dbo.fact_diagnostic_results a
> inner join dbo.lu_business_unit bu on a.business_unit_id =
> bu.business_unit_id
> inner join dbo.lu_date d on a.date_id=d.date_id
> group by bu.business_unit_id
> ,d.date_id
> --NOT WORKING - joined to 2 dimension tables, uses dimension table columns
> in group by select bu.business_unit_srccd
> ,d.calendar_date_desc
> ,sum(a.record_count_failed) record_count_1
> from dbo.fact_diagnostic_results a
> inner join dbo.lu_business_unit bu on a.business_unit_id =
> bu.business_unit_id
> inner join dbo.lu_date d on a.date_id=d.date_id
> group by bu.business_unit_srccd
> ,d.calendar_date_desc
> --WORKING - joined to 1 dimension table, uses dimension table columns in
> group by select bu.business_unit_srccd
> -- ,d.calendar_date_desc
> ,sum(a.record_count_failed) record_count_1
> from dbo.fact_diagnostic_results a
> inner join dbo.lu_business_unit bu on a.business_unit_id =
> bu.business_unit_id
> -- inner join dbo.lu_date d on a.date_id=d.date_id
> group by bu.business_unit_srccd
> -- ,d.calendar_date_desc
>|||Hi Adam
The execution plan does a Clustered Index Scan on the view; why are you
surprised that it uses the view?
Yes I could add business_unit_srccd to the view but this is just one of
about 30 columns on the business_unit table, I don't want to add them all to
the view.
The interesting thing is that it seems to work as long as I have the ID
columns in the Group By on the query. This is strange because the ID column
s
come from the dimension tables and aren't even part of the view so why are
they any different from the other columns on the dimension tables?
Atholl
"Adam Machanic" wrote:

> I'm surprised that third query is using the indexed view -- what is the
> execution plan for that?
> Can you add business_unit_srccd to the view?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Atholl" <Atholl@.discussions.microsoft.com> wrote in message
> news:6E3F6D63-6A57-4D9F-95B8-A6774E2D858B@.microsoft.com...
>
>|||"Atholl" <Atholl@.discussions.microsoft.com> wrote in message
news:3E604176-E361-41C9-9FC6-4677B7DAE514@.microsoft.com...
> Hi Adam
> The execution plan does a Clustered Index Scan on the view; why are you
> surprised that it uses the view?
Even though it's doing a scan on the view, it still needs to do some
sort of lookup operation to get the values for that missing column -- how
expensive is that? I was assuming it would be a fairly expensive operation,
but perhaps the dimension is not large?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--