performance difference: Dynamic Query for "order by" vs Sorting in Table properties

I have a question about performance difference.
I was hoping that there would be a difference in performance between
using a "dynamic query" for "order by" vs
"sorting" in "table/matrix properties"
My assumption was that once the dataset is populated, sorting it
through the table properties should be instant.
Unfortunately, I didn't see a difference.
Reporting services just appears to rerun the query...!!
Is this true? If so, shouldn't that be made better?
ThanksWhy is it that the MSFT team doesn't answer more than 20% of the
questions - specially if they have not been covered before?
This is an important question, and I would appreciate an answer.
I consulted the documentation and the books/ newsgroups that I could
find and I didn't find the answer to my question.|||Two points. When you see MS answering questions it occurs for two reasons.
One, a MS employee just jumping in and answering because they want to. It is
not a specified part of their job. For instance, someone from the testing
group or documentation group or a developer might jump in. Second, this is a
managed newsgroup. What that means is that it gets monitored for MSDN
subscription posting and makes sure they get answered (not necessarily by MS
but by anyone). So, your assumption that only MS answers count (20% figure)
is wayyyy off with regards to the purpose of the group. It is mostly peer
support. If guaranteed answers are a requirement for you then you need to
get an MSDN subscription. Visit the MS website for information on this. So
remember, this is peer support newsgroup for the most part and people are
volunteering their time to answer.
Now, about your question. You have already figured out what is going on
(when you change a parameter it reruns your query). Your question was a why
does it do this. I am not part of the development team but I have a good
understanding of how it all works. Although it seems obvious to you that the
way it currently works is brain dead, it is not as simple an issue as it
might seem. Parameters can be used multiple places. A single parameter can
be involved in expressions (like the sorting expression for a table in your
case). The same parameter can be part of a cascading parameter. You can have
multiple datasets so it could also at the same time be the input for a query
parameter, etc etc. So, even if they wanted to do as you suggest it is not
that straight forward. My feeling is that there is another reason as well.
Architecture. What you are interacting with, putting in the parameter, is
not a monolithic application. It is a portal to Reporting Services. You can
replace this with your own app (many people do) and use web services or URL
integration to tightly tie your app to Reporting Services. You can do this
so tightly that people do not even realize that the report is being rendered
somewhere else.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Harry" <harshwardhan@.mailcity.com> wrote in message
news:e48fb32a.0410260704.75ba5cb8@.posting.google.com...
> Why is it that the MSFT team doesn't answer more than 20% of the
> questions - specially if they have not been covered before?
> This is an important question, and I would appreciate an answer.
> I consulted the documentation and the books/ newsgroups that I could
> find and I didn't find the answer to my question.

Performance decline in parallel SPs execution

Hi,

We have a process that builds our data warehouse.

The processes execute SPs in serial order.

Each SP builds separate table.

Each table is build destructive (truncate and Insert Into).

I've tried to change the configuration by running 4 SP in parallel by SSIS to shorten the update time.

I've noticed in two declines in performance:

1. Each SP execution time is higher in the parallel execution in around 50% then in the serial execution. The CPU utilization is the same.

2. On each parallel execution we have a decline in performance of around 5 -10% compare to the previous parallel execution.

Do you have any directions to inquire?

Btw – we have Itanium 64bit x8 with 32GB memory

Thanks,

Assaf

It sounds like the stored procedures may be blocking each other when running in parallel.

You can verify if there is blocking by querying sys.dm_exec_requests and looking at the blocking_session_id column.

If you do see blocking you will want to look at the locks that stored procedure is taking, and determine if there is any way to reduce the locks held.

|||

Hi,

Sorry that I forgot mention it.

There is no locks between tables.

each SP right to only single table and Other SP do not read from other written tables.

Also validated it on server level.

Any other ideas?

Thanks,

Assaf

|||

If the stored procedures are inserting into tables in the same database then they could be contending for both physical IO on the data device and also be writing to the same log devices. The storage devices are quite often the bottlenecks in systems like this, and regardless of how fast the processors can run the disk can only write at a certain rate.

