subquery or not subquery? that is the question.

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 2005 Forums

Transact-SQL (2005)

subquery or not subquery? that is the question.

Author

Topic

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-10-06 : 14:53:48

Hello guys, how are you doing?
In a query that I made there is three columns that aggregates the number of items in group, which is possible to do it all at once only consulting it with sub queries, but I also could get these column’s values through separated queries. For performance concern should I leave it the way it is or do I make other queries?

Tell me if you understood the question I’m not chore about my English =S, and thank you very much.

Just to illustrate it, here is the query:

Select u.userType_id, u.cargo_id, u.nickname, u.e_mail, u.creation, u.credit, u.estado_id, u.image, u.slogan,
u.apresent, u.timeCredit, u.posts,
inv.nickname as inviter, count(ctTemp.post_id) as threadsCreated,
count(atTemp.post_id) as answers, count(invf.master_id) as friends
from caraUsers as u
Inner Join caraFriends as f on f.slave_id = u.user_id
Inner Join caraUsers as inv on inv.user_id = f.master_id
Inner Join (Select min(post_id) as post_id, user_id from caraPosts Group by thread_id, user_id) as ctTemp
on ctTemp.user_id = u.user_id
Left Join (Select post_id, user_id from caraPosts where answer = 1) as atTemp
on atTemp.user_id = u.user_id and atTemp.post_id <> ctTemp.post_id
Left Join (Select master_id from caraFriends where invited = 1) as invf on invf.master_id = u.user_id
Where u.user_id = @consultUser_id
Group by u.userType_id, u.cargo_id, u.nickname, u.e_mail, u.creation, u.credit, u.estado_id, u.image, u.slogan,
u.apresent, u.timeCredit, u.posts, inv.nickname

Ideas are bullet proof.

Vendetta.

Kristen
Test

22859 Posts

Posted - 2007-10-07 : 01:49:10

At a quick glance it looks to me as though you could move the COUNT()'s to the Sub Selects (and add Group By to them), and then not have a Group By on the outer select [assuming caraUsers is not returning multiple matching rows for the displayed columns]

Kristen

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-10-07 : 11:50:15

Thank you very much Kristen for the reply, I'm very impress to see people taking their time to try to understand a query like these without even knowing the data base's model, you didn't have to do it. I just asked if would be better get those COUNT()'s columns in another separated query rather than getting through the same query. Probably was my English, sorry about that. Per example if would be faster doing this way:

Select u.userType_id, u.cargo_id, u.nickname, u.e_mail, u.creation, u.credit, u.estado_id, u.image, u.slogan,
u.apresent, u.timeCredit, u.posts,
inv.nickname as inviter, count(atTemp.post_id) as answers, count(invf.master_id) as friends
from caraUsers as u
Inner Join caraFriends as f on f.slave_id = u.user_id
Inner Join caraUsers as inv on inv.user_id = f.master_id
Left Join (Select post_id, user_id from caraPosts where answer = 1) as atTemp
on atTemp.user_id = u.user_id and atTemp.post_id <> ctTemp.post_id
Left Join (Select master_id from caraFriends where invited = 1) as invf on invf.master_id = u.user_id
Where u.user_id = @consultUser_id
Group by u.userType_id, u.cargo_id, u.nickname, u.e_mail, u.creation, u.credit, u.estado_id, u.image, u.slogan,
u.apresent, u.timeCredit, u.posts, inv.nickname

Select min(post_id) as post_id, user_id from caraPosts Group by thread_id, user_id where user_id = @consultUser_id

Thank you again.

Ideas are bullet proof.

Vendetta.

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 01:24:09

What I was meaning was to change, say,

Left Join (Select post_id, user_id from caraPosts where answer = 1) as atTemp
on atTemp.user_id = u.user_id and atTemp.post_id <> ctTemp.post_id

to


Left Join 
(
    Select post_id, user_id, count(atTemp.post_id) as answers
    from caraPosts 
    where answer = 1
    GROUP BY post_id, user_id
) as atTemp 
     on atTemp.user_id = u.user_id 
    and atTemp.post_id <> ctTemp.post_id

Kristen

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-10-09 : 12:37:24

Well I can't do this, but never mind I'll just keep doing the way that I was doing. It might take just more time to process.

Thank you Kristen for your kindly help anyway. =D

Ideas are bullet proof.

Vendetta.

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 13:53:49

"Well I can't do this"

If you explain WHY you can't do it we may be able to help you come with a way that you CAN do something more performant

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-10-09 : 16:55:36

Because the way of my database's model is organized it wouldn't count the number of rows on the sub query as I wanted to, it would count the number of registries with the same value thread_id.
I made a mistake put all query. I should had put a sample version to explain my question :S.

Here it is a sample of the model and the query:
It is about a forum:

thread’s table

thread_id
forum_id
title
rate

post’s table

post_id
user_id
thread_id
msg

I have to make a query that returns the number of threads made by a user.

So I did this:

Select count(*) from (Select min(post_id) as post_id, user_id from posts Group by thread_id, user_id) as temp

Which works just fine. So it hit me if would be better get this value straight away and set it to a variable, or live it inside of the big query.

Like :

Declare @myPosts int

Set @myPosts = Select count(*) from (Select min(post_id) as post_id, user_id from posts Group by thread_id, user_id Where user_id = @consultUser_id) as temp

Or

Select u.userType_id, u.cargo_id, u.nickname, u.e_mail, u.creation, u.credit, u.estado_id, u.image, u.slogan,
u.apresent, u.timeCredit, u.posts,
inv.nickname as inviter, count(ctTemp.post_id) as threadsCreated,
count(atTemp.post_id) as answers, count(invf.master_id) as friends
from caraUsers as u
Left Join caraFriends as f on f.slave_id = u.user_id
Left Join caraUsers as inv on inv.user_id = f.master_id
Left Join (Select min(post_id) as post_id, user_id from caraPosts Group by thread_id, user_id) as ctTemp
on ctTemp.user_id = u.user_id
Left Join (Select post_id, user_id from caraPosts where answer = 1) as atTemp
on atTemp.user_id = u.user_id and atTemp.post_id <> ctTemp.post_id
Left Join (Select master_id from caraFriends where invited = 1) as invf on invf.master_id = u.user_id
Where u.user_id = @consultUser_id
Group by u.userType_id, u.cargo_id, u.nickname, u.e_mail, u.creation, u.credit, u.estado_id, u.image, u.slogan,
u.apresent, u.timeCredit, u.posts, inv.nickname

Of course I would have to request the rest of the user's information in another query.

Thank you Kristen to been persistent.=D

Ideas are bullet proof.

Vendetta.

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 22:34:21

"it would count the number of registries with the same value thread_id."

You could perhaps use

SUM(CASE WHEN ... IsSubQueryCondition ... THEN 1 ELSE 0 END)

instead of COUNT(*) then.

Either way, I reckon the nested selects in your original query are going to perform poorly when you have lots of data in your table.

Kristen

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-10-09 : 22:48:51

So the inserting SUM... in the sub query could increase the performance of my query?

PS: ohh yahh. You just got the point of my question. It would slow down the response, so would be better doing those "selects" sparely?

Thank you very much.

Ideas are bullet proof.

Vendetta.

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 00:18:01

"so would be better doing those "selects" sparely?"

Well, I think you are pulling LOTS of rows to then do a GROUP BY to get the COUNTs.

So you should do the counts in sub-select as Aggregates.

Or do them separately if that is easier, and you only want one row returned.

Kristen

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-10-10 : 00:41:31

hhmm Ok. Now I understand what I should concern about performance either.

Thank you very much Kristen, you did great. =D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources