locked
Problem with inserting with a large db RRS feed

  • Question

  • User932259438 posted

    Hi,

    I have one table with large data around 1mio rows but if i have a large data why taking so long for next inserting data?
    Is possible increase for fast?

    Regards

    Tuesday, January 23, 2018 7:48 PM

Answers

All replies

  • User1120430333 posted

    You are not supplying any information as what technology you are using and how you are doing it.

    Tuesday, January 23, 2018 7:53 PM
  • User932259438 posted

    Check code:

    ALTER PROCEDURE [dbo].[ImportSuggestion] 
    	@SuggestionXML AS XML,
    	@Result AS NVARCHAR(100) OUTPUT
    AS
    BEGIN
    	BEGIN TRY
    
    		----DECLARE @SuggestionTable table
    		----(
    		----	MatchId BIGINT,
    		----	OutComeId NVARCHAR(50),
    		----	OddsType INT,
    		----	RemainingLimit DECIMAL(18,2),
    		----	SetUpFirstLimit DECIMAL(18,2)
    		----)
    
    		----INSERT INTO @SuggestionTable(MatchId,OutComeId,OddsType,RemainingLimit,SetUpFirstLimit)
    		----SELECT MatchId,OutComeId,OddsType,RemainingLimit,SetUpFirstLimit
    		----FROM tbl_Suggestions TS
    		----INNER JOIN  @SuggestionXML.nodes('/ArrayOfSuggestions/Suggestions') C(Cou) 
    		----ON TS.MatchId =  C.Cou.value('(MatchId)[1]','bigint') AND 
    		----                 (TS.OutComeId = C.Cou.value('(OutComeId)[1]','NVARCHAR(50)') OR TS.OutComeId IS NULL) AND 
    		----				 TS.OutCome = C.Cou.value('(OutCome)[1]','NVARCHAR(50)') AND 
    		----				 TS.OddsType = C.Cou.value('(OddsType)[1]','int') AND 
    		----	             (TS.SpecialBetValue IS NOT NULL)	
    
    		--INSERT INTO tbl_Suggestions_DeleteRecords(SuggestionId, MatchId,OutCome,OutComeId,OddsType,[Value],SpecialBetValue,CreatedDate,IsLatest,RemainingLimit,IsOpen,SetUpFirstLimit,IsSendEmail)
    		--SELECT SuggestionId,MatchId,OutCome,OutComeId,OddsType,[Value],SpecialBetValue,CreatedDate,IsLatest,RemainingLimit,IsOpen,SetUpFirstLimit,IsSendEmail
    		--FROM tbl_Suggestions TS
    		--INNER JOIN  @SuggestionXML.nodes('/ArrayOfSuggestions/Suggestions') C(Cou) 
    		--ON TS.MatchId =  C.Cou.value('(MatchId)[1]','bigint') AND 
    		--                 (TS.OutComeId = C.Cou.value('(OutComeId)[1]','NVARCHAR(50)') OR TS.OutComeId IS NULL) AND 
    		--				 TS.OutCome = C.Cou.value('(OutCome)[1]','NVARCHAR(50)') AND 
    		--				 TS.OddsType = C.Cou.value('(OddsType)[1]','int') AND 
    		--	             (TS.SpecialBetValue IS NOT NULL)	
    
    		--DELETE TS
    		--FROM tbl_Suggestions TS
    		--INNER JOIN  @SuggestionXML.nodes('/ArrayOfSuggestions/Suggestions') C(Cou) 
    		--ON TS.MatchId =  C.Cou.value('(MatchId)[1]','bigint') AND 
    		--                 (TS.OutComeId = C.Cou.value('(OutComeId)[1]','NVARCHAR(50)') OR TS.OutComeId IS NULL) AND 
    		--				 TS.OutCome = C.Cou.value('(OutCome)[1]','NVARCHAR(50)') AND 
    		--				 TS.OddsType = C.Cou.value('(OddsType)[1]','int') AND 
    		--	             (TS.SpecialBetValue IS NOT NULL)	
    				
    		------UPDATE TS
    		------SET TS.Value = C.Cou.value('(Value)[1]','NVARCHAR(50)'), 
    		------LastUpdatedDate = GETDATE()
    		------FROM tbl_Suggestions TS
    		------INNER JOIN  @SuggestionXML.nodes('/ArrayOfSuggestions/Suggestions') C(Cou) 
    		------ON TS.MatchId =  C.Cou.value('(MatchId)[1]','bigint') AND 
    		------                 (TS.OutComeId = C.Cou.value('(OutComeId)[1]','NVARCHAR(50)') OR TS.OutComeId IS NULL) AND 
    		------				 TS.OutCome = C.Cou.value('(OutCome)[1]','NVARCHAR(50)') AND 
    		------				 TS.OddsType = C.Cou.value('(OddsType)[1]','int') AND 
    		------	             (TS.SpecialBetValue IS NULL)		
    
    
    		INSERT INTO tbl_Suggestions(MatchId,OutCome,OutComeId,OddsType,[Value],SpecialBetValue,CreatedDate,IsLatest,RemainingLimit,IsOpen,SetUpFirstLimit,IsSendEmail)
    		SELECT  DISTINCT C.Cou.value('(MatchId)[1]','bigint') As MatchId,C.Cou.value('(OutCome)[1]','NVARCHAR(50)') As OutCome,
    		C.Cou.value('(OutComeId)[1]','NVARCHAR(50)') As OutComeId,C.Cou.value('(OddsType)[1]','int') As OddsType,
    		C.Cou.value('(Value)[1]','NVARCHAR(50)') As OddValue,C.Cou.value('(SpecialBetValue)[1]','NVARCHAR(50)') As SpecialBetValue,GETDATE(),
    		1,COALESCE(200, 
    		200
    
    		, 200
    		), CASE WHEN C.Cou.value('(Value)[1]','NVARCHAR(50)')='OFF' THEN 'FALSE' ELSE 'TRUE' END,COALESCE(200,
    		200
    
    		, 200
    		) 
    		 ,'FALSE' 
    		FROM @SuggestionXML.nodes('/ArrayOfSuggestions/Suggestions') C(Cou)
    		INNER JOIN tbl_Matches TM ON TM.MatchId=C.Cou.value('(MatchId)[1]','bigint')
    		--LEFT JOIN tbl_Limits LM ON LM.SportId=TM.SportId AND LM.CategoryId = TM.CategoryId 
    		--                                                 AND (LM.UniqueTournamentId = TM.UniqueTournamentId OR LM.UniqueTournamentId IS NULL) 
    		                                                -- AND (LM.TournamentId = TM.TournamentId OR LM.TournamentId IS NULL)
    		--LEFT JOIN tbl_LimitsMarketsManual LMM ON LMM.SportId=TM.SportId AND LMM.CategoryId = TM.CategoryId 
    		--                                                                AND (LMM.UniqueTournamentId = TM.UniqueTournamentId OR LMM.UniqueTournamentId IS NULL)
    																		--AND (LMM.TournamentId = TM.TournamentId OR LMM.TournamentId IS NULL)
    					
    		LEFT JOIN tbl_Suggestions TS ON TS.MatchId = C.Cou.value('(MatchId)[1]','bigint') AND
    										 (TS.OutComeId = C.Cou.value('(OutComeId)[1]','NVARCHAR(50)') OR TS.OutComeId IS NULL) AND 
    										 TS.OddsType = C.Cou.value('(OddsType)[1]','int') AND TS.IsLatest=1 AND TS.OutCome = C.Cou.value('(OutCome)[1]','NVARCHAR(50)') AND 
    										(TS.SpecialBetValue = C.Cou.value('(SpecialBetValue)[1]','NVARCHAR(50)')OR TS.SpecialBetValue IS NULL)		
    
    		
    		WHERE TS.MatchId IS NULL AND TS.OutComeId IS NULL AND TS.OddsType IS NULL
    
    	
    		--Update Suggestion
    		UPDATE TM
    		SET TM.CountSuggestion = T.OddsTypeCount
    		FROM tbl_Matches TM
    		INNER JOIN
    		(
    			SELECT COUNT(1) AS OddsTypeCount,T.MatchId
    			FROM tbl_Matches TM
    			INNER JOIN
    			(
    				SELECT 1 AS MatchidCount, TM.MatchId AS MatchId
    				FROM [dbo].[tbl_Suggestions] TS
    				INNER JOIN tbl_Matches TM ON TS.MatchId=TM.MatchId AND TS.IsLAtest=1
    				INNER JOIN @SuggestionXML.nodes('/ArrayOfSuggestions/Suggestions') C(Cou) ON TS.MatchId = C.Cou.value('(MatchId)[1]','bigint') AND TS.OutComeId = C.Cou.value('(OutComeId)[1]','NVARCHAR(50)') AND
    										TS.OddsType = C.Cou.value('(OddsType)[1]','int')
    				GROUP BY TM.MatchId, TS.OddsType
    			) AS T
    			ON T.MatchId = TM.MatchId
    			GROUP BY T.MatchId
    		) AS T
    		ON T.MatchId = TM.MatchId
    
    		UPDATE TS
    		SET TS.IsOpen='FALSE'
    		FROM tbl_Suggestions TS
    		INNER JOIN
    		(
    			SELECT DISTINCT TS.MatchId,TS.OddsType
    			FROM [dbo].[tbl_Suggestions] TS
    			INNER JOIN
    			(
    				SELECT MatchId,OddsType 
    				FROM [dbo].[tbl_Suggestions]
    				WHERE [Value]='OFF'
    				GROUP BY MatchId,OddsType 
    			) AS T ON TS.MatchId=T.MatchId AND TS.OddsType=T.OddsType
    			INNER JOIN @SuggestionXML.nodes('/ArrayOfSuggestions/Suggestions') C(Cou) ON TS.MatchId = C.Cou.value('(MatchId)[1]','bigint')  AND
    										TS.OddsType = C.Cou.value('(OddsType)[1]','int')
    		) AS OFFVALUE ON TS.MatchId=OFFVALUE.MatchId AND TS.OddsType=OFFVALUE.OddsType
    
    		SET @Result='Sucess'
    	END TRY
    	BEGIN CATCH
    		SELECT ERROR_MESSAGE ( )
    		SET @Result='Fail'
    	END CATCH
    END
    

    Tuesday, January 23, 2018 7:57 PM
  • User1120430333 posted

    T-SQL can be discussed at the SQL Server forum.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 23, 2018 9:12 PM
  • User-707554951 posted

    Hi progy85

    You can see in the end part where you is firing update queries 2 times

    In that update queries you is firing 2 select queries using join.

    So as you said there are too many records in table.

    So it is possible that insertion part get execute quickly but
    update query part taking much time because of this select statements

    I suggest you could make some test on his side.

    like first only try to insert without update and notice the time.

    It will give you an idea that issue is related with insertion or not.

    If the issue is not related with insertion then try to modify the update query

    Besides, you also could take a look at followoing similar thread:

    https://stackoverflow.com/a/18777209/9143922

    https://ask.sqlservercentral.com/questions/95663/where-to-look-when-inserts-are-slow.html

    http://use-the-index-luke.com/sql/dml/insert

    Best regards

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 24, 2018 8:01 AM