Need help in optmizing SQL Server queries

Question

I need help with optimizing some of my SQL queries. I'm not good in SQL performance. I have a SQL Server 2008 RS Express and I can't use DTA.

May be can help me with optimising and manually creating indexes for these two queries:

SELECT tblBlogs.RecordID, tblBlogs.RecordText, tblBlogs.CDate, tblBlogs.UserID, tblBlogs.Comments, tblUsers.Username, tblUserpics.UserpicName 
FROM  ( 
      SELECT tblBlogs_2.RecordID, tblBlogs_2.RecordText, tblBlogs_2.CDate, tblBlogs_2.UserID, COUNT(dbo.tblBlogComments.CommentID) AS Comments 
      FROM ( 
      SELECT TOP (150) RecordID, RecordText, CDate, UserID 
           FROM dbo.tblBlogs AS tblBlogs_1 
           ORDER BY RecordID DESC 
           ) AS tblBlogs_2 
      LEFT OUTER JOIN dbo.tblBlogComments ON tblBlogs_2.RecordID = tblBlogComments.RecordID
      GROUP BY tblBlogs_2.RecordID, tblBlogs_2.RecordText, tblBlogs_2.CDate, tblBlogs_2.UserID 
     ) AS tblBlogs  
INNER JOIN dbo.tblUsers ON tblBlogs.UserID = tblUsers.UserID  
LEFT OUTER JOIN dbo.tblUserpics ON tblBlogs.UserID = tblUserpics.UserID
ORDER BY tblBlogs.CDate DESC

This must select top 150 ros from Blogs table with User details + Comments for every single Blog entry.

SELECT f.ForumID, f.ForumName, t.ThreadName, m.MsgID, m.MsgName, m.MsgBody, m.UserID,   m.CDate, m.IP, u.Username, tblCities.CityName, 
t.IsClosed, ISNULL(u.Msgs, 0) AS Posts, ISNULL(tblUserpics.UserpicName, '') AS UserpicName, t.IsPoll,  
t.IsPollMultiple, ISNULL(u.Crashes, 0) AS Crashes, 0 AS LastMsgID, m.IsFlood, ISNULL(u.RepaGood, 0) AS RepaGood, ISNULL(u.RepaBad, 0)  
AS RepaBad, ISNULL(dbo.vMsgsRepaGood.RepaGood, 0) AS MsgRepaGood, ISNULL(dbo.vMsgsRepaBad.RepaBad, 0) AS MsgRepaBad, t.ThreadID, 
tblUserPrivateStatuses.StatusName AS PrivateStatus 
FROM tblMsgs AS m  
INNER JOIN tblThreads AS t ON m.ThreadID = t.ThreadID 
INNER JOIN tblForums AS f ON t.ForumID = f.ForumID  
INNER JOIN tblUsers AS u ON m.UserID = u.UserID  
LEFT OUTER JOIN tblUserPrivateStatuses ON u.UserID = dbo.tblUserPrivateStatuses.UserID  
LEFT OUTER JOIN tblCities ON u.CityID = dbo.tblCities.CityID  
LEFT OUTER JOIN tblUserpics ON u.UserID = dbo.tblUserpics.UserID  
LEFT OUTER JOIN vMsgsRepaGood ON m.MsgID = vMsgsRepaGood.MsgID  
LEFT OUTER JOIN vMsgsRepaBad ON m.MsgID = vMsgsRepaBad.MsgID  
WHERE m.ThreadID = "& ThreadID & " AND IsFlood = 0 
GROUP BY f.ForumID, f.ForumName, t.ThreadName, m.MsgID, m.MsgName, m.MsgBody, m.UserID, m.CDate, m.IP, u.Username, tblCities.CityName, t.IsClosed, u.Msgs, dbo.tblUserpics.UserpicName, t.IsPoll, t.IsPollMultiple, u.Crashes, m.IsFlood, u.RepaGood, u.RepaBad, vMsgsRepaGood.RepaGood, vMsgsRepaBad.RepaBad, t.ThreadID, tblUserPrivateStatuses.StatusName 
ORDER BY m.CDate</pre>

This query selects all not flood messages from specific Thread from specific Forum with User details (Registration date, number of good/bad reputation, number of crashes, number of post on this whole forum, city, userpic).

Or maybe somebody can tell me about free tools for optimizing queries and creating indexes?

score 1 · Accepted Answer · edited May 23 '17 at 10:32

1

There is a lot to talk about here, and without significantly more information, it's going to be impossible for anyone to help with your query fully.

Precaution: If you have a DBA for your system, check with them before indexing anything, especially on a live system. They can even help, if you're nice to them. If the system is used by many others, be careful before changing anything like indexes.

A basic tip on indexing, if you don't want to dive deep into the problem, is: index by the following, in this order:

Join predicates
Filter
Order by / Group By / etc.

Also:

Make sure whatever columns possible are non-null.
Use data types that make sense - store nothing as varchar if it's an integer or date. (Column width matters. Use the smallest data type you can, if possible.)
Make sure your joins are the same data type - int to int, varchar to varchar, and so on.
If possible, use unique, non-null indexes on each join predicate in each tables.

Do all of this, and you'll be well on your way. But if you need this stuff regularly, learn it! There is a lot out there, and it is a deep topic, but you can make queries MUCH better if you know what you are doing.

Edit: The syntax for building indexes is here: How do I index a database column. The How/Why is here: How does database indexing work?

edited May 23 '17 at 10:32

Community

1
1

answered Jun 15 '12 at 17:21

David Manheim

2,553
2
27
42

I don't have DBA. Can you please explain how I can index by something? The only index I know it's by column in table. – udar_molota Jun 15 '12 at 17:47
Exactly - on each table, index by the column or columns that you need. – David Manheim Jun 15 '12 at 17:50
look up Create Index in Books online. – HLGEM Jun 15 '12 at 17:51
I know how create index by column, but how can I index by join/filer/order by? – udar_molota Jun 15 '12 at 18:00
@udar_molota: You need to index the tables, using columns. You pick which columns to use to index the tables based on what the query does - For an index with multiple columns, You want to pick the columns you join on first, then the columns you filter by, and lastly those you group by. – David Manheim Jun 15 '12 at 18:03
Here is the link to Books Online about Syntax: http://msdn.microsoft.com/en-us/library/ms188783.aspx – David Manheim Jun 15 '12 at 18:07

Need help in optmizing SQL Server queries

1 Answers1