Queries should return same results... but don't........

Thanks guys. I have been reading about CTEs and my view is that it
helps with performance with regards using sub queries? That seems to
be the main benefit.

Back to Sub Queries. Surely this:

SELECT Firstname, Surname
FROM users u
INNER JOIN usercars uc ON uc.userid = u.id
(Getting a list of all users who own a car)

would be much more efficient than:

SELECT Firstname, Surname
FROM users
WHERE id IN
(SELECT userid FROM usercar)

?

On Tue, 26 Aug 2008 17:57:59 -0700 (PDT), Cralis
wrote:

>Thanks guys. I have been reading about CTEs and my view is that it
>helps with performance with regards using sub queries? That seems to
>be the main benefit.

No, CTEs don't make any real difference in performance. SQL Server
combines the CTE with the query that references it before creating the
execution plan. This is no different than the way it treats a view,
or a derived table.

>Back to Sub Queries. Surely this:
>
>SELECT Firstname, Surname
>FROM users u
>INNER JOIN usercars uc ON uc.userid = u.id
>(Getting a list of all users who own a car)
>
>would be much more efficient than:
>
>SELECT Firstname, Surname
>FROM users
>WHERE id IN
>(SELECT userid FROM usercar)

Since they would return different results it seems pointless to
compare performance. The first returns the same user multiple times
if that user matches multiple rows in the usercar table. The second
returns each user just once. And while you are looking at
alternative, be sure to consider an EXISTS with a correlated subquery.

SELECT Firstname, Surname
FROM users
WHERE EXISTS
(SELECT * FROM usercar
WHERE users.ID = usercar.userid)

In theory this would perform better than your second IN query if
usercars is indexed on userid. That is because EXISTS stops at the
first match. In fact, the optimizer understands that the IN and
EXISTS queries are equivalent and usually performs the same processing
for either one.

Roy Harvey
Beacon Falls, CT

No, there is no performance benefit compared to subqueries, because a CTE
_is_ a type of subquery.
The benefit is that the subquery can be defined one and then used multiple
times. There are certain complex queries that are just easier to write when
you use a CTE.

Also, you still seem to be implying that subqueries are slow. Did you read
my first response in this thread? There is nothing bad about subqueries.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com


"Cralis" wrote in message
news:d7822693-f67e-45be-acd8-af6a3d086929-at-l33g2000pri.googlegroups.com...
> Thanks guys. I have been reading about CTEs and my view is that it
> helps with performance with regards using sub queries? That seems to
> be the main benefit.
>
> Back to Sub Queries. Surely this:
>
> SELECT Firstname, Surname
> FROM users u
> INNER JOIN usercars uc ON uc.userid = u.id
> (Getting a list of all users who own a car)
>
> would be much more efficient than:
>
> SELECT Firstname, Surname
> FROM users
> WHERE id IN
> (SELECT userid FROM usercar)
>
> ?

On Aug 27, 11:37*am, "Roy Harvey (SQL Server MVP)"
wrote:
....
> >WHERE id IN
> >(SELECT userid FROM usercar)
>
> Since they would return different results it seems pointless to
> compare performance. *The first returns the same user multiple times
> if that user matches multiple rows in the usercar table. *The second
> returns each user just once. *And while you are looking at
> alternative, be sure to consider an EXISTS with a correlated subquery.

I beg your pardon. Yes, that was an error I made. I am in the mindset
that subqueries should be avoided and swopped with joins instead IF
the same task can be performed using a join. Sorry for the error.

> Also, you still seem to be implying that subqueries are slow. Did you read
> my first response in this thread? There is nothing bad about subqueries.

I'm stuck in the anti-subquery mind set. I did read what you said and
that may make my development easier. If the optimiser can redo my
query in a way it feel is better, then I guess there is no performance
degredation.

