dcsimg
Results 1 to 5 of 5
  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Mar 2005
    Posts
    1,682

    cursor type and ADO

    i'm working with vb 6 and ADO.

    Database Access.

    I join a Access table with a linked Teradata table.

    the join is F1 (access table)<>F2 (Teradata table)

    F1 is indexed
    F2 no

    With a simple select without any type of Cursortype the extraction of recordset is very very slow...

    Tips to make fast....

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,709

    Re: cursor type and ADO

    I'm not surprised it's slow. a join based on an inequality is basically producing a Cartesian product - i.e. it's multiplying the two tables. Are you sure that's the dataset you want because it's likely to be HUGE? Perhaps if you could describe what you're trying to achieve we might be able to suggest a better approach.

    Assuming it is what you want I'd have thought the obvious tip would be to index F2, though I'm not sure how much good that'll do because, if the tables are on different platforms, I doubt either platform will be able to build an execution plan that accounted for the others indexes.

    Would it be possible to import one sides data into the other side, index it there and then query against both tables in the same database? That would give you a fighting chance of getting an execution plan that uses available indexes. There are potential concurrency issues with this, though, unless you lock both sides for the duration of the operation which may not be desirable.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,257

    Re: cursor type and ADO

    Left join and filter via null ?
    Add an index to the teradata (assuming you are allowed to)

    --

    edit: or you mean you are looking for differences in the same record ?

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    Mar 2005
    Posts
    1,682

    Re: cursor type and ADO

    Quote Originally Posted by Ecniv View Post
    Left join and filter via null ?
    Add an index to the teradata (assuming you are allowed to)

    --

    edit: or you mean you are looking for differences in the same record ?
    Left join and filter via null ?
    Not completlly understand....
    simple sql example, please

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,709

    Re: cursor type and ADO

    Explain to us what you're trying to achieve. If it really is an non-equal join then what you've done is correct but this would be an unusual requirement and likely to be slow. So explain to us what you're trying to achieve and we'll try to point you in the right direction.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

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


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.