bibi92
asked on
optimize the query for reduce cpu_time
Hello,
How can I optimize the query for reduce CPU time :
SET STATISTICS TIME ON
exec sp_executesql N'select N0."TESTID",N0."TESTNAME", N0."SYMTES T2",N0."SY MTEST3",N0 ."SYMTEST4 ",N0."SYMT EST5",N0." SYMTEST1", N0."CCY",N 0."COUNTRY ",N0."EXCH GID",N0."S TATUS",N0. "MKTGROUP" from "dbo"."TESTURITY" N0
where N0."TESTID" in
(@p0,@p1,@p2,@p3,@p4,@p5,@ p6,@p7,@p8 ,@p9,@p10, @p11,@p12, @p13,@p14, @p15,@p16, @p17,@p18, @p19,@p20, @p21,@p22, @p23,@p24, @p25,@p26, @p27,@p28, @p29,@p30, @p31,@p32, @p33,@p34, @p35,@p36, @p37,@p38, @p39,@p40, @p41,@p42, @p43,@p44, @p45,@p46, @p47,@p48, @p49,@p50, @p51,@p52, @p53,@p54, @p55,@p56, @p57,@p58, @p59,@p60, @p61,@p62, @p63,@p64, @p65,@p66, @p67,@p68, @p69,@p70, @p71,@p72, @p73,@p74, @p75,@p76, @p77,@p78, @p79,@p80, @p81,@p82, @p83,@p84, @p85,@p86, @p87,@p88) ',N'@p0
nvarchar(50),@p1 nvarchar(50),@p2 nvarchar(50),@p3 nvarchar(50),@p4 nvarchar(50),@p5 nvarchar(50),@p6 nvarchar(50),@p7 nvarchar(50),@p8 nvarchar(50),@p9 nvarchar(50),@p10 nvarchar(50),@p11 nvarchar(50),@p12 nvarchar(50),@p13 nvarchar(50),@p14 nvarchar(50),@p15 nvarchar(50),@p16 nvarchar(50),@p17 nvarchar(50),@p18 nvarchar(50),@p19 nvarchar(50),@p20 nvarchar(50),@p21 nvarchar(50),@p22 nvarchar(50),@p23 nvarchar(50),@p24 nvarchar(50),@p25 nvarchar(50),@p26 nvarchar(50),@p27 nvarchar(50),@p28 nvarchar(50),@p29 nvarchar(50),@p30 nvarchar(50),@p31 nvarchar(50),@p32 nvarchar(50),@p33 nvarchar(50),@p34 nvarchar(50),@p35 nvarchar(50),@p36 nvarchar(50),@p37 nvarchar(50),@p38 nvarchar(50),@p39 nvarchar(50),@p40 nvarchar(50),@p41 nvarchar(50),@p42 nvarchar(50),@p43 nvarchar(50),@p44 nvarchar(50),@p45 nvarchar(50),@p46 nvarchar(50),@p47 nvarchar(50),@p48 nvarchar(50),@p49 nvarchar(50),@p50 nvarchar(50),@p51 nvarchar(50),@p52 nvarchar(50),@p53 nvarchar(50),@p54 nvarchar(50),@p55 nvarchar(50),@p56 nvarchar(50),@p57 nvarchar(50),@p58 nvarchar(50),@p59 nvarchar(50),@p60 nvarchar(50),@p61 nvarchar(50),@p62 nvarchar(50),@p63 nvarchar(50),@p64 nvarchar(50),@p65 nvarchar(50),@p66 nvarchar(50),@p67 nvarchar(50),@p68 nvarchar(50),@p69 nvarchar(50),@p70 nvarchar(50),@p71 nvarchar(50),@p72 nvarchar(50),@p73 nvarchar(50),@p74 nvarchar(50),@p75 nvarchar(50),@p76 nvarchar(50),@p77 nvarchar(50),@p78 nvarchar(50),@p79 nvarchar(50),@p80 nvarchar(50),@p81 nvarchar(50),@p82 nvarchar(50),@p83 nvarchar(50),@p84 nvarchar(50),@p85 nvarchar(50),@p86 nvarchar(50),@p87 nvarchar(50),@p88 nvarchar(50)',@p0=N'000100 000000AI', @p1=N'0001 00000000BN P',@p2=N'0 0010000000 0CS',@p3=N '000100000 000EI',@p4 =N'0001000 00000GLE', @p5=N'0001 00000000SC R',@p6=N'0 0010000000 0UG',@p7=N '000200002 000AFX',@p 8=N'000200 002000DUE' ,@p9=N'000 200002000G MM',@p10=N '000300002 000VNI',@p 11=N'00030 0002000VNL ',@p12=N'0 0050000200 0BZU',@p13 =N'0005000 02000G',@p 14=N'00050 0002000GTK ',@p15=N'0 0050000200 0TIT',@p16 =N'0007000 02000EBS', @p17=N'000 700002000O MV',@p18=N '000700002 000RBI',@p 19=N'00070 0002000TKA ',@p20=N'0 0170000000 0AAK',@p21 =N'0017000 00000AOI', @p22=N'001 700000000A RCM',@p23= N'00170000 0000ASSA B',@p24=N'001700000000ATCO A',@p25=N'001700000000BALD B',@p26=N'001700000000BIGG TR B',@p27=N'001700000000BTS B',@p28=N'001700000000CASO ',@p29=N'0 0170000000 0CZON B',@p30=N'001700000000DORO ',@p31=N'0 0170000000 0ECEX',@p3 2=N'001700 000000EKTA B',@p33=N'001700000000ELOS B',@p34=N'001700000000ELUX B',@p35=N'001700000000ERIC B',@p36=N'001700000000HEXA B',@p37=N'001700000000HM B',@p38=N'001700000000INVE B',@p39=N'001700000000KAN' ,@p40=N'00 1700000000 LATO B',@p41=N'001700000000LOOM B',@p42=N'001700000000MEPR ',@p43=N'0 0170000000 0MVIR B',@p44=N'001700000000NDA SEK',@p45=N'001700000000NO BI',@p46=N '001700000 000OASM',@ p47=N'0017 00000000OP US',@p48=N '001700000 000ORI SDB',@p49=N'001700000000PA R',@p50=N' 0017000000 00PREC',@p 51=N'00170 0000000RSO F B',@p52=N'001700000000SAND ',@p53=N'0 0170000000 0SCA B',@p54=N'001700000000SEAM ',@p55=N'0 0170000000 0TESTU B',@p56=N'001700000000SSAB A',@p57=N'001700000000STAR ',@p58=N'0 0170000000 0STFY',@p5 9=N'001700 000000SWED A',@p60=N'001700000000SWMA ',@p61=N'0 0170000000 0TEL2 B',@p62=N'001700000000TLSN ',@p63=N'0 0170000000 0TRAD',@p6 4=N'001700 000000TREL B',@p65=N'001700000000UNIB SDB',@p66=N'001700000000VO LV B',@p67=N'001800002000AUTN ',@p68=N'0 0180000200 0EFGN',@p6 9=N'002200 000000BME' ,@p70=N'00 2200000000 REE',@p71= N'00220000 2000ROVI', @p72=N'003 400002000B T.A',@p73= N'00340000 2000HFD',@ p74=N'0034 00002000IT RK',@p75=N '003400002 000LNTA',@ p76=N'0034 00002000SB ER',@p77=N '005500000 000CARL B',@p78=N'005500000000COLO B',@p79=N'005500000000DANS KE',@p80=N '005500000 000DNORD', @p81=N'005 500000000N OVO B',@p82=N'005500000000TOP' ,@p83=N'00 5500000000 VWS',@p84= N'00650000 2000MTN',@ p85=N'0065 00002000OM N',@p86=N' 0065000020 00PPC',@p8 7=N'007200 002000BAKK A',@p88=N' 0072000020 00BWLPG'
CPU time = 14227 ms, elapsed time = 2296 ms.
Thanks
How can I optimize the query for reduce CPU time :
SET STATISTICS TIME ON
exec sp_executesql N'select N0."TESTID",N0."TESTNAME",
where N0."TESTID" in
(@p0,@p1,@p2,@p3,@p4,@p5,@
nvarchar(50),@p1 nvarchar(50),@p2 nvarchar(50),@p3 nvarchar(50),@p4 nvarchar(50),@p5 nvarchar(50),@p6 nvarchar(50),@p7 nvarchar(50),@p8 nvarchar(50),@p9 nvarchar(50),@p10 nvarchar(50),@p11 nvarchar(50),@p12 nvarchar(50),@p13 nvarchar(50),@p14 nvarchar(50),@p15 nvarchar(50),@p16 nvarchar(50),@p17 nvarchar(50),@p18 nvarchar(50),@p19 nvarchar(50),@p20 nvarchar(50),@p21 nvarchar(50),@p22 nvarchar(50),@p23 nvarchar(50),@p24 nvarchar(50),@p25 nvarchar(50),@p26 nvarchar(50),@p27 nvarchar(50),@p28 nvarchar(50),@p29 nvarchar(50),@p30 nvarchar(50),@p31 nvarchar(50),@p32 nvarchar(50),@p33 nvarchar(50),@p34 nvarchar(50),@p35 nvarchar(50),@p36 nvarchar(50),@p37 nvarchar(50),@p38 nvarchar(50),@p39 nvarchar(50),@p40 nvarchar(50),@p41 nvarchar(50),@p42 nvarchar(50),@p43 nvarchar(50),@p44 nvarchar(50),@p45 nvarchar(50),@p46 nvarchar(50),@p47 nvarchar(50),@p48 nvarchar(50),@p49 nvarchar(50),@p50 nvarchar(50),@p51 nvarchar(50),@p52 nvarchar(50),@p53 nvarchar(50),@p54 nvarchar(50),@p55 nvarchar(50),@p56 nvarchar(50),@p57 nvarchar(50),@p58 nvarchar(50),@p59 nvarchar(50),@p60 nvarchar(50),@p61 nvarchar(50),@p62 nvarchar(50),@p63 nvarchar(50),@p64 nvarchar(50),@p65 nvarchar(50),@p66 nvarchar(50),@p67 nvarchar(50),@p68 nvarchar(50),@p69 nvarchar(50),@p70 nvarchar(50),@p71 nvarchar(50),@p72 nvarchar(50),@p73 nvarchar(50),@p74 nvarchar(50),@p75 nvarchar(50),@p76 nvarchar(50),@p77 nvarchar(50),@p78 nvarchar(50),@p79 nvarchar(50),@p80 nvarchar(50),@p81 nvarchar(50),@p82 nvarchar(50),@p83 nvarchar(50),@p84 nvarchar(50),@p85 nvarchar(50),@p86 nvarchar(50),@p87 nvarchar(50),@p88 nvarchar(50)',@p0=N'000100
CPU time = 14227 ms, elapsed time = 2296 ms.
Thanks
ASKER
testid VARCHAR(50) thanks
Hi,
add this hint with( maxdop 1 ) and see what the statistics are like then.
Now why the emphasis on cpu?
Regards
David
add this hint with( maxdop 1 ) and see what the statistics are like then.
Now why the emphasis on cpu?
Regards
David
Assuming that you can do it as you are selecting them, put your IDs to search for in a Table, say #tempTable
and use [...] FROM TESTURITY No INNER JOIN #tempTable t ON No.TESTID = t.TESTID
and use [...] FROM TESTURITY No INNER JOIN #tempTable t ON No.TESTID = t.TESTID
First step for me is to make this a bit more readable. Please verify that this still works (produces expected results). While you're at it, please time this version's execution.
SET STATISTICS TIME ON
exec sp_executesql N'select N0.TESTID, N0.TESTNAME, N0.SYMTEST2, N0.SYMTEST3, N0.SYMTEST4, N0.SYMTEST5,
N0.SYMTEST1, N0.CCY,N0.COUNTRY, N0.EXCHGID, N0.STATUS,N0.MKTGROUP
from dbo.TESTURITY N0
where N0.TESTID in
(@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20,@p21,@p22,@p23,@p24,@p25,@p26,@p27,@p28,@p29,@p30,@p31,@p32,@p33,@p34,@p35,@p36,@p37,@p38,@p39,@p40,@p41,@p42,@p43,@p44,@p45,@p46,@p47,@p48,@p49,@p50,@p51,@p52,@p53,@p54,@p55,@p56,@p57,@p58,@p59,@p60,@p61,@p62,@p63,@p64,@p65,@p66,@p67,@p68,@p69,@p70,@p71,@p72,@p73,@p74,@p75,@p76,@p77,@p78,@p79,@p80,@p81,@p82,@p83,@p84,@p85,@p86,@p87,@p88)'
,N'@p0 nvarchar(50), @p1 nvarchar(50), @p2 nvarchar(50), @p3 nvarchar(50), @p4 nvarchar(50),
@p5 nvarchar(50), @p6 nvarchar(50), @p7 nvarchar(50), @p8 nvarchar(50), @p9 nvarchar(50),
@p10 nvarchar(50), @p11 nvarchar(50), @p12 nvarchar(50), @p13 nvarchar(50), @p14 nvarchar(50),
@p15 nvarchar(50), @p16 nvarchar(50), @p17 nvarchar(50), @p18 nvarchar(50), @p19 nvarchar(50),
@p20 nvarchar(50), @p21 nvarchar(50), @p22 nvarchar(50), @p23 nvarchar(50), @p24 nvarchar(50),
@p25 nvarchar(50), @p26 nvarchar(50), @p27 nvarchar(50), @p28 nvarchar(50), @p29 nvarchar(50),
@p30 nvarchar(50), @p31 nvarchar(50), @p32 nvarchar(50), @p33 nvarchar(50), @p34 nvarchar(50),
@p35 nvarchar(50), @p36 nvarchar(50), @p37 nvarchar(50), @p38 nvarchar(50), @p39 nvarchar(50),
@p40 nvarchar(50), @p41 nvarchar(50), @p42 nvarchar(50), @p43 nvarchar(50), @p44 nvarchar(50),
@p45 nvarchar(50), @p46 nvarchar(50), @p47 nvarchar(50), @p48 nvarchar(50), @p49 nvarchar(50),
@p50 nvarchar(50), @p51 nvarchar(50), @p52 nvarchar(50), @p53 nvarchar(50), @p54 nvarchar(50),
@p55 nvarchar(50), @p56 nvarchar(50), @p57 nvarchar(50), @p58 nvarchar(50), @p59 nvarchar(50),
@p60 nvarchar(50), @p61 nvarchar(50), @p62 nvarchar(50), @p63 nvarchar(50), @p64 nvarchar(50),
@p65 nvarchar(50), @p66 nvarchar(50), @p67 nvarchar(50), @p68 nvarchar(50), @p69 nvarchar(50),
@p70 nvarchar(50), @p71 nvarchar(50), @p72 nvarchar(50), @p73 nvarchar(50), @p74 nvarchar(50),
@p75 nvarchar(50), @p76 nvarchar(50), @p77 nvarchar(50), @p78 nvarchar(50), @p79 nvarchar(50),
@p80 nvarchar(50), @p81 nvarchar(50), @p82 nvarchar(50), @p83 nvarchar(50), @p84 nvarchar(50),
@p85 nvarchar(50), @p86 nvarchar(50), @p87 nvarchar(50), @p88 nvarchar(50)'
,@p0=N'000100000000AI', @p1=N'000100000000BNP', @p2=N'000100000000CS', @p3=N'000100000000EI',
@p4=N'000100000000GLE', @p5=N'000100000000SCR', @p6=N'000100000000UG', @p7=N'000200002000AFX',
@p8=N'000200002000DUE', @p9=N'000200002000GMM', @p10=N'000300002000VNI', @p11=N'000300002000VNL',
@p12=N'000500002000BZU', @p13=N'000500002000G', @p14=N'000500002000GTK', @p15=N'000500002000TIT',
@p16=N'000700002000EBS', @p17=N'000700002000OMV', @p18=N'000700002000RBI', @p19=N'000700002000TKA',
@p20=N'001700000000AAK', @p21=N'001700000000AOI', @p22=N'001700000000ARCM', @p23=N'001700000000ASSA B',
@p24=N'001700000000ATCO A', @p25=N'001700000000BALD B', @p26=N'001700000000BIGG TR B', @p27=N'001700000000BTS B',
@p28=N'001700000000CASO', @p29=N'001700000000CZON B', @p30=N'001700000000DORO', @p31=N'001700000000ECEX',
@p32=N'001700000000EKTA B', @p33=N'001700000000ELOS B', @p34=N'001700000000ELUX B', @p35=N'001700000000ERIC B',
@p36=N'001700000000HEXA B', @p37=N'001700000000HM B', @p38=N'001700000000INVE B', @p39=N'001700000000KAN',
@p40=N'001700000000LATO B', @p41=N'001700000000LOOM B', @p42=N'001700000000MEPR', @p43=N'001700000000MVIR B',
@p44=N'001700000000NDA SEK', @p45=N'001700000000NOBI', @p46=N'001700000000OASM', @p47=N'001700000000OPUS',
@p48=N'001700000000ORI SDB', @p49=N'001700000000PAR', @p50=N'001700000000PREC', @p51=N'001700000000RSOF B',
@p52=N'001700000000SAND', @p53=N'001700000000SCA B', @p54=N'001700000000SEAM', @p55=N'001700000000TESTU B',
@p56=N'001700000000SSAB A', @p57=N'001700000000STAR', @p58=N'001700000000STFY', @p59=N'001700000000SWED A',
@p60=N'001700000000SWMA', @p61=N'001700000000TEL2 B', @p62=N'001700000000TLSN', @p63=N'001700000000TRAD',
@p64=N'001700000000TREL B', @p65=N'001700000000UNIB SDB', @p66=N'001700000000VOLV B', @p67=N'001800002000AUTN',
@p68=N'001800002000EFGN', @p69=N'002200000000BME', @p70=N'002200000000REE', @p71=N'002200002000ROVI',
@p72=N'003400002000BT.A', @p73=N'003400002000HFD', @p74=N'003400002000ITRK', @p75=N'003400002000LNTA',
@p76=N'003400002000SBER', @p77=N'005500000000CARL B', @p78=N'005500000000COLO B', @p79=N'005500000000DANSKE',
@p80=N'005500000000DNORD', @p81=N'005500000000NOVO B', @p82=N'005500000000TOP', @p83=N'005500000000VWS',
@p84=N'006500002000MTN', @p85=N'006500002000OMN', @p86=N'006500002000PPC', @p87=N'007200002000BAKKA', @p88=N'007200002000BWLPG'
The next step is to replace the parameterized values with string literals:
SET STATISTICS TIME ON
exec sp_executesql N'select N0.TESTID, N0.TESTNAME, N0.SYMTEST2, N0.SYMTEST3, N0.SYMTEST4, N0.SYMTEST5,
N0.SYMTEST1, N0.CCY,N0.COUNTRY, N0.EXCHGID, N0.STATUS,N0.MKTGROUP
from dbo.TESTURITY N0
where N0.TESTID in
("000100000000AI", "000100000000BNP", "000100000000CS", "000100000000EI",
"000100000000GLE", "000100000000SCR", "000100000000UG", "000200002000AFX",
"000200002000DUE", "000200002000GMM", "000300002000VNI", "000300002000VNL",
"000500002000BZU", "000500002000G", "000500002000GTK", "000500002000TIT",
"000700002000EBS", "000700002000OMV", "000700002000RBI", "000700002000TKA",
"001700000000AAK", "001700000000AOI", "001700000000ARCM", "001700000000ASSA B",
"001700000000ATCO A", "001700000000BALD B", "001700000000BIGG TR B", "001700000000BTS B",
"001700000000CASO", "001700000000CZON B", "001700000000DORO", "001700000000ECEX",
"001700000000EKTA B", "001700000000ELOS B", "001700000000ELUX B", "001700000000ERIC B",
"001700000000HEXA B", "001700000000HM B", "001700000000INVE B", "001700000000KAN",
"001700000000LATO B", "001700000000LOOM B", "001700000000MEPR", "001700000000MVIR B",
"001700000000NDA SEK", "001700000000NOBI", "001700000000OASM", "001700000000OPUS",
"001700000000ORI SDB", "001700000000PAR", "001700000000PREC", "001700000000RSOF B",
"001700000000SAND", "001700000000SCA B", "001700000000SEAM", "001700000000TESTU B",
"001700000000SSAB A", "001700000000STAR", "001700000000STFY", "001700000000SWED A",
"001700000000SWMA", "001700000000TEL2 B", "001700000000TLSN", "001700000000TRAD",
"001700000000TREL B", "001700000000UNIB SDB", "001700000000VOLV B", "001800002000AUTN",
"001800002000EFGN", "002200000000BME", "002200000000REE", "002200002000ROVI",
"003400002000BT.A", "003400002000HFD", "003400002000ITRK", "003400002000LNTA",
"003400002000SBER", "005500000000CARL B", "005500000000COLO B", "005500000000DANSKE",
"005500000000DNORD", "005500000000NOVO B", "005500000000TOP", "005500000000VWS",
"006500002000MTN", "006500002000OMN", "006500002000PPC", "007200002000BAKKA", "007200002000BWLPG")'
What is the performance of the non-parameterized version?
We can then test to see if the order of the string literals matters:
SET STATISTICS TIME ON
exec sp_executesql N'select N0.TESTID, N0.TESTNAME, N0.SYMTEST2, N0.SYMTEST3, N0.SYMTEST4, N0.SYMTEST5,
N0.SYMTEST1, N0.CCY,N0.COUNTRY, N0.EXCHGID, N0.STATUS,N0.MKTGROUP
from dbo.TESTURITY N0
where N0.TESTID in
("000100000000AI", "000100000000BNP", "000100000000CS", "000100000000EI", "000100000000GLE",
"000100000000SCR", "000100000000UG", "000200002000AFX", "000200002000DUE", "000200002000GMM",
"000300002000VNI", "000300002000VNL", "000500002000BZU", "000500002000G", "000500002000GTK",
"000500002000TIT", "000700002000EBS", "000700002000OMV", "000700002000RBI", "000700002000TKA",
"001700000000AAK", "001700000000AOI", "001700000000ARCM", "001700000000ASSA B", "001700000000ATCO A",
"001700000000BALD B", "001700000000BIGG TR B", "001700000000BTS B", "001700000000CASO", "001700000000CZON B",
"001700000000DORO", "001700000000ECEX", "001700000000EKTA B", "001700000000ELOS B", "001700000000ELUX B",
"001700000000ERIC B", "001700000000HEXA B", "001700000000HM B", "001700000000INVE B", "001700000000KAN",
"001700000000LATO B", "001700000000LOOM B", "001700000000MEPR", "001700000000MVIR B", "001700000000NDA SEK",
"001700000000NOBI", "001700000000OASM", "001700000000OPUS", "001700000000ORI SDB", "001700000000PAR",
"001700000000PREC", "001700000000RSOF B", "001700000000SAND", "001700000000SCA B", "001700000000SEAM",
"001700000000SSAB A", "001700000000STAR", "001700000000STFY", "001700000000SWED A", "001700000000SWMA",
"001700000000TEL2 B", "001700000000TESTU B", "001700000000TLSN", "001700000000TRAD", "001700000000TREL B",
"001700000000UNIB SDB", "001700000000VOLV B", "001800002000AUTN", "001800002000EFGN", "002200000000BME",
"002200000000REE", "002200002000ROVI", "003400002000BT.A", "003400002000HFD", "003400002000ITRK",
"003400002000LNTA", "003400002000SBER", "005500000000CARL B", "005500000000COLO B", "005500000000DANSKE",
"005500000000DNORD", "005500000000NOVO B", "005500000000TOP", "005500000000VWS", "006500002000MTN",
"006500002000OMN", "006500002000PPC", "007200002000BAKKA", "007200002000BWLPG" )'
ASKER
Where can I use option maxdop 0 ?
With the order of the string literals matters :
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 22 ms, elapsed time = 22 ms.
(1 row(s) affected)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
With the order of the string literals matters :
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 22 ms, elapsed time = 22 ms.
(1 row(s) affected)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Thats because the implecit conversion between nVarchar and Varchar requires CPU.
What version are you using, I belive this was fixed in SQL 2012. Is it by chance SQL 2008 R2
Quote from your top query:
"...N'@p0 nvarchar(50),@p1 nvarchar(50),@p2 nvarchar(50)..."
and
"testid VARCHAR(50)"
This conversion is stealing CPU from you. I had a similar case, and when testing I could see that it went away on SQL2012.
Regards Marten
What version are you using, I belive this was fixed in SQL 2012. Is it by chance SQL 2008 R2
Quote from your top query:
"...N'@p0 nvarchar(50),@p1 nvarchar(50),@p2 nvarchar(50)..."
and
"testid VARCHAR(50)"
This conversion is stealing CPU from you. I had a similar case, and when testing I could see that it went away on SQL2012.
Regards Marten
ASKER
It's SQL SERVER 2008 R2.
Thanks
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Altering is done on a test system, not production ofcource
@martenrune
What have you tried?
What have you tried?
Hi aikimark
I had a different system where the 3'd party program asked using a nVarchar value. The index was missing, fixed that on this column (a varchar). But to my suprise the index was Always scanned, not searched.
When I altered the column in the database (on a test Environment) the Query optimizer correctly started using index seeks instead of index Scans.
This to the expense of a bigger column was still a huge gain for the system. After getting a OK from the vendor we changed the column type on production to nVarchar, and rebuilt the index created earlier.
This case looks similar.
Regards Marten
I had a different system where the 3'd party program asked using a nVarchar value. The index was missing, fixed that on this column (a varchar). But to my suprise the index was Always scanned, not searched.
When I altered the column in the database (on a test Environment) the Query optimizer correctly started using index seeks instead of index Scans.
This to the expense of a bigger column was still a huge gain for the system. After getting a OK from the vendor we changed the column type on production to nVarchar, and rebuilt the index created earlier.
This case looks similar.
Regards Marten
@martenrune
I have suggested two different versions of the query. Have you run them? What were the results?
I have suggested two different versions of the query. Have you run them? What were the results?
@aikimark
I'm not the asker. I'm an expert with similar experience, sharing this to the asker. Suggesting a simple column change would answer if this works to save CPU cycles.
In your case you do and I quote:
"...replace the parameterized values with string literals".
Wich essentialy does the same thing.
The Query optimizer is clever enough these days to use the where clauses in the most efficient way.
I have NOT examined the Query, for that I would like execution plans, statistics and index definitions. I merely suggest a simple test changing a column to nVarchar, and retrying the question.
I sense that you might be annoyed with my suggestion. Thats not for you to decide, but the asker to try various suggestions. With this answer to you I hope Everything is resolved.
I don't Think this thread is the Place to question others suggestions.
Best regards Marten
I'm not the asker. I'm an expert with similar experience, sharing this to the asker. Suggesting a simple column change would answer if this works to save CPU cycles.
In your case you do and I quote:
"...replace the parameterized values with string literals".
Wich essentialy does the same thing.
The Query optimizer is clever enough these days to use the where clauses in the most efficient way.
I have NOT examined the Query, for that I would like execution plans, statistics and index definitions. I merely suggest a simple test changing a column to nVarchar, and retrying the question.
I sense that you might be annoyed with my suggestion. Thats not for you to decide, but the asker to try various suggestions. With this answer to you I hope Everything is resolved.
I don't Think this thread is the Place to question others suggestions.
Best regards Marten
sorry, Marten. I copy/pasted the wrong ID.
@bibi92
I have suggested two different versions of the query. Have you run them? What were the results?
I have suggested two different versions of the query. Have you run them? What were the results?
@aikimark
Happens to all of us :-)
Regards Marten
Happens to all of us :-)
Regards Marten
ASKER
Thanks regards
Regards Marten