Thanks a lot for all your help and advise. I'm still battling with the
CTE stuff though. Spoken to people at work here - noone has heard
of them, and when I was asked, 'Well, whats special about them...' I
gave the simple 'WITH' example that is in the MSDN pages... and they
said.. 'Well, that's just a temp table'. And.. I had no come back. If
there is no performance increase... then I'm battling to find a reason
to change from using a temp table, except for the fact that you don't
have to CREATE.... and all that. It's neat, and in one small bit of
code, which is pretty good.

There *can* be a world of difference between using a temp table and a CTE (or derived table). A more
fair comparison would be a derived table or for that matter "a dynamic view". With a temp table, you
force SQL Server to Run the query that you use to populate the temp table and also populate that
temp table. And then it forces SQL Server to run your "final" query against the temp table. For CTE,
derived table and view, SQL Server can work it magic like "in lining"/flattening the CTE with the
"outer" query.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Cralis" wrote in message
news:cecd907e-0ddb-45ff-87ab-46d9564d5c03-at-v39g2000pro.googlegroups.com...
>
>> Also, you still seem to be implying that subqueries are slow. Did you read
>> my first response in this thread? There is nothing bad about subqueries.
>
> I'm stuck in the anti-subquery mind set. I did read what you said and
> that may make my development easier. If the optimiser can redo my
> query in a way it feel is better, then I guess there is no performance
> degredation.
>
> Thanks a lot for all your help and advise. I'm still battling with the
> CTE stuff though. Spoken to people at work here - noone has heard
> of them, and when I was asked, 'Well, whats special about them...' I
> gave the simple 'WITH' example that is in the MSDN pages... and they
> said.. 'Well, that's just a temp table'. And.. I had no come back. If
> there is no performance increase... then I'm battling to find a reason
> to change from using a temp table, except for the fact that you don't
> have to CREATE.... and all that. It's neat, and in one small bit of
> code, which is pretty good.

On Tue, 26 Aug 2008 23:47:53 -0700 (PDT), Cralis
wrote:

>Thanks a lot for all your help and advise. I'm still battling with the
>CTE stuff though. Spoken to people at work here - noone has heard
>of them, and when I was asked, 'Well, whats special about them...' I
>gave the simple 'WITH' example that is in the MSDN pages... and they
>said.. 'Well, that's just a temp table'. And.. I had no come back. If
>there is no performance increase... then I'm battling to find a reason
>to change from using a temp table, except for the fact that you don't
>have to CREATE.... and all that. It's neat, and in one small bit of
>code, which is pretty good.

CTE is relatively new so it is not surprising that some people have
not used it yet.

A CTE can always substitute for a derived table. The only advantage a
CTE has is that the code can be clearer because there is less nesting.
I consider that an important advantage. It also provides the option
of avoiding temporary tables. It temp tables were used for
performance reasons that isn't much help. However I have known temp
tables to be used because the programmer could not handle subqueries;
in that case CTEs offer the choice of eliminating subqueries while
staying within the programmer's ability to grasp what is going on.

A clear advantage that a CTE has over a derived table is that it can
be referenced multiple times but only defined once. With derived
tables you would have to repeat the entire SELECT for each instance.

The other feature that CTEs offer is that they can be somewhat
recursive, but that is getting rather advanced.

Roy Harvey
Beacon Falls, CT

Logically you might think of it like a temp table, but if you're concerned
about performance, temp tables will definitely give you a hit. There is
overhead to creating the table and to maintaining and reaccessing it. A CTE
is part of the query and you avoid a lot of the overhead. Both CTEs and
derived tables can replace temp tables so you can get the performance gain.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com


"Cralis" wrote in message
news:cecd907e-0ddb-45ff-87ab-46d9564d5c03-at-v39g2000pro.googlegroups.com...
>
>> Also, you still seem to be implying that subqueries are slow. Did you
>> read
>> my first response in this thread? There is nothing bad about subqueries.
>
> I'm stuck in the anti-subquery mind set. I did read what you said and
> that may make my development easier. If the optimiser can redo my
> query in a way it feel is better, then I guess there is no performance
> degredation.
>
> Thanks a lot for all your help and advise. I'm still battling with the
> CTE stuff though. Spoken to people at work here - noone has heard
> of them, and when I was asked, 'Well, whats special about them...' I
> gave the simple 'WITH' example that is in the MSDN pages... and they
> said.. 'Well, that's just a temp table'. And.. I had no come back. If
> there is no performance increase... then I'm battling to find a reason
> to change from using a temp table, except for the fact that you don't
> have to CREATE.... and all that. It's neat, and in one small bit of
> code, which is pretty good.

Kalen

I think it depends. As I know CTEs do not keep statistics ,so it might be
benefit of using temporaty tables (with indexes).


"Kalen Delaney" wrote in message
news:uSoBPxGCJHA.1632-at-TK2MSFTNGP06.phx.gbl...
> Logically you might think of it like a temp table, but if you're concerned
> about performance, temp tables will definitely give you a hit. There is
> overhead to creating the table and to maintaining and reaccessing it. A
> CTE is part of the query and you avoid a lot of the overhead. Both CTEs
> and derived tables can replace temp tables so you can get the performance
> gain.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> www.SQLTuners.com
>
>
> "Cralis" wrote in message
> news:cecd907e-0ddb-45ff-87ab-46d9564d5c03-at-v39g2000pro.googlegroups.com...
>>
>>> Also, you still seem to be implying that subqueries are slow. Did you
>>> read
>>> my first response in this thread? There is nothing bad about subqueries.
>>
>> I'm stuck in the anti-subquery mind set. I did read what you said and
>> that may make my development easier. If the optimiser can redo my
>> query in a way it feel is better, then I guess there is no performance
>> degredation.
>>
>> Thanks a lot for all your help and advise. I'm still battling with the
>> CTE stuff though. Spoken to people at work here - noone has heard
>> of them, and when I was asked, 'Well, whats special about them...' I
>> gave the simple 'WITH' example that is in the MSDN pages... and they
>> said.. 'Well, that's just a temp table'. And.. I had no come back. If
>> there is no performance increase... then I'm battling to find a reason
>> to change from using a temp table, except for the fact that you don't
>> have to CREATE.... and all that. It's neat, and in one small bit of
>> code, which is pretty good.
>
>

I've taken a step back to look into the definition of a Derived table.
I found an artical that explains it. It looked like a SubQuery to me,
but I never knew what was meant by a derived table. Now I do.

SELECT firstname, lastname FROM
(SELECT * FROM dbo.[User]
WHERE roleid = 7) AS ManagerTable
ORDER BY lastname, firstname DESC

So, basically, a derived table is a temporary View?

On initial inspection, I though. Oh... the inner 'SubQuery' will be
run for each row in.... Oh... then I realised whats actually
happening.

So, the inner select is run first, and then the outer select is run on
those results. Internally, only one query is run on the dbo.[User]
table, which seems OK.

Now, whats been said above is that 2005/8 is using CTEs instead of
Derived Tables?

So, to rewrite the query as a CTE, I'd say,

WITH
ManagersCTE(Firstname, Surname)
AS
(
SELECT Firstname, LastName
FROM dbo.[User]
WHERE RoleID = 7
)

SELECT * FROM ManagersCTE
ORDER BY Surname, LastName

In both cases... the 'inner'

SELECT * FROM dbo.[User]
WHERE roleid = 7

can only be used once.

I guess my example is a very VERY simple one, so it's not showing the
true benefit of CTEs. Because

SELECT firstname, lastname FROM
(SELECT * FROM dbo.[User]
WHERE roleid = 7) AS ManagerTable
ORDER BY lastname, firstname DESC

Seems like the more readable, cleaner way to do this and would be my
choice here (if there is no performance benefit). Am I right here? am
I on the way to 'getting it'?