As for the 10% degredation I'm not so sure. Is the 10% cumulative run on run or are the subsequent run all just 10% faster than the first? What is the recovery model of your database? If it is not Simple then it may be having to extend the logs if they are not being cleared between runs. On a data warehouse which is completely rebuilt and does not then have data written to it you can probably just use Simple recovery and do a full backup after the load if necessary.

Performance Complex SQL Issue

Hi,
I have a table ORDER_DETAIL with 22 million rows which has an index
of
(person_id, code_id, created_dtt)

I have another ORDER table with 5 million rows which has an index
of
(order_dtt, person_id)

I have a small CODES table with 1000 rows which allows me to get
the 50 or so codes I need. My query needs to be something like this:

select od.person_id, od.code_id
from order_detail od, order o, codes c
where o.order_dtt between sysdate-365 and sysdate
and o.person_id = od.person_id
and od.code_id in (select code_id from codes where code_type =
'MYCODE')
and od.create_dtt between sysdate-365 and sysdate

But is this using the full index on the ORDER_DETAIL table? Should I
be using EXISTS in some fashion instead?

Accessing the ORDER_DETAIL table is a pain because it is so large, the
code_id's I need are a relatively small number but the date range is
about 25% of the table - same with the ORDER table. However it is the
only way I can get to filtering down to the code_id.

This is a simplification of the problem - but accurate - adding
additional indexes is not an option.

thanks!

Tim[posted and mailed, please reply in news]

Tim Smith (timasmith@.hotmail.com) writes:
> I have a table ORDER_DETAIL with 22 million rows which has an index of
> (person_id, code_id, created_dtt)
> I have another ORDER table with 5 million rows which has an index of
> (order_dtt, person_id)
> I have a small CODES table with 1000 rows which allows me to get
> the 50 or so codes I need. My query needs to be something like this:
> select od.person_id, od.code_id
> from order_detail od, order o, codes c
> where o.order_dtt between sysdate-365 and sysdate
> and o.person_id = od.person_id
> and od.code_id in (select code_id from codes where code_type =
> 'MYCODE')
> and od.create_dtt between sysdate-365 and sysdate
> But is this using the full index on the ORDER_DETAIL table? Should I
> be using EXISTS in some fashion instead?

First, what is sysdate supposed to be? I ask because there is nothing
called sysdate in MS SQL Server. While this forum is for MS SQL Server,
it has happened before, that people have asked questions that have
applied to other database engines. While some SQL questions are fairly
generic, performance questions are often engine specific, because
different DB engines uses difference strategies.

The query as written contains a superfluous occurance of codes in
the FROM clause. This could lead to a cartesian join between codes
and the rest of the result set.

The simplest way to involve codes in the query would be:

select od.person_id, od.code_id
from order_detail od, order o, codes c
where o.order_dtt between sysdate-365 and sysdate
and o.person_id = od.person_id
and od.code_id = c.code_id
and c.code_type = 'MYCODE'
and od.create_dtt between sysdate-365 and sysdate

Now, assuming that you are using MS SQL Server 2000, I think you would get
better performance without involving the orders table at all. Had you been
able to narrow down the range to say 1% of the table with the condition on
orders.order_dtt, it would be another issue.

To join orders and order_details, there are three different join
strategies that SQL Server can use Nested Loops, Merge Join and
Hash Join. Nested Loops, means that for each matching rows in
Orders, look up a row in Order Details. When you look up 25% of
the rows, you will access several data pages more than once, and
you will get more reads than for a plain table scan on Order Details.

Merge Join and Hash Join both involves scanning the tables, but only
doing it once. I don't know if a merge join is possible in this case.

Of course, here I am discussing the example query you posted. The
actual query you have may be different.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Performance - tweaking possible?

Hi,
we have an app that is written in a "special" way. Not using SP's. Instead,
dynamic SQL is used.
Also, it's written in a way that in order to update single order, the app
sends 4-5 SQL's; some of them INSERTs and some SELECTs.
Now here is the problem: some of the procesess require updating hundreds of
thousands of "orders". Obviously the app will not (and IS not) going to
scale. When such processes executed, SQL Server is getting hammered. After
some time in the process - one CPU is getting to 100% utilization and stays
this way until the end; this slows down the system to the point that other,
read-only clients get "timeout expired".
Now here is the bad part - even running on top of the line quad server does
not help the problem - since one CPU gets to 100% sooner or later.
I was reading technet and they suggest that SQL Server cannot split very
small requests under such conditions. The problem that there are millions of
very small (uder 100ms) requests coming from single connection.
My question is: can I configure the server in a way that more than 1 CPU is
used?
Note: changing the code is not an option at this point.
Thanks,
Dima.First, check the server settings to ensure parallel execution is enabled
(it should be by default).
Second, start looking for another job. I'm not entirely joking, here. If it
is as bad as you describe and the management is unwilling to fix the
underlying flaws, then you are in database hell. Not a pretty place.
You said nothing about the indexing on the tables. Are there any? Any
clustered indexes? This is the one place where you can make changes that
won't likely break client side code.
Bob Castleman
DBA Poseur
"Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
news:%23YnIj09MFHA.1436@.TK2MSFTNGP10.phx.gbl...
> Hi,
> we have an app that is written in a "special" way. Not using SP's.
> Instead, dynamic SQL is used.
> Also, it's written in a way that in order to update single order, the app
> sends 4-5 SQL's; some of them INSERTs and some SELECTs.
> Now here is the problem: some of the procesess require updating hundreds
> of thousands of "orders". Obviously the app will not (and IS not) going to
> scale. When such processes executed, SQL Server is getting hammered. After
> some time in the process - one CPU is getting to 100% utilization and
> stays this way until the end; this slows down the system to the point that
> other, read-only clients get "timeout expired".
> Now here is the bad part - even running on top of the line quad server
> does not help the problem - since one CPU gets to 100% sooner or later.
> I was reading technet and they suggest that SQL Server cannot split very
> small requests under such conditions. The problem that there are millions
> of very small (uder 100ms) requests coming from single connection.
> My question is: can I configure the server in a way that more than 1 CPU
> is used?
> Note: changing the code is not an option at this point.
> Thanks,
> Dima.
>|||"Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
news:%23YnIj09MFHA.1436@.TK2MSFTNGP10.phx.gbl...
> Hi,
> we have an app that is written in a "special" way. Not using SP's.
> Instead, dynamic SQL is used.
> Also, it's written in a way that in order to update single order, the app
> sends 4-5 SQL's; some of them INSERTs and some SELECTs.
> Now here is the problem: some of the procesess require updating hundreds
> of thousands of "orders". Obviously the app will not (and IS not) going to
> scale. When such processes executed, SQL Server is getting hammered. After
> some time in the process - one CPU is getting to 100% utilization and
> stays this way until the end; this slows down the system to the point that
> other, read-only clients get "timeout expired".
> Now here is the bad part - even running on top of the line quad server
> does not help the problem - since one CPU gets to 100% sooner or later.
> I was reading technet and they suggest that SQL Server cannot split very
> small requests under such conditions. The problem that there are millions
> of very small (uder 100ms) requests coming from single connection.
> My question is: can I configure the server in a way that more than 1 CPU
> is used?
> Note: changing the code is not an option at this point.
>
Let me see if I get it:
You have one session running batch processes which are comprised of hundreds
of thousands of inexpensive queries (single-row updates, selects, etc).
When this process is running other connections experience poor performance
and even timeouts.
And you wonder if you can configure the server to utilize multiple CPU's for
the work of the batch process. If that is your question, the answer it no.
And you wouldn't want to even if you could. In such a situation you want
the batch session to consume LESS not MORE server resources, in order to not
adversely affect other users. Fundamentally there's nothing fatal about
having a long-running batch job spike one of your CPU's (unless that batch
job holds locks required by other sessions).
First, determine if the other session's timeouts are caused by CPU
contention or by lock waits. If the other sessions are waiting on locks
held by the batch process, then your situation is grim. If they are victims
of CPU contention, you might be able to reduce the CPU cost of the queries
and increase the CPU resources of the server and wiggle out of trouble.
Under your constraints, here's what you can do. Capture and analyze the
commands coming from the batch connection. Examine the query plans for each
one and try to optimize them using the index tuning wizard. But "tuning"
the database server rarely has much impact on poorly written applictations.
It's worth a try, but there are limits to what you can do. You might get
lucky and find some glaring omission in the indexing which improves the
application's performance (the worse the applictation, the poorer the
indexing, right?).
David|||Thanks David.
I have done what you suggesting and I am sure that this is CPU contention
problem.
Indexes are there and work fine in smaller batches. However, when there is
huge batch once in a while it overloads the CPU which affects whole server.
I was thinking about some setting I can set to evenly spread the load. So
instead of loading 1 CPU for 100%, I could load 4 with 20 each.
In my view, it is really a matter of configuration. If the app opened new
connection for every statement - this situation would not arise, however
there would be another performance problem - with too many opened/closed
connections per sec.
So, if I could tell SQL Server to e.g. handle each statement in different
threads, say in round robin way - this could be a way out.
Note: this is temporary solution for couple months to survive
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eKUpMt%23MFHA.244@.tk2msftngp13.phx.gbl...
> "Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
> news:%23YnIj09MFHA.1436@.TK2MSFTNGP10.phx.gbl...
> Let me see if I get it:
> You have one session running batch processes which are comprised of
> hundreds of thousands of inexpensive queries (single-row updates, selects,
> etc). When this process is running other connections experience poor
> performance and even timeouts.
> And you wonder if you can configure the server to utilize multiple CPU's
> for the work of the batch process. If that is your question, the answer
> it no. And you wouldn't want to even if you could. In such a situation
> you want the batch session to consume LESS not MORE server resources, in
> order to not adversely affect other users. Fundamentally there's nothing
> fatal about having a long-running batch job spike one of your CPU's
> (unless that batch job holds locks required by other sessions).
> First, determine if the other session's timeouts are caused by CPU
> contention or by lock waits. If the other sessions are waiting on locks
> held by the batch process, then your situation is grim. If they are
> victims of CPU contention, you might be able to reduce the CPU cost of the
> queries and increase the CPU resources of the server and wiggle out of
> trouble.
> Under your constraints, here's what you can do. Capture and analyze the
> commands coming from the batch connection. Examine the query plans for
> each one and try to optimize them using the index tuning wizard. But
> "tuning" the database server rarely has much impact on poorly written
> applictations. It's worth a try, but there are limits to what you can do.
> You might get lucky and find some glaring omission in the indexing which
> improves the application's performance (the worse the applictation, the
> poorer the indexing, right?).
> David
>|||Thanks Bob.
The reason for not changing the app is that new solution will be replacing
this soon. However, in the meantime I'm looking into what other options I
have there.
I'm just looking for a way to configure the SQL Server to may be somehow
"spread" the load
"Bob Castleman" <nomail@.here> wrote in message
news:%23ojhJl%23MFHA.2604@.TK2MSFTNGP10.phx.gbl...
> First, check the server settings to ensure parallel execution is enabled
> (it should be by default).
> Second, start looking for another job. I'm not entirely joking, here. If
> it is as bad as you describe and the management is unwilling to fix the
> underlying flaws, then you are in database hell. Not a pretty place.
> You said nothing about the indexing on the tables. Are there any? Any
> clustered indexes? This is the one place where you can make changes that
> won't likely break client side code.
> Bob Castleman
> DBA Poseur
> "Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
> news:%23YnIj09MFHA.1436@.TK2MSFTNGP10.phx.gbl...
>|||On Mon, 28 Mar 2005 16:28:51 -0500, "Dima Semensky"
<dsemen@.newsgroup.nospam> wrote:
>this way until the end; this slows down the system to the point that other,
>read-only clients get "timeout expired".
For one thing, check your isolation levels.
Some of your read-only clients are probably blocked, if it's
acceptable to run with dirty data you can run with NOLOCK and see if
that helps.
I can't think of any server-side tweaks that can help.
J.|||"Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
news:uF$k6V$MFHA.3900@.TK2MSFTNGP10.phx.gbl...
> Thanks David.
> I have done what you suggesting and I am sure that this is CPU contention
> problem.
> Indexes are there and work fine in smaller batches. However, when there is
> huge batch once in a while it overloads the CPU which affects whole
> server.
> I was thinking about some setting I can set to evenly spread the load. So
> instead of loading 1 CPU for 100%, I could load 4 with 20 each.
>
That's not what would happen. If you parallelize the load it would spike 4
CPU's to 100% for 1/4 the time (actually more since parallel plans are less
efficient).

> In my view, it is really a matter of configuration. If the app opened new
> connection for every statement - this situation would not arise, however
> there would be another performance problem - with too many opened/closed
> connections per sec.
The app would still be issuing the statements one at at time. But I'm
. How many CPU's are in the server?

> So, if I could tell SQL Server to e.g. handle each statement in different
> threads, say in round robin way - this could be a way out.
That's not really the case. Each thread is scheduled on any available CPU.
So a single connection issuing multiple single statements might have its
work scheduled on various CPU's or might have its work scheduled on the same
CPU each time. Which one of these happens is a detail of the SQL Server
User Mode Scheduler. But the net effect is the same. The batch processing
connection will account eat 1000ms of CPU time every second. If that is
250ms on each of 4 processors or 1000ms on 1 of 4 processors, the net effect
on other connections is the same. The batch process will occupy 25% of
available CPU resources.
One thing to check, though is that the batch process isn't using any
parallel query plans. With a parallel query plan the batch process could
monopolize more that 1 CPU. There is a server-wide setting for the maximum
degree of parallelism (MAXDOP). To protect online work from being adversely
affected by large batch jobs, you might want to set MAXDOP=1.

> Note: this is temporary solution for couple months to survive
>
Been there.
David|||Sorry for the sarcasm, but I still think you're in database hell.
You didn't say anything about memory utilization. Is just the CPU getting
consumed? What about other resources?
If the read only clients are truely read only (i.e. NEVER updating), then
how about replicating to another server? Then the read only clients would
not have to contend with cpu consumption on the transactional database.
Bob Castleman
DBA Poseur
"Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
news:evWx4W$MFHA.2580@.TK2MSFTNGP09.phx.gbl...
> Thanks Bob.
> The reason for not changing the app is that new solution will be replacing
> this soon. However, in the meantime I'm looking into what other options I
> have there.
> I'm just looking for a way to configure the SQL Server to may be somehow
> "spread" the load
> "Bob Castleman" <nomail@.here> wrote in message
> news:%23ojhJl%23MFHA.2604@.TK2MSFTNGP10.phx.gbl...
>|||4 CPU's. 4GB RAM, SAN
yes, the app would issue one at a time and there is no way around it.
However, if for example the SQL Server sent each statement to different
thread - then all CPU would be working instead of single.
It's a mistery for me why SQL Server goes to 100% at some point if the app
sends the statements in sequential order. May be something related to the
way it works with threads...
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OafLAh$MFHA.3668@.TK2MSFTNGP14.phx.gbl...
> "Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
> news:uF$k6V$MFHA.3900@.TK2MSFTNGP10.phx.gbl...
> That's not what would happen. If you parallelize the load it would spike
> 4 CPU's to 100% for 1/4 the time (actually more since parallel plans are
> less efficient).
>
> The app would still be issuing the statements one at at time. But I'm
> . How many CPU's are in the server?
>
> That's not really the case. Each thread is scheduled on any available
> CPU. So a single connection issuing multiple single statements might have
> its work scheduled on various CPU's or might have its work scheduled on
> the same CPU each time. Which one of these happens is a detail of the SQL
> Server User Mode Scheduler. But the net effect is the same. The batch
> processing connection will account eat 1000ms of CPU time every second.
> If that is 250ms on each of 4 processors or 1000ms on 1 of 4 processors,
> the net effect on other connections is the same. The batch process will
> occupy 25% of available CPU resources.
> One thing to check, though is that the batch process isn't using any
> parallel query plans. With a parallel query plan the batch process could
> monopolize more that 1 CPU. There is a server-wide setting for the
> maximum degree of parallelism (MAXDOP). To protect online work from being
> adversely affected by large batch jobs, you might want to set MAXDOP=1.
>
> Been there.
> David
>|||Thanks.
All reads are NOLOCK.
For a long time I couldn't understand why the thing times out. No or little
blocking, no deadlocks, all reads are done with NOLOCK.
Finally, I found that one of the CPU is hammered at 100% for several hours.
At this point (even this is quad server), reads start slowing down and
eventually time out.
"JRStern" <jxstern@.bogus.com> wrote in message
news:748h41l7i889u1n297o616jpagucieb4gs@.
4ax.com...
> On Mon, 28 Mar 2005 16:28:51 -0500, "Dima Semensky"
> <dsemen@.newsgroup.nospam> wrote:
> For one thing, check your isolation levels.
> Some of your read-only clients are probably blocked, if it's
> acceptable to run with dirty data you can run with NOLOCK and see if
> that helps.
> I can't think of any server-side tweaks that can help.
> J.
>

Perfomance

I have stored procedure that is taking longer time in production than in
Test. I narrowed the longer time being taken to the order by clause in a
subquery. The execution plans look different in the test than in production
server. The databases are identical. What could be causing this?
The production server is much beefier than the test.
Thanks
Rahul
Plenty of things, e.g. out of date statistics, missing indexes,
fragmentation, different size of data...
Can you be more specific about how the "execution plans look different"? Is
one doing a seek and the other a scan, or is one doing a different kind of
join, or...?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message
news:uwMMzLbuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>I have stored procedure that is taking longer time in production than in
>Test. I narrowed the longer time being taken to the order by clause in a
>subquery. The execution plans look different in the test than in production
>server. The databases are identical. What could be causing this?
> The production server is much beefier than the test.
> Thanks
> Rahul
>

Perfomance

I have stored procedure that is taking longer time in production than in
Test. I narrowed the longer time being taken to the order by clause in a
subquery. The execution plans look different in the test than in production
server. The databases are identical. What could be causing this?
The production server is much beefier than the test.
Thanks
RahulPlenty of things, e.g. out of date statistics, missing indexes,
fragmentation, different size of data...
Can you be more specific about how the "execution plans look different"? Is
one doing a seek and the other a scan, or is one doing a different kind of
join, or...?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message
news:uwMMzLbuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>I have stored procedure that is taking longer time in production than in
>Test. I narrowed the longer time being taken to the order by clause in a
>subquery. The execution plans look different in the test than in production
>server. The databases are identical. What could be causing this?
> The production server is much beefier than the test.
> Thanks
> Rahul
>

Perfomance

I have stored procedure that is taking longer time in production than in
Test. I narrowed the longer time being taken to the order by clause in a
subquery. The execution plans look different in the test than in production
server. The databases are identical. What could be causing this?
The production server is much beefier than the test.
Thanks
RahulPlenty of things, e.g. out of date statistics, missing indexes,
fragmentation, different size of data...
Can you be more specific about how the "execution plans look different"? Is
one doing a seek and the other a scan, or is one doing a different kind of
join, or...?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message
news:uwMMzLbuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>I have stored procedure that is taking longer time in production than in
>Test. I narrowed the longer time being taken to the order by clause in a
>subquery. The execution plans look different in the test than in production
>server. The databases are identical. What could be causing this?
> The production server is much beefier than the test.
> Thanks
> Rahul
>