question about performance of partition tables

1. When I partition a table ( 90 million rows ) into
five partitions. (create view as select * from table1
union select * from table 2 union ...)
I get a 2 x times query time increase, but by the time
#partitions = 10, the query time is same as the large
table. when #partitions = 15 query time > large table.
Ideally I would want to partition it into 50 states.
Why is it that parallel query execution does not speedup
the process.
The indexes are virtually the same ( most cases covering
non-clustered indexes.)
Is it because of merging the results and sorting them
( because of the group by/order by clauses )? however
the query plan says those are 0%.
You can ask me any question about the query statement,
table format and query plan execution.
I thought about this for a week and don't have an answer.There will always come a point where the resources will be overwhelmed byt
he requests. The more queries you attempt to do simultaneously the less
chances are they will each perform as well as running by them selves. At
some point the cpu or disk queues will start playing a factor. You also
have more overhead when trying to put all the results together and have more
chance of tempdb being a factor.
--
Andrew J. Kelly
SQL Server MVP
"Ramesh" <anonymous@.discussions.microsoft.com> wrote in message
news:119d201c3f56c$fba82640$a001280a@.phx.gbl...
> 1. When I partition a table ( 90 million rows ) into
> five partitions. (create view as select * from table1
> union select * from table 2 union ...)
> I get a 2 x times query time increase, but by the time
> #partitions = 10, the query time is same as the large
> table. when #partitions = 15 query time > large table.
> Ideally I would want to partition it into 50 states.
> Why is it that parallel query execution does not speedup
> the process.
> The indexes are virtually the same ( most cases covering
> non-clustered indexes.)
> Is it because of merging the results and sorting them
> ( because of the group by/order by clauses )? however
> the query plan says those are 0%.
> You can ask me any question about the query statement,
> table format and query plan execution.
> I thought about this for a week and don't have an answer.|||cpu utilization seems low : < 40 %
disk I/O queues < 15 on RAID-5 containing tables
(6 disks) and < 3 on RAID-10 containing indexes
(6 disks)
tempdb and log on seperate RAID-10 ( disk queues < 2)
(4 disks)
It is true that queries which only use indexes (covering)
work well upto 10 partitions
queries which would need to use leaf keys and then use
clustering keys work well upto 5 partitions.
Is there something I should know on creating the
clustered/non-clustered indexes different on the
partitioned tables?
Is there some material I can read on?
Is the windows 2000 server be part of the bottleneck?
Is there any specific parameters I should target and
resolve. ( I am aiming for 60 partitions : each with
1-2 million rows each to reduce skewness). I thought this
would speed up the queries 2 orders of magnitude. Sadly
this does not work the way I calculated.
>--Original Message--
>There will always come a point where the resources will
be overwhelmed byt
>he requests. The more queries you attempt to do
simultaneously the less
>chances are they will each perform as well as running by
them selves. At
>some point the cpu or disk queues will start playing a
factor. You also
>have more overhead when trying to put all the results
together and have more
>chance of tempdb being a factor.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Ramesh" <anonymous@.discussions.microsoft.com> wrote in
message
>news:119d201c3f56c$fba82640$a001280a@.phx.gbl...
>> 1. When I partition a table ( 90 million rows ) into
>> five partitions. (create view as select * from table1
>> union select * from table 2 union ...)
>> I get a 2 x times query time increase, but by the
time
>> #partitions = 10, the query time is same as the large
>> table. when #partitions = 15 query time > large table.
>> Ideally I would want to partition it into 50 states.
>> Why is it that parallel query execution does not
speedup
>> the process.
>> The indexes are virtually the same ( most cases
covering
>> non-clustered indexes.)
>> Is it because of merging the results and sorting them
>> ( because of the group by/order by clauses )? however
>> the query plan says those are 0%.
>> You can ask me any question about the query statement,
>> table format and query plan execution.
>> I thought about this for a week and don't have an
answer.
>
>.
>|||Disk queues of 15 or so on a 6 disk Raid 5 are still high enough to warrant
paying attention to them. That means you are in fact waiting on disk I/O.
You might get better results from combining all 12 disks from the indexes
and data into one Raid 10. As for the partitioning that is tough to say.
Most partitioning requires a lot of testing under your specific conditions
to determine which is best for you. Are you sure you need to partition that
table at all? 50 partitions is a lot and you only have 90 million rows.
What are the typical queries like against this table? Maybe just adjusting
the clustered index will do.
--
Andrew J. Kelly
SQL Server MVP
"Ramesh Krishnan" <anonymous@.discussions.microsoft.com> wrote in message
news:11d6201c3f62b$ea334100$a301280a@.phx.gbl...
> cpu utilization seems low : < 40 %
> disk I/O queues < 15 on RAID-5 containing tables
> (6 disks) and < 3 on RAID-10 containing indexes
> (6 disks)
> tempdb and log on seperate RAID-10 ( disk queues < 2)
> (4 disks)
> It is true that queries which only use indexes (covering)
> work well upto 10 partitions
> queries which would need to use leaf keys and then use
> clustering keys work well upto 5 partitions.
> Is there something I should know on creating the
> clustered/non-clustered indexes different on the
> partitioned tables?
> Is there some material I can read on?
> Is the windows 2000 server be part of the bottleneck?
> Is there any specific parameters I should target and
> resolve. ( I am aiming for 60 partitions : each with
> 1-2 million rows each to reduce skewness). I thought this
> would speed up the queries 2 orders of magnitude. Sadly
> this does not work the way I calculated.
> >--Original Message--
> >There will always come a point where the resources will
> be overwhelmed byt
> >he requests. The more queries you attempt to do
> simultaneously the less
> >chances are they will each perform as well as running by
> them selves. At
> >some point the cpu or disk queues will start playing a
> factor. You also
> >have more overhead when trying to put all the results
> together and have more
> >chance of tempdb being a factor.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Ramesh" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:119d201c3f56c$fba82640$a001280a@.phx.gbl...
> >> 1. When I partition a table ( 90 million rows ) into
> >> five partitions. (create view as select * from table1
> >> union select * from table 2 union ...)
> >> I get a 2 x times query time increase, but by the
> time
> >> #partitions = 10, the query time is same as the large
> >> table. when #partitions = 15 query time > large table.
> >>
> >> Ideally I would want to partition it into 50 states.
> >> Why is it that parallel query execution does not
> speedup
> >> the process.
> >>
> >> The indexes are virtually the same ( most cases
> covering
> >> non-clustered indexes.)
> >> Is it because of merging the results and sorting them
> >> ( because of the group by/order by clauses )? however
> >> the query plan says those are 0%.
> >>
> >> You can ask me any question about the query statement,
> >> table format and query plan execution.
> >>
> >> I thought about this for a week and don't have an
> answer.
> >
> >
> >.
> >sql

