help with query (select TOP DISTINCT)

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 with query (select TOP DISTINCT)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-08-28 : 12:03:33

Hi,

I'm attempting to limit my query, based on DISTINCT UD.userID

This query is a little tricky for me as I am not sure whether its best to select from tblLogins_Log or tblUserdetails as the base table.

Any help is much appreciated ! table structures are below.

Please let me know if I can post anything else to help.

Thanks again,
mike123

select top 30 LL.userID, UD.nameOnline, LL.IP, loginDate, countryCode, UD.date as joinDate,

(select count(messageFromID) FROM tblInstantMessage WHERE messageFromID = LL.userID) as OutBoundMessages,
(select count(commentFromID) FROM tblComment WHERE commentFromID = LL.userID) as OutBoundComments,
(select count(messageFrom) FROM tblMessage WHERE messageFrom = LL.userID) as OutBoundMail,
(select count(commentFromID) FROM tblPhotoComments WHERE commentFromID = LL.userID) as OutBoundPhotoComments

FROM tblLogins_Log LL

JOIN tblUserDetails UD on UD.userID = LL.userID

where countryCode in (select countryCode from tblBannedCountries)

ORDER BY loginDate DESC

table structures:

CREATE TABLE [dbo].[tblLogins_Log](
[loginID] [int] IDENTITY(1,1) NOT NULL,
[userID] [int] NULL,
[IP] [varchar](15) NOT NULL,
[loginDate] [datetime] NOT NULL,
[countryCode] [varchar](3) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tblBannedCountries](
[countryCode] [varchar](5) NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tblUserDetails](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[NameOnline] [varchar](15) NULL,
[joinDate] [datetime] NULL,
[LastLoggedIn] [datetime] NULL
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 12:14:28

It is almost always better to have the tables in the JOIN order with the table having smallest number of records first.

SELECT TOP 30	ud.userID,
		ud.nameOnline,
		ll.IP,
		ll.loginDate,
		ll.countryCode,
		ud.joinDate,
		(select count(a.messageFromID) FROM tblInstantMessage as a WHERE a.messageFromID = LL.userID) as OutBoundMessages,
		(select count(b.commentFromID) FROM tblComment as b WHERE b.commentFromID = LL.userID) as OutBoundComments,
		(select count(c.messageFrom) FROM tblMessage as c WHERE c.messageFrom = LL.userID) as OutBoundMail,
		(select count(d.commentFromID) FROM tblPhotoComments as d WHERE d.commentFromID = LL.userID) as OutBoundPhotoComments 
FROM		tblUserDetails AS ud
INNER JOIN	tblLogins_Log AS ll ON ll.userID = ud.userID
			AND ll.loginDate = ud.LastLoggedIn
INNER JOIN	tblBannedCountries AS bc ON bc.countryCode = ll.countryCode
ORDER BY	ud.LastLoggedIn DESC

SELECT TOP 30	ud.userID,
		ud.nameOnline,
		ll.IP,
		ll.loginDate,
		ll.countryCode,
		ud.joinDate,
		(select count(a.messageFromID) FROM tblInstantMessage as a WHERE a.messageFromID = LL.userID) as OutBoundMessages,
		(select count(b.commentFromID) FROM tblComment as b WHERE b.commentFromID = LL.userID) as OutBoundComments,
		(select count(c.messageFrom) FROM tblMessage as c WHERE c.messageFrom = LL.userID) as OutBoundMail,
		(select count(d.commentFromID) FROM tblPhotoComments as d WHERE d.commentFromID = LL.userID) as OutBoundPhotoComments 
FROM		tblBannedCountries AS bc
INNER JOIN	tblLogins_Log AS ll ON ll.countryCode = bc.countryCode
INNER JOIN	tblUserDetails AS ud ON ud.userID = ll.userID
			AND ud.LastLoggedIn = ll.loginDate
ORDER BY	ud.LastLoggedIn DESC

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

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-08-28 : 12:40:54

Hey Peso!

These queries both work and bring back the data exactly as I had hoped for :)

tblBannedCountries is my smallest table (Thank god!)

SO I will take your advice and use the 2nd of the 2 queries you have wrote. Perhaps this will be better for locking since we are selecting from the smallest tables first ?

Thanks once again for a great answer and a very helpful solution :)

Regards,
mike123

acollins74
Yak Posting Veteran

82 Posts

Posted - 2008-08-28 : 13:12:40

quote:
It is almost always better to have the tables in the JOIN order with the table having smallest number of records first.

.

Thats a Bold Statement Peso. Without SET FORCEPLAN, I don't believe it matters what order tables are presented.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 14:50:06

My experience is that it can have a matter. Not always, but sometimes.
I usually write queries this way, because when the execution plan go bad,
it is VERY easy to add an

OPTION (FORCE ORDER)

to the end of the query and compare the results.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 14:51:46

Mike123, try both queries and check the results for READS in SQL Profiler.
Also try both queries with the query hint I wrote about in previous post and compare results.
If you have time, please post back all four results.

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

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-08-28 : 16:53:55

Hey Peso,

Sure thing. Exactly which way is the best way to do this.

Should I run a trace on my live server? Or can I run from query analyzer somehow ?

Thanks again,
mike

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 16:56:47

Yes, you can run the queries from SSMS.
Let SQL Profiler run and run the two queries as is.
Add the query hint OPTION (FORCE ORDER) and run the two queries again.
Stop SQL Profiler and look at the results and post them here.
If needed, you can filter the SQL profiler results to either your account or SSMS application.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 07:21:07

quote:
Originally posted by acollins74

Thats a Bold Statement Peso. Without SET FORCEPLAN, I don't believe it matters what order tables are presented.

Also see
http://www.sqlservercentral.com/Forums/Topic552923-65-1.aspx

quote:
sql_er (8/27/2008)

The change was basically an INNER JOIN order. I had 3 tables in a query, and there were 2 ways to JOIN between them. When the query was originally written , I did not pay attention to it. I just chose one way randomly. However, as I found out now, joining another would would allow to use an INDEX on one of the biggest tables of the 3, thereby totally changing the execution plan, bringing down the sub-tree cost from 3.5 to 0.1 and the logical reads from 110,000 to less than 10,000.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources