Efficient Way of Writing this Query

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)

Efficient Way of Writing this Query

Author

Topic

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-03-31 : 16:28:35

I am using a Table Many Times in Left Outer Joins and Inner Joins for various Conditions,
is there anyway of writing a query using minimal Table usage, instead of Recurring all the time.

**********************************
SELECT
blog.blogid,
BM.TITLE,
U.USER_FIRSTNAME+ ' ' + U.USER_LASTNAME AS AUTHORNAME,
Blog_Entries = (cASE WHEN Blog_Entries is NULL or Blog_Entries = ' ' then 0 else Blog_Entries END),
Blog_NewEntries = (cASE WHEN Blog_NewEntries is NULL or Blog_NewEntries = ' ' then 0 else Blog_NewEntries END),
Blog_comments = (cASE WHEN Blog_comments is NULL or Blog_comments = ' ' then 0 else Blog_comments END),
dbo.DateFloor(VCOM.objCreationDate) AS CreationDate,
dbo.DateFloor(BLE.entryDate) AS Date_LastEntry
FROM vportal4VSEARCHCOMM.dbo.blog_metaData BM
INNER JOIN vportal4VSEARCHCOMM.dbo.blog BLOG
ON BM.BLOGID = BLOG.BLOGID
INNER JOIN vportal4VSEARCH.dbo.[USER] U
ON U.USER_ID = BLOG.OWNERID
INNER JOIN vportal4VSEARCHCOMM.dbo.vComm_obj VCOM
ON BLOG.vCommObjID = VCOM.vCommObjId
INNER JOIN vportal4VSEARCHCOMM.dbo.blog_entry BLE
ON BLOG.BLOGID = BLE.BLOGID
LEFT OUTER JOIN
( SELECT BlogID, Blog_Entries = COUNT(*)
FROM vportal4VSEARCHCOMM.dbo.Blog_Entry
GROUP BY BlogID )B on B.BLOGID = BM.BLOGID
LEFT OUTER JOIN
(
SELECT BlogID, Blog_NewEntries = COUNT(*)
FROM vportal4VSEARCHCOMM.dbo.Blog_Entry
WHERE ENTRYDATE > '01/01/2008'
GROUP BY BlogID )C on C.BLOGID = BM.BLOGID
LEFT OUTER JOIN
(
SELECT BEN.BLOGID, Blog_comments = COUNT(*)
FROM vportal4VSEARCHCOMM.dbo.blog_comment BC
INNER JOIN vportal4VSEARCHCOMM.dbo.blog_entry BEN
ON BEN.blog_entryId = BC.blogEntryId
GROUP BY BEN.BLOGID )D on D.BLOGID = BM.BLOGID
WHERE VCOM.objName like '%blog%'

thanks

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-31 : 18:51:16

Not sure what you mean, but I think a view might be what you are after.

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-03-31 : 18:57:06

i guess my question was not clear,

am using too many LEFT OUTER JOINs with the same table having diferent criteria,

like the one below

LEFT OUTER JOIN
( SELECT BlogID, Blog_Entries = COUNT(*)
FROM vportal4VSEARCHCOMM.dbo.Blog_Entry
GROUP BY BlogID )B on B.BLOGID = BM.BLOGID
LEFT OUTER JOIN
(
SELECT BlogID, Blog_NewEntries = COUNT(*)
FROM vportal4VSEARCHCOMM.dbo.Blog_Entry
WHERE ENTRYDATE > '01/01/2008'
GROUP BY BlogID )C on C.BLOGID = BM.BLOGID
LEFT OUTER JOIN
(
SELECT BEN.BLOGID, Blog_comments = COUNT(*)
FROM vportal4VSEARCHCOMM.dbo.blog_comment BC
INNER JOIN vportal4VSEARCHCOMM.dbo.blog_entry BEN
ON BEN.blog_entryId = BC.blogEntryId
GROUP BY BEN.BLOGID )D on D.BLOGID = BM.BLOGID
WHERE VCOM.objName like '%blog%'

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-31 : 19:03:55

If thats what you need then that's what you need. You can't change your requirements because you have to write SQL you find distasteful!
If you can point out the bits you are worried about I might be able to help a bit although except for the '%blog%' bit and your unnecessary case statements, it looks ok to me.

cgarcia
Starting Member

4 Posts

Posted - 2008-04-01 : 04:40:05

Temporary tables can be an alternative but I only recomend you when you have performance problems. Complex queries with multiple joined views or sub-queries, usually can cause performace degradation. If this is the case, considere the following steps:

1. Be sure that you have the appropiate indexes created for the query. Considere using the Tunning Advisor tool. It can really helpfull.

2. If the joined views or sub-quieries are going to return many rows, considere using indexed views and temporary tables.

3. For VLDB, considere using partitioning and filegroups.

3. Review and adapt your data model to fit your queries. Considere de-normalizing tables.

4. Talk to product people to change requirements.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-01 : 05:09:52

[code]SELECT q.BlogID,
q.Title,
q.AuthorName,
COUNT(*) AS Blog_Entries
SUM(q.Blog_NewEntries) AS Blog_NewEntries,
SUM(q.Blog_Comments) AS Blog_Comments,
MAX(DATEADD(DAY, '19000101', q.CreationDate)) AS CreationDate
MAX(DATEADD(DAY, '19000101', q.Date_LastEntry)) AS Date_LastEntry
FROM (
SELECT blog.BlogID,
bm.Title,
u.User_FirstName + ' ' + u.User_LastName AS AuthorName,
CASE
WHEN ble.EntryDate >= '20080101' THEN 1
ELSE 0
END AS Blog_NewEntries,
COALESCE(d.Blog_Comments, 0) AS Blog_Comments,
DATEDIFF(DAY, '19000101', vcom.objCreationDate) AS CreationDate,
DATEDIFF(DAY, '19000101', ble.EntryDate) AS Date_LastEntry
FROM vportal4VSEARCHCOMM.dbo.blog_metaData AS bm
INNER JOIN vportal4VSEARCHCOMM.dbo.blog AS blog ON blog.BlogID = bm.BlogID
INNER JOIN vportal4VSEARCH.dbo.[USER] AS u ON u.[User_ID] = blog.OwnerID
INNER JOIN (
SELECT vCommObjID,
objCreationDate
FROM vportal4VSEARCHCOMM.dbo.vComm_obj
WHERE objName like '%blog%'
) AS vcom ON vcom.vCommObjID = blog.vCommObjID
INNER JOIN vportal4VSEARCHCOMM.dbo.blog_entry AS ble ON ble.BlogID = blog.BlogID
LEFT JOIN (
SELECT BlogEntryID,
COUNT(*) AS Blog_Comments
FROM vportal4VSEARCHCOMM.dbo.Blog_Comment
GROUP BY BlogEntryID
) AS d on d.BlogEntryID = bm.BlogID
) AS q
GROUP BY q.BlogID,
q.Title,
q.AuthorName[/code]

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