Question about joining 2 tables??

Hi everyone,
I have problem to join 2 tables together to show the selected results
into a datagrid.
table1 hosts all customer personal information.
table2 hosts all the trasaction records for each customer
table1 has fields such as "customerID", "CustomerName" and "TelNumber".
(customerID is primary key in table1)
table2 has fields such as "cusomterID", "ComponentPurchase" and
"Quantity"
table1
CustomerID CustomerName TelNumber
55 John 1234566
56 David 6589211
table2
CustomerID ComponentPurchase Quantity
55 componentA 10
55 componentB 5
55 componentC 1
56 componentA 2
how can i join these two tables and get the result datagrid that list
each customer personal information
and the component he/she purchase listed below his/her personal
information' such as look like following
one
John 1234566
componentA 10
componentB 5
componentC 1
David 6589211
componentA 2
Could everyone give me some suggestion, or any article i can read?
thanks for your time
WingWing wrote:
> Hi everyone,
> I have problem to join 2 tables together to show the selected results
> into a datagrid.
> table1 hosts all customer personal information.
> table2 hosts all the trasaction records for each customer
> table1 has fields such as "customerID", "CustomerName" and
> "TelNumber". (customerID is primary key in table1)
> table2 has fields such as "cusomterID", "ComponentPurchase" and
> "Quantity"
> table1
> CustomerID CustomerName TelNumber
> 55 John 1234566
> 56 David 6589211
>
> table2
> CustomerID ComponentPurchase Quantity
> 55 componentA 10
> 55 componentB 5
> 55 componentC 1
> 56 componentA 2
>
> how can i join these two tables and get the result datagrid that list
> each customer personal information
> and the component he/she purchase listed below his/her personal
> information' such as look like following
> one
> John 1234566
> componentA 10
> componentB 5
> componentC 1
> David 6589211
> componentA 2
> Could everyone give me some suggestion, or any article i can read?
> thanks for your time
> Wing
This is a UI design issue and not a SQL Server one. I assume you know
how to write a SELECT statement that joins two tables together. How that
information is evenutally displays in the UI depends on what controls
you are using and your code. This is not really a SQL issue. Many data
grids support a parent-child style layout, but that's a question for
another group.
David Gugick - SQL Server MVP
Quest Software|||Thanks for your comment
Wing

