dcsimg
Results 1 to 17 of 17

Thread: Faster Teradata Recordset vb.net

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2015
    Posts
    50

    Faster Teradata Recordset vb.net

    Good day everyone.

    Have been working with oracle using ADODB in my project, have noticed that it takes the double of time or even more than Vba to download a query and its starting to bother.


    This is my connection Function

    Code:
    Public Function TERADATA_CONNECTION(ByVal ConnectionString As String) As ADODB.Connection
            Ora = New ADODB.Connection
            Ora.ConnectionTimeout = 120
            Ora.Open(connectionString, Oracle_Client.Ora_User.Text, Oracle_Client.Ora_Pass.Text)
            Ora.CommandTimeout = 86400
            Return Ora
        End Function
    This is my recordset process

    Code:
        Public Sub RUN_SQL(ByVal STthread_Data As thread_Data)
            Dim cn As ADODB.Connection = STthread_Data.Cnction
            Dim rs1 As ADODB.Recordset = STthread_Data.Rcrdset
            Dim qr As String = STthread_Data.Querry
    
    
            If Left(Trim(qr), 3) = "SEL" Then  
    
                With rs1
                    .CursorLocation = ADODB.CursorLocationEnum.adUseClient
                    .Open(qr, cn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockUnspecified)
                End With
    
            ElseIf Trim(qr) = "" Then
    
    
            Else 
                Ora.Execute(qr)
                Application.DoEvents()
                'SQL_STRING = ""
            End If
            ListProcessed.Add(rs1)
        End Sub
    Any ideas about how to speed up this? have already tried to change cursor etc but no luck, thanks a lot

  2. #2
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,191

    Re: Faster Teradata Recordset vb.net

    Why are you using ADODB with .Net? You should ideally be using Oracle's ODP.Net if you'll always be connecting to Oracle.

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,889

    Re: Faster Teradata Recordset vb.net

    This is .NET, right? All that you are doing is legacy stuff (ADO rather than ADO.NET, Left() and Trim(), Application.DoEvents). If you are using .NET, you might as well be using ADO.NET. Whether that will speed things up I couldn't say, because I have no experience with ADO vs ADO.NET. The two are considerably different in the way they work with data, so it wouldn't surprise me at all to find that there is a difference in performance, I just don't know what it would be.

    That Application.DoEvents will kill your performance, if it is actually hit, but considering that it's in one branch of a condition, I can't say whether or not it is being hit. What's it there for?

    EDIT: Didn't know about ODP.NET, but that's an even better suggestion, as it's probably an Oracle specific provider. There are similar providers for SQL Server...and kind of for Access.
    My usual boring signature: Nothing

  4. #4

    Thread Starter
    Member
    Join Date
    Oct 2015
    Posts
    50

    Re: Faster Teradata Recordset vb.net

    Quote Originally Posted by topshot View Post
    Why are you using ADODB with .Net? You should ideally be using Oracle's ODP.Net if you'll always be connecting to Oracle.
    Oh thats because I'm also connecting to sql Teradata, didnīt find a better way of doing it, will have a look into ODP.Net

    Quote Originally Posted by Shaggy Hiker View Post
    This is .NET, right? All that you are doing is legacy stuff (ADO rather than ADO.NET, Left() and Trim(), Application.DoEvents). If you are using .NET, you might as well be using ADO.NET. Whether that will speed things up I couldn't say, because I have no experience with ADO vs ADO.NET. The two are considerably different in the way they work with data, so it wouldn't surprise me at all to find that there is a difference in performance, I just don't know what it would be.

    That Application.DoEvents will kill your performance, if it is actually hit, but considering that it's in one branch of a condition, I can't say whether or not it is being hit. What's it there for?

    EDIT: Didn't know about ODP.NET, but that's an even better suggestion, as it's probably an Oracle specific provider. There are similar providers for SQL Server...and kind of for Access.
    application.DoEvents is being used there because sometimes I had problems on that line in specific when the execute command was just doing nothing , doevents solved it.

  5. #5

    Thread Starter
    Member
    Join Date
    Oct 2015
    Posts
    50

    Re: Faster Teradata Recordset vb.net

    This is my ODP connection but it's slow as well

    Code:
        Public Sub Sql_developer_conn()
            Sql = New OracleConnection()
            Sql.ConnectionString = connectionString
            Sql.Open()
            Oracle_Client.ToolStripStatusLabel1.Text = "LOGGED IN"
        End Sub
    this is the process part

    Code:
     Public Sub RUN_ORACLE()
    Dim cmd As New OracleCommand(SQL_STRING, Sql)
    cmd.ExecuteScalar()
    cmd.CommandType = CommandType.Text
            cmd.CommandType = CommandType.Text
            Dim da As New OracleDataAdapter(cmd)
            Dim cb As New OracleCommandBuilder(da)
            Dim ds As New DataSet
            da.Fill(ds)
            Dim t As DataTable = ds.Tables(0)
    
    end sub

  6. #6
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,191

    Re: Faster Teradata Recordset vb.net

    Quote Originally Posted by Valholy View Post
    Oh thats because I'm also connecting to sql Teradata, didnīt find a better way of doing it, will have a look into ODP.Net

    application.DoEvents is being used there because sometimes I had problems on that line in specific when the execute command was just doing nothing , doevents solved it.
    If you are connecting to SQL Server at the same time they would be different Connection objects.

    DoEvents is rarely needed but "doing nothing" doesn't mean much either.
    Quote Originally Posted by Valholy View Post
    This is my ODP connection but it's slow as well

    Code:
        Public Sub Sql_developer_conn()
            Sql = New OracleConnection()
            Sql.ConnectionString = connectionString
            Sql.Open()
            Oracle_Client.ToolStripStatusLabel1.Text = "LOGGED IN"
        End Sub
    this is the process part

    Code:
     Public Sub RUN_ORACLE()
    Dim cmd As New OracleCommand(SQL_STRING, Sql)
    cmd.ExecuteScalar()
    cmd.CommandType = CommandType.Text
            cmd.CommandType = CommandType.Text
            Dim da As New OracleDataAdapter(cmd)
            Dim cb As New OracleCommandBuilder(da)
            Dim ds As New DataSet
            da.Fill(ds)
            Dim t As DataTable = ds.Tables(0)
    
    end sub
    Define slow. The FIRST connection to an Oracle database is always MUCH slower because of all the authentication overhead.

    What is your Sql text? Are the appropriate columns indexed? What is the ExecuteScalar for?

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,793

    Re: Faster Teradata Recordset vb.net

    That's because you're executing it twice....

    .ExecuteScalar runs the query and then returns the first field of the first row... since you're not getting that value, there's no reason for it... delete it.
    Also, since all you're interested int is just a table, just fill the table, don't deal with a dataset...


    Code:
            Dim ds As New DataSet
            da.Fill(ds)
            Dim t As DataTable = ds.Tables(0)
    should be
    Code:
            Dim t As New DataTable
            da.Fill(t)

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Member
    Join Date
    Oct 2015
    Posts
    50

    Re: Faster Teradata Recordset vb.net

    thanks everyone will try and report back

  9. #9
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Faster Teradata Recordset vb.net

    Your title states Teradata, yet everything else you have shown indicates Oracle.

    I'm a bit confused, so feel free to ignore any or all of the following.

    A quick search indicates that Teradata and Oracle have a working relationship for inter-operation between them, but is using an Oracle provider the proper way to connect to Teradata?

    Perhaps a set of tools specifically designed for Teradata would be better?

    .NET Data Provider for Teradata
    Last edited by TnTinMN; Dec 7th, 2016 at 08:03 PM.

  10. #10

    Thread Starter
    Member
    Join Date
    Oct 2015
    Posts
    50

    Re: Faster Teradata Recordset vb.net

    Quote Originally Posted by TnTinMN View Post
    Your title states Teradata, yet everything else you have shown indicates Oracle.

    I'm a bit confused, so feel free to ignore any or all of the following.

    A quick search indicates that Teradata and Oracle have a working relationship for inter-operation between them, but is using an Oracle provider the proper way to connect to Teradata?

    Perhaps a set of tools specifically designed for Teradata would be better?

    .NET Data Provider for Teradata

    thanks a lot , right now Im using both adodb and odp for testing, turns out that with odp its downloading faster using a oracledatareader but at the end cant find a way to export the results to a text file it gives me an error on this "tw.Write(dr.GetOracleString(0))"

    Code:
        Public Sub RUN_ORACLE(ByVal STthread_Data As thread_Data)
            On Error GoTo errrors
            Dim cn As OracleConnection = STthread_Data.OrclCon
    
            Dim Flename As String = STthread_Data.FilePath & "\INSERTS" & STthread_Data.FileName
            Dim qr As String = STthread_Data.Query
            Dim tw As System.IO.StreamWriter = System.IO.File.CreateText(Flename)
            Dim flerr As System.IO.StreamWriter = STthread_Data.Errlg
    
            Dim cmd As New OracleCommand(qr, cn)
            cmd.CommandType = CommandType.Text
            Dim dr As OracleDataReader = cmd.ExecuteReader()
            Dim d As Integer = dr.FieldCount
            While dr.Read
                tw.Write(dr.GetOracleString(0))
            End While
            tw.Close()
            cn.Close()
            cn.Dispose()
            Exit Sub
    errrors:
            flerr.Write(Flename & " " & Err.Description)
            tw.Close()
        End Sub

  11. #11
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,191

    Re: Faster Teradata Recordset vb.net

    Quote Originally Posted by Valholy View Post
    thanks a lot , right now Im using both adodb and odp for testing, turns out that with odp its downloading faster using a oracledatareader but at the end cant find a way to export the results to a text file it gives me an error on this "tw.Write(dr.GetOracleString(0))"
    Crystal ball not working today... what is the exact error you are getting?

  12. #12

    Thread Starter
    Member
    Join Date
    Oct 2015
    Posts
    50

    Re: Faster Teradata Recordset vb.net

    Quote Originally Posted by topshot View Post
    Crystal ball not working today... what is the exact error you are getting?
    sorry "Specified cast is not valid."

  13. #13
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,191

    Re: Faster Teradata Recordset vb.net

    Then I'd guess you either have some null values or the column is not really a string type. You can verify the latter with dr.GetFieldType(0)

  14. #14

    Thread Starter
    Member
    Join Date
    Oct 2015
    Posts
    50

    Re: Faster Teradata Recordset vb.net

    Quote Originally Posted by topshot View Post
    Then I'd guess you either have some null values or the column is not really a string type. You can verify the latter with dr.GetFieldType(0)
    You're correcto, tried using getvalues and it works, but how can I identify what type of data it is? tried using cstr, cint and they wont work.

    thanks a lot

  15. #15
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,191

    Re: Faster Teradata Recordset vb.net

    dr.GetFieldType(0) doesn't give any indication what it is?

  16. #16
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,793

    Re: Faster Teradata Recordset vb.net

    I ran into something similar pulling data from SQL Server day before last... the field is in fact a number.... it is either a 1 or a 0 ... for some reason using reader.GetString(reader.GetOrdinal("FieldNAme")) threw a fit ... what was weird is that the code had been working for a year like that - over a year in fact - the source of the data was a query, and the query varied based on some other criteria ... all of the quereis are set up the same so that 1/0 number comes out the same in all cases... but for one it just would not work. I kept getting "Int32 cannot be converted to string" error ... wha?? I'd understand from string to number, but number to string wasn't working. ... .aaaaaaany ways... I ended up doing this:
    Code:
    fieldValue = String.Format("{0}", selectReader.GetValue(selectReader.GetOrdinal("FieldName")))
    works like a champ not... no matter what the source type is.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  17. #17

    Thread Starter
    Member
    Join Date
    Oct 2015
    Posts
    50

    Re: Faster Teradata Recordset vb.net

    Quote Originally Posted by topshot View Post
    dr.GetFieldType(0) doesn't give any indication what it is?
    getvalue worked and it was a number

    Quote Originally Posted by techgnome View Post
    I ran into something similar pulling data from SQL Server day before last... the field is in fact a number.... it is either a 1 or a 0 ... for some reason using reader.GetString(reader.GetOrdinal("FieldNAme")) threw a fit ... what was weird is that the code had been working for a year like that - over a year in fact - the source of the data was a query, and the query varied based on some other criteria ... all of the quereis are set up the same so that 1/0 number comes out the same in all cases... but for one it just would not work. I kept getting "Int32 cannot be converted to string" error ... wha?? I'd understand from string to number, but number to string wasn't working. ... .aaaaaaany ways... I ended up doing this:
    Code:
    fieldValue = String.Format("{0}", selectReader.GetValue(selectReader.GetOrdinal("FieldName")))
    works like a champ not... no matter what the source type is.

    -tg
    Thanks a lot!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width