October 13, 2014 at 10:13 pm
Anyone who know to advise of MSSQL 2008 Standard version With & Without Nested Loops (inner join) for Select statement which is the more performance?
Declare @id int
Declare @i int
Declare @U int
SELECT @i = count(id)
FROM dbo.
c WITH(NOLOCK) WHERE c.rid = @id AND ISNULL(c.flag,0) = 0
SELECT @U = count(id) FROM dbo.
m WITH(NOLOCK)
WHERE m.user_receive_id = @id AND ISNULL(m.delete_flag,0) = 0
select @inbox_count,@unread_count
=================================
SELECT i = count(id)
u = (SELECT @U = count(id) FROM dbo.
m WITH(NOLOCK)
WHERE m.rid = @id AND ISNULL(m.delete_flag,0) = 0 )
FROM dbo.
c WITH(NOLOCK) WHERE c.rid = @id AND ISNULL(c.flag,0) = 0
October 13, 2014 at 11:28 pm
kyiaun (10/13/2014)
Anyone who know to advise of MSSQL 2008 Standard version With & Without Nested Loops (inner join) for Select statement which is the more performance?Declare @id int
Declare @i int
Declare @U int
SELECT @i = count(id)
FROM dbo.
c WITH(NOLOCK) WHERE c.rid = @id AND ISNULL(c.flag,0) = 0
SELECT @U = count(id) FROM dbo.
m WITH(NOLOCK)
WHERE m.user_receive_id = @id AND ISNULL(m.delete_flag,0) = 0
select @inbox_count,@unread_count
=================================
SELECT i = count(id)
u = (SELECT @U = count(id) FROM dbo.
m WITH(NOLOCK)
WHERE m.rid = @id AND ISNULL(m.delete_flag,0) = 0 )
FROM dbo.
c WITH(NOLOCK) WHERE c.rid = @id AND ISNULL(c.flag,0) = 0
Quick thoughts, the isnull function in the where clause will prevent any index usage and result in a scan, change this to an or condition. Secondly why the nolock hint? It is not doing anything useful!
😎
October 13, 2014 at 11:37 pm
Step 1: If you need to have the exact number for count(id) for both queries, remove the NOLOCK hint. Search for "NOLOCK dirty reads" for details. Otherwise it's just an approximate number.
Step 2: Remove the function call against a column. The preferred method would be to change the table definition to make c.flag a non-NULL column and add 0 as a default value. (it seems like you don't differentiate between NULL and Zero anyway...). Alternatively, use c.flag = 0 OR c.flag IS NULL.
Step 3: Make sure you have covering indexes on the table (do you refer to a single table or two different ones?). With two dedicated indexes, each one just having two INT values, the queries should be fast enough without the NOLOCK hint... (Btw: Why is c.flag defined as INT instead of TINYINT?)
Finally, use two separate queries so each one can benefit from a separate covering index similar to your first version.
All the above assumes the queries aren't part of any kind of a loop (c.u.r.s.o.r. *cough* or WHILE loop). But if a loop is used, the major problem most probably isn't the query you posted...
October 15, 2014 at 11:11 am
I agree with others, particularly on ISNULL(). In fact, there's a simple, very easy to remember rule:
NEVER use ISNULL() in a WHERE or JOIN clause.
If, as is typical, the "soft delete" flags are just thrown in there out of habit, or "just in case", get rid of "soft delete" flags! Such flags really are a throw-back to tape master files, when every record had to be read anyway. [Yikes, did I just channel CELKO?! :shudder:]
For the table with the "rid" column, it's likely that column should be the clustering key. If so, after you do that, your code will run reasonably well no matter what the rest of your WHERE clause looks like.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply