SQL Server Performance

Very slow query with many sp_cursorfetch

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by kdefilip, Jun 4, 2007.

  1. kdefilip Member

    Hi.<br />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:<br />Execution Tree<br />--------------<br />Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[a].[APPL_ID] ASC))<br /> |--Hash Match(Left Outer Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[a].[APPL_ID])=([d].[APPL_ID]))<br /> |--Hash Match(Left Outer Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[a].[APPL_ID])=([an].[APPL_ID]))<br /> | |--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=':(' />[a].[APPL_ID]=[Expr1003]))<br /> | | |--Constant Scan<br /> | | |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DBName].[dbo].[T_APPLICANT] AS [a]))<br /> | |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DBName].[dbo].[T_APPLICANT_NUMBERS] AS [an]))<br /> |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FirstChoice].[dbo].[T_APPLICANT_DETAILS] AS [d]))<br /><br />There are hundreds of sp_cursorfetch executing one after the other. They look like this:<br />exec sp_cursorfetch 180150016, 16, 1, 1<br />exec sp_cursorfetch 180150016, 16, 2, 1<br />Each one changes just a little bit.<br />I can quite figure out what this search is doing, but it is taking minutes, no matter how much data is returned.<br />Thanks for any help. <br />I have the profile file available for anyone who is interested.<br />
  2. joechang New Member

    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?
  3. kdefilip Member

    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.<br /><br />Regarding the open cursor, in profiler, after the blank line that says "CursorOpen", on the next line there is a<br />RPC:Completed with the following info:<br />declare @P1 int<br />set @P1=180150016<br />declare @P2 int<br />set @P2=8<br />declare @P3 int<br />set @P3=1<br />declare @P4 int<br />set @P4=1563<br />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<br />select @P1, @P2, @P3, @P4<br /><br />After the cursor fetches, the SQL<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtStarting is this:<br />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 <br /><br />Thanks for your help.<br />
  4. joechang New Member

    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

  5. kdefilip Member

    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
  6. masqazi New Member

    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.
  7. satya Moderator

    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.
  8. jcarrera New Member

    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.
  9. satya Moderator

Share This Page