Query help

I'm trying to build a somewhat complicated query, well for me at least, and
I just can't figure it out.
I have a table called Inventory and a table called Auctions. The Auctions
table has a foreign key called InventoryID that links back to the Inventory
table. What I want to be able to do is to retrieve everything from the
Inventory table, and the most recent associated record in the Auctions
table. To complicate things, some Inventory items won't have any auctions
records so the columns that are returned from the auctions table need to be
null.
Here's the keys for the two tables:
Inventory Auctions
--
InventoryID AuctionID
InventoryID
An inventory item can have 0 or more auction records. The AuctionID field is
an identity field so the most recent auction record for an inventory item
can be found by: SELECT TOP 1 FROM Auctions WHERE InventoryID=XXX ORDER BY
AuctionID DESC
The result of the query would give me a single row that would contain all
the information from the inventory table and all of the information from the
most recent auction record. If no auction record was found, then these
fields would be null.
The fact that an inventory item might not have any auction records means
that I can't do a INNER JOIN, because if I do, any inventory items that
don't have at least one auction record won't get returned.
Most of my time in SQL has been spent doing pretty basic queries so I'm not
even sure where to begin to look for the best way to pull this off. If
someone could just point me in the right direction, I'll do the legwork, I
just don't know what my options are.
I appreciate any input or advice!Rachel
> table. What I want to be able to do is to retrieve everything from the
> Inventory table, and the most recent associated record in the Auctions
Since you did not provide ddl and sample data I tested it on Northwind db .
SELECT O.OrderId, Quantity from Orders O JOIN
(
SELECT OrderId,MAX(Quantity) Quantity FROM
[Order Details] GROUP BY OrderId
) OD ON OD.OrderId=O.OrderId
"Rachel Devons" <nononon@.nononon.com> wrote in message
news:ufAKr$0BFHA.3504@.TK2MSFTNGP12.phx.gbl...
> I'm trying to build a somewhat complicated query, well for me at least,
and
> I just can't figure it out.
> I have a table called Inventory and a table called Auctions. The Auctions
> table has a foreign key called InventoryID that links back to the
Inventory
> table. What I want to be able to do is to retrieve everything from the
> Inventory table, and the most recent associated record in the Auctions
> table. To complicate things, some Inventory items won't have any auctions
> records so the columns that are returned from the auctions table need to
be
> null.
> Here's the keys for the two tables:
> Inventory Auctions
> --
> InventoryID AuctionID
> InventoryID
> An inventory item can have 0 or more auction records. The AuctionID field
is
> an identity field so the most recent auction record for an inventory item
> can be found by: SELECT TOP 1 FROM Auctions WHERE InventoryID=XXX ORDER BY
> AuctionID DESC
> The result of the query would give me a single row that would contain all
> the information from the inventory table and all of the information from
the
> most recent auction record. If no auction record was found, then these
> fields would be null.
> The fact that an inventory item might not have any auction records means
> that I can't do a INNER JOIN, because if I do, any inventory items that
> don't have at least one auction record won't get returned.
> Most of my time in SQL has been spent doing pretty basic queries so I'm
not
> even sure where to begin to look for the best way to pull this off. If
> someone could just point me in the right direction, I'll do the legwork, I
> just don't know what my options are.
> I appreciate any input or advice!
>
>|||Rachel,
It should look something like this:
select
I.InventoryID, <other inventory columns>,
A.AuctionID, <other auction columns>
from Inventory as I left outer join Auctions as A
on A.InventoryID = I.InventoryID
and A.AuctionID = (
select max(AuctionID) from Auctions as Acopy
where Acopy.InventoryID = A.InventoryID
)
The left outer join will make sure each inventory item
shows up in the result (since the join has no WHERE
clause filtering out any inventory items).
The AuctionID = (select max...) will make sure that
whenever there are multiple AuctionID values for one
inventory item, you will only see the latest one.
A clustered index on Auctions(InventoryID, AuctionID)
should help if you don't already have one.
If this doesn't work out, post back and explain what
isn't working, preferably including CREATE TABLE statements
for your tables (simplified if necessary) and a few rows of
made-up by representative sample data as INSERT .. VALUES
statements to help explain what you need.
Steve Kass
Drew University
Rachel Devons wrote:

>I'm trying to build a somewhat complicated query, well for me at least, and
>I just can't figure it out.
>I have a table called Inventory and a table called Auctions. The Auctions
>table has a foreign key called InventoryID that links back to the Inventory
>table. What I want to be able to do is to retrieve everything from the
>Inventory table, and the most recent associated record in the Auctions
>table. To complicate things, some Inventory items won't have any auctions
>records so the columns that are returned from the auctions table need to be
>null.
>Here's the keys for the two tables:
>Inventory Auctions
>--
>InventoryID AuctionID
> InventoryID
>An inventory item can have 0 or more auction records. The AuctionID field i
s
>an identity field so the most recent auction record for an inventory item
>can be found by: SELECT TOP 1 FROM Auctions WHERE InventoryID=XXX ORDER BY
>AuctionID DESC
>The result of the query would give me a single row that would contain all
>the information from the inventory table and all of the information from th
e
>most recent auction record. If no auction record was found, then these
>fields would be null.
>The fact that an inventory item might not have any auction records means
>that I can't do a INNER JOIN, because if I do, any inventory items that
>don't have at least one auction record won't get returned.
>Most of my time in SQL has been spent doing pretty basic queries so I'm not
>even sure where to begin to look for the best way to pull this off. If
>someone could just point me in the right direction, I'll do the legwork, I
>just don't know what my options are.
>I appreciate any input or advice!
>
>
>|||Steve,
Thank you very much! That was much simpler than I had thought!
"Steve Kass" <skass@.drew.edu> wrote in message
news:u4%23xfZ1BFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Rachel,
> It should look something like this:
> select
> I.InventoryID, <other inventory columns>,
> A.AuctionID, <other auction columns>
> from Inventory as I left outer join Auctions as A
> on A.InventoryID = I.InventoryID
> and A.AuctionID = (
> select max(AuctionID) from Auctions as Acopy
> where Acopy.InventoryID = A.InventoryID
> )
> The left outer join will make sure each inventory item
> shows up in the result (since the join has no WHERE
> clause filtering out any inventory items).
> The AuctionID = (select max...) will make sure that
> whenever there are multiple AuctionID values for one
> inventory item, you will only see the latest one.
> A clustered index on Auctions(InventoryID, AuctionID)
> should help if you don't already have one.
> If this doesn't work out, post back and explain what
> isn't working, preferably including CREATE TABLE statements
> for your tables (simplified if necessary) and a few rows of
> made-up by representative sample data as INSERT .. VALUES
> statements to help explain what you need.
> Steve Kass
> Drew University
> Rachel Devons wrote:
>
and
Inventory
be
is
BY
the
not
I|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Could I make a sugesstion, based on guessing at your narrative and lack
of DDL?
CREATE TABLE Inventory
(item_id INTEGER NOT NULL,
.=2E.);
CREATE TABLE InventoryHistory
(item_id INTEGER NOT NULL
REFERENCES IInventory(item_id)
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME, -- null means current
item_status CHAR(3) NOT NULL
CHECK (item_status IN ('rec', 'bid', 'sld', ...)),
.=2E.
PRIMARY KEY (item_id, start_date));
This will let you build a status code system and track it over time.
An item has to be received, cataloged, bid on, sold, damaged, unsold,
etc. This lets you track the history and validate the transitions --
an item cannot be sold before it is cataloged, etc.
contain all the information from the inventory table and all of the
info=ADrmation from the
most recent auction record [sic]. If no auction record [sic] was found,
=ADthen these fields [sic] would be null. <<
Row are not records and columns are not fields. If you don't start
thinking in relational terms, you will imitate paper forms and
sequential file systems in SQL.
See what I mean about mimicking a sequential file or paper list? By
definition, an IDENTITY is a non-relational, unverifible physical
locator (like a record number on a magnetic tape) and cannot ever be a
relational key. You have a natural key with (item_id, start_date) to
track its status changes. Your query is now simple -- look for
"end_date IS NULL" and return that row.
Most of the time, a compicated query for a simple basic fact comes from
a bad data model.

query group by

have a table with records ranging from 0 to 100. some are repeated multiple
times, such as:
0
0
1
1
1
1
2
2
trying to figure out how to get a result that list the numbers and how many
times they each occur. such as.
num times
0 2
1 4
2 2
Any help would be appreciated. thanks,.
SELECT num, COUNT(*)
FROM table
GROUP BY num
ORDER BY num
http://www.aspfaq.com/
(Reverse address to reply.)
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:04CD49DB-34C4-4248-A874-5AFE88F4994C@.microsoft.com...
> have a table with records ranging from 0 to 100. some are repeated
multiple
> times, such as:
> 0
> 0
> 1
> 1
> 1
> 1
> 2
> 2
> trying to figure out how to get a result that list the numbers and how
many
> times they each occur. such as.
> num times
> 0 2
> 1 4
> 2 2
> Any help would be appreciated. thanks,.
|||I miswrote what I was looking for. Actually looking for the numbers of a
range. such as:
0 thru 9 22
10 thru 19 89
20 thru 29 34
or should this be done in a presentation layer.
thanks,
"Aaron [SQL Server MVP]" wrote:

> SELECT num, COUNT(*)
> FROM table
> GROUP BY num
> ORDER BY num
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Chad" <Chad@.discussions.microsoft.com> wrote in message
> news:04CD49DB-34C4-4248-A874-5AFE88F4994C@.microsoft.com...
> multiple
> many
>
>
|||If you have a finite set of numbers, you could write some really elaborate
CASE logic. But I think this kind of thing is better done at the
presentation layer.
http://www.aspfaq.com/
(Reverse address to reply.)
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:A7B99A2F-BF8A-46D0-B85F-78215711294A@.microsoft.com...
> I miswrote what I was looking for. Actually looking for the numbers of a
> range. such as:
> 0 thru 9 22
> 10 thru 19 89
> 20 thru 29 34
> or should this be done in a presentation layer.
|||That can easily be done by applying integer division. In the query
below, NumRange represents the lower bound of the range.
create table #t (num int,val int)
insert into #t values (0,1)
insert into #t values (1,1)
insert into #t values (9,2)
insert into #t values (10,3)
insert into #t values (15,8)
SELECT (num/10)*10 NumRange,SUM(val)
FROM #t
GROUP BY (num/10)*10
ORDER BY NumRange
drop table #t
Gert-Jan
Chad wrote:[vbcol=seagreen]
> I miswrote what I was looking for. Actually looking for the numbers of a
> range. such as:
> 0 thru 9 22
> 10 thru 19 89
> 20 thru 29 34
> or should this be done in a presentation layer.
> thanks,
> "Aaron [SQL Server MVP]" wrote:

query group by

have a table with records ranging from 0 to 100. some are repeated multiple
times, such as:
0
0
1
1
1
1
2
2
trying to figure out how to get a result that list the numbers and how many
times they each occur. such as.
num times
0 2
1 4
2 2
Any help would be appreciated. thanks,.SELECT num, COUNT(*)
FROM table
GROUP BY num
ORDER BY num
http://www.aspfaq.com/
(Reverse address to reply.)
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:04CD49DB-34C4-4248-A874-5AFE88F4994C@.microsoft.com...
> have a table with records ranging from 0 to 100. some are repeated
multiple
> times, such as:
> 0
> 0
> 1
> 1
> 1
> 1
> 2
> 2
> trying to figure out how to get a result that list the numbers and how
many
> times they each occur. such as.
> num times
> 0 2
> 1 4
> 2 2
> Any help would be appreciated. thanks,.|||I miswrote what I was looking for. Actually looking for the numbers of a
range. such as:
0 thru 9 22
10 thru 19 89
20 thru 29 34
or should this be done in a presentation layer.
thanks,
"Aaron [SQL Server MVP]" wrote:

> SELECT num, COUNT(*)
> FROM table
> GROUP BY num
> ORDER BY num
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Chad" <Chad@.discussions.microsoft.com> wrote in message
> news:04CD49DB-34C4-4248-A874-5AFE88F4994C@.microsoft.com...
> multiple
> many
>
>|||If you have a finite set of numbers, you could write some really elaborate
CASE logic. But I think this kind of thing is better done at the
presentation layer.
http://www.aspfaq.com/
(Reverse address to reply.)
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:A7B99A2F-BF8A-46D0-B85F-78215711294A@.microsoft.com...
> I miswrote what I was looking for. Actually looking for the numbers of a
> range. such as:
> 0 thru 9 22
> 10 thru 19 89
> 20 thru 29 34
> or should this be done in a presentation layer.|||That can easily be done by applying integer division. In the query
below, NumRange represents the lower bound of the range.
create table #t (num int,val int)
insert into #t values (0,1)
insert into #t values (1,1)
insert into #t values (9,2)
insert into #t values (10,3)
insert into #t values (15,8)
SELECT (num/10)*10 NumRange,SUM(val)
FROM #t
GROUP BY (num/10)*10
ORDER BY NumRange
drop table #t
Gert-Jan
Chad wrote:[vbcol=seagreen]
> I miswrote what I was looking for. Actually looking for the numbers of a
> range. such as:
> 0 thru 9 22
> 10 thru 19 89
> 20 thru 29 34
> or should this be done in a presentation layer.
> thanks,
> "Aaron [SQL Server MVP]" wrote:
>sql

query group by

