I am seeing varied behavior between different Remedy systems - however
most things seem equal.
Doe anybody know why a Remedy 7 server against a MSSQL DB would break
a query into many queries?
(The net difference is I am seeing on one box approx 400 statements
where another box I see 3 statements)
(Guess which one is faster)
Oh - and another question -- what is so important about helpText and
changeDiary. OK - I could guess on changeDiary -- but helpText?
I am seeing this on one particular Remedy server in sql logs:
MSSQL:BATCH BEGIN TRANSACTION
MSSQL:BATCH SELECT helpText FROM field WHERE (schemaId = 628) AND
(fieldId = 1)
MSSQL:BATCH SELECT changeDiary FROM field WHERE (schemaId = 628) AND
(fieldId = 1)
MSSQL:BATCH COMMIT TRANSACTION
MSSQL:BATCH BEGIN TRANSACTION
MSSQL:BATCH SELECT helpText FROM field WHERE (schemaId = 628) AND
(fieldId = 2)
MSSQL:BATCH SELECT changeDiary FROM field WHERE (schemaId = 628) AND
(fieldId = 2)
MSSQL:BATCH COMMIT TRANSACTION
MSSQL:BATCH BEGIN TRANSACTION
MSSQL:BATCH SELECT helpText FROM field WHERE (schemaId = 628) AND
(fieldId = 3)
MSSQL:BATCH SELECT changeDiary FROM field WHERE (schemaId = 628) AND
(fieldId = 3)
MSSQL:BATCH COMMIT TRANSACTION
MSSQL:BATCH BEGIN TRANSACTION
MSSQL:BATCH SELECT helpText FROM field WHERE (schemaId = 628) AND
(fieldId = 4)
MSSQL:BATCH SELECT changeDiary FROM field WHERE (schemaId = 628) AND
(fieldId = 4)
MSSQL:BATCH COMMIT TRANSACTION
MSSQL:BATCH BEGIN TRANSACTION
MSSQL:BATCH SELECT helpText FROM field WHERE (schemaId = 628) AND
(fieldId = 5)
MSSQL:BATCH SELECT changeDiary FROM field WHERE (schemaId = 628) AND
(fieldId = 5)
MSSQL:BATCH COMMIT TRANSACTION
.... (This goes on for every field in the form 628)
Which is approx 400 total statements.
However -- when I look at another Remedy system 7.0.01 patch 5
select @@version says:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer
Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I get this:
SELECT fieldId,helpText,changeDiary,owner,lastChanged FROM field WHERE
(schemaId = 1690) AND (fieldId IN
(1,2,3,4,5,6,7,8,15,112,160,179,70000083,300299400,300299500,300299600,3
00299700,300299800,400005200,400007300,400007500,400068300,400098500,536
870921,536870922,536870923,600000021,600000042,600000090,600000091,60000
0092,600000093,600000094,600000095,600000096,600000097,600000098,6000000
99,600000101,600000127,600000300,600000310,600000500,600000530,600001020
,
600003021,700000600,700000800,700000829,700001000,700001010,700001011,70
0001012,700001014,700001150,700001240,700001244,700001248,700001252,7000
01259,700001265,700001267,700001269,700001271,700001274,700001282,700001
285,700001398,700001486,700001560,700001591,700001806,700001807,700001808,700001809,700001810,700001811,700001812,700001813,700001814,700001815,700001816,700001817,700001818,700001819,700001820,700001821,700001822,700001823,700001824,700001825,700001826,700001827,700001828,700001829,700001830,700001831,700001832,700001833,700001834,700001835,700001836,700001837,700001838,700001839,700001840,700001841,700001842,700001843,700001844,700001845,700001846,700001847,700001848,700001849,700001850,700001851,700001852,700001853,700001854,700001855,700001856,700001857,700001858,700001859,700001860,700001861,700001862,700001863,700001864,700001923,700001972,700001974,700001987,700002012,700002303,700002396,700002400,700002450,700004000,700006300,700006310,700006556,700006557,700036000,700060521,700060522,700061000,700061001,700061002,700088100,700088101,700088102,700088103,700088104,700088106,700088170,700088475,700088489,700088607,700089541,700089542
)) ORDER BY fieldId ASC
And Oracle: 10 G2 (I think the version)
SELECT fieldId,helpText,changeDiary,owner,lastChanged FROM field WHERE
(schemaId = 610) AND (fieldId IN
(1,2,3,4,5,6,7,8,15,112,160,179,70000083,300299400,300299500,300299600,3
00299700,300299800,400005200,400007300,400007500,400068300,400098500,536
870921,536870922,536870923,536870924,600000021,600000042,600000090,60000
0091,600000092,600000093,600000094,600000095,600000096,600000097,6000000
98,600000099,600000101,600000127,600000300,600000310,600000500,600000530
,
600001020,600003021,700000600,700000800,700000829,700001000,700001010,70
0001011,700001012,700001014,700001150,700001240,700001244,700001248,7000
01252,700001259,700001265,700001267,700001269,700001271,700001274,700001
282,700001285,700001398,700001486,700001560,700001591,700001806,70000180
7,700001808,700001809,700001810,700001811,700001812,700001813,700001814,700001815,700001816,700001817,700001818,700001819,700001820,700001821,700001822,700001823,700001824,700001825,700001826,700001827,700001828,700001829,700001830,700001831,700001832,700001833,700001834,700001835,700001836,700001837,700001838,700001839,700001840,700001841,700001842,700001843,700001844,700001845,700001846,700001847,700001848,700001849,700001850,700001851,700001852,700001853,700001854,700001855,700001856,700001857,700001858,700001859,700001860,700001861,700001862,700001863,700001864,700001923,700001972,700001974,700001987,700002012,700002303,700002396,700002400,700002450,700004000,700006300,700006310,700006556,700006557,700036000,700060521,700060522,700061000,700061001,700061002,700088100,700088101,700088102,700088103,700088104,700088106,700088170,700088475,700088489,700088607,700089541,700089542,700089543,700089545,700089547,700089548,700089549,700089550
)) ORDER BY fieldId ASC
These larger statements - are one statement -- and much faster overall.
Any comments?
-John
--
John David Sundberg
235 East 6th Street, Suite 400B
St. Paul, MN 55101
(651) 556-0930-work
(651) 247-6766-cell
(651) 695-8577-fax
[EMAIL PROTECTED]
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
