Many - Many Update Queries Timing out

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Many - Many Update Queries Timing out

Author

Topic

ianfrater
Starting Member

6 Posts

Posted - 2014-05-14 : 10:03:29

Hi All,

Im Stuck with a simple issue.

In Query analyser im running large batches of update queries, if the total run time is greater than five minutes SQL will time out. I think im could be doing this an easier way without changing the default timeout.

SQL
INSERT INTO dbo.TblWaterFall SELECT dbo.QRYWaterfall .* FROM dbo.QRYWaterfall WHERE (Invd = CONVERT(DATETIME, '2013-07-14 00:00:00', 102))
INSERT INTO dbo.TblWaterFall SELECT dbo.QRYWaterfall .* FROM dbo.QRYWaterfall WHERE (Invd = CONVERT(DATETIME, '2013-07-15 00:00:00', 102))
INSERT INTO dbo.TblWaterFall SELECT dbo.QRYWaterfall .* FROM dbo.QRYWaterfall WHERE (Invd = CONVERT(DATETIME, '2013-07-16 00:00:00', 102))
INSERT INTO dbo.TblWaterFall SELECT dbo.QRYWaterfall .* FROM dbo.QRYWaterfall WHERE (Invd = CONVERT(DATETIME, '2013-07-17 00:00:00', 102))
INSERT INTO dbo.TblWaterFall SELECT dbo.QRYWaterfall .* FROM dbo.QRYWaterfall WHERE (Invd = CONVERT(DATETIME, '2013-07-18 00:00:00', 102))
INSERT INTO dbo.TblWaterFall SELECT dbo.QRYWaterfall .* FROM dbo.QRYWaterfall WHERE (Invd = CONVERT(DATETIME, '2013-07-19 00:00:00', 102))
INSERT INTO dbo.TblWaterFall SELECT dbo.QRYWaterfall .* FROM dbo.QRYWaterfall WHERE (Invd = CONVERT(DATETIME, '2013-07-20 00:00:00', 102))
INSERT INTO dbo.TblWaterFall SELECT dbo.QRYWaterfall .* FROM dbo.QRYWaterfall WHERE (Invd = CONVERT(DATETIME, '2013-07-21 00:00:00', 102))
INSERT INTO dbo.TblWaterFall SELECT dbo.QRYWaterfall .* FROM dbo.QRYWaterfall WHERE (Invd = CONVERT(DATETIME, '2013-07-22 00:00:00', 102))
INSERT INTO dbo.TblWaterFall SELECT dbo.QRYWaterfall .* FROM dbo.QRYWaterfall WHERE (Invd = CONVERT(DATETIME, '2013-07-23 00:00:00', 102))
INSERT INTO dbo.TblWaterFall SELECT dbo.QRYWaterfall .* FROM dbo.QRYWaterfall WHERE (Invd = CONVERT(DATETIME, '2013-07-24 00:00:00', 102))
INSERT INTO dbo.TblWaterFall SELECT dbo.QRYWaterfall .* FROM dbo.QRYWaterfall WHERE (Invd = CONVERT(DATETIME, '2013-07-25 00:00:00', 102))
INSERT INTO dbo.TblWaterFall SELECT dbo.QRYWaterfall .* FROM dbo.QRYWaterfall WHERE (Invd = CONVERT(DATETIME, '2013-07-26 00:00:00', 102))
INSERT INTO dbo.TblWaterFall SELECT dbo.QRYWaterfall .* FROM dbo.QRYWaterfall WHERE (Invd = CONVERT(DATETIME, '2013-07-27 00:00:00', 102))
INSERT INTO dbo.TblWaterFall SELECT dbo.QRYWaterfall .* FROM dbo.QRYWaterfall WHERE (Invd = CONVERT(DATETIME, '2013-07-28 00:00:00', 102))
INSERT INTO dbo.TblWaterFall SELECT dbo.QRYWaterfall .* FROM dbo.QRYWaterfall WHERE (Invd = CONVERT(DATETIME, '2013-07-29 00:00:00', 102))
INSERT INTO dbo.TblWaterFall SELECT dbo.QRYWaterfall .* FROM dbo.QRYWaterfall WHERE (Invd = CONVERT(DATETIME, '2013-07-30 00:00:00', 102))
INSERT INTO dbo.TblWaterFall SELECT dbo.QRYWaterfall .* FROM dbo.QRYWaterfall WHERE (Invd = CONVERT(DATETIME, '2013-07-31 00:00:00', 102))

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-14 : 12:13:05

Is dbo.QRYWaterfall a table? Does it have an index on the Invd column and is Invd a DATETIME? How many rows does each SELECT produce? How many rows are in each table? Is there a clustered index on dbo.TblWaterFall, if so what is it? Along the lines of a clustered index, do the rows coming from dbo.QRYWaterfall get inserted at the end of the clustered index or in the middle?, etc..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources