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?

db server slow - SET TRANSACTION LEVEL READ COMMITTED causing it?

Our entire server is extremely slow right now. We are getting locks left
and right. We've got locks on databases that we don't even use. When we
look at the activity and the locks that are taking the longest, we notice
that this statement is occurring over and over again:
SET TRANSACTION LEVEL READ COMMITTED
This is confusing to me because I thought this was the default? So I guess
I thought that this was being internally executed, or the equivalent, all
along. So, I don't know what to do or what to look at. Is there a setting
that could have been changed that would cause this behavior?
thanks,
Cory
Cory
> This is confusing to me because I thought this was the default?
Yes , it is
Do you have set SET TRANSACTION LEVEL READ COMMITTED in all stored
procedures. Its per connection ?
Try running SQL Server Profiler to see what is going on
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:uRW4%2370LHHA.536@.TK2MSFTNGP02.phx.gbl...
> Our entire server is extremely slow right now. We are getting locks left
> and right. We've got locks on databases that we don't even use. When we
> look at the activity and the locks that are taking the longest, we notice
> that this statement is occurring over and over again:
> SET TRANSACTION LEVEL READ COMMITTED
> This is confusing to me because I thought this was the default? So I
> guess I thought that this was being internally executed, or the
> equivalent, all along. So, I don't know what to do or what to look at.
> Is there a setting that could have been changed that would cause this
> behavior?
>
> thanks,
> Cory
>
>

db server slow - SET TRANSACTION LEVEL READ COMMITTED causing it?

Our entire server is extremely slow right now. We are getting locks left
and right. We've got locks on databases that we don't even use. When we
look at the activity and the locks that are taking the longest, we notice
that this statement is occurring over and over again:
SET TRANSACTION LEVEL READ COMMITTED
This is confusing to me because I thought this was the default? So I guess
I thought that this was being internally executed, or the equivalent, all
along. So, I don't know what to do or what to look at. Is there a setting
that could have been changed that would cause this behavior?
thanks,
CoryCory
> This is confusing to me because I thought this was the default?
Yes , it is
Do you have set SET TRANSACTION LEVEL READ COMMITTED in all stored
procedures. Its per connection ?
Try running SQL Server Profiler to see what is going on
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:uRW4%2370LHHA.536@.TK2MSFTNGP02.phx.gbl...
> Our entire server is extremely slow right now. We are getting locks left
> and right. We've got locks on databases that we don't even use. When we
> look at the activity and the locks that are taking the longest, we notice
> that this statement is occurring over and over again:
> SET TRANSACTION LEVEL READ COMMITTED
> This is confusing to me because I thought this was the default? So I
> guess I thought that this was being internally executed, or the
> equivalent, all along. So, I don't know what to do or what to look at.
> Is there a setting that could have been changed that would cause this
> behavior?
>
> thanks,
> Cory
>
>

db server slow - SET TRANSACTION LEVEL READ COMMITTED causing it?

Our entire server is extremely slow right now. We are getting locks left
and right. We've got locks on databases that we don't even use. When we
look at the activity and the locks that are taking the longest, we notice
that this statement is occurring over and over again:
SET TRANSACTION LEVEL READ COMMITTED
This is confusing to me because I thought this was the default? So I guess
I thought that this was being internally executed, or the equivalent, all
along. So, I don't know what to do or what to look at. Is there a setting
that could have been changed that would cause this behavior?
thanks,
CoryCory
> This is confusing to me because I thought this was the default?
Yes , it is
Do you have set SET TRANSACTION LEVEL READ COMMITTED in all stored
procedures. Its per connection ?
Try running SQL Server Profiler to see what is going on
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:uRW4%2370LHHA.536@.TK2MSFTNGP02.phx.gbl...
> Our entire server is extremely slow right now. We are getting locks left
> and right. We've got locks on databases that we don't even use. When we
> look at the activity and the locks that are taking the longest, we notice
> that this statement is occurring over and over again:
> SET TRANSACTION LEVEL READ COMMITTED
> This is confusing to me because I thought this was the default? So I
> guess I thought that this was being internally executed, or the
> equivalent, all along. So, I don't know what to do or what to look at.
> Is there a setting that could have been changed that would cause this
> behavior?
>
> thanks,
> Cory
>
>sql

DB Problem(slowed down drastically)

hello,
my database is getting down slow drastically
i am unable to understand the reason behind it
A query which usually doesn't take a second to execute is now taking
some min's to execute..
even i have truncated the log ..
and i have even run the DBCC CheckDB.

It runs fine for some time after performing these steps,but suddenly gets down at some point..

help rqd immediately
thnx & regards
pavan kashyapYou said "It runs fine for some time after performing these steps,but suddenly gets down at some point.." exactly how log does it take for the slow down to occure, minutes, hours, days?

When was the last time you updated statistics, rebuilt indexes and re-compiled stored procs?|||After every 4 hours its going down. its starts again once restarted the database. we also have a maintenance plan running|||Is this an OLTP or DSS system?

What do your maintance plans do?

When was the last time you updated statistics, rebuilt indexes and re-compiled stored procs?

What types of queries/updates occure during this 4 hour time period?