Question about joining 2 tables??

Hi everyone,
I have problem to join 2 tables together to show the selected results
into a datagrid.
table1 hosts all customer personal information.
table2 hosts all the trasaction records for each customer
table1 has fields such as "customerID", "CustomerName" and "TelNumber".
(customerID is primary key in table1)
table2 has fields such as "cusomterID", "ComponentPurchase" and
"Quantity"
table1
CustomerID CustomerName TelNumber
55 John 1234566
56 David 6589211
table2
CustomerID ComponentPurchase Quantity
55 componentA 10
55 componentB 5
55 componentC 1
56 componentA 2
how can i join these two tables and get the result datagrid that list
each customer personal information
and the component he/she purchase listed below his/her personal
information' such as look like following
one
John 1234566
componentA 10
componentB 5
componentC 1
David 6589211
componentA 2
Could everyone give me some suggestion, or any article i can read?
thanks for your time
WingWing wrote:
> Hi everyone,
> I have problem to join 2 tables together to show the selected results
> into a datagrid.
> table1 hosts all customer personal information.
> table2 hosts all the trasaction records for each customer
> table1 has fields such as "customerID", "CustomerName" and
> "TelNumber". (customerID is primary key in table1)
> table2 has fields such as "cusomterID", "ComponentPurchase" and
> "Quantity"
> table1
> CustomerID CustomerName TelNumber
> 55 John 1234566
> 56 David 6589211
>
> table2
> CustomerID ComponentPurchase Quantity
> 55 componentA 10
> 55 componentB 5
> 55 componentC 1
> 56 componentA 2
>
> how can i join these two tables and get the result datagrid that list
> each customer personal information
> and the component he/she purchase listed below his/her personal
> information' such as look like following
> one
> John 1234566
> componentA 10
> componentB 5
> componentC 1
> David 6589211
> componentA 2
> Could everyone give me some suggestion, or any article i can read?
> thanks for your time
> Wing
This is a UI design issue and not a SQL Server one. I assume you know
how to write a SELECT statement that joins two tables together. How that
information is evenutally displays in the UI depends on what controls
you are using and your code. This is not really a SQL issue. Many data
grids support a parent-child style layout, but that's a question for
another group.
--
David Gugick - SQL Server MVP
Quest Software|||Thanks for your comment
Wing

Question about joining 2 tables??

Hi everyone,
I have problem to join 2 tables together to show the selected results
into a datagrid.
table1 hosts all customer personal information.
table2 hosts all the trasaction records for each customer
table1 has fields such as "customerID", "CustomerName" and "TelNumber".
(customerID is primary key in table1)
table2 has fields such as "cusomterID", "ComponentPurchase" and
"Quantity"
table1
CustomerID CustomerName TelNumber
55 John 1234566
56 David 6589211
table2
CustomerID ComponentPurchase Quantity
55 componentA 10
55 componentB 5
55 componentC 1
56 componentA 2
how can i join these two tables and get the result datagrid that list
each customer personal information
and the component he/she purchase listed below his/her personal
information? such as look like following
one
John 1234566
componentA 10
componentB 5
componentC 1
David 6589211
componentA 2
Could everyone give me some suggestion, or any article i can read?
thanks for your time
Wing
Wing wrote:
> Hi everyone,
> I have problem to join 2 tables together to show the selected results
> into a datagrid.
> table1 hosts all customer personal information.
> table2 hosts all the trasaction records for each customer
> table1 has fields such as "customerID", "CustomerName" and
> "TelNumber". (customerID is primary key in table1)
> table2 has fields such as "cusomterID", "ComponentPurchase" and
> "Quantity"
> table1
> CustomerID CustomerName TelNumber
> 55 John 1234566
> 56 David 6589211
>
> table2
> CustomerID ComponentPurchase Quantity
> 55 componentA 10
> 55 componentB 5
> 55 componentC 1
> 56 componentA 2
>
> how can i join these two tables and get the result datagrid that list
> each customer personal information
> and the component he/she purchase listed below his/her personal
> information? such as look like following
> one
> John 1234566
> componentA 10
> componentB 5
> componentC 1
> David 6589211
> componentA 2
> Could everyone give me some suggestion, or any article i can read?
> thanks for your time
> Wing
This is a UI design issue and not a SQL Server one. I assume you know
how to write a SELECT statement that joins two tables together. How that
information is evenutally displays in the UI depends on what controls
you are using and your code. This is not really a SQL issue. Many data
grids support a parent-child style layout, but that's a question for
another group.
David Gugick - SQL Server MVP
Quest Software
|||Thanks for your comment
Wing

