Query takes long time in execute

[manoj.mcans]

Hi,
I have build one query which has 8 -9 subquery and 5-6 tables each table has 1/2 million records . i link tables via ODBC in oracle.this query take minimum 2 hour to execute,
Please any one could suggest me , how to optimize the query and achieve the result in less time.

Many Thanks

 

[namliam]

what are your queries??

The problem is probably that you are having the query executed by your local machine instead of the Oracle server. If you can manage to make it one query in a pass-through (PT) query, it will be done by the Oracle server.

 

[manoj.mcans]

Yah , I can not access the server , i have to run the queries on local machine , because there are lots of user who are accessing the server,

Pass Through query option is fine but problem is whenever i run my query using where clause if field name consist underscore in column name , it shows the error invalid column name i.e
if i run using pas through query
select equip_no from tablename it run prefectly fine.

if i run
select euip_no from tablename where euip_no like "00001%"
it show error invalid column name

Please suggest on this..

Many Thanks

 

[namliam]

You can access the server, if you can create links to the tables of Oracle, you can reach the server and you can run queries there.

It is NOT the underscore that is the problem I have many fieldnames with underscores in there.

If you do a PT query into Oracle you have to use Oracle SQL, in Oracle SQL a char comparison is to be done using the single quote ' not the double "
Double quotes denote the start and end of a column name, hence your query is searching for a columna named 00001% which it cannot find.

 

[manoj.mcans]

Many thanks from the last few days i was struggling with pass through query and i was putting the double quotes in where clause , that was showing error instead of using single quotes..
now its working

Thnak you very much

 

[namliam]

Just remember, PT queries need to be in the language of the DB they are going to .... not in Access SQL.

Your query should be considerably faster now.

Good luck !

 

[manoj.mcans]

Thanks ,
I am creating the queries using 3 database,

usually i was building the query using one database,
first i make DSN in Machine Data Source and then select what ever DSN i want and then run the query, Its run fine

Now Problem is:
could you please help me , how to connect more than one database with queries using Machine Data Source or by any other method.
the query i build that taking the data from more than one database.


Many Thanks
Manoj Mittal

 

[namliam]

what databases are you trying to reach?

Running queries over multiple databases is going to be SLOW AS HELL

 

[manoj.mcans]

I have 3 different database in oracle
1. for euipment detail
2. financial data
3 Asset status detail
i have to fetch data from each database according to requirment .
so different database has different username and different password for accessing.

when i make query in ms access with 2 or more database and run it then it pop up window each for each database username and password and then it connect
but problem is:
now i have converted all my queries in pass through query so how i will connect with different database.
Please suggest me any idea. it will be great help for me

Many Thanks
Manoj

 

[namliam]

How I would tacle this was to make a new user/schema in Oracle that has select rights on the databases you need to reach. Then have my access db connect into that Oracle schema/user and be done with permissions and logins.

PTs require the Oracle user to have access to the data requisted, therefor you need some Oracle permissions to access the data if you require cross database queries.

An alternative (but MUCH) slower is to make a PT for DB1 and a PT for DB2 then make a query in access (non - PT) to join the data.

 

[manoj.mcans]

Thanks ,
is there no other option exists, can i do code in VBA for pass through for making the Machine data source connection..

because if i make seperate pass through query for each database and then link then in ms access then also it very slow.

I got one another common problem that most of user faced.
some time by its own my odbc connection failed--could you please put light on this matter also..what may be reason for this ..why its happen surprising sometimes.

Many Thanks
Manoj

 

[namliam]

Yes, running queries on your local machine is slow... as long as you are needing multiple databases...

Maybe it is an idea to run a query into your database, actually storing the data in your local DB. then running the query in your local DB, tho you probably dont want such a solution, as speed is still going to be an issue.

Your best bet is taking care of the connections in oracle itself getting permissions inside oracle to access the other databases.

The ODBC error is probably due to a time out error, look into your PT queries and find the right property to allow for more time. Also do a search on google on "oracle odbc timeout" or something alike to find the oracle settings for the same.

If you have multiple users, maybe it is a good idea to run this stuff in one central database, "preparing" the data for the users. without them having the need to wait a long time.

 

[manoj.mcans]

Thanks,
Yah i have central database i.e in oracle all my team member use that via access odbc connection for generating the reports. we have more than one centeralize database. each user has its own credentials for that.

I face one new problem in pass through query i.e whenever i run pass through query it always pop up machine data source window and each time i have to select the database and then passowrd window and then run the query..Is there any option in ms access available so i didnot select machine data source again and again. only ask about user credential for oracle connection ??

one thing more , Can we make pass through query as parameterize query ??

Many Thanks
Manoj

 

[namliam]

In the properties of the PT query you can set the datasource even store username/pw if you like. If you have any of them blank access will popup for them.

No, you cannot have parameters in a PT query the same as you do with access.
It is oracle afterall... so you have to talk Oracle not access.
I know you can use binding in Oracle, but I am unsure of how to exactly use that.

Another option is to use VB to alter the SQL and write that to the waiting PT query before executing it.

 

[manoj.mcans]

Thanks yah i got it and found also that proprty for saving passowrd. then window does not pop up.
Thank you very much

one different query is:
if i run pass through query using vba and from then it ask for connection string ..i have also mention but i am not getting where the error coming ..
could you please sort out this prob also..
code is:
Dim conDatabase As ADODB.Connection
Dim cmd As ADODB.Command
Set conDatabase = New ADODB.Connection
Set cmd = New ADODB.Command
conDatabase.ConnectionString = "DSN=Name of DSN ;UID=UserName;DBQ=Database;Pwd=;ASY=OFF;"
cmd.ActiveConnection = conDatabase
cmd.CommandType = adCmdText
cmd.CommandText = SQLCode
conDatabase.Open
cmd.Execute

Oracle server is centeralize i run the report via Ms-Access form by using VBA

Please help it shows the error : Request operation requires an OLEDB session object, which is not supported by current provider.

Many Thanks
Manoj

 

[namliam]

I never use ADO, allways use DAO. So I am guessing here...
But in code generaly you cannot do popups you actually need to fill pwd.
You could do that using a popup box that they have to enter or something.

This is the same thing that when you run a parameter query in VBA you have to fill the parameters prior to running the query, instead of access popping them up for you.