have a table with records ranging from 0 to 100. some are repeated multiple
times, such as:
0
0
1
1
1
1
2
2
trying to figure out how to get a result that list the numbers and how many
times they each occur. such as.
num times
0 2
1 4
2 2
Any help would be appreciated. thanks,.SELECT num, COUNT(*)
FROM table
GROUP BY num
ORDER BY num
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:04CD49DB-34C4-4248-A874-5AFE88F4994C@.microsoft.com...
> have a table with records ranging from 0 to 100. some are repeated
multiple
> times, such as:
> 0
> 0
> 1
> 1
> 1
> 1
> 2
> 2
> trying to figure out how to get a result that list the numbers and how
many
> times they each occur. such as.
> num times
> 0 2
> 1 4
> 2 2
> Any help would be appreciated. thanks,.|||I miswrote what I was looking for. Actually looking for the numbers of a
range. such as:
0 thru 9 22
10 thru 19 89
20 thru 29 34
or should this be done in a presentation layer.
thanks,
"Aaron [SQL Server MVP]" wrote:
> SELECT num, COUNT(*)
> FROM table
> GROUP BY num
> ORDER BY num
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Chad" <Chad@.discussions.microsoft.com> wrote in message
> news:04CD49DB-34C4-4248-A874-5AFE88F4994C@.microsoft.com...
> > have a table with records ranging from 0 to 100. some are repeated
> multiple
> > times, such as:
> > 0
> > 0
> > 1
> > 1
> > 1
> > 1
> > 2
> > 2
> > trying to figure out how to get a result that list the numbers and how
> many
> > times they each occur. such as.
> > num times
> > 0 2
> > 1 4
> > 2 2
> >
> > Any help would be appreciated. thanks,.
>
>|||If you have a finite set of numbers, you could write some really elaborate
CASE logic. But I think this kind of thing is better done at the
presentation layer.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:A7B99A2F-BF8A-46D0-B85F-78215711294A@.microsoft.com...
> I miswrote what I was looking for. Actually looking for the numbers of a
> range. such as:
> 0 thru 9 22
> 10 thru 19 89
> 20 thru 29 34
> or should this be done in a presentation layer.|||That can easily be done by applying integer division. In the query
below, NumRange represents the lower bound of the range.
create table #t (num int,val int)
insert into #t values (0,1)
insert into #t values (1,1)
insert into #t values (9,2)
insert into #t values (10,3)
insert into #t values (15,8)
SELECT (num/10)*10 NumRange,SUM(val)
FROM #t
GROUP BY (num/10)*10
ORDER BY NumRange
drop table #t
Gert-Jan
Chad wrote:
> I miswrote what I was looking for. Actually looking for the numbers of a
> range. such as:
> 0 thru 9 22
> 10 thru 19 89
> 20 thru 29 34
> or should this be done in a presentation layer.
> thanks,
> "Aaron [SQL Server MVP]" wrote:
> > SELECT num, COUNT(*)
> > FROM table
> > GROUP BY num
> > ORDER BY num
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Chad" <Chad@.discussions.microsoft.com> wrote in message
> > news:04CD49DB-34C4-4248-A874-5AFE88F4994C@.microsoft.com...
> > > have a table with records ranging from 0 to 100. some are repeated
> > multiple
> > > times, such as:
> > > 0
> > > 0
> > > 1
> > > 1
> > > 1
> > > 1
> > > 2
> > > 2
> > > trying to figure out how to get a result that list the numbers and how
> > many
> > > times they each occur. such as.
> > > num times
> > > 0 2
> > > 1 4
> > > 2 2
> > >
> > > Any help would be appreciated. thanks,.
> >
> >
> >

Query for percentage of a SUM

I am trying to figure out the syntax for a query that will essentially
give me the Percentage each of my areas contributes to the Whole. I
know this can be achieved by multiple queries but I would like to keep
it intact as one single query if possible.

For Example I have the following data set--

AREA MOU
NE 1234
SO 4312
WE 12312
MW 97123
NE 1123
SO 31
WE 312
MW 971

The results I would like to see would look like

AREA MOU PERCENT
MW 98094 .83536
NE 2357 .02007
WE 12624 .10751
SO 4352 .03706

The query I came up with is--

SELECT DISTINCT Area, SUM(MOU) AS AREA_TOTAL, sum(MOU) /
(SELECT SUM(MOU) AS TOTAL_MOU
FROM [2004_NOVEMBER_COST])as
[PERCENT]
FROM [2004_NOVEMBER_COST]
GROUP BY Area

All seems to calculate with the exception of the Percent where all the
percentages are 0's.

I think I need to take the first line AREA_TOTAL and now divide by the
SUM(MOU) like this--
SELECT DISTINCT Area, SUM(MOU) AS AREA_TOTAL, AREA_TOTAL /
(SELECT SUM(MOU) AS TOTAL_MOU

but I get Invalid Column.

I essence I think it is a simple query but I am hitting a wall. Any
advice would help.

Thanks,
BenPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

You might also stop putting numbers at the front of names and using
proprietary square brackets. It looks like you are using one table per
month per year!! That would certainly not be the case in a properly
designed database; that would be serious attribute splitting.

Try qualifying the names better, something like this. Without DDL,
this is a wild guess:

SELECT A1.area, SUM(A1.mou) AS area_total,
(SUM (A1.mou) / (SELECT SUM (A2.mou)
FROM AreaCosts AS A2
WHERE A2.report_month = 11 ) ) AS
percentage
FROM AreaCosts AS A1
WHERE A1.report_month = 11
GROUP BY A1.area;|||On 13 Dec 2004 17:16:39 -0800, Ben wrote:

>All seems to calculate with the exception of the Percent where all the
>percentages are 0's.

Hi Ben,

I didn't study your query in detail, but I guess that it's correct (though
you don't need the DISTINCT if you already do GROUP BY!).

The reason the percentages are 0 is because SQL Server will use integer
division (both operands of the / operator are integer), discarding the
fractional part. Try running these:
SELECT 7 / 8
SELECT 7 / 8.0
SELECT 7.0 / 8

As you see, making sure that at least one of the operands is not integer
is enough to get an exact result. To get the same effect in your query,
just change "sum(MOU) / ..." to "CAST(sum(MOU) AS numeric) / ..."

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||[posted and mailed, please reply in news]

Ben (wiredog@.comcast.net) writes:
> All seems to calculate with the exception of the Percent where all the
> percentages are 0's.
> I think I need to take the first line AREA_TOTAL and now divide by the
> SUM(MOU) like this--
> SELECT DISTINCT Area, SUM(MOU) AS AREA_TOTAL, AREA_TOTAL /
> (SELECT SUM(MOU) AS TOTAL_MOU
> but I get Invalid Column.

You cannot use a column alias later in the query. Just imagine the
table you are querying actually have an AREA_TOTAL column. What would
happen then?

This may be the best way to write the query.

SELECT Area, SUM(MOU) AS Area_total, 1.0 * SUM(MOU) / x.grand_total
FROM tbl
CROSS JOIN (SELECT grand_total = SUM(MOU) FROM tbl) AS x
GROUP BY Area

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Query Cost - how is it calculated?

Hi,
I'm trying to figure out where the value of "Cost" comes from in the
Query Analyzer.
For example, when running this on Northwind:
select * from dbo.[Alphabetical list of products]
You get this 3 parts for the Query Plan. The part of
"Categories.PK_Categories" has a cost of 24% of the
total and a value of 0.0119... Where does this value
come from?
All I get is "cost for CPU" and "cost for I/O" - but adding
these together does not equal that value.
Anyone have a clue?
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
I doubt that anyone outside of Microsft, and maybe Kalen, could really
answer that question...
Not only does the optimizer estimate IO and CPU, it does some estimation
about how much physical IO is required based on some expectation of how much
of the data might be in memory... It also looks at the recent use of tables
to determine the level of locking for certain operations as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm trying to figure out where the value of "Cost" comes from in the
> Query Analyzer.
> For example, when running this on Northwind:
> select * from dbo.[Alphabetical list of products]
> You get this 3 parts for the Query Plan. The part of
> "Categories.PK_Categories" has a cost of 24% of the
> total and a value of 0.0119... Where does this value
> come from?
> All I get is "cost for CPU" and "cost for I/O" - but adding
> these together does not equal that value.
>
> Anyone have a clue?
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
>
|||Hello Wayne,

> I doubt that anyone outside of Microsft, and maybe Kalen, could really
> answer that question...
> Not only does the optimizer estimate IO and CPU, it does some estimation
> about how much physical IO is required based on some expectation of how
much
> of the data might be in memory... It also looks at the recent use of
tables
> to determine the level of locking for certain operations as well...
Hmm - when extracting a plan via SHOWPLAN_ALL, I do get the estimates
of some of the info (IO/CPU), but I have no idea where the actual "cost"
comes
from.
I've been fiddling around with multiplying IO * "nr of executes" and CPU *
nr of
executes, but it doesn't really add up properly... :-/
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
[vbcol=seagreen]
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
> news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
SQL
>

Query Cost - how is it calculated?

Hi,
I'm trying to figure out where the value of "Cost" comes from in the
Query Analyzer.
For example, when running this on Northwind:
select * from dbo.[Alphabetical list of products]
You get this 3 parts for the Query Plan. The part of
"Categories.PK_Categories" has a cost of 24% of the
total and a value of 0.0119... Where does this value
come from?
All I get is "cost for CPU" and "cost for I/O" - but adding
these together does not equal that value.
Anyone have a clue?
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.comI doubt that anyone outside of Microsft, and maybe Kalen, could really
answer that question...
Not only does the optimizer estimate IO and CPU, it does some estimation
about how much physical IO is required based on some expectation of how much
of the data might be in memory... It also looks at the recent use of tables
to determine the level of locking for certain operations as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm trying to figure out where the value of "Cost" comes from in the
> Query Analyzer.
> For example, when running this on Northwind:
> select * from dbo.[Alphabetical list of products]
> You get this 3 parts for the Query Plan. The part of
> "Categories.PK_Categories" has a cost of 24% of the
> total and a value of 0.0119... Where does this value
> come from?
> All I get is "cost for CPU" and "cost for I/O" - but adding
> these together does not equal that value.
>
> Anyone have a clue?
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
>|||Hello Wayne,
> I doubt that anyone outside of Microsft, and maybe Kalen, could really
> answer that question...
> Not only does the optimizer estimate IO and CPU, it does some estimation
> about how much physical IO is required based on some expectation of how
much
> of the data might be in memory... It also looks at the recent use of
tables
> to determine the level of locking for certain operations as well...
Hmm - when extracting a plan via SHOWPLAN_ALL, I do get the estimates
of some of the info (IO/CPU), but I have no idea where the actual "cost"
comes
from.
I've been fiddling around with multiplying IO * "nr of executes" and CPU *
nr of
executes, but it doesn't really add up properly... :-/
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
> news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I'm trying to figure out where the value of "Cost" comes from in the
> > Query Analyzer.
> >
> > For example, when running this on Northwind:
> >
> > select * from dbo.[Alphabetical list of products]
> >
> > You get this 3 parts for the Query Plan. The part of
> > "Categories.PK_Categories" has a cost of 24% of the
> > total and a value of 0.0119... Where does this value
> > come from?
> >
> > All I get is "cost for CPU" and "cost for I/O" - but adding
> > these together does not equal that value.
> >
> >
> > Anyone have a clue?
> >
> > --
> > With regards,
> >
> > Martijn Tonies
> > Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS
SQL
> > Server
> > Upscene Productions
> > http://www.upscene.com
> >
> >
> >
>

Query Cost - how is it calculated?

Hi,
I'm trying to figure out where the value of "Cost" comes from in the
Query Analyzer.
For example, when running this on Northwind:
select * from dbo.[Alphabetical list of products]
You get this 3 parts for the Query Plan. The part of
"Categories.PK_Categories" has a cost of 24% of the
total and a value of 0.0119... Where does this value
come from?
All I get is "cost for CPU" and "cost for I/O" - but adding
these together does not equal that value.
Anyone have a clue?
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.comI doubt that anyone outside of Microsft, and maybe Kalen, could really
answer that question...
Not only does the optimizer estimate IO and CPU, it does some estimation
about how much physical IO is required based on some expectation of how much
of the data might be in memory... It also looks at the recent use of tables
to determine the level of locking for certain operations as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm trying to figure out where the value of "Cost" comes from in the
> Query Analyzer.
> For example, when running this on Northwind:
> select * from dbo.[Alphabetical list of products]
> You get this 3 parts for the Query Plan. The part of
> "Categories.PK_Categories" has a cost of 24% of the
> total and a value of 0.0119... Where does this value
> come from?
> All I get is "cost for CPU" and "cost for I/O" - but adding
> these together does not equal that value.
>
> Anyone have a clue?
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
>|||Hello Wayne,

> I doubt that anyone outside of Microsft, and maybe Kalen, could really
> answer that question...
> Not only does the optimizer estimate IO and CPU, it does some estimation
> about how much physical IO is required based on some expectation of how
much
> of the data might be in memory... It also looks at the recent use of
tables
> to determine the level of locking for certain operations as well...
Hmm - when extracting a plan via SHOWPLAN_ALL, I do get the estimates
of some of the info (IO/CPU), but I have no idea where the actual "cost"
comes
from.
I've been fiddling around with multiplying IO * "nr of executes" and CPU *
nr of
executes, but it doesn't really add up properly... :-/
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com

>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
> news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
>

Query Confusion

Hello,
I have a large database and am trying to figure out why the following
queries differ so much in speed. I am selecting from two tables related by a
unique ID field. There are approximately 26mil rows of data in each table.
select v1.zip,v2.f1 from v1,v2 where (v1.zip=44122 OR v1.zip=56879 OR
v1.zip=90210) AND v1.uid=v2.f1;
- this query completes in about 7 seconds
select v1.zip,v2.f1,v2.f63 from v1,v2 where (v1.zip=44122 OR v1.zip=56879 OR
v1.zip=90210) AND v1.uid=v2.f1;
- this query completes in
My only guess is this. The only indexed fields in the query are v1.zip,
v1.uid and v2.f1. Due to the fact that the table v2 is quite large, it's
simply a matter of the time it's taking MSSQL to fine the appropriate values
in the database file.
Can I split up my database into many filegroups and actually assign a
filegroup to specific tables?
Thanks.
and v2.f63=1;
stooky
Have you seen that query optimizer is available to use the index?
"stooky" <stooky@.discussions.microsoft.com> wrote in message
news:0A0C35BA-8FF7-4FE6-B08A-BFE78B017DA5@.microsoft.com...
> Hello,
> I have a large database and am trying to figure out why the following
> queries differ so much in speed. I am selecting from two tables related by
a
> unique ID field. There are approximately 26mil rows of data in each table.
> select v1.zip,v2.f1 from v1,v2 where (v1.zip=44122 OR v1.zip=56879 OR
> v1.zip=90210) AND v1.uid=v2.f1;
> - this query completes in about 7 seconds
> select v1.zip,v2.f1,v2.f63 from v1,v2 where (v1.zip=44122 OR v1.zip=56879
OR
> v1.zip=90210) AND v1.uid=v2.f1;
> - this query completes in
> My only guess is this. The only indexed fields in the query are v1.zip,
> v1.uid and v2.f1. Due to the fact that the table v2 is quite large, it's
> simply a matter of the time it's taking MSSQL to fine the appropriate
values
> in the database file.
> Can I split up my database into many filegroups and actually assign a
> filegroup to specific tables?
> Thanks.
> and v2.f63=1;
|||The answer to the first query can be obtained from looking at the index
ONLY, without going to the table ... The index covers that query... The
second query includes a non-indexed column in the column list, so the rows
must be fetched - which would be much slower...
Search for 'Covering index' on google or in books on line...
Hope this helps
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"stooky" <stooky@.discussions.microsoft.com> wrote in message
news:0A0C35BA-8FF7-4FE6-B08A-BFE78B017DA5@.microsoft.com...
> Hello,
> I have a large database and am trying to figure out why the following
> queries differ so much in speed. I am selecting from two tables related by
> a
> unique ID field. There are approximately 26mil rows of data in each table.
> select v1.zip,v2.f1 from v1,v2 where (v1.zip=44122 OR v1.zip=56879 OR
> v1.zip=90210) AND v1.uid=v2.f1;
> - this query completes in about 7 seconds
> select v1.zip,v2.f1,v2.f63 from v1,v2 where (v1.zip=44122 OR v1.zip=56879
> OR
> v1.zip=90210) AND v1.uid=v2.f1;
> - this query completes in
> My only guess is this. The only indexed fields in the query are v1.zip,
> v1.uid and v2.f1. Due to the fact that the table v2 is quite large, it's
> simply a matter of the time it's taking MSSQL to fine the appropriate
> values
> in the database file.
> Can I split up my database into many filegroups and actually assign a
> filegroup to specific tables?
> Thanks.
> and v2.f63=1;

Query Confusion

Hello,
I have a large database and am trying to figure out why the following
queries differ so much in speed. I am selecting from two tables related by a
unique ID field. There are approximately 26mil rows of data in each table.
select v1.zip,v2.f1 from v1,v2 where (v1.zip=44122 OR v1.zip=56879 OR
v1.zip=90210) AND v1.uid=v2.f1;
- this query completes in about 7 seconds
select v1.zip,v2.f1,v2.f63 from v1,v2 where (v1.zip=44122 OR v1.zip=56879 OR
v1.zip=90210) AND v1.uid=v2.f1;
- this query completes in
My only guess is this. The only indexed fields in the query are v1.zip,
v1.uid and v2.f1. Due to the fact that the table v2 is quite large, it's
simply a matter of the time it's taking MSSQL to fine the appropriate values
in the database file.
Can I split up my database into many filegroups and actually assign a
filegroup to specific tables?
Thanks.
and v2.f63=1;stooky
Have you seen that query optimizer is available to use the index?
"stooky" <stooky@.discussions.microsoft.com> wrote in message
news:0A0C35BA-8FF7-4FE6-B08A-BFE78B017DA5@.microsoft.com...
> Hello,
> I have a large database and am trying to figure out why the following
> queries differ so much in speed. I am selecting from two tables related by
a
> unique ID field. There are approximately 26mil rows of data in each table.
> select v1.zip,v2.f1 from v1,v2 where (v1.zip=44122 OR v1.zip=56879 OR
> v1.zip=90210) AND v1.uid=v2.f1;
> - this query completes in about 7 seconds
> select v1.zip,v2.f1,v2.f63 from v1,v2 where (v1.zip=44122 OR v1.zip=56879
OR
> v1.zip=90210) AND v1.uid=v2.f1;
> - this query completes in
> My only guess is this. The only indexed fields in the query are v1.zip,
> v1.uid and v2.f1. Due to the fact that the table v2 is quite large, it's
> simply a matter of the time it's taking MSSQL to fine the appropriate
values
> in the database file.
> Can I split up my database into many filegroups and actually assign a
> filegroup to specific tables?
> Thanks.
> and v2.f63=1;|||The answer to the first query can be obtained from looking at the index
ONLY, without going to the table ... The index covers that query... The
second query includes a non-indexed column in the column list, so the rows
must be fetched - which would be much slower...
Search for 'Covering index' on google or in books on line...
Hope this helps
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"stooky" <stooky@.discussions.microsoft.com> wrote in message
news:0A0C35BA-8FF7-4FE6-B08A-BFE78B017DA5@.microsoft.com...
> Hello,
> I have a large database and am trying to figure out why the following
> queries differ so much in speed. I am selecting from two tables related by
> a
> unique ID field. There are approximately 26mil rows of data in each table.
> select v1.zip,v2.f1 from v1,v2 where (v1.zip=44122 OR v1.zip=56879 OR
> v1.zip=90210) AND v1.uid=v2.f1;
> - this query completes in about 7 seconds
> select v1.zip,v2.f1,v2.f63 from v1,v2 where (v1.zip=44122 OR v1.zip=56879
> OR
> v1.zip=90210) AND v1.uid=v2.f1;
> - this query completes in
> My only guess is this. The only indexed fields in the query are v1.zip,
> v1.uid and v2.f1. Due to the fact that the table v2 is quite large, it's
> simply a matter of the time it's taking MSSQL to fine the appropriate
> values
> in the database file.
> Can I split up my database into many filegroups and actually assign a
> filegroup to specific tables?
> Thanks.
> and v2.f63=1;

Query Confusion

Hello,
I have a large database and am trying to figure out why the following
queries differ so much in speed. I am selecting from two tables related by a
unique ID field. There are approximately 26mil rows of data in each table.
select v1.zip,v2.f1 from v1,v2 where (v1.zip=44122 OR v1.zip=56879 OR
v1.zip=90210) AND v1.uid=v2.f1;
- this query completes in about 7 seconds
select v1.zip,v2.f1,v2.f63 from v1,v2 where (v1.zip=44122 OR v1.zip=56879 OR
v1.zip=90210) AND v1.uid=v2.f1;
- this query completes in
My only guess is this. The only indexed fields in the query are v1.zip,
v1.uid and v2.f1. Due to the fact that the table v2 is quite large, it's
simply a matter of the time it's taking MSSQL to fine the appropriate values
in the database file.
Can I split up my database into many filegroups and actually assign a
filegroup to specific tables?
Thanks.
and v2.f63=1;stooky
Have you seen that query optimizer is available to use the index?
"stooky" <stooky@.discussions.microsoft.com> wrote in message
news:0A0C35BA-8FF7-4FE6-B08A-BFE78B017DA5@.microsoft.com...
> Hello,
> I have a large database and am trying to figure out why the following
> queries differ so much in speed. I am selecting from two tables related by
a
> unique ID field. There are approximately 26mil rows of data in each table.
> select v1.zip,v2.f1 from v1,v2 where (v1.zip=44122 OR v1.zip=56879 OR
> v1.zip=90210) AND v1.uid=v2.f1;
> - this query completes in about 7 seconds
> select v1.zip,v2.f1,v2.f63 from v1,v2 where (v1.zip=44122 OR v1.zip=56879
OR
> v1.zip=90210) AND v1.uid=v2.f1;
> - this query completes in
> My only guess is this. The only indexed fields in the query are v1.zip,
> v1.uid and v2.f1. Due to the fact that the table v2 is quite large, it's
> simply a matter of the time it's taking MSSQL to fine the appropriate
values
> in the database file.
> Can I split up my database into many filegroups and actually assign a
> filegroup to specific tables?
> Thanks.
> and v2.f63=1;|||The answer to the first query can be obtained from looking at the index
ONLY, without going to the table ... The index covers that query... The
second query includes a non-indexed column in the column list, so the rows
must be fetched - which would be much slower...
Search for 'Covering index' on google or in books on line...
Hope this helps
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"stooky" <stooky@.discussions.microsoft.com> wrote in message
news:0A0C35BA-8FF7-4FE6-B08A-BFE78B017DA5@.microsoft.com...
> Hello,
> I have a large database and am trying to figure out why the following
> queries differ so much in speed. I am selecting from two tables related by
> a
> unique ID field. There are approximately 26mil rows of data in each table.
> select v1.zip,v2.f1 from v1,v2 where (v1.zip=44122 OR v1.zip=56879 OR
> v1.zip=90210) AND v1.uid=v2.f1;
> - this query completes in about 7 seconds
> select v1.zip,v2.f1,v2.f63 from v1,v2 where (v1.zip=44122 OR v1.zip=56879
> OR
> v1.zip=90210) AND v1.uid=v2.f1;
> - this query completes in
> My only guess is this. The only indexed fields in the query are v1.zip,
> v1.uid and v2.f1. Due to the fact that the table v2 is quite large, it's
> simply a matter of the time it's taking MSSQL to fine the appropriate
> values
> in the database file.
> Can I split up my database into many filegroups and actually assign a
> filegroup to specific tables?
> Thanks.
> and v2.f63=1;