A question about speed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

A question about speed

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-01 : 07:40:41

Safari writes "Suppose I use the Northwind database.
I want to display all categories with the CategoryID, CategoryName, and the number of all products in this category.
There two way to do this:
Q1:
select c.CategoryID, c.CategoryName,
(select count(ProductID) from Products
where CategoryID=c.CategoryID) as NumberOfProducts
from Categories c;
Q2:
select c.CategoryID, c.CategoryName, p.NumberOfProducts
from Categories c, (select CategoryID, count(ProductID) as NumberOfProducts
from Products group by CategoryID) p
where c.CategoryID = p.CategoryID

Please explain me which is the faster query ?"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-07-01 : 08:33:18

Q3:


select
    c.CategoryID,
    c.CategoryName,
    count(*)
from
    dbo.Categories c
    inner join dbo.Products p
        on c.CategoryID = p.CategoryID
group by
    c.CategoryID,
    c.CategoryName

All three have similar execution plans ... they vary a bit on the placement of aggregation. If you are trying to tune a particular query, you should use your ddl and data as there really isn't much in Northwind for this kind of testing.

That said, you should get into the habit of prefixing you table names with the owner. Also, you should do away with the old school joins and join us over here in SQL-92-land.

Jay White
{0}

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-02 : 04:57:46

quote:

Also, you should do away with the old school joins and join us over here in SQL-92-land.

are you..... preaching?

I have tried the INNERs and JOINs but there is something about using = and the way I think. Even the =* makes more sense than a RIGHT JOIN or whatever. Don't know... maybe I am just from the 'old school'.... can't get over it yet.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-07-02 : 07:08:43

quote:
are you..... preaching?...but there is something about using = and the way I think...

If you would plase turn in you bible
To Tips & Tricks according to SQL Server MVPs.
In the 4th paragraph and the 7th word.
Brother Page47, would you please read to the choir for me son?

Jay White
{0}

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-02 : 07:09:22

quote:
Even the =* makes more sense than a RIGHT JOIN or whatever. Don't know... maybe I am just from the 'old school'.... can't get over it yet.

Someday your *= style joins won't work at all anymore; now might be a good time to get over it before you're forced to get over it and have to rewrite everything.

Don't forget that the *= syntax doesn't always return the correct results. If you have Inside SQL Server by Microsoft Press I suggest you read up on them there.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources