Veeeeeeeeeery slow queries in MS SQL 2005

[3fonov]

Hello all! I've got some problem. When i'm executing about 10 stored procedures it's tooks 5 second to complete. Here is procedures code: ALTER PROCEDURE [dbo].[update_source] -- Add the parameters for the stored procedure here @internal_id int, @parent_id int, @sstate tinyint, @title nvarchar(50), @rng decimal(4,2), @lat dbo.latitude, @lon dbo.longtitude, @course dbo.angle, @speed dbo.speed, @stype xml AS BEGIN SET NOCOUNT ON; DECLARE @source_id int; DECLARE @state int; SET @source_id = (SELECT source_id FROM sources WHERE internal_id = @internal_id); IF @source_id IS NULL BEGIN INSERT INTO sources (internal_id, parent_id, updated, sstate, title, rng, course, speed, stype,lat,lon) VALUES (@internal_id, @parent_id, getutcdate(), @sstate, @title, @rng, @course, @speed, @stype, @lat*100000000, @lon*10000000); SET @source_id = (SELECT source_id FROM sources WHERE internal_id = @internal_id); END ELSE SET @state = (SELECT sstate FROM sources WHERE source_id = @source_id); UPDATE sources SET parent_id = @parent_id, updated = getutcdate(), sstate = @sstate, title = @title, rng = @rng, course = @course, speed = @speed, stype = @stype, lat = @lat*100000000, lon = @lon*10000000 WHERE source_id = @source_id; INSERT INTO source_states (source_id, sstate, acquired) VALUES (@source_id, @sstate, getutcdate()); END and i'm execute it like this: EXEC update_source 2190074, 5, 1, N'2190074', 0,57.7386783333333,10.57468,0,0,N''; EXEC update_source 2573335, 5, 1, N'2573335', 0,63.8,9.73333333333333,0,0,N''; ..... ..... ..... EXEC update_source 2573105, 5, 1, N'2573105', 0,59.6166666666667,10.5166666666667,0,0,N''; EXEC update_source 2573235, 5, 1, N'2573235', 0,60.4,5.35,0,0,N''; I think that mistake not in my code but somewhere in server configuration. But i can't find it out. Thanks a lot!

[andyharman]

Which columns have indexes or primary keys specified? I would expect a clustered primary-key on the "internal_id" column and a unique index (or unique constraint) on the "source_id" column. Regards Andy

[3fonov]

Yes it is. And it doesn't depends on table size or data size. When it's executed in Managment Studio with Client statistics everything is zero. Thank you for quick reply

[andyharman]

If you run your 10 stored procedure calls from a Management Studio query then how long does it take? If it takes a long time then I would be looking at a configuration issue (you should be able to comfortably hit between 30-50 calls per second). o What other indexes do you have on that table? o Do you have any triggers? o What else is happening on the database server? o Are you data files held in a compressed folder? o Is the transaction log being written to a separate disk? If it is really quick through the SQL-Server tools then I would be looking at your front-end code. o Are you using the native SqlClient connection? o Are you reusing your dbcommand object (using parameters). o Are you opening a separate connection for each call? o Is the database on the same server? Do you have network problems? Hope that helps. Andy

[3fonov]

Thanks for help but... I try to run this on clean installation on empty tables and got this in Profiler: Print Screen. There is no triggers, compressed folders, and so on. Theres is selected a bit more complicated procedure, but as i think it must not take 3 seconds to run got it indexes or not. The same routines on MySQL server takes zero time and CPU perfomance. Thanks for advises.

[Pete OHanlon]

First of all, is source_id an autoincrement column? If it is, don't reselect it - use the Scope_Identity() function to retrieve it in your insert, e.g.

INSERT INTO sources
(internal_id, parent_id, updated, sstate, title, rng, course, speed, stype,lat,lon)
VALUES
(@internal_id, @parent_id, getutcdate(), @sstate, @title, @rng, @course, @speed, @stype, @lat*100000000, @lon*10000000);
SET @source_id = SCOPE_IDENTITY()

If it's a uniqueidentifier column, select the GUID yourself and then insert it as part of the insert statement. Secondly, why are you performing the SELECT @state = select in the update portion? I can't see anywhere that you are using this in your procedure. Third, you are performing a calculation in your query. I would consider either moving it outside the query, or using a computed column in the table.

Deja View - the feeling that you've seen this post before.

[3fonov]

I move this database to MS SQL Express and it's start working 30 times faster as i assumed it must. So problem somewhere in server configuration not database design. Thanks