Performance of query execution

Hello friends,

Suppose I have 5000 records and I want to perform an operation on it.

I want to use 2 columns in sub-query for all above records.

Example:

Way-1:

DECLARE @TempSizeID INT;
SET @NewSizeID = ISNULL((SELECT ISNULL(id,0) FROM tb_sizes WHERE StoreID=@NewStoreID),0);
IF(@NewSizeID = 0)
BEGIN
-- sub-query for insert data
SET @NewSizeID = SCOPE_IDENTITY()
END
ELSE
BEGIN
IF NOT EXISTS (SELECT ISNULL(SizeID,0) FROM tb_ProductColorSizeMapping WHERE ProductID = @NewProductID AND SizeID = @NewSizeID)
BEGIN
-- sub-query for insert mapping data
END
END

Way-2:

DECLARE @TempSizeID INT;
IF(@ISNULL((SELECT ISNULL(id,0) FROM tb_sizes WHERE StoreID=@NewStoreID),0) = 0)
BEGIN
-- sub-query for insert data
SET @NewSizeID = SCOPE_IDENTITY()
END
ELSE
BEGIN
IF NOT EXISTS (SELECT ISNULL(SizeID,0) FROM tb_ProductColorSizeMapping WHERE ProductID = @NewProductID AND SizeID =
(ISNULL((SELECT ISNULL(id,0) FROM tb_sizes WHERE StoreID=@NewStoreID),0)))
BEGIN
-- sub-query for insert mapping data
END
END

In way-1 it number of queries is less but it takes storage to store id
In way-2 it number of queries is more but and not store id
Can anybody suggest me which is fast?.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Dharmraj Thakur8y ago
Accepted answer
Hello Jadav,

Off Course, Way-1 is better than another...

In your case... storing the id is faster than executing the query again.
Bhavesh Jadav8y ago
Thanks to both of you for giving your suggestion.
Josephs8y ago
Hi,

When you use Order or subquery performance slow.

so you need avoid then select your columns only