A. Abdis
1/13/2003 9:15:00 PM
I believe the attached files should be everything you need as far as data
definition to repro this. The LKUP_MFGPN table is pop'ed with a cross ref
between the INTID and the MFGPN after being uppercased and having all
non-alpha-numeric characters removed. this was to allow salesmen some leway
in looking up part numbers which have wacky equal signs, pound signs,
ampersands and other such funcky stuff in them.
"Angel Saenz-Badillos[MS]" <angelsa@online.microsoft.com> wrote in message
news:utkxqQzuCHA.2544@TK2MSFTNGP11...
> Could you post your database schema and the stored procedure you are using
> to retrieve it? I would like to get a repro of this,
> Thank you,
> --
> Angel Saenz-Badillos [MS] Managed Providers
> This posting is provided "AS IS", with no warranties, and confers no
rights.
> Please do not send email directly to this alias. This alias is for
newsgroup
> purposes only.
>
>
> "A. Abdis" <aabdis@yahoo.com> wrote in message
> news:u6PZvUyuCHA.2556@TK2MSFTNGP10...
> > I am desperate at this point, after searching google, google.groups and
> > msdn.
> >
> > I have a search webservice. It is given a part number, and performs one
of
> > three searches, based on criteria:
> > 1. internal data id - the unique id used in the db tables
> > 2. sales part number - the part number our sales people refer to it by
> > 3. manufacturers part number - clear enough.
> >
> > 1 works fine, blazingly fast as youd expect, response times of 0.01 to
> 0.05
> > of a second on average, regardless data or not.
> > 2 again works fine, regardless of data or not, response times of 0.01 to
> > 0.07 of a second on average.
> >
> > The problem comes in at 3, searching by MFG p/n. If there are no
matches,
> it
> > returns fine, 0.01 of a second or so. as soon as there are ANY results,
> even
> > just one, the response time jumps up to anywhere from *12* to *27*
> > seconds... obviously unacceptable. I am using the
System.Data.OracleClient
> > component to connect to an Oracle 9.2 database. through other testing,
> > The calls to search by 1, 2, or 3 use IDENTICAL code, the only thing
that
> > changes is the stored procedure name (OracleCommand.CommandText), from
> > SEARCH.INTID to SEARCH.SLSID to SEARCH.MFGID.
> >
> > I have already determined it has nothing to do with the DB. The search
is
> > done via the stored procedure SEARCH.MFGID. The WebSvc calls the
> StoredProc,
> > which returns a REF CURSOR. I tried pulling the SQL out from the stored
> proc
> > and executing it in a sql worksheet. It returned in under a 100th of a
> > second. So i thought maybe somehow the procedure call was being slow, so
i
> > made an external sql block to execute the procedure call, again, this
> > returns in under a 100th of a second.
> > The hold up is on the line of code da.Fill(ds,"results") where da is my
> > dataadapter, and ds my dataset.
> >
> > I have looked everywhere and can find NOTHing different between a MFGPN
> > search and a SLSPN search that would cause this abysmal performance. My
> > WebService is in VB.NET.
> >
> > If you can help, i would be overjoyed!
> >
> > Thanks in advance,
> >
> > Aaron Abdis.
> > aabdis @ mecnet . com
> >
> > Manchester Technologies, Inc.
> >
> >
>
>
begin 666 package.txt
M4&%C:V%G92!(96%D97(Z#0H)4$%#2T%'12!314%20T@-"@E!4PT*"0E465!%
M(')E=$-U<G-O<B!)4R!2148@0U524T]2.PT*#0H)"2TM(%1(25,@1E5.0U1)
M3TX@3$]/2U,@4$%25%,@55 @0ED@24Y415).04P@4$%25"!.54U"15(-"@D)
M1E5.0U1)3TX@24Y4240H:4E.5$E$($E.(%9!4D-(05(R+"!I0U535$Y/($E.
M(%9!4D-(05(R+"!2151604P@24X@3U54(')E=$-U<G-O<BD@4D5455).($Y5
M34)%4CL-"@T*"0DM+2!42$E3($953D-424].($Q/3TM3(%!!4E13(%50($)9
M($U&1R!005)4($Y534)%4@T*"0E&54Y#5$E/3B!-1D=)1"AI349'240@24X@
M5D%20TA!4C(L(&E#55-43D\@24X@5D%20TA!4C(L(%)%5%9!3"!)3B!/550@
M<F5T0W5R<V]R*2!215154DX@3E5-0D52.PT*#0H)"2TM(%1(25,@1E5.0U1)
M3TX@3$]/2U,@4$%25%,@55 @0ED@4T%,15,@4$%25"!.54U"15(-"@D)1E5.
M0U1)3TX@4TQ3240H:5-,4TE$($E.(%9!4D-(05(R+"!I0U535$Y/($E.(%9!
M4D-(05(R+"!2151604P@24X@3U54(')E=$-U<G-O<BD@4D5455).($Y534)%
M4CL-"@E%3D0@4T5!4D-(.PT*#0I086-K86=E($)O9'DZ#0H)4$%#2T%'12!"
M3T19(%-%05)#2 T*"4%3#0H)"2TM(%1(25,@1E5.0U1)3TX@4T5!4D-(15,@
M4U1225!0140@24Y415).04P@240-"@D)1E5.0U1)3TX@24Y4240H:4E.5$E$
M($E.(%9!4D-(05(R+"!I0U535$Y/($E.(%9!4D-(05(R+"!2151604P@24X@
M3U54(')E=$-U<G-O<BD@4D5455).($Y534)%4B!)4PT*"0D):7-4:&5R92 @
M($Y534)%4B Z/2 P.PT*"0E"14=)3@T*"0D)4T5,14-4($-/54Y4*"HI($E.
M5$\@:7-4:&5R92!&4D]-(%!23T0@5TA%4D4@24Y4240@3$E+12!I24Y4240[
M#0H)"0E)1B!I<U1H97)E(#X@,"!42$5.#0H)"0D)3U!%3B!2151604P@1D]2
M#0H)"0D)"5-%3$5#5"!0+DE.5$E$+"!34"Y04D]$54-43E5-(%-,4TE$+"!3
M4"Y33%-#3T1%+"!#+D1%4T-225!424].($-!5$5'3U)9+"!6+E1%6%0@349'
M+"!0+DU&1U!.($U&1U!.+ T*"0D)"0D)358N5$585"!$15-#4DE05$E/3BP@
M4"Y)34%'149)3$4L($,N24U!1T5&24Q%($1%1DE-04=%1DE,12P@4U N4519
M24Y35$]#2R!35$]#2RP@4U N4TA/5U=%0EE.(%-(3U=714(L#0H)"0D)"0E'
M151?4%)/1%]04DE#12A0+DE.5$E$+&E#55-43D\I(%!224-%+"!'151?4%)/
M1%]02T=?4519*% N24Y4240I(%!+1U%460T*"0D)"0E&4D]-(%!23T0@4"P@
M5D]#04(@5BP@34%)3E-014,@32P@359/0T%"($U6+"!#051%1T]262!#+"!3
M3%-04D]$(%-0#0H)"0D)"5=(15)%(% N24Y4240@3$E+12!I24Y4240@04Y$
M#0H)"0D)"0E0+DE.5$E$(#T@4U N24Y4240@04Y$#0H)"0D)"0E34"Y32$]7
M5T5"64X@/2 Q($%.1 T*"0D)"0D)4"Y-1D=)1" ](%8N240@04Y$#0H)"0D)
M"0E0+D-!5$5'3U)9240@/2!#+DE$($%.1 T*"0D)"0D)4"Y)3E1)1" ]($TN
M24Y4240@04Y$#0H)"0D)"0E-+D1)4U!,05E/4D1%4B ](#$@04Y$#0H)"0D)
M"0E-+D)/1%E)1" ]($U6+DE$.PT*"0D)14Q310T*"0D)"4]014X@4D545D%,
M($9/4@T*"0D)"0E314Q%0U0@3E5,3"!)3E1)1"P@3E5,3"!33%-)1"P@3E5,
M3"!33%-#3T1%+"!.54Q,($-!5$5'3U)9+"!.54Q,($U&1RP@3E5,3"!-1D=0
M3BP@3E5,3"!$15-#4DE05$E/3BP-"@D)"0D)"4Y53$P@24U!1T5&24Q%+"!.
M54Q,($1%1DE-04=%1DE,12P@3E5,3"!35$]#2RP@3E5,3"!04DE#12P@3E5,
M3"!32$]75T5"#0H)"0D)"4923TT@1%5!3"!72$5212 P(#P^(# [#0H)"0E%
M3D0@248[#0H-"@D)"5)%5%523B!I<U1H97)E.PT*"0EE>&-E<'1I;VX-"@D)
M"7=H96X@;W1H97)S('1H96X-"@D)"0ER86ES93L-"@D)14Y$($E.5$E$.PT*
M#0H)"2TM(%1(25,@1E5.0U1)3TX@4T5!4D-(15,@4U1225!0140@349'4$X@
M*$%,4$A!3E5-($].3%DI#0H)"4953D-424].($U&1U!.*&E-1D=03B!)3B!6
M05)#2$%2,BP@:4-54U1.3R!)3B!605)#2$%2,BP@4D545D%,($E.($]55"!R
M971#=7)S;W(I(%)%5%523B!.54U"15(@25,-"@D)"6ES5&AE<F4@("!.54U"
M15(@.CT@,#L-"@D)0D5'24X-"@D)"5-%3$5#5"!#3U5.5"@J*2!)3E1/(&ES
M5&AE<F4@1E)/32!,2U507TU&1U!.(%=(15)%($%.7TU&1U!.($Q)2T4@:4U&
M1U!..PT*"0D)248@:7-4:&5R92 ^(# @5$A%3@T*"0D)"4]014X@4D545D%,
M($9/4@T*"0D)"0E314Q%0U0@4"Y)3E1)1"P@4U N4%)/1%5#5$Y532!33%-)
M1"P@4U N4TQ30T]$12P@0RY$15-#4DE05$E/3B!#051%1T]262P@5BY415A4
M($U&1RP@4"Y-1D=03B!-1D=03BP-"@D)"0D)"4U6+E1%6%0@1$530U))4%1)
M3TXL(% N24U!1T5&24Q%+"!#+DE-04=%1DE,12!$149)34%'149)3$4L(%-0
M+E%464E.4U1/0TL@4U1/0TLL(%-0+E-(3U=714)93B!32$]75T5"+ T*"0D)
M"0D)1T547U!23T1?4%))0T4H4"Y)3E1)1"QI0U535$Y/*2!04DE#12P@1T54
M7U!23T1?4$M'7U%462A0+DE.5$E$*2!02T=15%D-"@D)"0D)1E)/32!,2U50
M7TU&1U!.($PL(%!23T0@4"P@5D]#04(@5BP@34%)3E-014,@32P@359/0T%"
M($U6+"!#051%1T]262!#+"!33%-04D]$(%-0#0H)"0D)"5=(15)%($PN04Y?
M349'4$X@3$E+12!I349'4$X@04Y$#0H)"0D)"0E,+DE.5$E$(#T@4"Y)3E1)
M1"!!3D0-"@D)"0D)"4PN24Y4240@/2!34"Y)3E1)1"!!3D0-"@D)"0D)"5-0
M+E-(3U=714)93B ](#$@04Y$#0H)"0D)"0E0+DU&1TE$(#T@5BY)1"!!3D0-
M"@D)"0D)"5 N0T%414=/4EE)1" ]($,N240@04Y$#0H)"0D)"0E0+DE.5$E$
M(#T@32Y)3E1)1"!!3D0-"@D)"0D)"4TN1$E34$Q!64]21$52(#T@,2!!3D0-
M"@D)"0D)"4TN0D]$64E$(#T@358N240[#0H)"0E%3%-%#0H)"0D)"4]014X@
M4D545D%,($9/4@T*"0D)"0D)4T5,14-4($Y53$P@24Y4240L($Y53$P@4TQ3
M240L($Y53$P@4TQ30T]$12P@3E5,3"!#051%1T]262P@3E5,3"!-1D<L($Y5
M3$P@349'4$XL($Y53$P@1$530U))4%1)3TXL#0H)"0D)"0D)"4Y53$P@24U!
M1T5&24Q%+"!.54Q,($1%1DE-04=%1DE,12P@3E5,3"!35$]#2RP@3E5,3"!0
M4DE#12P@3E5,3"!32$]75T5"#0H)"0D)"4923TT@1%5!3"!72$5212 P(#P^
M(# [#0H)"0E%3D0@248[#0H-"@D)"5)%5%523B!I<U1H97)E.PT*"0EE>&-E
M<'1I;VX-"@D)"7=H96X@;W1H97)S('1H96X-"@D)"0ER86ES93L-"@D)14Y$
M($U&1U!..PT*#0H)"2TM(%1(25,@1E5.0U1)3TX@4T5!4D-(15,@4U1225!0
M140@4TQ3240@*$%,4$A!3E5-($].3%DI#0H)"4953D-424].(%-,4TE$*&E3
M3%-)1"!)3B!605)#2$%2,BP@:4-54U1.3R!)3B!605)#2$%2,BP@4D545D%,
M($E.($]55"!R971#=7)S;W(I(%)%5%523B!.54U"15(@25,-"@D)"6ES5&AE
M<F4@("!.54U"15(@.CT@,#L-"@D)0D5'24X-"@D)"5-%3$5#5"!#3U5.5"@J
M*2!)3E1/(&ES5&AE<F4@1E)/32!33%-04D]$(%=(15)%(%-,4T-/1$4@3$E+
M12!I4TQ3240[#0H)"0E)1B!I<U1H97)E(#X@,"!42$5.#0H)"0D)3U!%3B!2
M151604P@1D]2#0H)"0D)"5-%3$5#5"!0+DE.5$E$+"!34"Y04D]$54-43E5-
M(%-,4TE$+"!34"Y33%-#3T1%+"!#+D1%4T-225!424].($-!5$5'3U)9+"!6
M+E1%6%0@349'+"!0+DU&1U!.($U&1U!.+ T*"0D)"0D)358N5$585"!$15-#
M4DE05$E/3BP@4"Y)34%'149)3$4L($,N24U!1T5&24Q%($1%1DE-04=%1DE,
M12P@4U N451924Y35$]#2R!35$]#2RP@4U N4TA/5U=%0EE.(%-(3U=714(L
M#0H)"0D)"0E'151?4%)/1%]04DE#12A0+DE.5$E$+&E#55-43D\I(%!224-%
M+"!'151?4%)/1%]02T=?4519*% N24Y4240I(%!+1U%460T*"0D)"0E&4D]-
M(%-,4U!23T0@4U L(%!23T0@4"P@5D]#04(@5BP@34%)3E-014,@32P@359/
M0T%"($U6+"!#051%1T]262!##0H)"0D)"5=(15)%(%-0+E-,4T-/1$4@3$E+
M12!I4TQ3240@04Y$#0H)"0D)"0E34"Y32$]75T5"64X@/2 Q($%.1 T*"0D)
M"0D)4U N24Y4240@/2!0+DE.5$E$($%.1 T*"0D)"0D)4"Y-1D=)1" ](%8N
M240@04Y$#0H)"0D)"0E0+D-!5$5'3U)9240@/2!#+DE$($%.1 T*"0D)"0D)
M4"Y)3E1)1" ]($TN24Y4240@04Y$#0H)"0D)"0E-+D1)4U!,05E/4D1%4B ]
M(#$@04Y$#0H)"0D)"0E-+D)/1%E)1" ]($U6+DE$.PT*"0D)14Q310T*"0D)
M"4]014X@4D545D%,($9/4@T*"0D)"0E314Q%0U0@3E5,3"!)3E1)1"P@3E5,
M3"!33%-)1"P@3E5,3"!33%-#3T1%+"!.54Q,($-!5$5'3U)9+"!.54Q,($U&
M1RP@3E5,3"!-1D=03BP@3E5,3"!$15-#4DE05$E/3BP-"@D)"0D)"4Y53$P@
M24U!1T5&24Q%+"!.54Q,($1%1DE-04=%1DE,12P@3E5,3"!35$]#2RP@3E5,
M3"!04DE#12P@3E5,3"!32$]75T5"#0H)"0D)"4923TT@1%5!3"!72$5212 P
M(#P^(# [#0H)"0E%3D0@248[#0H-"@D)"5)%5%523B!I<U1H97)E.PT*"0EE
M>&-E<'1I;VX-"@D)"7=H96X@;W1H97)S('1H96X-"@D)"0ER86ES93L-"@D)
;14Y$(%-,4TE$.PT*#0I%3D0@4T5!4D-(.PT*
`
end
begin 666 tables.txt
M+RH-"B @4V-R:7!T(&9O<B!397)V97(@5$535$1"("A/<F%C;&4Y:2!%;G1E
M<G!R:7-E($5D:71I;VX@4F5L96%S92 Y+C(N,"XQ+C @+2!0<F]D=6-T:6]N
M*2!O;B S,BUB:70@5VEN9&]W<PT**B\-"@T*+RH-"B @5&%B;&4H<RD-"BHO
M#0H-"D-214%412!404),12!#051%1T]260T**$E$($-(05(H,BD@3D]4($Y5
M3$P-"BQ$15-#4DE05$E/3B!605)#2$%2,B@X,"D@3D]4($Y53$P-"BQ)34%'
M149)3$4@5D%20TA!4C(H,3 I#0HL0T].4U1204E.5"!02U]#051%1T]262!0
M4DE-05)9($M%62 H240I#0HI#0HO#0H-"D-214%412!404),12!-5D]#04(-
M"BA)1"!605)#2$%2,B@Q,"D@3D]4($Y53$P-"BQ415A4(%9!4D-(05(R*#(P
M,# I#0HL0T].4U1204E.5"!02U]-5D]#04(@4%))34%262!+15D@*$E$*0T*
M*0T*+PT*#0I#4D5!5$4@5$%"3$4@5D]#04(-"BA)1"!605)#2$%2,B@Q,"D@
M3D]4($Y53$P-"BQ415A4(%9!4D-(05(R*#(U-2D-"BQ#3TY35%)!24Y4(%!+
M7U9/0T%"(%!224U!4ED@2T59("A)1"D-"BD-"B\-"@T*0U)%051%(%1!0DQ%
M(%!23T0-"BA)3E1)1"!605)#2$%2,B@T,"D@3D]4($Y53$P-"BQ#051%1T]2
M64E$($-(05(H,BD@3D]4($Y53$P-"BQ)34%'149)3$4@5D%20TA!4C(H,3 I
M($Y/5"!.54Q,#0HL349'240@5D%20TA!4C(H,3 I($Y/5"!.54Q,#0HL349'
M4$X@5D%20TA!4C(H-# I($Y/5"!.54Q,#0HL0T].4U1204E.5"!02U]04D]$
M(%!224U!4ED@2T59("A)3E1)1"D-"BD-"B\-"@T*0U)%051%(%1!0DQ%($U!
M24Y34$5##0HH24Y4240@5D%20TA!4C(H-# I($Y/5"!.54Q,#0HL2$12240@
M5D%20TA!4C(H,3 I($Y/5"!.54Q,#0HL0D]$64E$(%9!4D-(05(R*#$P*2!.
M3U0@3E5,3 T*+$1)4U!,05E/4D1%4B!.54U"15(@3D]4($Y53$P-"BQ#3TY3
M5%)!24Y4(%!+7TU!24Y34$5#(%!224U!4ED@2T59("A)3E1)1"P@2$12240L
M($)/1%E)1"D-"BD-"B\-"@T*0U)%051%(%1!0DQ%(%-,4U!23T0-"BA04D]$
M54-43E5-($Y534)%4B@Q,BPP*2!.3U0@3E5,3 T*+%-,4T-/1$4@5D%20TA!
M4C(H,3(I#0HL349'0T]$12!605)#2$%2,B@V*0T*+$U&1U!.(%9!4D-(05(R
M*#0P*0T*+$-!5$5'3U)9240@5D%20TA!4C(H."D-"BQ714)04DE#14%-5"!.
M54U"15(H,34L,BD-"BQ15%E)3E-43T-+($Y534)%4B@Q,"PR*2!$149!54Q4
M(# @3D]4($Y53$P-"BQ!1$1%1$1!5$54($1!5$4-"BQ-3T1)1DE%1$1!5$54
M($1!5$4-"BQ15%E/3D]21$52($Y534)%4B@Q,"PR*2!$149!54Q4(# @3D]4
M($Y53$P-"BQ)3E1)1"!605)#2$%2,B@T,"D-"BQ714)#3U-4($Y534)%4B@Q
M-2PR*0T*+%-(3U=714)93B!.54U"15(H,2PP*2!$149!54Q4(#$-"BQ#3TY3
M5%)!24Y4(%!+7U-,4U!23T0@4%))34%262!+15D@*%!23T150U1.54TI#0HI
M#0HO#0H-"D-214%412!404),12!,2U507TU&1U!.#0HH24Y4240@5D%20TA!
M4C(H-# I($Y/5"!.54Q,#0HL349'240@5D%20TA!4C(H,3 I($1%1D%53%0@
M)U=(3R<@3D]4($Y53$P-"BQ!3E]-1D=03B!605)#2$%2,B@T,"D@3D]4($Y5
M3$P-"BQ#3TY35%)!24Y4(%!+7TQ+55!?349'4$X@4%))34%262!+15D@*$E.
,5$E$*0T**0T*+PT*
`
end