3
Answers

Performance of query execution

Bhavesh Jadav

Bhavesh Jadav
6y
743
1
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:
  1. DECLARE @TempSizeID INT;  
  2. SET @NewSizeID = ISNULL((SELECT ISNULL(id,0) FROM tb_sizes WHERE StoreID=@NewStoreID),0);  
  3.   
  4. IF(@NewSizeID = 0)  
  5. BEGIN  
  6.     -- sub-query for insert data  
  7.     SET @NewSizeID = SCOPE_IDENTITY()     
  8. END  
  9. ELSE  
  10. BEGIN  
  11.     IF NOT EXISTS (SELECT ISNULL(SizeID,0) FROM tb_ProductColorSizeMapping WHERE ProductID = @NewProductID AND SizeID = @NewSizeID)           
  12.     BEGIN  
  13.         -- sub-query for insert mapping data   
  14.     END  
  15. END 


Way-2:
  1. DECLARE @TempSizeID INT;  
  2.   
  3. IF(@ISNULL((SELECT ISNULL(id,0) FROM tb_sizes WHERE StoreID=@NewStoreID),0) = 0)  
  4. BEGIN  
  5.     -- sub-query for insert data  
  6.     SET @NewSizeID = SCOPE_IDENTITY()     
  7. END  
  8. ELSE  
  9. BEGIN  
  10.     IF NOT EXISTS (SELECT ISNULL(SizeID,0) FROM tb_ProductColorSizeMapping WHERE ProductID = @NewProductID AND SizeID =   
  11.     (ISNULL((SELECT ISNULL(id,0) FROM tb_sizes WHERE StoreID=@NewStoreID),0)))            
  12.     BEGIN  
  13.         -- sub-query for insert mapping data   
  14.     END  
  15. 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?.
Answers (3)
Next Recommended Forum