Comparing two different ODBC system-DNS entries. Which is faster? PS script

i want to find out which ODBC Connection is faster on my VM with Windows Server 2008 R2. I have two different ODBC providers. The first is “Oracle Instant Client ODBC” in v11. The other “Microsoft ODBC for Oracle”. I thought, that i can use Powershell to test how long two simmilar querys run with each provider. (Later i also want to test if the MS Provider for Oracle is faster on Win08R2 or on Win12R2)

I found a nice script here: LINK

i modified it only a bit. So now i have tihs litte script:

 #SQL Authentication
 $t_start = Get-Date 

 $connectstring = "DSN=instant;Uid=USER;Pwd=PASSWORD;"
 #Windows Authentication
 #$connectstring = "DSN=myDsn;"
 $sql = "SELECT * FROM TABLE1 WHERE ROWNUM <= 900;"

 $conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)
 $conn.open()
 $cmd = New-Object system.Data.Odbc.OdbcCommand($sql,$conn)
 $da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)
 $dt = New-Object system.Data.datatable
 $null = $da.fill($dt)
 $conn.close()
 $dt | measure

 $t_end = Get-Date
 Write-Host ($t_end - $t_start)

It works fine for litte querys but the problem is that if i want to select more than 840 rows i get an error:

Exception calling "Fill" with 1 argument (s): "Input string was not in a correct format."
Line: 14 Sign: 2
    In Zeile:14 Zeichen:2
    +  $null = $da.fill($dt)
    +  ~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : FormatException

The result of the skript is:

Count    : 840
Average  : 
Sum      : 
Maximum  : 
Minimum  : 
Property : 

00:00:01.4530713

If i change the SQL-Query to “ROWNUM <= 839” it works. If i change to “ROWNUM <= 841” i get this error.

My aim was to really make a big query like “ROWNUM <= 1.000.000“

The table i query has about 1.200.000 rows…

Is there a posibility to change something, so that i am able to query large tables? Or has anyone a nice freeware tool i can use to query ODBC-SYSTEM-DSN entries and get a usefull querytime so that i can compare my different ODBC’s ?

I haven’t done a lot of PS so not sure what the error is really saying but… it looks like you are trying to fill a local table with the output from the select *? If that is true, I am wondering if PS created a temp table with a number/int column type for rownum but the rownum value in row 840 or 841 is something other than a number/int. Or something similar for another column.

It is expecting one data type when setting up the the temp table but is finding another.

I have seen this happen more than a few times over the years of doing data etl.

Is rownum ALWAYS in number?

What does all the data row 840 and 841 look like?

What happens if instead of select * you itemized one field? i.e. SELECT [first_ name] FROM TABLE1 WHERE ROWNUM <= 900;

What if you changed the rownum? between 900 and 1800?

BTW: cool script!