ARSLIST,
The issues ended up being the Indexes on the forms, Remedy
thought it has certain Indexes and they did NOT match what MS SQL
thought.
Fixed and tested. Thanks, Doug
________________________________
From: Tanner, Doug
Sent: Friday, October 03, 2008 8:01 AM
To: [email protected]
Subject: RE: SQL Delete - Slow on Import - Thoughts
Thanks to all on the list who responded, you validated my thoughts and
gave me another. Working with DBA's this morning. I will post the fix
:-)
Doug
________________________________
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Joe DeSouza
Sent: Thursday, October 02, 2008 2:37 PM
To: [email protected]
Subject: Re: SQL Delete - Slow on Import - Thoughts
Are your transaction logs full or getting there? Try flushing them.
Joe
----- Original Message ----
From: "Tanner, Doug" <[EMAIL PROTECTED]>
To: [email protected]
Sent: Thursday, October 2, 2008 2:04:03 PM
Subject: Re: SQL Delete - Slow on Import - Thoughts
**
Thanks Joe, Same sized table and beefier Prod Boxes :-)
________________________________
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Joe DeSouza
Sent: Wednesday, October 01, 2008 3:34 PM
To: [email protected]
Subject: Re: SQL Delete - Slow on Import - Thoughts
It takes about 3 seconds to run this statement on your production box.
DELETE FROM T793 WHERE C1 = N'0000042433'
Is T793 a larger table on production than it is on development? That
could be the only reason why it takes 3 seconds to delete on production
from T973 as deletes are not immediately committed to the database, but
written to the transaction log in case of a rollback.
Joe
----- Original Message ----
From: "Tanner, Doug" <[EMAIL PROTECTED]>
To: [email protected]
Sent: Wednesday, October 1, 2008 3:14:46 PM
Subject: SQL Delete - Slow on Import - Thoughts
**
ARSLIST (DBA's?)
I have an issue after a recent hardware upgrade
(Application Server and MS SQL Server) where the system is running
extremely fast and acceptable, unless I run arimportcmd or arimport. It
does not matter what file or table I am selecting for the "Import" the
deciding factor is what option I use in the Import (See Below) ( I am
working with our DBA as well)
Here are the details
ARS 6.3 Patch 23
MS SQL 2000 (Remote Enterprise Server)
Server D = Development
This system runs great all the time including arimport and arimport
command
Server P = Production
This system runs great except when we run arimport and arimport command
with the option of Update OLD record with New Record Data (Due to data
requirements I MUST use this option)
(SLOW - PRODUCTION - Import set to - Update OLD record with New Record
Data)
21:23:06.5310 */SELECT C1 FROM T793 WHERE C1 = N'0000042433'
21:23:06.5310 */BEGIN TRANSACTION
21:23:06.5310 */SELECT C1 FROM T793 WHERE C1 = N'0000042433'
21:23:06.5310 */SELECT
C1,C2,C3,C4,C5,C6,C7,C8,0,C112,C1000005076,C1000005078,C1000005079,C1000
005080,C1000005081,C1000005082,C1000005083,C1000005084,C1000005085,C1000
005086,C1000005087,C1000005088,C1000005089,C1000005090,C1000005091,C1000
005092,C1000005093,C1000005094,C1000005095,C1000005096,C1000005097,C1000
005098,C1000005099,C1000005100,C1000005101,C1000005102,C1000005103,C1000
005104,C1000005105 FROM T793 WHERE C1 = N'0000042433'
21:23:06.5310 */SELECT entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4 FROM H793
WHERE entryId = N'0000042433'
21:23:06.5310 */DELETE FROM T793 WHERE C1 = N'0000042433'
21:23:06.5620 */DELETE FROM H793 WHERE entryId = N'0000042433'
21:23:06.5620 */SELECT nextId FROM arschema WHERE schemaId = 793
21:23:06.5620 */INSERT INTO T793
(C2,C3,C4,C5,C6,C7,C8,C112,C1000005076,C1000005078,C1000005079,C10000050
80,C1000005081,C1000005082,C1000005083,C1000005084,C1000005085,C10000050
86,C1000005087,C1000005088,C1000005089,C1000005090,C1000005091,C10000050
92,C1000005093,C1000005094,C1000005095,C1000005096,C1000005097,C10000050
98,C1000005099,C1000005100,C1000005101,C1000005102,C1000005103,C10000051
04,C1000005105,C1) VALUES
(N'tanned01',1125429522,NULL,N'AR_ESCALATOR',1125436833,1,N'Vendors',NUL
L,N'0000042433',N'JC DIPIAZZA INC',NULL,N'WALDWICK',N'07463',N'NJ',N'2
NORTH
STREET',NULL,N'W',N'X',NULL,NULL,NULL,N'0052',NULL,NULL,NULL,NULL,NULL,N
ULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N'0000042433')
21:23:06.5620 */INSERT INTO H793 (entryId,T0,U0,T1,U1) VALUES
(N'0000042433',1125429522,N'tanned01',1125436833,N'AR_ESCALATOR')
21:23:06.5620 */COMMIT TRANSACTION
(FAST - DEVELOPMENT Import set to - Update OLD record with New Record
Data)
19:26:34.9220 */SELECT C1 FROM T793 WHERE C1 = N'0000042433'
19:26:34.9220 */OK
19:26:34.9220 */BEGIN TRANSACTION
19:26:34.9220 */OK
19:26:34.9220 */SELECT C1 FROM T793 WHERE C1 = N'0000042433'
19:26:34.9220 */OK
19:26:34.9220 */SELECT
C1,C2,C3,C4,C5,C6,C7,C8,0,C112,C1000005076,C1000005078,C1000005079,C1000
005080,C1000005081,C1000005082,C1000005083,C1000005084,C1000005085,C1000
005086,C1000005087,C1000005088,C1000005089,C1000005090,C1000005091,C1000
005092,C1000005093,C1000005094,C1000005095,C1000005096,C1000005097,C1000
005098,C1000005099,C1000005100,C1000005101,C1000005102,C1000005103,C1000
005104,C1000005105,C536870913 FROM T793 WHERE C1 = N'0000042433'
19:26:34.9220 */DELETE FROM T793 WHERE C1 = N'0000042433'
19:26:34.9220 */DELETE FROM H793 WHERE entryId = N'0000042433'
19:26:34.9220 */OK
19:26:34.9220 */INSERT INTO T793
(C2,C3,C4,C5,C6,C7,C8,C112,C1000005076,C1000005078,C1000005079,C10000050
80,C1000005081,C1000005082,C1000005083,C1000005084,C1000005085,C10000050
86,C1000005087,C1000005088,C1000005089,C1000005090,C1000005091,C10000050
92,C1000005093,C1000005094,C1000005095,C1000005096,C1000005097,C10000050
98,C1000005099,C1000005100,C1000005101,C1000005102,C1000005103,C10000051
04,C1000005105,C536870913,C1) VALUES
(N'tanned01',1125429522,NULL,N'AR_ESCALATOR',1125436833,1,N'Vendors',NUL
L,N'0000042433',N'JC DIPIAZZA INC',NULL,N'WALDWICK',N'07463',N'NJ',N'2
NORTH
STREET',NULL,N'W',N'X',NULL,NULL,NULL,N'0052',NULL,NULL,NULL,NULL,NULL,N
ULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1222385194,N'0000042433
')
19:26:34.9220 */OK
19:26:34.9220 */INSERT INTO H793 (entryId,T0,U0,T1,U1) VALUES
(N'0000042433',1125429522,N'tanned01',1125436833,N'AR_ESCALATOR')
19:26:34.9220 */OK
19:26:34.9220 */COMMIT TRANSACTION
Thoughts?
Doug Tanner
Manager, Enterprise Workflow
Remedy Skilled Professional (RSP)
Office (704) 328-3178
Cell (980) 230-8563
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
__Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
html___
DISCLAIMER Important! This message is intended for the above named person(s)
only and is CONFIDENTIAL AND PROPRIETARY. If you are not the intended recipient
of this e-mail and have received it in error, please immediately notify the
sender by return email and then delete it from your mailbox. This message may
be protected by the attorney-client privilege and/or work product doctrine.
Accessing, copying, disseminating or re-using any of the information contained
in this e-mail by anyone other than the intended recipient is strictly
prohibited. Finally, you should check this email and any attachments for the
presence of viruses, as the sender accepts no liability for any damage caused
by any virus transmitted by this email. Thank you.
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
