Posted in General

Database Comparision Results between SQL Server 2000 and Oracle 9i

Ok here is my outcome of my tests. I ran these tests on my home PC. When Oracle services was running, SQL Server services were stopped and vice versa.

It took 13 minutes some odd seconds to create 10 million records, but Oracle took 1:21 seconds

I also observered that DB size has grown in SQL Server when i added records. but when i dropped the table or deleted the records, the database file size did not come down (as expected). However, Oracle took 8:51 minutes to delete all the records where as SQL Server took 6:06 minutes. But Dropping the Table in Oracle was instant. to Below are my SQL Scripts that i used on Oracle. Please let me know if you notice behaviours.


--SqlCreateTable.sql
--Creates Table and inserts 10 records
CREATE TABLE SIZETEST
(
COL NVARCHAR2(10)
);

--Add 10 records
INSERT INTO SizeTest(Col)
SELECT 'AAAAAAAAAA' FROM DUAL
UNION
SELECT 'BBBBBBBBBB' FROM DUAL
UNION
SELECT 'CCCCCCCCCC' FROM DUAL
UNION
SELECT 'DDDDDDDDDD' FROM DUAL
UNION
SELECT 'DDDDDDDDDD' FROM DUAL
UNION
SELECT 'FFFFFFFFFF' FROM DUAL
UNION
SELECT 'GGGGGGGGGG' FROM DUAL
UNION
SELECT 'HHHHHHHHHH' FROM DUAL
UNION
SELECT 'IIIIIIIIII' FROM DUAL
UNION
SELECT 'JJJJJJJJJJ' FROM DUAL


--InsertScript.sql
--5 Times for each TRANS
--2 times = 10k records
--Each record has 10 characters

DECLARE
I NUMBER(10);
N NUMBER(10);
BEGIN
N := 20;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'dd-MM-yyyy hh:mi:ss'));
FOR i IN 1..N
LOOP
INSERT INTO SizeTest(Col)
SELECT Col FROM SizeTest;
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'dd-MM-yyyy hh:mi:ss'));
END;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s