DB2 query in an expression (Slow ??) -- sql server reporting services

sql server reporting services : DB2 query in an expression (Slow ??)

creative NO[at]SPAM mailcity.com
8/18/2004 5:42:35 PM

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?

Bruce Loehle-Conger
8/19/2004 12:47:08 AM

Just 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

[quoted text, click to view]

creative NO[at]SPAM mailcity.com
8/20/2004 11:37:18 AM

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?

Bruce Loehle-Conger
8/20/2004 3:42:30 PM

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

[quoted text, click to view]

creative NO[at]SPAM mailcity.com
8/20/2004 8:05:07 PM

Thanks for the reply.

isn't "Like" only for string data types?

My col3 and col4 are numeric columns...

Bruce Loehle-Conger
8/21/2004 9:23:07 AM

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

[quoted text, click to view]

creative NO[at]SPAM mailcity.com
8/21/2004 11:25:39 AM

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...

Bruce Loehle-Conger
8/21/2004 6:04:58 PM

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

home · blog · groups

Questions? Comments? Contact the dn