Performance - Can't explain performance difference between machines

I have an application that will perform several thousand transactions.
Each transaction consists of 10-30 INSERT or UPDATE's. The transcations
are properly committed, with occasional rollbacks (1-5 per 1000
transactions). 

I can run the application against two different databases. Both are ASE
11.9.2, and they reside on the same subnet (so there are no network
differences). The application runs on a third machine. 

When I time the individual transacions on the two machines, I find that
one machine is a factor of 3-4 times FASTER than the other. What is odd
is that the FAST machine is a simple desktop, while the SLOW machine is
the new database server (4x processors, etc). I've isolated the timing
down to the execution of the INSERT/UPDATE stmts - so there are no other
I/O factors that figure into the equation. 

My question is this: What kind of performance affecting parameters can I
look at on these two machines which might shed some light on the
situation? 

Thanks for any tips or advice. 

Dan

-- 
Daniel J. Sperka
Sr. Developer/Analyst
Virtual Properties, Inc. 
(608) 271-9601
mailto:dsperka@virtualproperties.com
0
Daniel
8/15/2000 6:55:19 PM
sybase.sqlserver.general 4269 articles. 0 followers. Follow

2 Replies
312 Views

Similar Articles

[PageSpeed] 57

> When I time the individual transacions on the two machines, I find that
> one machine is a factor of 3-4 times FASTER than the other. What is odd
> is that the FAST machine is a simple desktop, while the SLOW machine is
> the new database server (4x processors, etc). I've isolated the timing
> down to the execution of the INSERT/UPDATE stmts - so there are no other
> I/O factors that figure into the equation.

What are you getting with 'set showplan on' and 'set statistics io on'?  I'd
be looking for the same query plan as well as same/similar I/O counts.

Are there any triggers on the tables?  If so ... are there any references to
a table(s) that would require an index which is missing, ie, any chance of a
table scan?

If using triggers, and/or stored procs called by the triggers ... any issues
with datatype mismatches between variables and the table(s) being queried?

Any b/locking issues on the 'slow' dataserver?

Any difference in index, or locking strategies, on your table(s)?

Do you have any way to check out the I/O rates at the OS level?  Any
bottlenecks on controller, disk, or RAID construct?

You mention that you're seeing some rollback's ... so this is probably a
moot point ... transaction wrappers (begin/commit tran) can help cut down on
log I/O's (when preforming multiple DML operations) thus giving a sizable
improvement in throughput ... any chance that a transaction wrapper is
missing on the slow dataserver (eg, differently coded stored proc)?
--
Mark A. Parsons

Iron Horse, Inc.  iron_horse@compuserve.com
Pegasys (200), Ltd  pegasys@internet.co.nz


0
Mark
8/16/2000 1:37:34 AM
Daniel J. Sperka wrote:

> I can run the application against two different databases. Both are ASE
> 11.9.2, and they reside on the same subnet (so there are no network
> differences). The application runs on a third machine.

	Exactly how different are these two databases? Is one a copy
	of the other? What about the physical layouts and the i/o
	subsystem?

> When I time the individual transacions on the two machines, I find that
> one machine is a factor of 3-4 times FASTER than the other. What is odd
> is that the FAST machine is a simple desktop, while the SLOW machine is
> the new database server (4x processors, etc). I've isolated the timing
> down to the execution of the INSERT/UPDATE stmts - so there are no other
> I/O factors that figure into the equation.

	Well, more than likely the routine is only running on one CPU
	anyway. How do the CPUs compare?

> My question is this: What kind of performance affecting parameters can I
> look at on these two machines which might shed some light on the
> situation?

	Page splits, triggers, disk speed, layouts, etc. You'll have
	to do as Mark suggests and look at showplans and stats. Also
	check what else if different - item by item.

-am
0
Anthony
8/16/2000 4:38:22 AM
Reply: