Hi,
I have to two colums
first colum second colum
1 0
2 0
3 0
4 1
5 1
6 2
7 2
8 3
9 3
10 4
11 5
What I need to do is retrieving all the records from "second colum" which equal to the records in "first colum" and the records in that "first colum" must have 0 in "second colum."
In short: I need to retrieve the records in second colum: 1, 1, 2, 2, 3, 3
Please, help me with that queryCan I just check:
Select the unique numbers from the second column, check that that number exists in the first column, then check if those rows have 0 as the second column?
|||Ugly as sin...
SELECT col1
FROM testTable
WHERE (col1 IN
(SELECT col1
FROM testTable
WHERE (col1 IN
(SELECT DISTINCT col2
FROM testtable)))) AND (col2 = 0)
|||in your example, i don't see any values in the second column that equal the first column...could you verify that?
the query would look something like the following though:
select second_column
from [yourTableName]
where second_columsn <> 0 AND second_column = first_column
|||Hey Jayson, I think you missed his question just by a bit...It isn'tRows where first column = second column, but where second column [values] are infirst column...Sort of a parent id type of thing.
Simplistically, I think this is what he's looking for:
SELECT [second column] FROM table
WHERE [second column] IN (SELECT [first column] FROM table WHERE [second column] = 0)
Showing posts with label second. Show all posts
Showing posts with label second. Show all posts
Wednesday, March 28, 2012
Tuesday, March 20, 2012
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.
>
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.
>
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.
>
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.
>
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.
>
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.
>
Monday, March 12, 2012
Query over two columns
Hi,
I have a table that includes two Datetime columns. The first column holds
the date. e.g 2006/5/10 and the second column holds time e,g 08:15:25.
I am having a problems specifing a query that will list the rows in the
table between a date time range.
i.e return all events between 2006/5/8 09:00:00 and 2006/5/10 21:30:00
Can anyone give me any suggesstions on how to do this?
Thanks
Maccawhy do you store it in two datetime columns?
or is it 2 varchar columns?
If so then try this.
select * from [a table]
where cast(datecol + ' ' + timecol as datetime) between
'2006/5/8 09:00:00' and '2006/5/10 21:30:00'|||Would it be an idea to have an overlay view where you make a
combination of both the date and the time field into a real datetime
format?|||Would it be an idea to have an overlay view where you make a
combination of both the date and the time field into a real datetime
format?
I have a table that includes two Datetime columns. The first column holds
the date. e.g 2006/5/10 and the second column holds time e,g 08:15:25.
I am having a problems specifing a query that will list the rows in the
table between a date time range.
i.e return all events between 2006/5/8 09:00:00 and 2006/5/10 21:30:00
Can anyone give me any suggesstions on how to do this?
Thanks
Maccawhy do you store it in two datetime columns?
or is it 2 varchar columns?
If so then try this.
select * from [a table]
where cast(datecol + ' ' + timecol as datetime) between
'2006/5/8 09:00:00' and '2006/5/10 21:30:00'|||Would it be an idea to have an overlay view where you make a
combination of both the date and the time field into a real datetime
format?|||Would it be an idea to have an overlay view where you make a
combination of both the date and the time field into a real datetime
format?
Friday, March 9, 2012
query optimisation question
I have a quick question regarding a query I'm working on. I was wondering why the first one runs much quicker than the second as I can't understand it myself.
Query 1:
SELECT pi.jjobno,
pi.jpi06,
pi.jq01,
pi.jq02,
pi.jq03,
pi.jq04,
pi.jq05,
pi.jq06,
pi.jq07,
pi.jq08
FROM jpost_insp pi
WHERE pi.jinspected_date IS NOT NULL
AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
FROM jpost_insp
GROUP BY jjobno)
AND pi.jjobno = :p_job_no
AND ROWNUM = 1
Query 2:
SELECT pi.jjobno,
pi.jpi06,
pi.jq01,
pi.jq02,
pi.jq03,
pi.jq04,
pi.jq05,
pi.jq06,
pi.jq07,
pi.jq08
FROM jpost_insp pi
WHERE pi.jinspected_date IS NOT NULL
AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
FROM jpost_insp
WHERE pi.jjobno = :p_job_no
GROUP BY jjobno)
AND pi.jjobno = :p_job_no
AND ROWNUM = 1
The only difference is that in query 2 I have included a where clause in the subquery. The field jjobno is an indexed field so surely that by specifying an exact jjobno in an index field this would be quicker than specifying nothing? Could someone explain this to me? I'm using oracle version 7.3. Thanks in advance.You have (accidentally I presume) correlated the subquery to the main query in the second version by referring to "pi.jjobno". Alias "pi" is defined in the main query. Perhaps you mean to do this:
SELECT pi.jjobno,
pi.jpi06,
pi.jq01,
pi.jq02,
pi.jq03,
pi.jq04,
pi.jq05,
pi.jq06,
pi.jq07,
pi.jq08
FROM jpost_insp pi
WHERE pi.jinspected_date IS NOT NULL
AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
FROM jpost_insp pi2
WHERE pi2.jjobno = :p_job_no
GROUP BY jjobno)
AND pi.jjobno = :p_job_no
AND ROWNUM = 1|||Of course, the hint needs changing now!|||Good spot I missed that completely. Why would I have to change the hint?
thanks,|||Well, either change the hint to use alias pi2, or change the table alias from pi2 to jpost_insp. They have to be the same in both places!
Query 1:
SELECT pi.jjobno,
pi.jpi06,
pi.jq01,
pi.jq02,
pi.jq03,
pi.jq04,
pi.jq05,
pi.jq06,
pi.jq07,
pi.jq08
FROM jpost_insp pi
WHERE pi.jinspected_date IS NOT NULL
AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
FROM jpost_insp
GROUP BY jjobno)
AND pi.jjobno = :p_job_no
AND ROWNUM = 1
Query 2:
SELECT pi.jjobno,
pi.jpi06,
pi.jq01,
pi.jq02,
pi.jq03,
pi.jq04,
pi.jq05,
pi.jq06,
pi.jq07,
pi.jq08
FROM jpost_insp pi
WHERE pi.jinspected_date IS NOT NULL
AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
FROM jpost_insp
WHERE pi.jjobno = :p_job_no
GROUP BY jjobno)
AND pi.jjobno = :p_job_no
AND ROWNUM = 1
The only difference is that in query 2 I have included a where clause in the subquery. The field jjobno is an indexed field so surely that by specifying an exact jjobno in an index field this would be quicker than specifying nothing? Could someone explain this to me? I'm using oracle version 7.3. Thanks in advance.You have (accidentally I presume) correlated the subquery to the main query in the second version by referring to "pi.jjobno". Alias "pi" is defined in the main query. Perhaps you mean to do this:
SELECT pi.jjobno,
pi.jpi06,
pi.jq01,
pi.jq02,
pi.jq03,
pi.jq04,
pi.jq05,
pi.jq06,
pi.jq07,
pi.jq08
FROM jpost_insp pi
WHERE pi.jinspected_date IS NOT NULL
AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
FROM jpost_insp pi2
WHERE pi2.jjobno = :p_job_no
GROUP BY jjobno)
AND pi.jjobno = :p_job_no
AND ROWNUM = 1|||Of course, the hint needs changing now!|||Good spot I missed that completely. Why would I have to change the hint?
thanks,|||Well, either change the hint to use alias pi2, or change the table alias from pi2 to jpost_insp. They have to be the same in both places!
Subscribe to:
Posts (Atom)