DB2 query in an expression (Slow ?)

I am using DB2 as my datasource.
I have a query that takes 10 sec.
I needed to add report parameters to this query, so I converted it
into an expression. However, now the report is taking longer than the
original query - just because I converted the query into an
expression...
Has anyone seen this? What would be the work around for this?
ThanksJust to be sure. When you added parameters did you add it to the query or
did you do a filter. If you are using a filter then it brings over all the
data before applying the filter. Also, it is very unlikely that you needed
to change it into an expression.
Bruce L-C
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0408181551.5abb1d88@.posting.google.com...
> I am using DB2 as my datasource.
> I have a query that takes 10 sec.
> I needed to add report parameters to this query, so I converted it
> into an expression. However, now the report is taking longer than the
> original query - just because I converted the query into an
> expression...
> Has anyone seen this? What would be the work around for this?
> Thanks|||I am using it in a filter... so I guess you are right. It brings the
whole data over.. and it's slow.
Now I am doing as below:
select col1, col2 from tab1 where col3=?
But I want to be able to change the filter from col3=? to col4=?
How do I do that?
Thanks in advance.|||I'm confused. Is the issue that you want to dynamically craete the sql
string. Sometimes having it be col3 and sometimes col4.
You can put an expression in the generic designer. Or, you can have a
selection of all for the parameters where when they select all the parameter
value is % (is the wildcard for db2 a *, if so then use a * instead of a %).
Then change it to use like.
select col1, col2 from tab1 where col3 like ? and col4 like ?
Bruce L-C
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0408201037.59d458ce@.posting.google.com...
> I am using it in a filter... so I guess you are right. It brings the
> whole data over.. and it's slow.
> Now I am doing as below:
> select col1, col2 from tab1 where col3=?
> But I want to be able to change the filter from col3=? to col4=?
> How do I do that?
> Thanks in advance.|||Thanks for the reply.
isn't "Like" only for string data types?
My col3 and col4 are numeric columns...
what would I do there?|||OK, then what you need to do is have a dynamic query. This means you make
the query an expression. Use the generic query designer and put in the
expression. I suggest first assigning the expression to a textbox to debug
what you are doing, make sure you get the SQL string you want. Here is an
example posted yesterday by Donovan Smith of MS:
="select Col1, Col2 from Table" & iif (Parameters!FilterByDate.Value ==true, " where Date > '" & Parameters!FilterDate.Value & "'", "")
Bruce L-C
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0408201905.447fb7d6@.posting.google.com...
> Thanks for the reply.
> isn't "Like" only for string data types?
> My col3 and col4 are numeric columns...
> what would I do there?|||Bruce,
Expressions are bad performance!! Which is why I started this posting.
Here is what I am doing now. Hopefully this will work good:
select col 1, col2 from table1
where
((1 = ? AND col4 = ?) OR (1=? AND col5 = ?) OR (1=? AND col6 = ?))
This way I can say "1" / "0" to any parameter that I want to pass...
It doubles the number of my internal parameters... but it seems to work...
What do you think?|||No, expression do not give bad performance, filters give bad performance
because filters bring over all the data prior to filtering. It is two
different things. However, I only use expressions as a last alternative and
what you have below is definitely a good way to solve the problem.
Bruce L-C
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0408211025.46ab1122@.posting.google.com...
> Bruce,
> Expressions are bad performance!! Which is why I started this posting.
> Here is what I am doing now. Hopefully this will work good:
>
> select col 1, col2 from table1
> where
> ((1 = ? AND col4 = ?) OR (1=? AND col5 = ?) OR (1=? AND col6 = ?))
>
> This way I can say "1" / "0" to any parameter that I want to pass...
> It doubles the number of my internal parameters... but it seems to work...
> What do you think?