multiple preparedStatements in one method.
When one is creating preparedStatements will it have a significant impact on performance if you declare several of them in the same line of code or is it better if they are spaced out ?
Ps. I know that the code should be broken up so that this isn't an issue but in this case it isn't possible, There is too much interdependancies on the queries to find out what goes into the next part of the query.
If all you are going to say is to break it up I appreciate the effort but that is not a possible solution in this case. I am simply trying to understand weather or not creating all of the preparedStatements at once would be smarter than trying to break up their creation.
hopefully that makes sense.
[729 byte] By [
Aknibbsa] at [2007-11-27 2:04:08]
# 1
I don't quite understand your problem, can you give a rough code version of what you are trying to achieve?:)
# 2
> When one is creating preparedStatements will it have
> a significant impact on performance if you declare
> several of them in the same line of code or is it
> better if they are spaced out ?
makes no difference, as the thread will not return until the first statement is executed. so the answer is no.
> Ps. I know that the code should be broken up so that
> this isn't an issue but in this case it isn't
> possible, There is too much interdependancies on the
> queries to find out what goes into the next part of
> the query.
again, no worries here
you are fine, just code the application the way you THINK it should be
worry about performance later, if there's a problem
# 3
> When one is creating preparedStatements will it have
> a significant impact on performance if you declare
> several of them in the same line of code or is it
> better if they are spaced out ?
>
Performance in terms of prepared statements depends on how they are used after they are created, not during.
> Ps. I know that the code should be broken up so that
> this isn't an issue but in this case it isn't
> possible, There is too much interdependancies on the
> queries to find out what goes into the next part of
> the query.
Huh? Presumabably this isn't something that could be done with one statement rather that attempting artificial constructs.
# 4
> > When one is creating preparedStatements will it
> have
> > a significant impact on performance if you declare
> > several of them in the same line of code or is it
> > better if they are spaced out ?
>
> makes no difference, as the thread will not return
> until the first statement is executed. so the answer
> is no.
Right but I was thinking more on the database side if there was outside work that may have to be done, and while that is executing my code could be completing some other task. I was looking at it as though the java code was one thread and the db part was another, if there was work happening on the db that was making the java thread hold. The reason I ask is because based on testing it seemed to be slightly faster to break them up slightly. (Granted that may because of load on the database or network traffic as it's not a dedicated environment - another part of the reason I asked).
>
> > Ps. I know that the code should be broken up so
> that
> > this isn't an issue but in this case it isn't
> > possible, There is too much interdependancies on
> the
> > queries to find out what goes into the next part
> of
> > the query.
>
> again, no worries here
> you are fine, just code the application the way you
> THINK it should be
> worry about performance later, if there's a problem
I am at that stage. I did it the way that I thought it should be done but the performance was terrible. It was taking about 7 seconds to run. I went through the code and managed to get that down to 3 seconds. Just the other day I discovered that using SCROLL_INSENSITVE over SCROLL_SENSITIVE dropped that number to between 3/4 and 1.5 seconds. If I could cut that by 50 % one more time it would fall into an acceptable range.
# 5
> > When one is creating preparedStatements will it
> have
> > a significant impact on performance if you declare
> > several of them in the same line of code or is it
> > better if they are spaced out ?
> >
>
> Performance in terms of prepared statements depends
> on how they are used after they are created, not
> during.
How can one effect their use once created ? Isn't it simply plug in values and get back a result set ?
>
> > Ps. I know that the code should be broken up so
> that
> > this isn't an issue but in this case it isn't
> > possible, There is too much interdependancies on
> the
> > queries to find out what goes into the next part
> of
> > the query.
>
> Huh? Presumabably this isn't something that could be
> done with one statement rather that attempting
> artificial constructs.
It may be possible that it could be done in one statement but there are two problems with that
1) It would be some gargantuan horrible mess that no-one would want to maintain. There are several tables that all depend on another table which depend on a third table which depends on the second which depends on the result of 1 and 3 combined etc etc. It's hard enough to read when broken up into blocks as it is. If it wasn't for all the interdependancies I could at least make it all seperate methods, but from what I understand passing around resultsets is a terrible idea.
2) I don't even think I could write the sql required and have it return in any sort of reasonable time.
thanks for all the input.
# 6
> When one is creating preparedStatements will it have
> a significant impact on performance if you declare
> several of them in the same line of code or is it
> better if they are spaced out ?
Well, there's no difference betweenint fred=42, wilma=137;
andint fred=42;
int wilma=137;
andint fred=42;
int wilma=137;
The compiler treats them identically. So I don't see why PreparedStatements would be any different.
But I can't imagine declaring more than one PreparedStatement on a single line anyway. Not if you're assigning a value, at least. Or am I misinterpreting your question?
# 7
> > When one is creating preparedStatements will it
> have
> > a significant impact on performance if you declare
> > several of them in the same line of code or is it
> > better if they are spaced out ?
>
> Well, there's no difference betweenint fred=42,
> wilma=137;
andint fred=42;
> int wilma=137;
andint fred=42;
>
> int wilma=137;
The compiler treats them
> identically. So I don't see why PreparedStatements
> would be any different.
My thinking was:
The analogy I can think of is going to a parts desk. I need 2 items. It takes them 10 seconds to give me the first item, but then they have to do a minute of paperwork. If I can go out and do something while they do that minute of paperwork things will be faster right ?
>
> But I can't imagine declaring more than one
> PreparedStatement on a single line anyway.
I have something like this: (example off the top of my head)
In one method I have 6 preparedStatements. P1 is used to find P2 and P3. P3 is used to find P4, P4 and P2 are used to get P5, P5 is used to get a new P1, P2 and P5 give a new P3 and P3 and P1 give P6 (Basically it's a horribly convoluted system to get a value out)
I am talking doing :
p1 = conn.preparestatement(query1, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
p2 = conn.preparestatement(query2, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
p3 = conn.preparestatement(query3, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
p4 = conn.preparestatement(query4, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
p5 = conn.preparestatement(query5, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
p6 = conn.preparestatement(query6, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
>Not if
> you're assigning a value, at least. Or am I
> misinterpreting your question?
# 8
> How can one effect their use once created ? Isn't it
> simply plug in values and get back a result set ?
>
Because it already exists in the database and via that there is reuse when it is actually used.
Additionally there is batch processing as well.
> >
> > Huh? Presumabably this isn't something that could be
> > done with one statement rather that attempting
> > artificial constructs.
>
> It may be possible that it could be done in one
> statement but there are two problems with that
> 1) It would be some gargantuan horrible mess that
> no-one would want to maintain. There are several
> tables that all depend on another table which depend
> on a third table which depends on the second which
> depends on the result of 1 and 3 combined etc etc.
> It's hard enough to read when broken up into blocks
> as it is. If it wasn't for all the
> interdependancies I could at least make it all
> seperate methods, but from what I understand passing
> around resultsets is a terrible idea.
Then some where you are missing the point.
When you optimize code the point is to make it faster because it is a problem. Thus making it harder to maintain is an acceptable trade off.
Presumably there is some reason you can't use stored procs for this which not only would be faster but would be easier to maintain as well.
>
> 2) I don't even think I could write the sql required
> and have it return in any sort of reasonable time.
Huh?
I suspect that if there is a solution that involves a single statement then it is going to be faster than anything else that you can do.
Note additionally that drivers can usually handle more than one statement in a single call.
# 9
> In one method I have 6 preparedStatements. P1 is
> used to find P2 and P3. P3 is used to find P4, P4
> and P2 are used to get P5, P5 is used to get a new
> P1, P2 and P5 give a new P3 and P3 and P1 give P6
> (Basically it's a horribly convoluted system to get
> a value out)
>
Not particularly unless it actually keeps recursing further.
# 10
> > How can one effect their use once created ? Isn't
> it
> > simply plug in values and get back a result set ?
> >
>
> Because it already exists in the database and via
> that there is reuse when it is actually used.
Right which is why I couldn't see how I could make any difference in this time. I know that they are re-used making them faster. (I know what you mean even though I can't convey exactly what I am trying to say.)
> Additionally there is batch processing as well.
I'll have to have a look and see if this can be any better.
> >
> > Huh? Presumabably this isn't something that could
> be
> > done with one statement rather that attempting
> > artificial constructs.
>
> It may be possible that it could be done in one
> statement but there are two problems with that
> 1) It would be some gargantuan horrible mess that
> no-one would want to maintain. There are several
> tables that all depend on another table which
> depend
> on a third table which depends on the second which
> depends on the result of 1 and 3 combined etc etc.
> It's hard enough to read when broken up into blocks
> as it is. If it wasn't for all the
> interdependancies I could at least make it all
> seperate methods, but from what I understand
> passing
>around resultsets is a terrible idea.
> Then some where you are missing the point.
>
> When you optimize code the point is to make it faster
> because it is a problem. Thus making it harder to
> maintain is an acceptable trade off.
Good point.
> Presumably there is some reason you can't use stored
> procs for this which not only would be faster but
> would be easier to maintain as well.
>
>
> >
> > 2) I don't even think I could write the sql
> required
> > and have it return in any sort of reasonable time.
>
> Huh?
Basically I'm saying when it comes to sql I am not very good at coming up with complex queries that work.
>
> I suspect that if there is a solution that involves a
> single statement then it is going to be faster than
> anything else that you can do.
>
> Note additionally that drivers can usually handle
> more than one statement in a single call.
It's not a single statement in a call, it's the same statement where the parameters change several times over the course of the method.
# 11
> > In one method I have 6 preparedStatements. P1 is
> > used to find P2 and P3. P3 is used to find P4, P4
> > and P2 are used to get P5, P5 is used to get a new
> > P1, P2 and P5 give a new P3 and P3 and P1 give P6
> > (Basically it's a horribly convoluted system to
> get
> > a value out)
> >
>
> Not particularly unless it actually keeps recursing
> further.
This was the sample as I do not feel like writing out the full actual structure.
# 12
> > > How can one effect their use once created ?
> Isn't
> it
> > simply plug in values and get back a result set ?
> >
>
> Because it already exists in the database and via
> that there is reuse when it is actually used.
>
> Right which is why I couldn't see how I could make
> any difference in this time. I know that they are
> re-used making them faster. (I know what you mean
> even though I can't convey exactly what I am trying
> to say.)
No idea what you are trying to say. But regardless the performance is achieved via usage not creation.
>
> It's not a single statement in a call, it's the same
> statement where the parameters change several times
> over the course of the method.
Which sounds like a batch statement.
# 13
> > > > How can one effect their use once created ?
> > Isn't
> > it
> > > simply plug in values and get back a result set
> ?
> > >
> >
> > Because it already exists in the database and via
> > that there is reuse when it is actually used.
> >
> > Right which is why I couldn't see how I could make
> > any difference in this time. I know that they are
> > re-used making them faster. (I know what you mean
> > even though I can't convey exactly what I am
> trying
> > to say.)
>
> No idea what you are trying to say. But regardless
> the performance is achieved via usage not creation.
What I was trying to do was see if there was any manner in which to increase performance during the creation as well - basically reducing the overhead while creating them.
>
> >
> > It's not a single statement in a call, it's the
> same
> > statement where the parameters change several
> times
> > over the course of the method.
>
> Which sounds like a batch statement.
Again I'm going to have to look into this one.
thanks.
*Edit*
Stupid server won't let me award points. I'll have to try it again in a while.
