asked on

MS SQL Stored Procedure and very slow response

Hello All,
I have a web system which checks around 5000 rows of data and for each row of data performs the checks and then sends the individual row back to the database (MS SQL Server 2000) with the checks complete and a PASS, FAIL or NO ANSWER. This updates various things and is taking around 10 seconds which is fine as I can show the user a waiting page. However when I add the crucial bit in the code box below, it takes 9 minutes! This bit of the query works out the totals for each id in the 5000 rows of data e.g. this is sent back to the database:

10001, PASS

The query below works out the totals and then updates another table. The system then sends another bit of data

10001, PASS - run query
10001, FAIL ... 5000 times

As I only want one row of summary per item it has to check to see if anything exists and then do an insert or update. I am sure there is a more efficient way of doing this so any help at all would be greatly appreciated!

Declare @Count int 
 
Set @Count = (SELECT COUNT(Field1) FROM db1.dbo.tb1 WHERE Field1=@intField1 AND Field2=@intField2)
 
If @Count = 0 
Begin
	if @strState='Field5' and @Count = 0 
		begin
			INSERT INTO db1.dbo.tb1 (Field1, Field2 , Field3, Field4, Field5, Field6, Field7, Field8)  VALUES ( @intField1, @intField2, 100, 1, 1, 0, 0, @intField8)
		end
 
	 if  @strState='Field6' and @Count = 0 
		begin
			INSERT INTO db1.dbo.tb1( Field1, Field2 , Field3, Field4, Field5, Field6, Field7, Field8)  VALUES ( @intField1, @intField2, 0, 1, 0, 1, 0, @intField8)
		end
 
	 if  @strState='Field7' and @Count = 0 
		begin
			INSERT INTO db1.dbo.tb1 ( Field1, Field2 , Field3, Field4, Field5, Field6, Field7, Field8)  VALUES ( @intField1, @intField2, 0, 1, 0, 0, 1, @intField8)
		end
End
 
If @Count = 1 
Begin
 
Declare @Field4 int
Declare @Field5 int
Declare @Field6 int
Declare @Field7 int
 
Set @Field4 = (SELECT Field4 FROM db1.dbo.tb1 WHERE Field1=@intField1 AND Field2=@intField2) 
Set @Field4 = @Field4 +1
 
	If @strState='Field5' and @Count = 1 
		begin
 
			Set @Field5 = (SELECT Field5 FROM db1.dbo.tb1 WHERE Field1=@intField1 AND Field2=@intField2)
 
			Set @Field5 = @Field5  + 1
 
			Set @Field6 = (SELECT Field6 FROM db1.dbo.tb1 WHERE Field1=@intField1 AND Field2=@intField2)
			Set @Field7 = (SELECT Field7 FROM db1.dbo.tb1 WHERE Field1=@intField1 AND Field2=@intField2) 
			
			If @Field6=0 and @Field7=0
				begin
					UPDATE db1.dbo.tb1 SET Field1=@intField1, Field2=@intField2 , Field3=100 , Field4=@Field4 , Field5=@Field5, Field8=@intField8 WHERE  Field1=@intField1 and  Field2=@intField2
				end
			else
				begin
					UPDATE db1.dbo.tb1 SET Field1=@intField1, Field2=@intField2 , Field3=0 , Field4=@Field4 , Field5=@Field5, Field8=@intField8 WHERE  Field1=@intField1 and  Field2=@intField2
				end
		end
 
	 if  @strState='Field6' and @Count = 1 
		begin
 
			Set @Field6 = (SELECT Field6 FROM db1.dbo.tb1 WHERE Field1=@intField1 AND Field2=@intField2)
			Set @Field6 =  @Field6 + 1
 
			UPDATE db1.dbo.tb1 SET Field1=@intField1, Field2=@intField2 , Field3=0, Field4=@Field4, Field6=@Field6, Field8=@intField8  WHERE  Field1=@intField1 and  Field2=@intField2
		end
 
	 if  @strState='Field7' and @Count = 1 
		begin
 
			Set @Field7 = (SELECT Field7 FROM db1.dbo.tb1 WHERE Field1=@intField1 AND Field2=@intField2) 
			Set @Field7 = @Field7  + 1
 
			UPDATE db1.dbo.tb1 SET Field1=@intField1, Field2=@intField2 , Field3=0, Field4=@Field4, Field7= @Field7, Field8=@intField8  WHERE  Field1=@intField1 and  Field2=@intField2
		end
End

Open in new window

pidge_uk

ASKER

Another thing, If I run this query on my test server it runs in 16 seconds. The only difference is there is more data on the live site and also we have a web server and a database server where as the test server has MS SQL running locally.

