user5740086 user5740086 - 6 months ago 13
SQL Question

How can I increase speed of SQL query?

As a Image that I sent , image

For getting count of comment can I add one filed in table of Module or not , my mean is for big record like 100 million comments or big project, which one is better/faster adding one filed to module and after each inserting comment update it or have a relationship

For getting count of comment , I must to choose which one :

select Module.Id,
(SELECT COUNT(*) AS Expr1
FROM dbo.CommentTable
WHERE (CommentTable.MuoduleId= Module.userid)) AS commentCount
from Model


or

select Module.Id, Module.CountComment

Answer

I suggest you compute it on the fly instead of saving the count in the table itself. To get the count of comments of each Module:

SELECT
    m.id,
    CommentCount = COUNT(c.ModuleId)
FROM Module m
LEFT JOIN CommentTable c
    ON c.ModuleId = m.Id
GROUP BY m.id

This will be faster if you have an index on CommentTable(ModuleId):

CREATE NONCLUSTERED INDEX NCI_CommentTable_ModuleId ON CommentTable(ModuleId)