December 26, 2007 at 10:41 am
Hi
When users accesssing from application, In the current activity window i can see sp_cursorprepexec and sp_cursorunprepare statements.This are taking too long to execute. how can they be avoided?
December 26, 2007 at 10:51 am
Without seeing the offending code, the short answer would be simply to rewrite the code so that it's performance enabled. Step 1... avoid cursors. Step 2... avoid 99.9% of loops. Step 3... avoid "hidden RBAR" in CTE's, correlated subqueries, and functions.
I realize that these two spocs sometimes appear even when setbased code is used... usually, if the code is taking too long, it simply means the code wasn't written with performance in mind and/or the tables are not properly indexed.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2007 at 2:46 am
or you might be using navision !!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 28, 2007 at 11:45 am
Navision will find and rewrite performance challenged code?
Ok, since you brougt it up, I'll bite... what does Navision do?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 31, 2007 at 10:47 am
sadly navision, a product from microsoft, uses almost exculsively client side cursors executing ad-hoc and dynamic sql. So if you're trying to tune this ERP system expect to see lots of sp_execute commands!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 14, 2008 at 9:48 pm
Are you using parameterised code ?
//Not parameterised
SqlDataReader myReader;
SqlCommand cmd = new SqlCommand("SELECT column1 FROM table1 WHERE colum1 = 'xyz'", sqlConn);
myReader = cmd.ExecuteReader();
----------------------------------
//parameterised
SqlDataReader myReader;
SqlCommand cmd = new SqlCommand("SELECT column1 FROM table1 WHERE column1 = @Col1Param", sqlConn);
cmd.Parameters.Add("@Col1Param", "xyz");
myReader = cmd.ExecuteReader();
May 21, 2008 at 3:54 am
Hi we have SQL 2000 Standard Edition in our environment and we are using Siebel as client application. when the users try to exeucte the queries it is taking much time and the application is hanging sometimes. please find the below query from profiler. kindly suggest what might be the reason and how to improve the performance.
declare @P1 int
set @P1=1222
declare @P2 int
set @P2=180152998
declare @P3 int
set @P3=16
declare @P4 int
set @P4=1
declare @P5 int
set @P5=1
exec sp_cursorprepexec @P1 output, @P2 output, N'@P1 varchar(30),@P2 varchar(100),@P3 varchar(100),@P4 varchar(100),@P5 varchar(100),@P6 varchar(100),@P7 varchar(100),@P8 varchar(100),@P9 varchar(100),@P10 varchar(100),@P11 varchar(100),@P12 varchar(100),@P13 varchar(100),@P14 varchar(100),@P15 varchar(100),@P16 varchar(100),@P17 varchar(100),@P18 varchar(100),@P19 varchar(100),@P20 varchar(100),@P21 varchar(100),@P22 varchar(100),@P23 varchar(100),@P24 varchar(100),@P25 varchar(100),@P26 varchar(100),@P27 varchar(100),@P28 varchar(100),@P29 varchar(100),@P30 varchar(100),@P31 varchar(100),@P32 varchar(100),@P33 varchar(100),@P34 varchar(100),@P35 varchar(100),@P36 varchar(100),@P37 varchar(30),@P38 varchar(30),@P39 varchar(30),@P40 varchar(30),@P41 varchar(30)', N'SELECT
T1.CONFLICT_ID,
CONVERT (VARCHAR (10),T1.LAST_UPD, 101) + '' '' + CONVERT (VARCHAR (10),T1.LAST_UPD, 8),
CONVERT (VARCHAR (10),T1.CREATED, 101) + '' '' + CONVERT (VARCHAR (10),T1.CREATED, 8),
T1.LAST_UPD_BY,
T1.CREATED_BY,
T1.MODIFICATION_NUM,
T1.ROW_ID,
T12.NAME,
T11.ATTRIB_47,
T15.NAME,
T1.ASGN_POSTN_ID,
T1.CARRIER_CD,
T10.DESC_TEXT,
T1.CARRIER_PRIOR_CD,
T8.ATTRIB_01,
T12.X_IUK_ACCNT_BU_LOC,
CONVERT (VARCHAR (10),T7.CREATED, 101) + '' '' + CONVERT (VARCHAR (10),T7.CREATED, 8),
T14.ATTRIB_43,
T14.ATTRIB_46,
T2.ADDR_LINE_2,
T14.ATTRIB_42,
T1.RECD_COMMENTS,
T6.ORDER_ID,
T4.ORDER_ITEM_ID,
T7.ORDER_NUM,
T7.ORDER_TYPE_ID,
CONVERT (VARCHAR (10),T1.PLND_SHIP_DT, 101) + '' '' + CONVERT (VARCHAR (10),T1.PLND_SHIP_DT, 8),
CONVERT (VARCHAR (10),T1.RECD_DT, 101) + '' '' + CONVERT (VARCHAR (10),T1.RECD_DT, 8),
T7.SR_ID,
CONVERT (VARCHAR (10),T1.SHIP_DT, 101) + '' '' + CONVERT (VARCHAR (10),T1.SHIP_DT, 8),
CONVERT (VARCHAR (10),T1.REQ_SHIP_DT, 101) + '' '' + CONVERT (VARCHAR (10),T1.REQ_SHIP_DT, 8),
T1.SHIP_INVLOC_ID,
T3.NAME,
T2.ADDR,
T2.CITY,
T13.FST_NAME,
T13.LAST_NAME,
T1.SHIP_ADDR_ID,
T2.STATE,
T2.ZIPCODE,
T1.SHIP_METH_CD,
T1.SHIP_NUM,
T1.STATUS_CD,
T8.ATTRIB_02,
T1.FRGHT_AMT_CURCY_CD,
T1.INSUR_AMT_CURCY_CD,
T1.INSUR_VAL_CURCY_CD,
T5.LOGIN,
T1.WAYBILL_NUM,
T9.NAME,
T2.COUNTRY
FROM
dbo.S_SHIPMENT T1
INNER JOIN dbo.S_ADDR_ORG T2 ON T1.SHIP_ADDR_ID = T2.ROW_ID
INNER JOIN dbo.S_INVLOC T3 ON T1.SHIP_INVLOC_ID = T3.ROW_ID
INNER JOIN dbo.S_ORDPART_MVMT T4 ON T1.ROW_ID = T4.SHIPMENT_ID
INNER JOIN dbo.S_USER T5 ON T1.LAST_UPD_BY = T5.PAR_ROW_ID
INNER JOIN dbo.S_ORDER_ITEM T6 ON T4.ORDER_ITEM_ID = T6.ROW_ID
INNER JOIN dbo.S_ORDER T7 ON T6.ORDER_ID = T7.ROW_ID
INNER JOIN dbo.S_ORDER_ITEM_X T8 ON T6.ROW_ID = T8.PAR_ROW_ID
INNER JOIN dbo.S_ORDER_TYPE T9 ON T7.ORDER_TYPE_ID = T9.ROW_ID
INNER JOIN dbo.S_SRV_REQ T10 ON T7.SR_ID = T10.ROW_ID
INNER JOIN dbo.S_SRV_REQ_X T11 ON T7.SR_ID = T11.ROW_ID
INNER JOIN dbo.S_ORG_EXT T12 ON T2.OU_ID = T12.PAR_ROW_ID
INNER JOIN dbo.S_CONTACT T13 ON T6.SHIP_CON_ID = T13.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT_X T14 ON T6.SHIP_CON_ID = T14.ROW_ID
LEFT OUTER JOIN dbo.S_POSTN T15 ON T1.ASGN_POSTN_ID = T15.PAR_ROW_ID
WHERE
(T12.X_IUK_ACCNT_BU_LOC = @P1 AND ((T3.NAME LIKE @P2 OR T3.NAME LIKE @P3 OR T3.NAME LIKE @P4 OR T3.NAME LIKE @P5) AND UPPER(T3.NAME) LIKE UPPER(@P6) OR (T3.NAME LIKE @P7 OR T3.NAME LIKE @P8 OR T3.NAME LIKE @P9 OR T3.NAME LIKE @P10) AND UPPER(T3.NAME) LIKE UPPER(@P11) OR (T3.NAME LIKE @P12 OR T3.NAME LIKE @P13 OR T3.NAME LIKE @P14 OR T3.NAME LIKE @P15) AND UPPER(T3.NAME) LIKE UPPER(@P16) OR (T3.NAME LIKE @P17 OR T3.NAME LIKE @P18 OR T3.NAME LIKE @P19 OR T3.NAME LIKE @P20) AND UPPER(T3.NAME) LIKE UPPER(@P21) OR (T3.NAME LIKE @P22 OR T3.NAME LIKE @P23 OR T3.NAME LIKE @P24 OR T3.NAME LIKE @P25) AND UPPER(T3.NAME) LIKE UPPER(@P26) OR (T3.NAME LIKE @P27 OR T3.NAME LIKE @P28 OR T3.NAME LIKE @P29 OR T3.NAME LIKE @P30) AND UPPER(T3.NAME) LIKE UPPER(@P31) OR (T3.NAME LIKE @P32 OR T3.NAME LIKE @P33 OR T3.NAME LIKE @P34 OR T3.NAME LIKE @P35) AND UPPER(T3.NAME) LIKE UPPER(@P36)) AND (T1.STATUS_CD LIKE @P37 OR T1.STATUS_CD LIKE @P38 OR T1.STATUS_CD LIKE @P39 OR T1.STATUS_CD LIKE @P40) AND UPPER(T1.STATUS_CD) = UPPER(@P41))
ORDER BY
T1.SHIP_NUM', @P3 output, @P4 output, @P5 output, 'US', 'be%', 'Be%', 'bE%', 'BE%', 'BellSouth%AMW%', 'as%', 'As%', 'aS%', 'AS%', 'Asset Management%AMW%', 'se%', 'Se%', 'sE%', 'SE%', 'Sears%AMW%', 'em%', 'Em%', 'eM%', 'EM%', 'EMBARQ%AMW%', 'ke%', 'Ke%', 'kE%', 'KE%', 'Keyspan%AMW%', 've%', 'Ve%', 'vE%', 'VE%', 'Verizon%AMW%', 'ge%', 'Ge%', 'gE%', 'GE%', 'GE TRANSPORTATION%AMW%', 'op%', 'Op%', 'oP%', 'OP%', 'Open'
select @P1, @P2, @P3, @P4, @P5
Thanks
Kum
May 21, 2008 at 7:16 pm
15 table join with a non-indexable WHERE clause and a killer ORDER BY and we wonder why this code takes a bit to run? First thing to do would to break the query up to return the smallest possible result set of the critical driving information and then use that result to get the rest of the related information. Getting the query to use Index SEEKS would be a big help, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2008 at 5:06 am
GOD how I LOVE packaged app vendors!!! They make me soooo much money dealing with their horrid performance! 😀 Part of the problem is that they will build a set of objects and code that can work on 3-5 database platforms. You simply CANNOT optimize much in that situation.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 22, 2008 at 7:09 am
Hi Jeff Thanks for the reply. I am attaching few more Qureries from the profiler. Please check it and kinldy suggest what might be the reason for the slowness/hanging. we are using SQL Standard Edition. Is the problem with SQL edition or some other reason. please suggest.
Thanks
Kum
May 22, 2008 at 8:54 am
Kuma, that is an AWFUL lot of free help you are seeking. Those are very complex queries, the table and indexing structure is unknown, data volumes and distribution is unknown, etc. It could well take days to analyze and tune those queries. Forums are best for small, targeted assistance with a specific issue. Sound like you need to hire someone to provide you with some professional performance enhancement work.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 22, 2008 at 6:30 pm
No problem... quick look says they all suffer from the same problems and all are in need of a major rewrite.
Heh... Kevin... I'm right there with you... I've recently been "continued" with a company to do just that... replace the crap code that a 3rd party wrote. They "continued" me because I gave them a near freebie ayear ago... took a process that did dupe check across 93 databases (4 mega-rows each) that used to take 10-24 hours of constant baby sitting (it would many times fail even though it was only doing 62 of the databases) and turned it into a job (actually, kind'a slow for me) that takes 11-17 minutes and hasn't failed at all in the year that it's been running.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 27, 2008 at 1:12 pm
Wow, megajoins.
Do the joinable fields used in the query have an index? (the id fields)
Do the queryfilter fields have an index?
A:
Perhaps an index on T1.OWNER_EMP_ID and an index on T2.VAL might help. (equals)
Performance: using != disallows indexseeks.
((T1.SR_TYPE_CD != ''Internal'' AND T2.VAL = @P2) AND
(T1.OWNER_EMP_ID = @P3) AND
(T1.TEMPLATE_FLG != ''Y''))
B:
Index on T1.BU_ID and T1.ASSET_NUM might help.
Performance: wildcard at the second character might force a table scan.
C: same as B
D: index on T1.SERIAL_NUM might help
E: same as D
F: looks better, lots of equal operators.
Is there a minimum for RPR_PROMISE_DT ? Otherwise it might go back crawling in ancient history.
The others are using not equal again (!=)
March 18, 2009 at 9:06 am
Hi
I stumbled across this thread and have a little advice.
The queries look very Siebel to me. There are two approaches to tackle Siebel performance problems but they both start from the same place.
Get the execution plan and carefully analyse it.
A "simple" approach to this is to do a "set showplan_all on" and put the output into Excel.
Go through it looking for hash matches and scans (clustered index scans and index scans).
Look carefully at these tables. You will also see what indices are being used for the execution plan.
I don't know if you are comfortable with showplan output but persevere - it has a lot of good stuff in it and when you pop it into Excel you can highlight areas that you want to look at and get rid of bits that are uninteresting.
A DBA only approach is:
Make sure all your indices on the base tables are defragmented. Use ALTER INDEX ... REBUILD or DBCC DBREINDEX to achieve this. This is a heavy process and should be done when the system is quiescent. Please don't waste time on the EIM tables.
Make sure all your statistics are up-to-date. For my Siebel system I like FULLSCAN and so does Siebel. This is also a heavy task.
Only when you are sure that it is all up-to-date do you think about adding indices and statistics to the problem.
Siebel Tools does not support INCLUDE columns in an index. You may have to manually add these to improve performance and remove Bookmark Lookups from your execution plan.
Please keep your index names less than or equal to 30 characters or your dev-2-prod will fail. It's a bit of a silly limitation in the Siebel dev-2-prod utility.
The other approach:
Speak to your Siebel application people about the queries in question. Are they using unnecessary base tables? This can easily mess up the execution plan and cost a lot of time.
Please note that I am coming from a Siebel 7 & 8 perspective on SQL Server 2000 and 2005.
Good luck
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply