Where To Put This?

join on two different kinds of db

Page 1 of 1

6 Replies - 1942 Views - Last Post: 15 July 2003 - 02:40 PM Rate Topic: -----

#1 Noffke  Icon User is offline

  • D.I.C Regular

Reputation: 0
  • View blog
  • Posts: 497
  • Joined: 09-August 02

Where To Put This?

Posted 11 July 2003 - 02:28 PM

I am looking at someone else's code here and I came up with an idea on how to cut some time out of this program. I am programming in VB (maybe this should go over in the VB forum?) and using ADO. I have two different kind of databases that I need to pull data from. Currently the program checks one entry at a time from db1->table against all the entries in the db2->table.

I think* that I can cut down the number of queries from about 5000 down to 1 or 2.

so my question is can one JOIN two tables, using ADO, that come from two different kinds of databases? this could be a dumb question....I haven't had time to research it and won't be able to until next week sometime, so I figured I would start here.

I don't know how expensive SQL queries are through ADO(enlighten me if you do) but my theory is if I can cut out 5000 instructions in VB then it should have some impact on the speed of the program.

Is This A Good Question/Topic? 0
  • +

Replies To: Where To Put This?

#2 klewlis  Icon User is offline

  • cur tu me vexas?

Reputation: 8
  • View blog
  • Posts: 1,723
  • Joined: 09-November 01

Re: Where To Put This?

Posted 11 July 2003 - 02:46 PM

i'm not aware of any way to query two separate db's at once.

what kind of db is it?
Was This Post Helpful? 0
  • +
  • -

#3 Noffke  Icon User is offline

  • D.I.C Regular

Reputation: 0
  • View blog
  • Posts: 497
  • Joined: 09-August 02

Re: Where To Put This?

Posted 11 July 2003 - 02:56 PM

one is access and the other is Microsoft SQL...

do you know much about ADO? can you make queries on recordsets once you pull them in?
Was This Post Helpful? 0
  • +
  • -

#4 klewlis  Icon User is offline

  • cur tu me vexas?

Reputation: 8
  • View blog
  • Posts: 1,723
  • Joined: 09-November 01

Re: Where To Put This?

Posted 11 July 2003 - 06:24 PM

hm.... no... i don't think it's possible.

oracle can do something similar, i'm told.

your other option is to store the object in the script and process it in the code instead....
Was This Post Helpful? 0
  • +
  • -

#5 Noffke  Icon User is offline

  • D.I.C Regular

Reputation: 0
  • View blog
  • Posts: 497
  • Joined: 09-August 02

Re: Where To Put This?

Posted 15 July 2003 - 07:51 AM

that seems like a lot of memory use though....of course this machine is used for nothing else and the program eats up all the memory anyway...

Im a newb to the database world but I'd rather deal with it in a database because it is easier to deal with. Is there a way to import a table into access? I know you can import text files, I wonder if I could import this table in, or if I could pull it in through ADO and use ADO to create the table in the access database.

either way it will have to wait, this was just a program I was supposed to 'look' at to learn a certain process.
Was This Post Helpful? 0
  • +
  • -

#6 klewlis  Icon User is offline

  • cur tu me vexas?

Reputation: 8
  • View blog
  • Posts: 1,723
  • Joined: 09-November 01

Re: Where To Put This?

Posted 15 July 2003 - 12:41 PM

Noffke, on Jul 15 2003, 08:51 AM, said:

that seems like a lot of memory use though....of course this machine is used for nothing else and the program eats up all the memory anyway...

Im a newb to the database world but I'd rather deal with it in a database because it is easier to deal with. Is there a way to import a table into access? I know you can import text files, I wonder if I could import this table in, or if I could pull it in through ADO and use ADO to create the table in the access database.

either way it will have to wait, this was just a program I was supposed to 'look' at to learn a certain process.

you can *manually* import a table into an access database... ie, you have another table from somewhere and you go into access and open your db and import it.... but that's a pain and doesn't always work.

if you want the script to do it then you will have to write something to pull all the info out of one db and place it into another... a better option if you are doing a lot or if the db's are different in structure and stuff.... there are several options but it just depends on how you want to do it...
Was This Post Helpful? 0
  • +
  • -

#7 Noffke  Icon User is offline

  • D.I.C Regular

Reputation: 0
  • View blog
  • Posts: 497
  • Joined: 09-August 02

Re: Where To Put This?

Posted 15 July 2003 - 02:40 PM

I guess I just have to figure out if it is worth it to copy an entire table from one database to another. I mean the whole purpose of this was to cut out a function that was run 5000 times and the function involved a query every time. It seems like I'll just be trading that function out for a loop that iterates 5000 times. Although it might be faster, I don't know that it will be THAT much faster.

thanks for the help though.

This post has been edited by Noffke: 15 July 2003 - 02:41 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1