SQL Server Performance Forum – Threads Archive

Very slow query with many sp_cursorfetch

Hi. I have a vendor app that on particular searches is very, very slow. Looking at profiler I see it doing hundreds of cursor fetches. The execution tree looks like this: Execution Tree ————– Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[a].[APPL_ID] ASC)) |–Hash Match(Left Outer Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[a].[APPL_ID])=([d].[APPL_ID])) |–Hash Match(Left Outer Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[a].[APPL_ID])=([an].[APPL_ID])) | |–Hash Match(Right Semi Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1003])=([a].[APPL_ID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[DBName].[dbo].[T_APPLICANT] AS [a])) | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[DBName].[dbo].[T_APPLICANT_NUMBERS] AS [an])) |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[FirstChoice].[dbo].[T_APPLICANT_DETAILS] AS [d])) There are hundreds of sp_cursorfetch executing one after the other. They look like this: exec sp_cursorfetch 180150016, 16, 1, 1 exec sp_cursorfetch 180150016, 16, 2, 1 Each one changes just a little bit. I can quite figure out what this search is doing, but it is taking minutes, no matter how much data is returned. Thanks for any help. I have the profile file available for anyone who is interested.

throw this vendor app away
if it uses the API Server cursors (ie, sp_cursor calls) this is an ancient API for the old days when the client did not have the facilities to handle multiple rows,
you are paying a horrible over head,
something like 99% tax for the network round-trip look for the sp_cursoropen call at the head of the sp_cursorfetch to see what the original query was why is table scans are used on three tables,
are there no search arguments?

I assumed the scans were the result of improper indexes. The user has a choice of between about 50 search options. In this case I believe I picked only 3. However, no matter how many you choose, the search is still a dog. Most of the time seems to be consumed during these cursor fetches. Regarding the open cursor, in profiler, after the blank line that says "CursorOpen", on the next line there is a RPC:Completed with the following info: declare @P1 int set @P1=180150016 declare @P2 int set @P2=8 declare @P3 int set @P3=1 declare @P4 int set @P4=1563 exec sp_cursoropen @P1 output, N’SELECT APPL_ID FROM T_APPLICANT (nolock) WHERE APPL_STATUS = ”A” ORDER BY APPL_ID ‘, @P2 output, @P3 output, @P4 output select @P1, @P2, @P3, @P4 After the cursor fetches, the SQL<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />tmtStarting is this: SELECT a.APPL_ID, a.APPL_LASTNAME, a.APPL_FIRSTNAME, a.APPL_STATUS, an.NUM_HOMEPHONE, a.APPL_SSN, d.DET_TPC, d.DET_PPC, d.DET_RPC FROM (T_APPLICANT a (nolock) LEFT JOIN T_APPLICANT_NUMBERS an (nolock) ON a.APPL_ID = an.APPL_ID) LEFT JOIN T_APPLICANT_DETAILS d (nolock) ON a.APPL_ID=d.APPL_ID WHERE a.APPL_ID IN (401300, 403162, 413051, 417619, 422429, 432176, 439135, 440633, 440675, 440766, 440878, 440906, 441102, 441240, 441340, 441402, 441442, 441472, 441582, 441588, 441685, 441872, 441941, 442050, 442110, 442158, 442168, 442260, 442352, 442415, 442443, 442469, 442515, 442740, 442745, 442912, 442945, 442951, 443294, 443313, 443375, 443597, 443602, 443604, 443628, 443665, 443706, 443776, 443832, 443916, 444081, 444230, 444569, 444712, 444779, 444887, 444914, 445004, 445230, 445270, 445452, 445530, 445575, 445641, 445730, 445768, 445879, 445962, 445985, 446051, 446111, 446124, 446145, 446160, 446251, 446288, 446305, 446404, 446406, 446419, 446447, 446466, 446488, 446501, 446621, 446739, 446743, 446790, 446886, 446949, 447014, 447043, 447079, 447088, 447211, 447213, 447227, 447228, 447354) OR a.APPL_ID IN (447476, 447511, 447514, 447678, 447749, 447821, 447950, 448105, 448314, 448315, 448318, 448399, 448402, 448408, 448430, 448473, 448494, 448501, 448556, 448558, 448680, 448808, 448817, 448878, 448910, 449021, 449202, 449206, 449297, 449338, 449373, 449405, 449470, 449472, 449526, 449605, 449692, 449727, 449797, 449836, 449837, 449874, 449880, 449895, 450028, 450075, 450107, 450130, 450255, 450301, 450373, 450374, 450440, 450486, 450684, 450686, 450815, 450837, 450842, 450857, 450922, 450955, 450987, 451067, 451102, 451134, 451209, 451261, 451266, 451270, 451390, 451563, 451594, 451603, 451630, 451651, 451667, 451682, 451739, 451862, 451882, 452051, 452076, 452101, 452125, 452127, 452161, 452194, 452230, 452377, 452406, 452460, 452516, 452556, 452620, 452648, 452722, 452734, 452746, 452831) OR a.APPL_ID IN (452845, 452862, 452995, 453021, 453028, 453057, 453099, 453125, 453138, 453276, 453360, 453376, 453377, 453383, 453443, 453459, 453573, 453586, 453655, 453699, 453758, 453765, 453774, 453790, 453826, 453875, 453890, 453892, 453911, 453913, 453915, 453916, 454036, 454047, 454079, 454102, 454109, 454136, 454141, 454153, 454240, 454249, 454296, 454301, 454304, 454333, 454363, 454403, 454433, 454448, 454461, 454505, 454556, 454580, 454608, 454660, 454732, 454748, 454792, 454810, 454858, 454884, 454885, 454891, 454911, 454918, 454943, 454955, 454969, 454994, 455021, 455110, 455148, 455159, 455208, 455238, 455244, 455252, 455260, 455276, 455280, 455294, 455313, 455326, 455417, 455420, 455442, 455475, 455482, 455490, 455491, 455499, 455537, 455568, 455592, 455616, 455657, 455659, 455673, 455682) OR a.APPL_ID IN (455692, 455694, 455716, 455719, 455722, 455724, 455726, 455728, 455733, 455740, 455744, 455746, 455750, 455753, 455755, 455756, 455757, 455758, 455764, 455766, 455768, 455769, 455771, 455774, 455776, 455779, 455783, 455786, 455789, 455792, 455799, 455817, 455826, 455835, 455839, 455843, 455844, 455846, 455850, 455853, 455854, 455855, 455856, 455863, 455867, 455881, 455883, 455885, 455888, 455890, 455891, 455894, 455895, 455896, 455898, 455899, 455900, 455901, 455902, 455906, 455908, 455911, 455912, 455913, 455917, 455918, 455919, 455921, 455922, 455923, 455924, 455925, 455927, 455928, 455930, 455931, 455932, 455933, 455943, 455944, 455951, 455955, 455956, 455960, 455961, 455962, 455963, 455964, 455965, 455966, 455969, 455971, 455972, 455991, 455992, 455994, 455996, 455997, 455998, 456005) OR a.APPL_ID IN (456006, 456007, 456008, 456010, 456014, 456015, 456016, 456017, 456044, 456048, 456056, 456057, 456058, 456059, 456064, 456065, 456066, 456079, 456086, 456089, 456091, 456093, 456098, 456099, 456100, 456103, 456116, 456119, 456123, 456124, 456127, 456128, 456130, 456131, 456132, 456133, 456134, 456135, 456136, 456143, 456159, 456161, 456175, 456176, 456177, 456178, 456180, 456181, 456182, 456184, 456185, 456186, 456187, 456188, 456189, 456190, 456198, 456205, 456207, 456210, 456221, 456225, 456226, 456228, 456235, 456250, 456265, 456268, 456271, 456275, 456276, 456280, 456286, 456307, 456311, 456322, 456323, 456327, 456332, 456334, 456350, 456357, 456358, 456360, 456365, 456369, 456373, 456374, 456375, 456377, 456378, 456387, 456402, 456419, 456424, 456426, 456428, 456429, 456430, 456431) OR a.APPL_ID IN (456432, 456434, 456472, 456474, 456475, 456476, 456477, 456481, 456482, 456483, 456485, 456486, 456491, 456514, 456515, 456526, 456529, 456533, 456536, 456547, 456548, 456550, 456559, 456561, 456562, 456563, 456565, 456594, 456595, 456596, 456597, 456598, 456600, 456606, 456607, 456643, 456650, 456659, 456661, 456662, 456668, 456669, 456670, 456671, 456672, 456688, 456690, 456717, 456718, 456719, 456722, 456732, 456740, 456747, 456764, 456765, 456766, 456770, 456771, 456772, 456777, 456778, 456779, 456782, 456784, 456785, 456786, 456794, 456797, 456802, 456803, 456808, 456822, 456828, 456831, 456834, 456836, 456839, 456840, 456841, 456843, 456844, 456846, 456847, 456848, 456854, 456859, 456864, 456871, 456873, 456875, 456880, 456881, 456884, 456886, 456904, 456907, 456915, 456919, 456921) OR a.APPL_ID IN (456923, 456924, 456925, 456927, 456928, 456950, 456957, 456960, 456962, 456985, 456986, 456987, 456989, 456990, 456991, 456992, 456996, 457031, 457035, 457037, 457038, 457039, 457048, 457049, 457050, 457051, 457126, 457252, 457265, 457276, 457317, 457419, 457428, 457527, 457531, 457562, 457566, 457586, 457600, 457648, 457680, 457707, 457785, 457803, 457838, 457849, 457853, 457857, 457859, 457877, 457897, 457902, 457903, 457922, 457926, 457939, 457940, 457944, 457947, 457948, 457953, 457978, 457982, 457983, 457986, 457989, 458007, 458010, 458014, 458017, 458024, 458030, 458037, 458038, 458047, 458051, 458053, 458055, 458063, 458069, 458071, 458082, 458083, 458085, 458086, 458087, 458093, 458095, 458097, 458098, 458103, 458105, 458113, 458115, 458116, 458117, 458118, 458121, 458123, 458126) OR a.APPL_ID IN (458129, 458130, 458134, 458140, 458142, 458143, 458147, 458149, 458150, 458152, 458154, 458156, 458161, 458166, 458170, 458172, 458173, 458176, 458179, 458180, 458185, 458188, 458189, 458190, 458191, 458192, 458193, 458194, 458195, 458202, 458205, 458206, 458207, 458211, 458214, 458215, 458216, 458217, 458218, 458222, 458225, 458226, 458229, 458232, 458233, 458236, 458238, 458239, 458241, 458242, 458245, 458246, 458247, 458274, 458282, 458288, 458297, 458300, 458307, 458309, 458315, 458318, 458319, 458322, 458326, 458330, 458331, 458339, 458354, 458355, 458357, 458364, 458368, 458369, 458370, 458371, 458373, 458374, 458375, 458376, 458377, 458378, 458380, 458383, 458388, 458389, 458390, 458395, 458396, 458401, 458410, 458416, 458422, 458430, 458432, 458435, 458439, 458457, 458460, 458465) OR a.APPL_ID IN (458468, 458469, 458472, 458473, 458476, 458477, 458479, 458486, 458490, 458493, 458497, 458504, 458505, 458506, 458517, 458521, 458526, 458528, 458530, 458536, 458537, 458538, 458540, 458542, 458544, 458546, 458547, 458550, 458554, 458556, 458557, 458567, 458589, 458591, 458596, 458607, 458609 , 458616, 458619, 458620, 458621, 458635, 458637, 458638, 458639, 458643, 458647, 458652, 458653, 458658, 458667, 458682, 458685, 458695, 458709, 458717, 458725, 458738, 458741, 458742, 458757, 458758, 458761, 458764, 458765, 458767, 458768, 458770, 458772, 458775, 458777, 458778, 458790, 458792, 458795, 458798, 458809, 458813, 458820, 458823, 458826, 458827, 458830, 458835, 458851, 458854, 458855, 458858, 458860, 458861, 458862, 458866, 458871, 458878, 458899, 458900, 458907, 458908, 458910, 458912) OR a.APPL_ID IN (458914, 458915, 458932, 458933, 458935, 458941, 458943) ORDER BY a.APPL_ID Thanks for your help.

this has got to be the stupidest application architecture/query i have seen,
well, ok, there have been others i reiterate, unless you enjoy pain and suffering
ditch this app i assume T_APPLICANT is clustered on APPL_ID?
I would need to see the indexes on:
T_APPLICANT_NUMBERS
T_APPLICANT_DETAILS

Here’s the index builds on the two tables you asked about. CREATE INDEX [T_APPLICANT_DETAILS4] ON [dbo].[T_APPLICANT_DETAILS]([APPL_ID], [DET_TPC], [DET_PPC], [DET_RPC]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO CREATE INDEX [T_APPLICANT_DETAILS6] ON [dbo].[T_APPLICANT_DETAILS]([DET_OFFICE], [APPL_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO /****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC (‘CREATE STATISTICS [hind_2105058535_1A_2A] ON [dbo].[T_APPLICANT_DETAILS] ([APPL_ID], [DET_OFFICE]) ‘)
GO /****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC (‘CREATE STATISTICS [hind_53575229_1A_2A] ON [dbo].[T_APPLICANT_NUMBERS] ([APPL_ID], [NUM_HOMEPHONE]) ‘)
GO

i too face severe performance issue with a similar stupid vendor application. the worse thing is, that vendor is not give any support for long and we dont have source code. so what options i have to increase the performance, what so ever.

Then you are out of luck if the vendor is refusing in that way. Refer tohttp://www.sqlteam.com/article/server-side-cursors-and-ado-cursor-types and the problem is you will not be able to attempt any code modifications here.Rather only by having proper or atleast sensible indexes you can attain somewhat better performance than the current situation. But many application performance problems aren’t caused by the configuration of the database server, OS, hardware, or network. Most often, the application causes poor performance. In particular, the way in which applications talk to the database through ADO can cause slow performance.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

I have the same issue, users complian about the program is slow and the vendor support says the problem is the network is slow…lol
your examples look like the same as the app we have.

JCarrera
As you refer that problem is similar, were you able to obtain the required statistics in terms of CPU, Disk, Memory and network on the server. See my blog on baseline and benchmarking to obtain required information http://sqlserver-qa.net/blogs/perftune/archive/2007/12/19/2981.aspx where you can start working on it.

]]>