help condensing multiple queries to one

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)

help condensing multiple queries to one

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-30 : 15:43:00

Hi,

I have the below query that has multiple select statements from the same query. Is it possible to run this more efficiently with a SUM/ CASE statement so that its just one SELECT ?

I just used a SUM(CASE WHEN ....... statement on another query, but I am having trouble figuring out how to integrate it on this one, and if its even possible or worth it.

Thanks very much for any help!!
mike123

CREATE PROCEDURE [dbo].[select_mailbox_Count]
(
@userID int
)
AS SET NOCOUNT ON

SELECT count(*) as totalInbox,

(SELECT count(*) FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND checked = 0 and deletedbyRecipient =0 ) as totalInbox_UnRead,
(SELECT count(*) FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND checked = 2 and deletedbyRecipient =0 ) as totalInbox_UnReplied,
(SELECT count(*) FROM tblMessage M WHERE messageFrom=@userID AND deletedByRecipient = 0 AND deletedBySender = 0 ) as totalOutbox,
(SELECT count(*) FROM tblMessage M WHERE messageFrom=@userID or messageTo =@userID AND deletedByRecipient = 0 and deletedBySender = 0 ) as totalTrash

FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND deletedByRecipient = 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-30 : 19:09:57

[code]SELECT COUNT(*) AS totalInbox,
SUM(CASE WHEN m.checked = 0 THEN 1 ELSE 0 END) as totalInbox_UnRead,
SUM(CASE WHEN m.checked = 2 THEN 1 ELSE 0 END) as totalInbox_UnReplied
FROM tblMessage AS m
INNER JOIN tblUserDetails AS ud ON ud.userID = m.messageFrom
WHERE m.messageTo = @userID
AND m.deletedByRecipient = 0

SELECT SUM(CASE WHEN messageFrom = @userID THEN 1 ELSE 0 END) as totalOutbox,
COUNT(*) as totalTrash
FROM tblMessage
WHERE @userID IN (messageFrom, messageTo)
AND deletedByRecipient = 0
AND deletedBySender = 0[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-30 : 19:19:03

What's the purpose of table "tblUserDetails" here?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-30 : 19:24:07

quote:
Originally posted by mike123

...
,(SELECT count(*) FROM tblMessage M WHERE messageFrom=@userID or messageTo =@userID AND deletedByRecipient = 0 and deletedBySender = 0 ) as totalTrash

Ooops... Beware of operator presedence!

What you have written can be translated into

WHERE (messageFrom=@userID) or (messageTo =@userID AND deletedByRecipient = 0 and deletedBySender = 0)

Look at my previous reply.

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-31 : 04:32:15

Hi Peso,

Thanks very much for picking up on that !!

Your are indeed correct.

I was expecting it to run alot faster than it currently is. (Just running in QA it takes the same amount of time to run, which I know is not a very good way of testing) In my head it I was thinking since its half the select statements, but the selects are a bit more demanding, we should see maybe 30%-50% improvement ?? Not sure if this is logical way of thinking

Will this query look at indexes differently? Will I have to change anything on the index end ?

Thanks once again,
Mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-31 : 04:51:22

If tblUserDetails is not really used, you can concatenate all five aggregations into one query.

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-31 : 04:52:16

Hey Peso,

Other thing I just noticed when integrating is that it returns 2 different resultsets. My application would like them in 1, so I have used this technique I picked up from you before. I've succesfully implemented it in other queries, but because of the way this one is structured, I can't get the syntax right. Any help is much appreciated. I've declared all the variables below.

Thanks very much once again!!
mike123

DECLARE @totalInbox INT, @totalInbox_UnRead INT, @totalInbox_UnReplied INT, @totalOutbox INT, @totalTrash INT

SELECT @totalInbox = COUNT(*) AS totalInbox,
SUM(CASE WHEN m.checked = 0 THEN 1 ELSE 0 END) as @totalInbox_UnRead,
SUM(CASE WHEN m.checked = 2 THEN 1 ELSE 0 END) as totalInbox_UnReplied
FROM tblMessage AS m
JOIN tblUserDetails AS ud ON ud.userID = m.messageFrom
WHERE m.messageTo = @userID
AND m.deletedByRecipient = 0

SELECT SUM(CASE WHEN messageFrom = @userID THEN 1 ELSE 0 END) as totalOutbox,
COUNT(*) as totalTrash
FROM tblMessage
WHERE @userID IN (messageFrom, messageTo)
AND deletedByRecipient = 0
AND deletedBySender = 0

SELECT @totalInbox as totalInbox, @totalInbox_UnRead as totalInbox_UnRead, @totalInbox_UnReplied as totalInbox_UnReplied, @totalOutbox as totalOutbox, @totalTrash as totalTrash

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-31 : 04:54:39

quote:
Originally posted by Peso

If tblUserDetails is not really used, you can concatenate all five aggregations into one query.

I forgot to mention, I use the tbluserdetails, because there is some orphaned data in the column and when I use a join, it ends up not counting the orphaned data.

It's orphaned by design as deleting the records is a nightly event, as it was straining the server too much when it deletes them automatically.

Hopefully this doesnt sound too crazy of a design ?

thanks again :)
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-31 : 04:58:33

If tblUserDetails is not needed try this

SELECT		@totalInbox = SUM(CASE WHEN messageTo = @userID THEN 1 ELSE 0 END) AS totalInbox,
		@totalInbox_UnRead = SUM(CASE WHEN messageTo = @userID AND checked = 0 THEN 1 ELSE 0 END) as totalInbox_UnRead,
		@totalInbox_UnReplied = SUM(CASE WHEN messageTo = @userID AND checked = 2 THEN 1 ELSE 0 END) as totalInbox_UnReplied
		@totalOutbox = SUM(CASE WHEN messageFrom = @userID AND deletedBySender = 0 THEN 1 ELSE 0 END) as totalOutbox,
		@totalTrash = SUM(CASE WHEN messageFrom = @userID AND deletedBySender = 0 THEN 1 ELSE 0 END) as totalTrash
FROM		tblMessage
WHERE		@userID IN (messageFrom, messageTo)
		AND deletedByRecipient = 0

SELECT	@totalInbox as totalInbox,
	@totalInbox_UnRead as totalInbox_UnRead,
	@totalInbox_UnReplied as totalInbox_UnReplied,
	@totalOutbox as totalOutbox,
	@totalTrash as totalTrash

You can do exactly the same thing with the two separate queries

SELECT		@totalInbox = COUNT(*),
		@totalInbox_UnRead  = SUM(CASE WHEN m.checked = 0 THEN 1 ELSE 0 END),
		@totalInbox_UnReplied = SUM(CASE WHEN m.checked = 2 THEN 1 ELSE 0 END)
FROM		tblMessage AS m
INNER JOIN	tblUserDetails AS ud ON ud.userID = m.messageFrom
WHERE		m.messageTo = @userID
		AND m.deletedByRecipient = 0

SELECT	@totalOutbox = SUM(CASE WHEN messageFrom = @userID THEN 1 ELSE 0 END),
	@totalTrash = COUNT(*)
FROM	tblMessage
WHERE	@userID IN (messageFrom, messageTo)
	AND deletedByRecipient = 0
	AND deletedBySender = 0

SELECT	@totalInbox as totalInbox,
	@totalInbox_UnRead as totalInbox_UnRead,
	@totalInbox_UnReplied as totalInbox_UnReplied,
	@totalOutbox as totalOutbox,
	@totalTrash as totalTrash

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-31 : 05:00:11

Is tblUserDetails unique over column "userID"?

Try this

SELECT		@totalInbox = SUM(CASE WHEN m.messageTo = @userID AND ud.userID IS NOT NULL THEN 1 ELSE 0 END) AS totalInbox,
		@totalInbox_UnRead = SUM(CASE WHEN m.messageTo = @userID AND ud.userID IS NOT NULL AND m.checked = 0 THEN 1 ELSE 0 END) as totalInbox_UnRead,
		@totalInbox_UnReplied = SUM(CASE WHEN m.messageTo = @userID AND m.checked = 2 THEN 1 ELSE 0 END) as totalInbox_UnReplied
		@totalOutbox = SUM(CASE WHEN m.messageFrom = @userID AND m.deletedBySender = 0 THEN 1 ELSE 0 END) as totalOutbox,
		@totalTrash = SUM(CASE WHEN m.messageFrom = @userID AND m.deletedBySender = 0 THEN 1 ELSE 0 END) as totalTrash
FROM		tblMessage AS m
LEFT JOIN	tblUserDetails AS ud ON ud.userID = m.messageFrom
WHERE		@userID IN (m.messageFrom, m.messageTo)
		AND m.deletedByRecipient = 0

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-31 : 07:24:15

quote:

Originally posted by Peso

Is tblUserDetails unique over column "userID"?

Try this

SELECT		@totalInbox = SUM(CASE WHEN m.messageTo = @userID AND ud.userID IS NOT NULL THEN 1 ELSE 0 END) AS totalInbox,
		@totalInbox_UnRead = SUM(CASE WHEN m.messageTo = @userID AND ud.userID IS NOT NULL AND m.checked = 0 THEN 1 ELSE 0 END) as totalInbox_UnRead,
		@totalInbox_UnReplied = SUM(CASE WHEN m.messageTo = @userID AND m.checked = 2 THEN 1 ELSE 0 END) as totalInbox_UnReplied
		@totalOutbox = SUM(CASE WHEN m.messageFrom = @userID AND m.deletedBySender = 0 THEN 1 ELSE 0 END) as totalOutbox,
		@totalTrash = SUM(CASE WHEN m.messageFrom = @userID AND m.deletedBySender = 0 THEN 1 ELSE 0 END) as totalTrash
FROM		tblMessage AS m
LEFT JOIN	tblUserDetails AS ud ON ud.userID = m.messageFrom
WHERE		@userID IN (m.messageFrom, m.messageTo)
		AND m.deletedByRecipient = 0

E 12°55'05.25"
N 56°04'39.16"

Hi Peso,

You are correct, tblUserDetails.userID is an Identity column.

I just tried your query, but its returning

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'AS'.

I noticed a missing comma here "as totalInbox_UnReplied,"

and added it in, but no luck. I can't see anything else with your query ?

Again thanks very much !!

mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-31 : 11:49:45

[code]SELECT @totalInbox = SUM(CASE WHEN m.messageTo = @userID AND ud.userID IS NOT NULL THEN 1 ELSE 0 END) AS totalInbox,
@totalInbox_UnRead = SUM(CASE WHEN m.messageTo = @userID AND ud.userID IS NOT NULL AND m.checked = 0 THEN 1 ELSE 0 END),
@totalInbox_UnReplied = SUM(CASE WHEN m.messageTo = @userID AND m.checked = 2 THEN 1 ELSE 0 END) as totalInbox_UnReplied,
@totalOutbox = SUM(CASE WHEN m.messageFrom = @userID AND m.deletedBySender = 0 THEN 1 ELSE 0 END) as totalOutbox,
@totalTrash = SUM(CASE WHEN m.messageFrom = @userID AND m.deletedBySender = 0 THEN 1 ELSE 0 END) as totalTrash
FROM tblMessage AS m
LEFT JOIN tblUserDetails AS ud ON ud.userID = m.messageFrom
WHERE @userID IN (m.messageFrom, m.messageTo)
AND m.deletedByRecipient = 0[/code]

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-04-01 : 12:08:46

quote:

Originally posted by Peso

SELECT		@totalInbox = SUM(CASE WHEN m.messageTo = @userID AND ud.userID IS NOT NULL THEN 1 ELSE 0 END) AS totalInbox,
		@totalInbox_UnRead = SUM(CASE WHEN m.messageTo = @userID AND ud.userID IS NOT NULL AND m.checked = 0 THEN 1 ELSE 0 END),
		@totalInbox_UnReplied = SUM(CASE WHEN m.messageTo = @userID AND m.checked = 2 THEN 1 ELSE 0 END) as totalInbox_UnReplied,
		@totalOutbox = SUM(CASE WHEN m.messageFrom = @userID AND m.deletedBySender = 0 THEN 1 ELSE 0 END) as totalOutbox,
		@totalTrash = SUM(CASE WHEN m.messageFrom = @userID AND m.deletedBySender = 0 THEN 1 ELSE 0 END) as totalTrash
FROM		tblMessage AS m
LEFT JOIN	tblUserDetails AS ud ON ud.userID = m.messageFrom
WHERE		@userID IN (m.messageFrom, m.messageTo)
		AND m.deletedByRecipient = 0

E 12°55'05.25"
N 56°04'39.16"

i keep looking over the code and cant figure out why, I am still getting this error ??

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.

any help much appreciated
thx!!,
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-01 : 12:14:54

quote:

Originally posted by mike123

quote:

Originally posted by Peso

SELECT		@totalInbox = SUM(CASE WHEN m.messageTo = @userID AND ud.userID IS NOT NULL THEN 1 ELSE 0 END) AS totalInbox,
		@totalInbox_UnRead = SUM(CASE WHEN m.messageTo = @userID AND ud.userID IS NOT NULL AND m.checked = 0 THEN 1 ELSE 0 END),
		@totalInbox_UnReplied = SUM(CASE WHEN m.messageTo = @userID AND m.checked = 2 THEN 1 ELSE 0 END) as totalInbox_UnReplied,
		@totalOutbox = SUM(CASE WHEN m.messageFrom = @userID AND m.deletedBySender = 0 THEN 1 ELSE 0 END) as totalOutbox,
		@totalTrash = SUM(CASE WHEN m.messageFrom = @userID AND m.deletedBySender = 0 THEN 1 ELSE 0 END) as totalTrash
FROM		tblMessage AS m
LEFT JOIN	tblUserDetails AS ud ON ud.userID = m.messageFrom
WHERE		@userID IN (m.messageFrom, m.messageTo)
		AND m.deletedByRecipient = 0

E 12°55'05.25"
N 56°04'39.16"

You dont need these aliases here

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources