JDBC look Up query taking more time to process

[Former Member]

Hi all,

I am using JDBC Look Up in my project to retreive data from multiple tables.But the problem here is it is taking 10-12 minutes for processing the query which has around 770 records.So my question is there any way that i can reduce the time of processing query.

Ex:Select EmpId from EmpTable where EmpId>500 and EmpName="Arun"

Here I have 770 records and it is taking 12 minutes to retreive results.

Is there any database tuning technique that we can use in JDBC Look up in PI?

Thanks in Advance.

Regards,

Yeshwanth

[Former Member · Accepted solution]

For me also it was taking really long, but after i have done indexing on database tables, it became pretty fast.

So make sure you have indexing on your oracle table on which you doing query.

Thanks,

Hetal

[Former Member · Accepted solution]

@ Hetal Shah - This answer has already been given above, why you are repeating the same thing again??

This is not the first time that you have done this, I have noticed in few other forum posts, your answers are exactly the copy of the ones that has already been given prior to you.

If you have something new, then please do share with the forum.

--Abhi

[Former Member · Accepted solution]

I am not sure Abhi, and to be really frank i think u r really having lods of time to OBSERVE other ppl and really not to answer.

By the way thanks for the comment and i dont think i need to give explaination to you.

Better to use ur energy in replying for questions rather than making comments on others.

Thanks,

Hetal

Edited by: hetal shah on Jun 30, 2010 12:18 AM

[Former Member · Accepted solution]

@ Hetal,

I am not sure Abhi, and to be really frank i think u r really having lods of time to OBSERVE other ppl and really not to answer.
By the way thanks for the comment and i dont think i dont need to give explaination to you.

Yes, I have enough time to OBSERVE other people's answers, but at least I don't do copy-paste Don't get pissed buddy, come up with creative answers ...

And I am not asking for any explanations

[former_member181985]

> I am using JDBC Look Up in my project to retrieve data from multiple tables.But the problem here is it is taking 10-12 minutes for processing the query which has around 770 records.So my question is there any way that i can reduce the time of processing query.

This is happening since you are executing the Query (Select F1 from tablename where segment1='X', segment2='Y',segment3='X1',segment4='Y1',segment5='X2',segment6='Y2') for each record. So, the execution is 770 times in your case, which means it is opening and closing DB Accessor channel 770 times which is basically causing performance.

Better pass all fields in one Context as input to JDBCLookUP UDF and logically iterate by opening only one DB accessor.

I am just providing a rough UDF code, so it might not work at first shot. Change it according to your requirement.

Note: UDF should be advanced in nature (context or Queue)

Sample Code

Channel channel = null;
Map rowMap = null;
DataBaseAccessor accessor = null;
DataBaseResult resultSet = null;

try
{
		//Determine a channel, as created in the Configuration
		channel = LookupService.getChannel("<BUSINESSYSTEM>","<CHANNELNAME>");// give your service and channel

		//Get a system accessor for the channel. As the call is being made to an DB, an DatabaseAccessor is obtained.
		accessor = LookupService.getDataBaseAccessor(channel);	
}
catch (Exception e1)
{
	e1.printStackTrace();
}

for (i=0; i<a.length;i++ )
{
	//Query = "Select F1 from tablename where segment1='X', segment2='Y',segment3='X1',segment4='Y1',segment5='X2',segment6='Y2'";
	Query = "Select " + a<i> + " from tablename where segment1='X', segment2='Y',segment3='X1',segment4='Y1',segment5='X2',segment6='Y2'";

	try{
			resultSet = null;
			//Execute Query and get the values in resultset
			resultSet = accessor.execute(Query);

			for(Iterator rows = resultSet.getRows();rows.hasNext();)
			{
				rowMap = (Map)rows.next();
				result.addValue((String)rowMap.get(a<i>)); //this statement might not be correct as your query is already fetching F1, so make some adjust in this statement accordingly
			}

		}
		catch(Exception e2)
		{
			//result.addValue(ex.getMessage());
			e2.printStackTrace();
		}

}

try{
		if (accessor!=null) 
			accessor.close();
	}
	catch(Exception e3)
	{
		e3.printStackTrace();
	}

Let us know the outcome.......

Regards,

Praveen Gujjeti.

Edited by: Praveen Gujjeti on Jun 29, 2010 10:38 PM

[Former Member]

Yeshwanth,

But the problem here is it is taking 10-12 minutes for processing the query which has around 770 records.So my question is there any way that i can reduce the time of processing query.

Make sure indexing has been done.

Regards,

Neetesh

[Former Member]

Post your Query . I need to check joins between table used.

[Former Member]

Hi ,

My query Look likes below:

Select F1 from tablename where segment1='X', segment2='Y',segment3='X1',segment4='Y1',segment5='X2',segment6='Y2'

F1 is the field name.

Regards,

Yeshwanth R

[rajasekhar_reddy14]

How yoy connecting to Data Base??

direclty from Lookup code or calling Receiver Communication channel in UDF??

Regards,

Raj

[Former Member]

Hi ,

I am using JDBC look up Code.

Regards,

Yeshwanth

[rajasekhar_reddy14]

1)Dont perfrom data base connectivity directly from UDF,it is not a best practise,better to create JDBC Receiver Communication channel , then call this communication channel from UDF.

Data base perfrom depend on the query what you have written,

just perfrom single lookup to test the performaance,if it is well and good then the problem with your query.If you selecting data from more than one table we face perfromace issues,this could be the one reason,writing better query may solve your problem.

Regards,

Raj