Skip to Content
1
Former Member
Oct 16, 2012 at 02:32 PM

SAP Hana Vs MSSQL on Amazon AWS

48 Views

Hi,

I'm testing the performances of HANA VS. MSSQL on Amazon AWS.

I have a little testing application written in VB.NET that connects to HANA through ODBC (.NET System.Data.Odbc) and connects to MSSQL through SqlClient (.NET System.Data.SqlClient)

I'm using 3 machines on AWS:

AppSrv: t1.micro (Application)

HANA: m2.4xlarge (HANA DB)

MSSQL: m2.4xlarge (MSSQL DB)


On both HANA and MSSQL i have a table called "FORSELECTER" (ROWSTORE) with 1 milion rows.

Here's the structure of the table:

Name SQL Data Type Dim CODE NVARCHAR 4000 DESCRIPTION NVARCHAR 4000 PRICE INTEGER
SCP0 NVARCHAR 4000 SCP1 NVARCHAR 4000 ..... NVARCHAR 4000 ..... NVARCHAR 4000 SCP30 NVARCHAR 4000

No indexes have been used.

Doing a "SELECT TOP 1 *" would give you:

CODE DESCRIPTION PRICE SCP0 SCP1 SCP.. SCP... SCP30 TC43440 TD43440 2728999 X XX

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX







Taking some code from my testing application:

Here are the objects I use for connecting to the DB (HANA or MSSQL)

...

Private connection As System.Data.Common.DbConnection = Nothing

Private command As System.Data.Common.DbCommand = Nothing

Private reader As System.Data.Common.DbDataReader

...

I initialize them differently depending on the DB I'm working on that moment:

...

Select Case mUsedDbAPI

Case DbAPIEnum.Odbc

'

connection = New System.Data.Odbc.OdbcConnection(sConStrHANA)

command = New System.Data.Odbc.OdbcCommand()

Case DbAPIEnum.SqlClient

connection = New System.Data.SqlClient.SqlConnection(sConStrMSSQL)

command = New System.Data.SqlClient.SqlCommand()

End Select

...

For testing purposes i tried as well not to use IHERITANCE:

...

Private hConnection As System.Data.Odbc.OdbcConnection = Nothing

Private hCommand As System.Data.Odbc.OdbcCommand = Nothing

...

hConnection = New System.Data.Odbc.OdbcConnection(sConStrHANA)

hCommand = New System.Data.Odbc.OdbcCommand

Attached you'll find a text file (results.log) with some SQL statements and the rows affected (Rows), time in seconds (s) and speed (Rows/s),copy pasting from that file:

**************************

Odbc (this means I'm connecting to HANA using ODBC with inheritance)

SELECT *

FROM FORSELECTER

WHERE SUBSTRING(CODE, 3, 4) = '3991' (111 Rows) (0.1716022 s) (646.844853970404 Rows/s)

**************************

SqlClient (this means I'm connecting to MSSQL using SqlClient)

SELECT *

FROM FORSELECTER

WHERE SUBSTRING(CODE, 3, 4) = '3991' (111 Rows) (0.0312004 s) (3557.64669683722 Rows/s)

**************************

HANA SPECIFIC (this means I'm connecting to HANA , NO inheritance)

SELECT *

FROM FORSELECTER

WHERE SUBSTRING(CODE, 3, 4) = '3991' (111 Rows) (0.1248184 s) (889.291963364376 Rows/s)

As you can see in these and the other examples you'll find in the attached file (results.txt) MSSQL is faster than HANA.

As HANA works in memory and MSSQL works on disks (I avoided localised selects, cleaned the buffer etc) there must be something I am missing.

Any clues? Maybe some settings or other things?

Attachments

results.txt.zip (1.0 kB)