Query Performance

Hi,
I have one query for two groups of tables(3 tables). These two groups of
tables have the same data and schema but second group has been partitioned
based on OrderDate Column. By setting "Statistics IO On" I see very less IO
for second(partitioned) group and I expect it to run faster, but the query
for both groups finish in 10 seconds!
This is my query for non-partitioned tables:
select c.*,o.*,od.* from
sales.SalesOrderHeader o join Sales.SalesOrderDetail od
on o.salesorderid=od.salesorderid
join sales.customer c
on c.customerid=o.customerid
where (o.orderdate between '2003-01-01 00:00:00:000' and '2003-12-31
23:59:59:997')
and o.customerid>117
-- IO STATS:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 1618, physical reads
3, read-ahead reads 2383, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 703, physical reads 1,
read-ahead reads 699, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'Customer'. Scan count 1, logical reads 105, physical reads 1,
read-ahead reads 103, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
--
And for partitioned tables:
select c.*,o.*,od.* from
orders2 o join orderdetails od
on o.salesorderid=od.salesorderid and o.orderdate=od.orderdate
join sales.customer c
on c.customerid=o.customerid
where (o.orderdate between '2003-01-01 00:00:00:000' and '2003-12-31
23:59:59:997')
and o.customerid>117
-- IO STATS:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'OrderDetails'. Scan count 1, logical reads 748, physical reads 1,
read-ahead reads 743, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'Orders2'. Scan count 1, logical reads 318, physical reads 1,
read-ahead reads 316, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'Customer'. Scan count 1, logical reads 105, physical reads 1,
read-ahead reads 103, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
--
Is this what I must expect or the performance should be different?
Thanks in advance,
Leilayou'd be better off ot get rid of the paritions and create decent
indexes.
as a start,
customer.customerid
orders2.salesorderid
salesordersdetail.salesorderid
using * to describe your table name is a recipe for long term
catastrophes when a new guy adds a new table someday.|||I agree! Sometimes a non-partitioned covering index performs much better.
But I'm curious about the reason of same query performance with different
IOs!
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1147020901.724076.61860@.e56g2000cwe.googlegroups.com...
> you'd be better off ot get rid of the paritions and create decent
> indexes.
> as a start,
> customer.customerid
> orders2.salesorderid
> salesordersdetail.salesorderid
> using * to describe your table name is a recipe for long term
> catastrophes when a new guy adds a new table someday.
>|||ok, is the data IDENTICAL for each group of data?
external factors such as caching, how many times you have run the
query, and disk utilization can have an impact on results you are
seeing.
I would also question that salesorderdetails and orderdetails don't
have the same data.|||Actually one group has been created using Make Table Query (Select
...into...) from another group. Therefore the tables in the left of JOIN
keyword are the same, and the right tables are identical to each other in
each query.
I cleaned the cache each time before executing the query to force the query
to reference the disk. I'm sure that the conditions are exactly the same for
the queries!
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1147101576.517569.325820@.i40g2000cwc.googlegroups.com...
> ok, is the data IDENTICAL for each group of data?
> external factors such as caching, how many times you have run the
> query, and disk utilization can have an impact on results you are
> seeing.
> I would also question that salesorderdetails and orderdetails don't
> have the same data.
>