Forum  Migrations  Oracle to SQL S...  Compare SELECT speed on Oracle and SQL Server
Previous Previous
 
Next Next
New Post 11/21/2008 7:37 AM
  jweil
1 posts
No Ranking


Compare SELECT speed on Oracle and SQL Server 
Modified By jweil  on 11/21/2008 7:48:03 AM)

 


Hello everybody,

I am evaluating migration from Oracle 10.1 to SQL Server 2005. The result will be considered a success if a few time-consuming queries show the same or better performance after the database has been moved.

The question is: what is the best way to compare the execution speed of an Oracle SELECT statement against the equivalent SELECT issued on SQL Server? The contents of source and target databases should be identical so that the returned rows are the same.

I would prefer using native mechanisms of these databases and not the third party tools, which could have a bias towards one of the platforms.

 
New Post 11/21/2008 8:54 AM
  galams
1 posts
No Ranking


Re: Compare SELECT speed on Oracle and SQL Server 

 

We should measure the query execution time which is called Query Elapsed Time; by the way, this term exists in both platforms and is interpreted equally as
 
Elapsed Time = the wall clock time from the moment you enter the command until it terminates; this includes time for parsing/executing/fetching, time spent waiting for other users, I/O time, etc.
 
Additionally, it turns out that the wall clock is not required itself as both Oracle and SQL Server measure and save the Elapsed Time  for all executed queries themselves and can show it to users. Besides, the Elapsed Time, measured by DBMS, does not contain the time, expended to set a connection ,which is mandatory when starting queries in the command line. Thus, if you trust to objectivity of a stopwatch which is started by a sportsman himself, then you should only know how to display these figures and the task is solved. It is easy to do this:
 
  •    1. include SET TIMING ON in the script in Oracle
  •    2. use SET STATISTICS TIME ON in SQL Server
 
Gala.

 

 
New Post 11/24/2008 9:53 AM
  laxxx
1 posts
No Ranking


Re: Compare SELECT speed on Oracle and SQL Server 

 

If we measure the execution time of a single statement, then I agree with the author. But if we measure the time in multi-user mode, that would mean testing the performance of the system as a whole, and that is a lot more difficult task.

 
New Post 11/25/2008 3:59 AM
  R.Alex
3 posts
No Ranking


Re: Compare SELECT speed on Oracle and SQL Server 

 

IMHO it is pretty problematic to dig into SET STATISTICS TIME ON results.

It is simpler to use this SQL Server code:

declare @t1 datetime, @t2 datetime
set @t1=getdate()
--command--
set @t2=getdate()
select @t2-@t1


and Oracle code:

select current_timestamp from dual;
-- command --
select current_timestamp from dual;

 
New Post 11/25/2008 4:09 AM
  yurir
3 posts
No Ranking


Re: Compare SELECT speed on Oracle and SQL Server 

 

And how could I execute this Oracle code if the --command-- is a SELECT statement returning multi-row result set? This will not work like an SQL and also like PL/SQL. Also, how is it possible to get the comparable difference between the two current_timestamp values?

 

 
Previous Previous
 
Next Next
  Forum  Migrations  Oracle to SQL S...  Compare SELECT speed on Oracle and SQL Server