shru_0409

check which query is taking extra time
try to commit the rows after some limit. i think redo log generated more..

pidge_uk

ASKER

Hello there,

Thanks for the help. The query which is taking the time is included above.

pidge_uk

ASKER

Hello All,

I have improved things with the following code snippet (taken the loop from another solution) but it is still slow. tb1 has over 2,000,000 rows could this be the issue even though the select queries have wheres?

Thanks for any help.


 
Begin
		declare @nextid int
		declare @lastid int
		declare @selectcmd nvarchar(255)
		Declare @intPercentage as int
 
		
		CREATE TABLE #tmploop(
			id int identity(1,1) not null,
			PDID nvarchar(10) not null
			)
		CREATE INDEX[IX_loop_id] ON [#tmploop]([id]) ON [PRIMARY]
		 
		insert into #tmploop(PDID) 
		select distinct A.field1 from db1.dbo.tb1 A where field2=@intfield2
 
		 
		select @lastid = max(id) from #tmploop
		set @nextid = 1
		 
		while @nextid <= @lastid
		BEGIN
		
		Declare @PDID As integer
		Set @PDID = (SELECT PDID FROM #tmploop WHERE ID=@nextid)
 
		Declare @C_P As integer
		Set @C_P = (SELECT COUNT(Result)  from db1.dbo.tb1 A where A.field1=@PDID AND field2=@intfield2  AND Result= 'PASS')
 
		Declare @C_F As integer
		Set @C_F = (SELECT COUNT(Result)  from db1.dbo.tb1 A where  A.field1=@PDID AND field2=@intfield2  AND Result= 'FAIL')
 
		Declare @C_N As integer
		Set @C_N = (SELECT COUNT(Result)  from db1.dbo.tb1 A where A.field1=@PDID AND field2=@intfield2  AND Result= 'NOANSWER')
 
		Set @intPercentage = 100
		Declare @total As int
 
		Set @total = @C_F + @C_N + @C_P
		
			Begin
				if @C_F= 0 AND @C_N=0
					begin
						INSERT INTO db2.dbo.tb2 (field1, field2 , field3, total, pass, fail, noanswer, percentage)  VALUES ( @PDID, @intfield2, 100, @total, @C_P,@C_F, @C_N, @intPercentage)
					end
			
				else
					begin
						INSERT INTO db2.dbo.tb2( field1, field2 , field3, total, pass, fail, noanswer, percentage)  VALUES ( @PDID, @intfield2, 0, @total, @C_P, @C_F, @C_N, @intPercentage)
					end
			
			End
				
		Set @nextid = @nextid + 1
		
		END
 
End
 
Drop table #tmploop
GO

Open in new window

pidge_uk

ASKER

Oh this is the error I get:

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteScalar()

profya

No details is available for us to test, however, your approach looks to me similar to a cursor, especially the while loop. I think the key step in enhancing the query performance is to take advantage of aggregation.
This condition field2=@intfield2 is meaningless since the temporary table has this condition applied when it is filled up.
You actually as I can see want to count result types, in this case you can for example create another temporary table and fill it with something like:
INSERT INTO #newTempTable SELECT result, COUNT(*) AS result_count FROM db1.dbo.tb1 GROUP BY result
You will get three records, then you can get their value with something like:
SELECT @C_P=result_count FROM #newTempTable WHERE result='PASS'
Then do your calculations and insert it into the final table.

Of course you can combine the query that counts the result into one query using sub queries, something like:
SELECT @C_P=(SELECT COUNT(*) FROM db1.dbo.tb1 WHERE result='PASS') , @C_F=(SELECT COUNT(*) FROM db1.dbo.tb1 WHERE result='FAIL') AS fail_results, .........

However, the core is to change the query plan and strategy and to avoid running into loops as much as possible, the database engine knows how to optimize queries if well planned. In addition to all that what I said is only suggestion, you have to make sure that it meets your logic.
I hope this helps.

profya

I forgot something, you can increase the query timeout, however, this is not the best solution, you have to optimize your query plan first.

pidge_uk

ASKER

Hello Profya,

Thanks for that. I had actually thought I needed to improve the query plan so I made the system work out the totals as it went along and then just add the counts to the insert on the database.Am I right in thinking a count statement on 2 million rows of data even though the count is looking at a few thousand will be a slow query? Surely SQL can manage counts on a million rows without a problem.

ASKER CERTIFIED SOLUTION

profya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pidge_uk

ASKER

Thanks for that profya. It was indeed a badly designed query that was casuing the problems. Thanks for answering.