Hi,
I'm new to queries and I'm stuck with joins...
I have the following tables, and would like to get the total of the
products, given a BranchId:
table Product
ProductId: PK
ProductName
table Branch
BranchId
table Inventory
InventoryId: PK
BranchId: FK to Branch.BranchId
table InventoryDetail
InventoryDetailId: PK
InventoryId: FK to Inventory.InventoryId
ProductId: FK to Product.ProductId
Quantity
Here is what I started:
CREATE PROCEDURE SelectProductByName(@.BranchId INT, @.ProductName
NVARCHAR(100))
AS
SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
FROM Product AS P
LEFT JOIN InventoryDetail AS ID ON ID.ProductId = P.ProductId
/*And waht now with*/
RETURNHello, Michael
I guess you want something like this:
SELECT P.ProductId, P.ProductName, X.Qty
FROM Product AS P LEFT JOIN (
SELECT ID.ProductId, Sum(ID.Quantity) AS Qty
FROM InventoryDetail ID
INNER JOIN Inventory I ON ID.InventoryId = I.InventoryId
WHERE I.BranchId = @.BranchId
GROUP BY ID.ProductId
) X ON P.Product = X.ProductId
WHERE P.ProductName = @.ProductName
This query may also work, but the first query is better:
SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
FROM Product AS P
LEFT JOIN InventoryDetail ID ON P.ProductId = ID.ProductId
LEFT JOIN Inventory I ON ID.InventoryId = I.InventoryId
AND I.BranchId = @.BranchId
WHERE P.ProductName = @.ProductName
GROUP BY P.ProductId, P.ProductName
Razvan|||Hi Razvan,
Thank you very much for the help.
Why is the first first query better than the second one?
Also, I've come up with the following query. Is it correct?
SELECT P.ProductId, P.ProductName,
(SELECT SUM(ID.Quantity) FROM InventoryDetail AS ID
JOIN Inventory AS I ON I.InventoryId = ID.InventoryId
WHERE (I.BranchId = @.BranchId) AND (ID.ProductId = P.ProductId))
AS Qty
FROM Product AS P
WHERE (P.ProductName = @.ProductName)
Regards
Razvan Socol wrote:
> Hello, Michael
> I guess you want something like this:
> SELECT P.ProductId, P.ProductName, X.Qty
> FROM Product AS P LEFT JOIN (
> SELECT ID.ProductId, Sum(ID.Quantity) AS Qty
> FROM InventoryDetail ID
> INNER JOIN Inventory I ON ID.InventoryId = I.InventoryId
> WHERE I.BranchId = @.BranchId
> GROUP BY ID.ProductId
> ) X ON P.Product = X.ProductId
> WHERE P.ProductName = @.ProductName
> This query may also work, but the first query is better:
> SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
> FROM Product AS P
> LEFT JOIN InventoryDetail ID ON P.ProductId = ID.ProductId
> LEFT JOIN Inventory I ON ID.InventoryId = I.InventoryId
> AND I.BranchId = @.BranchId
> WHERE P.ProductName = @.ProductName
> GROUP BY P.ProductId, P.ProductName
> Razvan
>|||Hello, Michael
> Why is the first first query better than the second one?
Before testing the queries (because I had no DDL and sample data, see:
http://www.aspfaq.com/etiquette.asp?id=5006), I wrote that the first
query is better, because the second query seemed unsafe to me. Now that
I've tested it (by translating it for one of my own schemas), I can say
that the second query is plain wrong: it doesn't return the expected
results. I intended to write the second query this way:
SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
FROM Product AS P
LEFT JOIN InventoryDetail ID
INNER JOIN Inventory I ON ID.InventoryId = I.InventoryId
ON P.ProductId = ID.ProductId AND I.BranchId = @.BranchId
WHERE P.ProductName = @.ProductName
GROUP BY P.ProductId, P.ProductName
Regarding the weird placement of the ON clause, see:
http://groups.google.com/group/micr...br />
2579509b
> Also, I've come up with the following query. Is it correct?
Yes, it is correct. Actually, I think it's identical (in terms of
performance) with my first query, but your query is better because is
more readable. The corrected second query (although it produces an
execution plan with the same cost as the other two), seems to me a bit
slower, because the "Stream Aggregate" step (the GROUP BY clause) is
done on a wider row. Also, it's a lot harder to understand, so that's
why I still think it's the worse among the three queries.
Razvan|||Hi Razvan,
Sorry, I didn't provide the DDL. Now that I know how to do it, I'll
provide it next time.
I'll go on with my query as I already understood it, keeping a copy of
yours if needed.
Thanks a lot
Razvan Socol wrote:
> Hello, Michael
>
>
> Before testing the queries (because I had no DDL and sample data, see:
> http://www.aspfaq.com/etiquette.asp?id=5006), I wrote that the first
> query is better, because the second query seemed unsafe to me. Now that
> I've tested it (by translating it for one of my own schemas), I can say
> that the second query is plain wrong: it doesn't return the expected
> results. I intended to write the second query this way:
> SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
> FROM Product AS P
> LEFT JOIN InventoryDetail ID
> INNER JOIN Inventory I ON ID.InventoryId = I.InventoryId
> ON P.ProductId = ID.ProductId AND I.BranchId = @.BranchId
> WHERE P.ProductName = @.ProductName
> GROUP BY P.ProductId, P.ProductName
> Regarding the weird placement of the ON clause, see:
> http://groups.google.com/group/micr... />
492579509b
>
>
> Yes, it is correct. Actually, I think it's identical (in terms of
> performance) with my first query, but your query is better because is
> more readable. The corrected second query (although it produces an
> execution plan with the same cost as the other two), seems to me a bit
> slower, because the "Stream Aggregate" step (the GROUP BY clause) is
> done on a wider row. Also, it's a lot harder to understand, so that's
> why I still think it's the worse among the three queries.
> Razvan
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Since you also did not give any specs, can I assume that inventory is
spread over branches? Can I assume the product is identified by some
industry standard code, such as a UPC? And why did you create that
useless inventory details table? An inventory is not like an invoice.
CREATE TABLE Products
(upc CHAR(13) NOT NULL PRIMARY KEY, --upc?
product_name CHAR(20) NOT NULL,
.);
CREATE TABLE Branches
(branch_id INTEGER NOT NULL PRIMARY KEY,
.);
CREATE TABLE Inventory
(branch_id INTEGER NOT NULL
REFERENCES Branches(branch_id),
upc CHAR(13) NOT NULL
REFERENCES Products(upc),
PRIMARY KEY (branch_id, upc),
qty_on_hand INTEGER DEFAULT 0 NOT NULL
CHECK (qty_on_hand >= 0)
The right design makes it easy:
SELECT P.upc, SUM(I.qty_on_hand)
FROM Products AS P
LEFT OUTER JOIN
Inventory AS I
ON P.upc= I.upc
GROUP BY P.upc;|||Hi Celko,
I already got an answer from Razvan.
I needed the inventory detail table to hold the different quantities
with their respective notes, such as bin location, qty of damaged items
included in the inventory,...
Now that I know how to post DDL, I'll do it next time. As for the sample
data, I don't have any yet. I'm starting to design the database from
scratch.
Thanks for the help
--CELKO-- wrote:
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Since you also did not give any specs, can I assume that inventory is
> spread over branches? Can I assume the product is identified by some
> industry standard code, such as a UPC? And why did you create that
> useless inventory details table? An inventory is not like an invoice.
> CREATE TABLE Products
> (upc CHAR(13) NOT NULL PRIMARY KEY, --upc?
> product_name CHAR(20) NOT NULL,
> ..);
> CREATE TABLE Branches
> (branch_id INTEGER NOT NULL PRIMARY KEY,
> ..);
> CREATE TABLE Inventory
> (branch_id INTEGER NOT NULL
> REFERENCES Branches(branch_id),
> upc CHAR(13) NOT NULL
> REFERENCES Products(upc),
> PRIMARY KEY (branch_id, upc),
> qty_on_hand INTEGER DEFAULT 0 NOT NULL
> CHECK (qty_on_hand >= 0)
>
>
> The right design makes it easy:
> SELECT P.upc, SUM(I.qty_on_hand)
> FROM Products AS P
> LEFT OUTER JOIN
> Inventory AS I
> ON P.upc= I.upc
> GROUP BY P.upc;
>|||>> I needed the inventory detail table to hold the different quantities with
their respective notes, such as bin location, qty of damaged items included
in the inventory,...<<
That is the usual inventory design; several different quantity
attributes (on-hand, on order, on hold, damaged, expired, etc. Since
these are all distinct attributes of an inventory held at a p[articular
branch, you simply extend the table. That data and the needed check
constraints were not shown in your specs (or lack of specs, really).
There is no need to create a separate inventory_id., which is a poor
attempt at a surrogate for a branch_id. You are splitting the actual
primary key (branch_id, upc) across tables. Ask yourself what meaning
an inventory has apart from the goods it holds. Look at your
pseudo-code; what maps yhour magical "InventoryDetailId" to one and
only one product, and assures that it is the RIGHT product? Nothing.
The split key invites disaster.
Please consider a correct design instead.|||Designing a database is not so simple...
I've already changed my design several times. At first because of a lack
of knowledge in database design (this is actually my first database with
SqlServer, before this was 2 simple databases with MS Access)
If you don't mind I'll really need a help. I'll try to make it as simple
as I can.
Our company has 2 branches (I'll generalise this to several branches).
Products can be stocked in either of these branches.
When counting a product, we need the date of the counting, and the
details (quantity + comments, such as damaged, carton no, bin
location,... which are "free-hand" text note). Note that branches count
their products independently of each other.
So, I came up with these tables:
CREATE TABLE Product (
ProductId BIGINT IDENTITY PRIMARY KEY,
ProductName NVARCHAR(50) NOT NULL,
..
)
CREATE TABLE Branch (
BranchId INT IDENTITY PRIMARY KEY,
BranchName NVARCHAR(50) NOT NULL,
..
)
CREATE TABLE Inventory (
InventoryId BIGINT IDENTITY PRIMARY KEY,
InventoryDate DATETIME NOT NULL,
BranchId INT NOT NULL REFERENCES Branch(BranchId),
..
)
CREATE TABLE InventoryDetail (
InventoryDetailId BIGINT IDENTITY PRIMARY KEY,
InventoryId BIG INT NOT NULL REFERENCES Inventory(InventoryId),
ProductId BIG INT NOT NULL REFERENCES Product(ProductId),
Quantity INT NOT NULL,
Comments NVARCHAR(100) NULL
)
As for sample data, I don't have any yet, but here is what we want:
Lady coat no 634:
Main branch: counted on 14/02/06
- 60 pcs, ctn 123
- 6 pcs, on the shelf (in the shop)
Second branch: counted on 16/02/06
- 3 pcs, on the shelf
Lady pant no 132:
Main branch: counted on 21/02/06
- 2 pcs, a customer has specially ordered these for himself
Lady shoes no 852:
Main branch: counted on 22/02/06
- 12 pcs, in stock shelf no 3
- 12 pcs, waiting to be sent to branch 2
- 2 pcs, in the shop
Second branch: counted on 20/02/06
- 0 pcs, placed the order from Main branch already
--CELKO-- wrote:
>
> That is the usual inventory design; several different quantity
> attributes (on-hand, on order, on hold, damaged, expired, etc. Since
> these are all distinct attributes of an inventory held at a p[articular
> branch, you simply extend the table. That data and the needed check
> constraints were not shown in your specs (or lack of specs, really).
> There is no need to create a separate inventory_id., which is a poor
> attempt at a surrogate for a branch_id. You are splitting the actual
> primary key (branch_id, upc) across tables. Ask yourself what meaning
> an inventory has apart from the goods it holds. Look at your
> pseudo-code; what maps yhour magical "InventoryDetailId" to one and
> only one product, and assures that it is the RIGHT product? Nothing.
> The split key invites disaster.
> Please consider a correct design instead.
>
Showing posts with label queries. Show all posts
Showing posts with label queries. Show all posts
Monday, March 26, 2012
Product Price Range Query
I've noticed that some search queries on e-commerce sites return "item
price range hyperlinks" as filters for matched items.
So you would have something like:
Under $50 $75-$100 $150-$250
in a row with only the applicable price ranges to the queried products
showing. So, with the above example, if there weren't any products
between $101 and $149, that price range wouldn't show.
I'm wondering about the structure of the SQL for this type of query.
On the surface, I could do multiple queries for each of the price
ranges I needed and union them together, but I'm wondering if there is
a more efficient way to do it in one pass.
Ian<dontspammenow@.yahoo.com> wrote in message
news:1132866827.910562.120930@.g43g2000cwa.googlegroups.com...
> I've noticed that some search queries on e-commerce sites return "item
> price range hyperlinks" as filters for matched items.
> So you would have something like:
> Under $50 $75-$100 $150-$250
> in a row with only the applicable price ranges to the queried products
> showing. So, with the above example, if there weren't any products
> between $101 and $149, that price range wouldn't show.
> I'm wondering about the structure of the SQL for this type of query.
> On the surface, I could do multiple queries for each of the price
> ranges I needed and union them together, but I'm wondering if there is
> a more efficient way to do it in one pass.
> Ian
>
Put your possible price ranges in a table:
CREATE TABLE price_ranges (low_price DECIMAL(10,2) NOT NULL, high_price
DECIMAL (10,2) NOT NULL, CHECK (low_price < high_price), PRIMARY KEY
(low_price, high_price));
INSERT INTO price_ranges (low_price, high_price)
SELECT 1, 50 UNION ALL
SELECT 50, 75 UNION ALL
SELECT 75, 100 UNION ALL
SELECT 100, 150 UNION ALL
SELECT 150, 250 UNION ALL
SELECT 250, 99999999 ;
Now try one of these queries:
SELECT R.low_price, R.high_price
FROM northwind.dbo.products AS P,
price_ranges AS R
WHERE P.unitprice >= R.low_price
AND P.unitprice < R.high_price
/* AND ... ? */
GROUP BY R.low_price, R.high_price ;
SELECT R.low_price, R.high_price
FROM price_ranges AS R
WHERE EXISTS
(SELECT *
FROM northwind.dbo.products AS P
WHERE P.unitprice >= R.low_price
AND P.unitprice < R.high_price
/* AND ... ? */) ;
David Portas
SQL Server MVP
--
price range hyperlinks" as filters for matched items.
So you would have something like:
Under $50 $75-$100 $150-$250
in a row with only the applicable price ranges to the queried products
showing. So, with the above example, if there weren't any products
between $101 and $149, that price range wouldn't show.
I'm wondering about the structure of the SQL for this type of query.
On the surface, I could do multiple queries for each of the price
ranges I needed and union them together, but I'm wondering if there is
a more efficient way to do it in one pass.
Ian<dontspammenow@.yahoo.com> wrote in message
news:1132866827.910562.120930@.g43g2000cwa.googlegroups.com...
> I've noticed that some search queries on e-commerce sites return "item
> price range hyperlinks" as filters for matched items.
> So you would have something like:
> Under $50 $75-$100 $150-$250
> in a row with only the applicable price ranges to the queried products
> showing. So, with the above example, if there weren't any products
> between $101 and $149, that price range wouldn't show.
> I'm wondering about the structure of the SQL for this type of query.
> On the surface, I could do multiple queries for each of the price
> ranges I needed and union them together, but I'm wondering if there is
> a more efficient way to do it in one pass.
> Ian
>
Put your possible price ranges in a table:
CREATE TABLE price_ranges (low_price DECIMAL(10,2) NOT NULL, high_price
DECIMAL (10,2) NOT NULL, CHECK (low_price < high_price), PRIMARY KEY
(low_price, high_price));
INSERT INTO price_ranges (low_price, high_price)
SELECT 1, 50 UNION ALL
SELECT 50, 75 UNION ALL
SELECT 75, 100 UNION ALL
SELECT 100, 150 UNION ALL
SELECT 150, 250 UNION ALL
SELECT 250, 99999999 ;
Now try one of these queries:
SELECT R.low_price, R.high_price
FROM northwind.dbo.products AS P,
price_ranges AS R
WHERE P.unitprice >= R.low_price
AND P.unitprice < R.high_price
/* AND ... ? */
GROUP BY R.low_price, R.high_price ;
SELECT R.low_price, R.high_price
FROM price_ranges AS R
WHERE EXISTS
(SELECT *
FROM northwind.dbo.products AS P
WHERE P.unitprice >= R.low_price
AND P.unitprice < R.high_price
/* AND ... ? */) ;
David Portas
SQL Server MVP
--
Wednesday, March 21, 2012
Processor Utilization
Hi
I am running SQL Server 2000 on a 2 processor system (Windows 2003
Standard). I regularily run large queries. When I run the queries the CPU
utilization is consitantly around 11%.
It appears as if something is limiting SQL Server to 11% CPU per
process/query. I have verified that its not I/O bound.
The Processor properties are set as follows:
- All 2 processors are checked in Processor Control
- "Maximum worker threads" is set to 255
- "Boost SQL Server priority on Windows" is not checked
- "Use Windows NT fibres" is checked
- "Use all available processors" is checked
Why is the CPU not fully utilized? What does "Boost SQL Server priority on
Windows" do?
Any assistance would be greatly appreciated
Thanks
Hi
You may want to check the boost priority option and not use fibres (as
fibres can cause other problems). You could also try using the MAXDOP hint to
only use one processor.
Monitor context switches to see if there is an excessive number before
considering using fibres. Ken Hendersons book The Gurus Guide to SQL Server
Architecture and Internals ISBN 0201700476 has a good section on this.
John
"Macisu" wrote:
> Hi
> I am running SQL Server 2000 on a 2 processor system (Windows 2003
> Standard). I regularily run large queries. When I run the queries the CPU
> utilization is consitantly around 11%.
> It appears as if something is limiting SQL Server to 11% CPU per
> process/query. I have verified that its not I/O bound.
> The Processor properties are set as follows:
> - All 2 processors are checked in Processor Control
> - "Maximum worker threads" is set to 255
> - "Boost SQL Server priority on Windows" is not checked
> - "Use Windows NT fibres" is checked
> - "Use all available processors" is checked
> Why is the CPU not fully utilized? What does "Boost SQL Server priority on
> Windows" do?
> Any assistance would be greatly appreciated
> Thanks
I am running SQL Server 2000 on a 2 processor system (Windows 2003
Standard). I regularily run large queries. When I run the queries the CPU
utilization is consitantly around 11%.
It appears as if something is limiting SQL Server to 11% CPU per
process/query. I have verified that its not I/O bound.
The Processor properties are set as follows:
- All 2 processors are checked in Processor Control
- "Maximum worker threads" is set to 255
- "Boost SQL Server priority on Windows" is not checked
- "Use Windows NT fibres" is checked
- "Use all available processors" is checked
Why is the CPU not fully utilized? What does "Boost SQL Server priority on
Windows" do?
Any assistance would be greatly appreciated
Thanks
Hi
You may want to check the boost priority option and not use fibres (as
fibres can cause other problems). You could also try using the MAXDOP hint to
only use one processor.
Monitor context switches to see if there is an excessive number before
considering using fibres. Ken Hendersons book The Gurus Guide to SQL Server
Architecture and Internals ISBN 0201700476 has a good section on this.
John
"Macisu" wrote:
> Hi
> I am running SQL Server 2000 on a 2 processor system (Windows 2003
> Standard). I regularily run large queries. When I run the queries the CPU
> utilization is consitantly around 11%.
> It appears as if something is limiting SQL Server to 11% CPU per
> process/query. I have verified that its not I/O bound.
> The Processor properties are set as follows:
> - All 2 processors are checked in Processor Control
> - "Maximum worker threads" is set to 255
> - "Boost SQL Server priority on Windows" is not checked
> - "Use Windows NT fibres" is checked
> - "Use all available processors" is checked
> Why is the CPU not fully utilized? What does "Boost SQL Server priority on
> Windows" do?
> Any assistance would be greatly appreciated
> Thanks
Processor Utilization
Hi
I am running SQL Server 2000 on a 2 processor system (Windows 2003
Standard). I regularily run large queries. When I run the queries the CPU
utilization is consitantly around 11%.
It appears as if something is limiting SQL Server to 11% CPU per
process/query. I have verified that its not I/O bound.
The Processor properties are set as follows:
- All 2 processors are checked in Processor Control
- "Maximum worker threads" is set to 255
- "Boost SQL Server priority on Windows" is not checked
- "Use Windows NT fibres" is checked
- "Use all available processors" is checked
Why is the CPU not fully utilized? What does "Boost SQL Server priority on
Windows" do?
Any assistance would be greatly appreciated
ThanksHi
You may want to check the boost priority option and not use fibres (as
fibres can cause other problems). You could also try using the MAXDOP hint to
only use one processor.
Monitor context switches to see if there is an excessive number before
considering using fibres. Ken Hendersons book The Gurus Guide to SQL Server
Architecture and Internals ISBN 0201700476 has a good section on this.
John
"Macisu" wrote:
> Hi
> I am running SQL Server 2000 on a 2 processor system (Windows 2003
> Standard). I regularily run large queries. When I run the queries the CPU
> utilization is consitantly around 11%.
> It appears as if something is limiting SQL Server to 11% CPU per
> process/query. I have verified that its not I/O bound.
> The Processor properties are set as follows:
> - All 2 processors are checked in Processor Control
> - "Maximum worker threads" is set to 255
> - "Boost SQL Server priority on Windows" is not checked
> - "Use Windows NT fibres" is checked
> - "Use all available processors" is checked
> Why is the CPU not fully utilized? What does "Boost SQL Server priority on
> Windows" do?
> Any assistance would be greatly appreciated
> Thanks
I am running SQL Server 2000 on a 2 processor system (Windows 2003
Standard). I regularily run large queries. When I run the queries the CPU
utilization is consitantly around 11%.
It appears as if something is limiting SQL Server to 11% CPU per
process/query. I have verified that its not I/O bound.
The Processor properties are set as follows:
- All 2 processors are checked in Processor Control
- "Maximum worker threads" is set to 255
- "Boost SQL Server priority on Windows" is not checked
- "Use Windows NT fibres" is checked
- "Use all available processors" is checked
Why is the CPU not fully utilized? What does "Boost SQL Server priority on
Windows" do?
Any assistance would be greatly appreciated
ThanksHi
You may want to check the boost priority option and not use fibres (as
fibres can cause other problems). You could also try using the MAXDOP hint to
only use one processor.
Monitor context switches to see if there is an excessive number before
considering using fibres. Ken Hendersons book The Gurus Guide to SQL Server
Architecture and Internals ISBN 0201700476 has a good section on this.
John
"Macisu" wrote:
> Hi
> I am running SQL Server 2000 on a 2 processor system (Windows 2003
> Standard). I regularily run large queries. When I run the queries the CPU
> utilization is consitantly around 11%.
> It appears as if something is limiting SQL Server to 11% CPU per
> process/query. I have verified that its not I/O bound.
> The Processor properties are set as follows:
> - All 2 processors are checked in Processor Control
> - "Maximum worker threads" is set to 255
> - "Boost SQL Server priority on Windows" is not checked
> - "Use Windows NT fibres" is checked
> - "Use all available processors" is checked
> Why is the CPU not fully utilized? What does "Boost SQL Server priority on
> Windows" do?
> Any assistance would be greatly appreciated
> Thanks
Processor Utilization
Hi
I am running SQL Server 2000 on a 2 processor system (Windows 2003
Standard). I regularily run large queries. When I run the queries the CPU
utilization is consitantly around 11%.
It appears as if something is limiting SQL Server to 11% CPU per
process/query. I have verified that its not I/O bound.
The Processor properties are set as follows:
- All 2 processors are checked in Processor Control
- "Maximum worker threads" is set to 255
- "Boost SQL Server priority on Windows" is not checked
- "Use Windows NT fibres" is checked
- "Use all available processors" is checked
Why is the CPU not fully utilized? What does "Boost SQL Server priority on
Windows" do?
Any assistance would be greatly appreciated
ThanksHi
You may want to check the boost priority option and not use fibres (as
fibres can cause other problems). You could also try using the MAXDOP hint t
o
only use one processor.
Monitor context switches to see if there is an excessive number before
considering using fibres. Ken Hendersons book The Gurus Guide to SQL Server
Architecture and Internals ISBN 0201700476 has a good section on this.
John
"Macisu" wrote:
> Hi
> I am running SQL Server 2000 on a 2 processor system (Windows 2003
> Standard). I regularily run large queries. When I run the queries the CPU
> utilization is consitantly around 11%.
> It appears as if something is limiting SQL Server to 11% CPU per
> process/query. I have verified that its not I/O bound.
> The Processor properties are set as follows:
> - All 2 processors are checked in Processor Control
> - "Maximum worker threads" is set to 255
> - "Boost SQL Server priority on Windows" is not checked
> - "Use Windows NT fibres" is checked
> - "Use all available processors" is checked
> Why is the CPU not fully utilized? What does "Boost SQL Server priority on
> Windows" do?
> Any assistance would be greatly appreciated
> Thanks
I am running SQL Server 2000 on a 2 processor system (Windows 2003
Standard). I regularily run large queries. When I run the queries the CPU
utilization is consitantly around 11%.
It appears as if something is limiting SQL Server to 11% CPU per
process/query. I have verified that its not I/O bound.
The Processor properties are set as follows:
- All 2 processors are checked in Processor Control
- "Maximum worker threads" is set to 255
- "Boost SQL Server priority on Windows" is not checked
- "Use Windows NT fibres" is checked
- "Use all available processors" is checked
Why is the CPU not fully utilized? What does "Boost SQL Server priority on
Windows" do?
Any assistance would be greatly appreciated
ThanksHi
You may want to check the boost priority option and not use fibres (as
fibres can cause other problems). You could also try using the MAXDOP hint t
o
only use one processor.
Monitor context switches to see if there is an excessive number before
considering using fibres. Ken Hendersons book The Gurus Guide to SQL Server
Architecture and Internals ISBN 0201700476 has a good section on this.
John
"Macisu" wrote:
> Hi
> I am running SQL Server 2000 on a 2 processor system (Windows 2003
> Standard). I regularily run large queries. When I run the queries the CPU
> utilization is consitantly around 11%.
> It appears as if something is limiting SQL Server to 11% CPU per
> process/query. I have verified that its not I/O bound.
> The Processor properties are set as follows:
> - All 2 processors are checked in Processor Control
> - "Maximum worker threads" is set to 255
> - "Boost SQL Server priority on Windows" is not checked
> - "Use Windows NT fibres" is checked
> - "Use all available processors" is checked
> Why is the CPU not fully utilized? What does "Boost SQL Server priority on
> Windows" do?
> Any assistance would be greatly appreciated
> Thanks
Tuesday, March 20, 2012
Processing Queries via Email
In SQL 2000, we processed queries and return results to user. In SQL 2005, we
have not been successful in doing so. I do not see any way using DB Mail to
process incoming mail.
We have set up SQL Mail and are able to send mail with no problem. We had
planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
we could migrate to whatever process would provide the same functionality.
So, two questions:
1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
queries and returning results to user?
2. What provides the equivalent functionality in SQL 2005? Is it done via
Reporting Service?
If someone can point me in the right direction for either question, I would
be very grateful.
Hi Roger
AFAIK there is no method of reading a mailbox with database mail, therefore
you would have to retain your current process of reading the mailbox or
possibly use the CLR to do this (for example see
http://support.microsoft.com/kb/813349 ). You could use a different (possibly
more reliable) method of delivery such as a web service instead which can be
hosted by the database server.
John
"RogerT" wrote:
> In SQL 2000, we processed queries and return results to user. In SQL 2005, we
> have not been successful in doing so. I do not see any way using DB Mail to
> process incoming mail.
> We have set up SQL Mail and are able to send mail with no problem. We had
> planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
> we could migrate to whatever process would provide the same functionality.
> So, two questions:
> 1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
> queries and returning results to user?
> 2. What provides the equivalent functionality in SQL 2005? Is it done via
> Reporting Service?
> If someone can point me in the right direction for either question, I would
> be very grateful.
have not been successful in doing so. I do not see any way using DB Mail to
process incoming mail.
We have set up SQL Mail and are able to send mail with no problem. We had
planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
we could migrate to whatever process would provide the same functionality.
So, two questions:
1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
queries and returning results to user?
2. What provides the equivalent functionality in SQL 2005? Is it done via
Reporting Service?
If someone can point me in the right direction for either question, I would
be very grateful.
Hi Roger
AFAIK there is no method of reading a mailbox with database mail, therefore
you would have to retain your current process of reading the mailbox or
possibly use the CLR to do this (for example see
http://support.microsoft.com/kb/813349 ). You could use a different (possibly
more reliable) method of delivery such as a web service instead which can be
hosted by the database server.
John
"RogerT" wrote:
> In SQL 2000, we processed queries and return results to user. In SQL 2005, we
> have not been successful in doing so. I do not see any way using DB Mail to
> process incoming mail.
> We have set up SQL Mail and are able to send mail with no problem. We had
> planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
> we could migrate to whatever process would provide the same functionality.
> So, two questions:
> 1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
> queries and returning results to user?
> 2. What provides the equivalent functionality in SQL 2005? Is it done via
> Reporting Service?
> If someone can point me in the right direction for either question, I would
> be very grateful.
Processing Queries via Email
In SQL 2000, we processed queries and return results to user. In SQL 2005, w
e
have not been successful in doing so. I do not see any way using DB Mail to
process incoming mail.
We have set up SQL Mail and are able to send mail with no problem. We had
planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
we could migrate to whatever process would provide the same functionality.
So, two questions:
1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
queries and returning results to user?
2. What provides the equivalent functionality in SQL 2005? Is it done via
Reporting Service?
If someone can point me in the right direction for either question, I would
be very grateful.Hi Roger
AFAIK there is no method of reading a mailbox with database mail, therefore
you would have to retain your current process of reading the mailbox or
possibly use the CLR to do this (for example see
http://support.microsoft.com/kb/813349 ). You could use a different (possibl
y
more reliable) method of delivery such as a web service instead which can be
hosted by the database server.
John
"RogerT" wrote:
> In SQL 2000, we processed queries and return results to user. In SQL 2005,
we
> have not been successful in doing so. I do not see any way using DB Mail t
o
> process incoming mail.
> We have set up SQL Mail and are able to send mail with no problem. We had
> planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail unti
l
> we could migrate to whatever process would provide the same functionality.
> So, two questions:
> 1. Has anyone been successful using SQL Mail on SQL 2005 to process incomi
ng
> queries and returning results to user?
> 2. What provides the equivalent functionality in SQL 2005? Is it done via
> Reporting Service?
> If someone can point me in the right direction for either question, I woul
d
> be very grateful.
e
have not been successful in doing so. I do not see any way using DB Mail to
process incoming mail.
We have set up SQL Mail and are able to send mail with no problem. We had
planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
we could migrate to whatever process would provide the same functionality.
So, two questions:
1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
queries and returning results to user?
2. What provides the equivalent functionality in SQL 2005? Is it done via
Reporting Service?
If someone can point me in the right direction for either question, I would
be very grateful.Hi Roger
AFAIK there is no method of reading a mailbox with database mail, therefore
you would have to retain your current process of reading the mailbox or
possibly use the CLR to do this (for example see
http://support.microsoft.com/kb/813349 ). You could use a different (possibl
y
more reliable) method of delivery such as a web service instead which can be
hosted by the database server.
John
"RogerT" wrote:
> In SQL 2000, we processed queries and return results to user. In SQL 2005,
we
> have not been successful in doing so. I do not see any way using DB Mail t
o
> process incoming mail.
> We have set up SQL Mail and are able to send mail with no problem. We had
> planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail unti
l
> we could migrate to whatever process would provide the same functionality.
> So, two questions:
> 1. Has anyone been successful using SQL Mail on SQL 2005 to process incomi
ng
> queries and returning results to user?
> 2. What provides the equivalent functionality in SQL 2005? Is it done via
> Reporting Service?
> If someone can point me in the right direction for either question, I woul
d
> be very grateful.
Processing Queries via Email
In SQL 2000, we processed queries and return results to user. In SQL 2005, we
have not been successful in doing so. I do not see any way using DB Mail to
process incoming mail.
We have set up SQL Mail and are able to send mail with no problem. We had
planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
we could migrate to whatever process would provide the same functionality.
So, two questions:
1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
queries and returning results to user?
2. What provides the equivalent functionality in SQL 2005? Is it done via
Reporting Service?
If someone can point me in the right direction for either question, I would
be very grateful.Hi Roger
AFAIK there is no method of reading a mailbox with database mail, therefore
you would have to retain your current process of reading the mailbox or
possibly use the CLR to do this (for example see
http://support.microsoft.com/kb/813349 ). You could use a different (possibly
more reliable) method of delivery such as a web service instead which can be
hosted by the database server.
John
"RogerT" wrote:
> In SQL 2000, we processed queries and return results to user. In SQL 2005, we
> have not been successful in doing so. I do not see any way using DB Mail to
> process incoming mail.
> We have set up SQL Mail and are able to send mail with no problem. We had
> planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
> we could migrate to whatever process would provide the same functionality.
> So, two questions:
> 1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
> queries and returning results to user?
> 2. What provides the equivalent functionality in SQL 2005? Is it done via
> Reporting Service?
> If someone can point me in the right direction for either question, I would
> be very grateful.
have not been successful in doing so. I do not see any way using DB Mail to
process incoming mail.
We have set up SQL Mail and are able to send mail with no problem. We had
planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
we could migrate to whatever process would provide the same functionality.
So, two questions:
1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
queries and returning results to user?
2. What provides the equivalent functionality in SQL 2005? Is it done via
Reporting Service?
If someone can point me in the right direction for either question, I would
be very grateful.Hi Roger
AFAIK there is no method of reading a mailbox with database mail, therefore
you would have to retain your current process of reading the mailbox or
possibly use the CLR to do this (for example see
http://support.microsoft.com/kb/813349 ). You could use a different (possibly
more reliable) method of delivery such as a web service instead which can be
hosted by the database server.
John
"RogerT" wrote:
> In SQL 2000, we processed queries and return results to user. In SQL 2005, we
> have not been successful in doing so. I do not see any way using DB Mail to
> process incoming mail.
> We have set up SQL Mail and are able to send mail with no problem. We had
> planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
> we could migrate to whatever process would provide the same functionality.
> So, two questions:
> 1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
> queries and returning results to user?
> 2. What provides the equivalent functionality in SQL 2005? Is it done via
> Reporting Service?
> If someone can point me in the right direction for either question, I would
> be very grateful.
Friday, March 9, 2012
Process throttling
Hi,
We have an application accessing an SQL Database. The problem is that due to
a flaw in design, when queries are submitted we get 100% cpu usage on the
server. Is there any way to limit processing power for specific access?
Thanks
NichHi,
Try to identify the SQL's which is doing a table scan. Then based on the
where condition in the select statement
create useful indexes, this will definitely reduce the Disk reads, which in
turn reduces the CPU usage.
Thanks
Hari
MCDBA
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
to
Mike Kruchten
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
to
Thanks, we will be using the Index tune-up wizard in fact, but does any one
know if a physical cpu usage limit can be set in any way apart from using
indexes to maximize table access speed
Thanks
Nich
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
in
of
your processors. that would leave the other processors free to do other stu
ff.
you could also try lowering the priority of the sqlserver process.
i think it would be nice if we could set priority levels for db user account
s
so that certain users couldn't hog db resources or certain users' queries wo
uld
get higher priority than others.
Microsoft News - SQL Server wrote:
[QUOTE]
> Hi Hari,
> Thanks, we will be using the Index tune-up wizard in fact, but does any on
e
> know if a physical cpu usage limit can be set in any way apart from using
> indexes to maximize table access speed
> Thanks
> Nich
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
> in
> due
> the
We have an application accessing an SQL Database. The problem is that due to
a flaw in design, when queries are submitted we get 100% cpu usage on the
server. Is there any way to limit processing power for specific access?
Thanks
NichHi,
Try to identify the SQL's which is doing a table scan. Then based on the
where condition in the select statement
create useful indexes, this will definitely reduce the Disk reads, which in
turn reduces the CPU usage.
Thanks
Hari
MCDBA
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
quote:
> Hi,
> We have an application accessing an SQL Database. The problem is that due
to
quote:|||No.
> a flaw in design, when queries are submitted we get 100% cpu usage on the
> server. Is there any way to limit processing power for specific access?
> Thanks
> Nich
>
Mike Kruchten
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
quote:
> Hi,
> We have an application accessing an SQL Database. The problem is that due
to
quote:|||Hi Hari,
> a flaw in design, when queries are submitted we get 100% cpu usage on the
> server. Is there any way to limit processing power for specific access?
> Thanks
> Nich
>
Thanks, we will be using the Index tune-up wizard in fact, but does any one
know if a physical cpu usage limit can be set in any way apart from using
indexes to maximize table access speed
Thanks
Nich
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
quote:
> Hi,
> Try to identify the SQL's which is doing a table scan. Then based on the
> where condition in the select statement
> create useful indexes, this will definitely reduce the Disk reads, which
in
quote:|||if you have a multiprocessor machine, you could limit sql server to a subset
> turn reduces the CPU usage.
> Thanks
> Hari
> MCDBA
> "Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
> news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
due[QUOTE]
> to
the[QUOTE]
>
of
your processors. that would leave the other processors free to do other stu
ff.
you could also try lowering the priority of the sqlserver process.
i think it would be nice if we could set priority levels for db user account
s
so that certain users couldn't hog db resources or certain users' queries wo
uld
get higher priority than others.
Microsoft News - SQL Server wrote:
[QUOTE]
> Hi Hari,
> Thanks, we will be using the Index tune-up wizard in fact, but does any on
e
> know if a physical cpu usage limit can be set in any way apart from using
> indexes to maximize table access speed
> Thanks
> Nich
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
> in
> due
> the
Process throttling
Hi,
We have an application accessing an SQL Database. The problem is that due to
a flaw in design, when queries are submitted we get 100% cpu usage on the
server. Is there any way to limit processing power for specific access?
Thanks
NichHi,
Try to identify the SQL's which is doing a table scan. Then based on the
where condition in the select statement
create useful indexes, this will definitely reduce the Disk reads, which in
turn reduces the CPU usage.
Thanks
Hari
MCDBA
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have an application accessing an SQL Database. The problem is that due
to
> a flaw in design, when queries are submitted we get 100% cpu usage on the
> server. Is there any way to limit processing power for specific access?
> Thanks
> Nich
>|||No.
Mike Kruchten
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have an application accessing an SQL Database. The problem is that due
to
> a flaw in design, when queries are submitted we get 100% cpu usage on the
> server. Is there any way to limit processing power for specific access?
> Thanks
> Nich
>|||Hi Hari,
Thanks, we will be using the Index tune-up wizard in fact, but does any one
know if a physical cpu usage limit can be set in any way apart from using
indexes to maximize table access speed
Thanks
Nich
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Try to identify the SQL's which is doing a table scan. Then based on the
> where condition in the select statement
> create useful indexes, this will definitely reduce the Disk reads, which
in
> turn reduces the CPU usage.
> Thanks
> Hari
> MCDBA
> "Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
> news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > We have an application accessing an SQL Database. The problem is that
due
> to
> > a flaw in design, when queries are submitted we get 100% cpu usage on
the
> > server. Is there any way to limit processing power for specific access?
> >
> > Thanks
> >
> > Nich
> >
> >
>|||if you have a multiprocessor machine, you could limit sql server to a subset of
your processors. that would leave the other processors free to do other stuff.
you could also try lowering the priority of the sqlserver process.
i think it would be nice if we could set priority levels for db user accounts
so that certain users couldn't hog db resources or certain users' queries would
get higher priority than others.
Microsoft News - SQL Server wrote:
> Hi Hari,
> Thanks, we will be using the Index tune-up wizard in fact, but does any one
> know if a physical cpu usage limit can be set in any way apart from using
> indexes to maximize table access speed
> Thanks
> Nich
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Try to identify the SQL's which is doing a table scan. Then based on the
> > where condition in the select statement
> > create useful indexes, this will definitely reduce the Disk reads, which
> in
> > turn reduces the CPU usage.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
> > news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > >
> > > We have an application accessing an SQL Database. The problem is that
> due
> > to
> > > a flaw in design, when queries are submitted we get 100% cpu usage on
> the
> > > server. Is there any way to limit processing power for specific access?
> > >
> > > Thanks
> > >
> > > Nich
> > >
> > >
> >
> >
We have an application accessing an SQL Database. The problem is that due to
a flaw in design, when queries are submitted we get 100% cpu usage on the
server. Is there any way to limit processing power for specific access?
Thanks
NichHi,
Try to identify the SQL's which is doing a table scan. Then based on the
where condition in the select statement
create useful indexes, this will definitely reduce the Disk reads, which in
turn reduces the CPU usage.
Thanks
Hari
MCDBA
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have an application accessing an SQL Database. The problem is that due
to
> a flaw in design, when queries are submitted we get 100% cpu usage on the
> server. Is there any way to limit processing power for specific access?
> Thanks
> Nich
>|||No.
Mike Kruchten
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have an application accessing an SQL Database. The problem is that due
to
> a flaw in design, when queries are submitted we get 100% cpu usage on the
> server. Is there any way to limit processing power for specific access?
> Thanks
> Nich
>|||Hi Hari,
Thanks, we will be using the Index tune-up wizard in fact, but does any one
know if a physical cpu usage limit can be set in any way apart from using
indexes to maximize table access speed
Thanks
Nich
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Try to identify the SQL's which is doing a table scan. Then based on the
> where condition in the select statement
> create useful indexes, this will definitely reduce the Disk reads, which
in
> turn reduces the CPU usage.
> Thanks
> Hari
> MCDBA
> "Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
> news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > We have an application accessing an SQL Database. The problem is that
due
> to
> > a flaw in design, when queries are submitted we get 100% cpu usage on
the
> > server. Is there any way to limit processing power for specific access?
> >
> > Thanks
> >
> > Nich
> >
> >
>|||if you have a multiprocessor machine, you could limit sql server to a subset of
your processors. that would leave the other processors free to do other stuff.
you could also try lowering the priority of the sqlserver process.
i think it would be nice if we could set priority levels for db user accounts
so that certain users couldn't hog db resources or certain users' queries would
get higher priority than others.
Microsoft News - SQL Server wrote:
> Hi Hari,
> Thanks, we will be using the Index tune-up wizard in fact, but does any one
> know if a physical cpu usage limit can be set in any way apart from using
> indexes to maximize table access speed
> Thanks
> Nich
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Try to identify the SQL's which is doing a table scan. Then based on the
> > where condition in the select statement
> > create useful indexes, this will definitely reduce the Disk reads, which
> in
> > turn reduces the CPU usage.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
> > news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > >
> > > We have an application accessing an SQL Database. The problem is that
> due
> > to
> > > a flaw in design, when queries are submitted we get 100% cpu usage on
> the
> > > server. Is there any way to limit processing power for specific access?
> > >
> > > Thanks
> > >
> > > Nich
> > >
> > >
> >
> >
Subscribe to:
Posts (Atom)