Question about Joining 2 tables?

Hi everyone,

I have problem to join 2 tables together to show the selected results
into a datagrid.

table1 hosts all customer personal information.
table2 hosts all the trasaction records for each customer

table1 has fields such as "customerID", "CustomerName" and "TelNumber".
(customerID is primary key in table1)

table2 has fields such as "cusomterID", "ComponentPurchase" and
"Quantity"

table1

CustomerID CustomerName TelNumber
55 John 1234566
56 David 6589211

table2

CustomerID ComponentPurchase Quantity
55 componentA 10
55 componentB 5
55 componentC 1
56 componentA 2

how can i join these two tables and get the result datagrid that list
each customer personal information and the component he/she purchased
listed below his/her personal information?? such as look like following
one

--------------
John 1234566
componentA 10
componentB 5
componentC 1
--------------
David 6589211
componentA 2
--------------

Could everyone give me some suggestion, or any article i can read?

thanks for your time

Wing"Wing" <li.alwin@.gmail.com> wrote in news:1140828715.240420.308800
@.u72g2000cwu.googlegroups.com:

> Hi everyone,
> I have problem to join 2 tables together to show the selected results
> into a datagrid.
>
> Could everyone give me some suggestion, or any article i can read?

My suggestion:

Do your own homework. You'll learn more from the class that way.|||I am doing my own homework, just need some knowledge that to start my
work.
thanks for comment.sql

Question about EXECUTE AS USER

Can anyone tell me why I'll receive a error via following steps? Thanks in advance!

1. Create a database “TESTDB” and a table “Table1”

2. Create a login “TestLogin”, which is db_owner roles of both msdb and TESTDB

3. Create a DML trigger for Table1 by following script

CREATE TRIGGER TRG1

ON Table1

FOR INSERT, UPDATE, DELETE

AS

SELECT * FROM msdb..sysjobs

GO

4. Open SSMS, login as TestLogin, execute following statement

SELECT * FROM msdb..sysjobs

It will succeed to select data in msdb..sysjobs

5. Open another SSMS, login as sa, execute following statement

use TEST

execute as user='TestLogin'

select * From msdb..sysjobs

I receive an error about permission to select data from sysjobs, why?

Add the EXECUTE AS to the end of the Query.

SELECT *

FROM msdb..sysjobs

EXECUTE AS user='TestLogin'

|||

The reason why “SELECT * FROM msdb..sysjobs”fails under an impersonated context (EXECUTE AS USER) is because the impersonation mechanism you are calling is (by default) bound only to the current database (TESTDB), but you are trying to access data from a different DB (msdb).

As Arnie suggested, one potential solution may be use the current execution context to gether the information from msdb, and after that impersonate, but it would really depend on what you are trying to accomplish on this task.

I would recommend the following topics from BOL:

· Understanding Context Switching (http://msdn2.microsoft.com/en-us/library/ms191296.aspx)

· Extending Database Impersonation by Using EXECUTE AS (http://msdn2.microsoft.com/en-us/library/ms188304.aspx )

My guess s that the trigger you are trying to create is intended to have a controlled escalation of the privileges of the invoker in order to gather information from msdb and accomplish some task, correct?

If this is the case, I would suggest evaluating using digital signatures for this task. I have an example in my blog that probably may help you to get started (not exactly the same scenario, but I hope it will be useful):
http://blogs.msdn.com/raulga/archive/2006/10/30/using-a-digital-signature-as-a-secondary-identity-to-replace-cross-database-ownership-chaining.aspx

If you have further questions, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine