Query timesout when processing >7000 records

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 2005 Forums

Transact-SQL (2005)

Query timesout when processing >7000 records

Author

Topic

martinzima
Starting Member

3 Posts

Posted - 2008-04-29 : 05:50:30

I am running a pivot query on approx 13000 records. When I run it on the whole dataset it timesout after 30 seconds but when I run it on 6500 (half at a time) it completes in about 2 seconds.

Can someone please help!?

martinzima
Starting Member

3 Posts

Posted - 2008-04-29 : 06:34:39

NB: I am running SQL Server 2005 through the SQL Server Management Studio on Windows 2003 R2

bfoster
Starting Member

30 Posts

Posted - 2008-04-29 : 11:21:21

Can you provide more information about your query such as the query itself?

martinzima
Starting Member

3 Posts

Posted - 2008-05-01 : 12:58:45

SELECT RespondantID, [1] AS Q1, [2] AS Q2, [3] AS Q3, [4] AS Q4, [5] AS Q5, [6] AS Q6, [7] AS Q7,

AS Q8, [9] AS Q9, [10] AS Q10, [11] AS Q11, [12] AS Q12, [13] AS Q13, [14] AS Q14, [15] AS Q15, [16] AS Q16, [17] AS Q17, [18] AS Q18, [19] AS Q19, [20] AS Q20, [21] AS Q21, [22] AS Q22, [23] AS Q23, [24] AS Q24, [25] AS Q25, [26] AS Q26, [27] AS Q27, [28] AS Q28, [29] AS Q29, [30] AS Q30, [31] AS Q31, [32] AS Q32, [33] AS Q33, [34] AS Q34, [35] AS Q35, [36] AS Q36, [37] AS Q37, [38] AS Q38, [39] AS Q39, [40] AS Q40, [41] AS Q41, [42] AS Q42, [43] AS Q43, [44] AS Q44, [45] AS Q45, [46] AS Q46, [47] AS Q47, [48] AS Q48, [49] AS Q49, [50] AS Q50, [51] AS Q51, [52] AS Q52, [53] AS Q53, [54] AS Q54, [55] AS Q55, [56] AS Q56, [57] AS Q57, [58] AS Q58, [59] AS Q59, [60] AS Q60, [61] AS Q61, [62] AS Q62, [63] AS Q63, [64] AS Q64, [65] AS Q65, [66] AS Q66, [67] AS Q67, [68] AS Q68, [69] AS Q69

FROM (SELECT RespondantID, QuestionID, Answer
FROM A
WHERE SurveyID = 34) AS preData PIVOT (MAX(Answer) FOR QuestionID IN ([0], [1], [2], [3], [4], [5], [6], [7],

, [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31], [32], [33], [34], [35], [36], [37], [38], [39], [40], [41], [42], [43], [44], [45], [46], [47], [48], [49], [50], [51], [52], [53], [54], [55], [56], [57], [58], [59], [60], [61], [62], [63], [64], [65], [66], [67], [68], [69])) AS data

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-01 : 13:01:03

Have you had a look at execution plans in both the cases and spotted any differences?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources