Hi,
Assuming that I have partitioned my Orders table based on OrderDate column
on 4 disks (each quarter on separate disk), do I need 4 processors for real
parallelism?
I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
resultset in parallel by having one CPU, or necessarily I must have one a
separate CPU for each disk?
Thanks in advance,
Leila"Leila" <Leilas@.hotpop.com> wrote in message
news:#kFBVxrVHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Assuming that I have partitioned my Orders table based on OrderDate column
> on 4 disks (each quarter on separate disk), do I need 4 processors for
> real parallelism?
> I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
> resultset in parallel by having one CPU, or necessarily I must have one a
> separate CPU for each disk?
Only computers that have more than one CPU can use parallel queries.
Degree of Parallelism
http://msdn2.microsoft.com/en-us/library/ms188611.aspx
However there's no rule that you have to put each partition on its own disk.
Instead you could store all or some of your partitions on a filegroup that
spans all the disks. Then every query will use all the disks evenly.
David
Showing posts with label quarter. Show all posts
Showing posts with label quarter. Show all posts
Monday, March 19, 2012
Data Partitioning (SQL Server 2005)
Data Partitioning (SQL Server 2005)
Hi,
Assuming that I have partitioned my Orders table based on OrderDate column
on 4 disks (each quarter on separate disk), do I need 4 processors for real
parallelism?
I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
resultset in parallel by having one CPU, or necessarily I must have one a
separate CPU for each disk?
Thanks in advance,
Leila
"Leila" <Leilas@.hotpop.com> wrote in message
news:#kFBVxrVHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Assuming that I have partitioned my Orders table based on OrderDate column
> on 4 disks (each quarter on separate disk), do I need 4 processors for
> real parallelism?
> I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
> resultset in parallel by having one CPU, or necessarily I must have one a
> separate CPU for each disk?
Only computers that have more than one CPU can use parallel queries.
Degree of Parallelism
http://msdn2.microsoft.com/en-us/library/ms188611.aspx
However there's no rule that you have to put each partition on its own disk.
Instead you could store all or some of your partitions on a filegroup that
spans all the disks. Then every query will use all the disks evenly.
David
Assuming that I have partitioned my Orders table based on OrderDate column
on 4 disks (each quarter on separate disk), do I need 4 processors for real
parallelism?
I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
resultset in parallel by having one CPU, or necessarily I must have one a
separate CPU for each disk?
Thanks in advance,
Leila
"Leila" <Leilas@.hotpop.com> wrote in message
news:#kFBVxrVHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Assuming that I have partitioned my Orders table based on OrderDate column
> on 4 disks (each quarter on separate disk), do I need 4 processors for
> real parallelism?
> I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
> resultset in parallel by having one CPU, or necessarily I must have one a
> separate CPU for each disk?
Only computers that have more than one CPU can use parallel queries.
Degree of Parallelism
http://msdn2.microsoft.com/en-us/library/ms188611.aspx
However there's no rule that you have to put each partition on its own disk.
Instead you could store all or some of your partitions on a filegroup that
spans all the disks. Then every query will use all the disks evenly.
David
Data Partitioning (SQL Server 2005)
Hi,
Assuming that I have partitioned my Orders table based on OrderDate column
on 4 disks (each quarter on separate disk), do I need 4 processors for real
parallelism?
I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
resultset in parallel by having one CPU, or necessarily I must have one a
separate CPU for each disk?
Thanks in advance,
Leila"Leila" <Leilas@.hotpop.com> wrote in message
news:#kFBVxrVHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Assuming that I have partitioned my Orders table based on OrderDate column
> on 4 disks (each quarter on separate disk), do I need 4 processors for
> real parallelism?
> I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
> resultset in parallel by having one CPU, or necessarily I must have one a
> separate CPU for each disk?
Only computers that have more than one CPU can use parallel queries.
Degree of Parallelism
http://msdn2.microsoft.com/en-us/library/ms188611.aspx
However there's no rule that you have to put each partition on its own disk.
Instead you could store all or some of your partitions on a filegroup that
spans all the disks. Then every query will use all the disks evenly.
David
Assuming that I have partitioned my Orders table based on OrderDate column
on 4 disks (each quarter on separate disk), do I need 4 processors for real
parallelism?
I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
resultset in parallel by having one CPU, or necessarily I must have one a
separate CPU for each disk?
Thanks in advance,
Leila"Leila" <Leilas@.hotpop.com> wrote in message
news:#kFBVxrVHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Assuming that I have partitioned my Orders table based on OrderDate column
> on 4 disks (each quarter on separate disk), do I need 4 processors for
> real parallelism?
> I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
> resultset in parallel by having one CPU, or necessarily I must have one a
> separate CPU for each disk?
Only computers that have more than one CPU can use parallel queries.
Degree of Parallelism
http://msdn2.microsoft.com/en-us/library/ms188611.aspx
However there's no rule that you have to put each partition on its own disk.
Instead you could store all or some of your partitions on a filegroup that
spans all the disks. Then every query will use all the disks evenly.
David
Friday, February 17, 2012
Data from previous quarter
I am trying to extract data from the previous quarter, however, when the
previous quarter is also the previous year, I need to compensate for that.
Here is my code and I am getting an error on the DatePart(q,ServiceDate)=4
syntax.
SELECT Region, ServiceDate
FROM dbo.CommunityService
WHERE Case When (DatePart(q,GetDate())) = 1 Then
DatePart(q, ServiceDate) = 4 AND
Year(ServiceDate) = Year(DateAdd(y, GetDate(), -1))
Else DatePart(q,ServiceDate) = DatePart(q,Getdate())-1 and
Year(ServiceDate) = Year(GetDate())
End
--
What am I missing?If you first determine the start and ending date for the period you want,
then the where condition will be more simple. For example, the following
would be Nov 2005 - Jan 2006:
select @.StartDate = '2005/11/01'
select @.EndDate = '2005/02/01'
. . .
where
ServiceDate >= @.StartDate and ServiceDate < @.EndDate
"Ken D." <KenD@.discussions.microsoft.com> wrote in message
news:40BBC4EC-8E33-40D5-92F9-87C472F36879@.microsoft.com...
>I am trying to extract data from the previous quarter, however, when the
> previous quarter is also the previous year, I need to compensate for that.
> Here is my code and I am getting an error on the DatePart(q,ServiceDate)=4
> syntax.
> --
> SELECT Region, ServiceDate
> FROM dbo.CommunityService
> WHERE Case When (DatePart(q,GetDate())) = 1 Then
> DatePart(q, ServiceDate) = 4 AND
> Year(ServiceDate) = Year(DateAdd(y, GetDate(), -1))
> Else DatePart(q,ServiceDate) = DatePart(q,Getdate())-1 and
> Year(ServiceDate) = Year(GetDate())
> End
> --
> What am I missing?|||Ken,
I would do it as follows. Your thinking is fine & the logic here is the same
as your own. Obviously I can't test this as you've posted no DDL etc., but
it does parse.
Cheers,
Robert
SELECT cs.Region, cs.ServiceDate
FROM dbo.CommunityService cs
WHERE
(DATEPART(Q, cs.ServiceDate) =
(/* Expression = Relevant Quarter based on current date */
CASE (DATEPART(Q, GETDATE())-1)
WHEN 0 THEN 4 ELSE (DATEPART(Q, GETDATE())-1) END
))
AND
(YEAR(cs.ServiceDate)=
(/* Expression = Relevant Year based on current date */
CASE (DATEPART(Q, GETDATE())-1)
WHEN 0 THEN (YEAR(GETDATE())-1) ELSE YEAR(GETDATE()) END
))
"Ken D." <KenD@.discussions.microsoft.com> wrote in message
news:40BBC4EC-8E33-40D5-92F9-87C472F36879@.microsoft.com...
>I am trying to extract data from the previous quarter, however, when the
> previous quarter is also the previous year, I need to compensate for that.
> Here is my code and I am getting an error on the DatePart(q,ServiceDate)=4
> syntax.
> --
> SELECT Region, ServiceDate
> FROM dbo.CommunityService
> WHERE Case When (DatePart(q,GetDate())) = 1 Then
> DatePart(q, ServiceDate) = 4 AND
> Year(ServiceDate) = Year(DateAdd(y, GetDate(), -1))
> Else DatePart(q,ServiceDate) = DatePart(q,Getdate())-1 and
> Year(ServiceDate) = Year(GetDate())
> End
> --
> What am I missing?|||Robert,
This is precisely what I am looking for. It worked like a charm. I will be
keeping this code for use anytime I need previous qtr or mon. Thank you so
much for your help.
Have a great day..
Ken
"Robert Ellis" wrote:
> Ken,
> I would do it as follows. Your thinking is fine & the logic here is the sa
me
> as your own. Obviously I can't test this as you've posted no DDL etc., but
> it does parse.
> Cheers,
> Robert
> SELECT cs.Region, cs.ServiceDate
> FROM dbo.CommunityService cs
> WHERE
> (DATEPART(Q, cs.ServiceDate) =
> (/* Expression = Relevant Quarter based on current date */
> CASE (DATEPART(Q, GETDATE())-1)
> WHEN 0 THEN 4 ELSE (DATEPART(Q, GETDATE())-1) END
> ))
> AND
> (YEAR(cs.ServiceDate)=
> (/* Expression = Relevant Year based on current date */
> CASE (DATEPART(Q, GETDATE())-1)
> WHEN 0 THEN (YEAR(GETDATE())-1) ELSE YEAR(GETDATE()) END
> ))
>
>
> "Ken D." <KenD@.discussions.microsoft.com> wrote in message
> news:40BBC4EC-8E33-40D5-92F9-87C472F36879@.microsoft.com...
>
>
previous quarter is also the previous year, I need to compensate for that.
Here is my code and I am getting an error on the DatePart(q,ServiceDate)=4
syntax.
SELECT Region, ServiceDate
FROM dbo.CommunityService
WHERE Case When (DatePart(q,GetDate())) = 1 Then
DatePart(q, ServiceDate) = 4 AND
Year(ServiceDate) = Year(DateAdd(y, GetDate(), -1))
Else DatePart(q,ServiceDate) = DatePart(q,Getdate())-1 and
Year(ServiceDate) = Year(GetDate())
End
--
What am I missing?If you first determine the start and ending date for the period you want,
then the where condition will be more simple. For example, the following
would be Nov 2005 - Jan 2006:
select @.StartDate = '2005/11/01'
select @.EndDate = '2005/02/01'
. . .
where
ServiceDate >= @.StartDate and ServiceDate < @.EndDate
"Ken D." <KenD@.discussions.microsoft.com> wrote in message
news:40BBC4EC-8E33-40D5-92F9-87C472F36879@.microsoft.com...
>I am trying to extract data from the previous quarter, however, when the
> previous quarter is also the previous year, I need to compensate for that.
> Here is my code and I am getting an error on the DatePart(q,ServiceDate)=4
> syntax.
> --
> SELECT Region, ServiceDate
> FROM dbo.CommunityService
> WHERE Case When (DatePart(q,GetDate())) = 1 Then
> DatePart(q, ServiceDate) = 4 AND
> Year(ServiceDate) = Year(DateAdd(y, GetDate(), -1))
> Else DatePart(q,ServiceDate) = DatePart(q,Getdate())-1 and
> Year(ServiceDate) = Year(GetDate())
> End
> --
> What am I missing?|||Ken,
I would do it as follows. Your thinking is fine & the logic here is the same
as your own. Obviously I can't test this as you've posted no DDL etc., but
it does parse.
Cheers,
Robert
SELECT cs.Region, cs.ServiceDate
FROM dbo.CommunityService cs
WHERE
(DATEPART(Q, cs.ServiceDate) =
(/* Expression = Relevant Quarter based on current date */
CASE (DATEPART(Q, GETDATE())-1)
WHEN 0 THEN 4 ELSE (DATEPART(Q, GETDATE())-1) END
))
AND
(YEAR(cs.ServiceDate)=
(/* Expression = Relevant Year based on current date */
CASE (DATEPART(Q, GETDATE())-1)
WHEN 0 THEN (YEAR(GETDATE())-1) ELSE YEAR(GETDATE()) END
))
"Ken D." <KenD@.discussions.microsoft.com> wrote in message
news:40BBC4EC-8E33-40D5-92F9-87C472F36879@.microsoft.com...
>I am trying to extract data from the previous quarter, however, when the
> previous quarter is also the previous year, I need to compensate for that.
> Here is my code and I am getting an error on the DatePart(q,ServiceDate)=4
> syntax.
> --
> SELECT Region, ServiceDate
> FROM dbo.CommunityService
> WHERE Case When (DatePart(q,GetDate())) = 1 Then
> DatePart(q, ServiceDate) = 4 AND
> Year(ServiceDate) = Year(DateAdd(y, GetDate(), -1))
> Else DatePart(q,ServiceDate) = DatePart(q,Getdate())-1 and
> Year(ServiceDate) = Year(GetDate())
> End
> --
> What am I missing?|||Robert,
This is precisely what I am looking for. It worked like a charm. I will be
keeping this code for use anytime I need previous qtr or mon. Thank you so
much for your help.
Have a great day..
Ken
"Robert Ellis" wrote:
> Ken,
> I would do it as follows. Your thinking is fine & the logic here is the sa
me
> as your own. Obviously I can't test this as you've posted no DDL etc., but
> it does parse.
> Cheers,
> Robert
> SELECT cs.Region, cs.ServiceDate
> FROM dbo.CommunityService cs
> WHERE
> (DATEPART(Q, cs.ServiceDate) =
> (/* Expression = Relevant Quarter based on current date */
> CASE (DATEPART(Q, GETDATE())-1)
> WHEN 0 THEN 4 ELSE (DATEPART(Q, GETDATE())-1) END
> ))
> AND
> (YEAR(cs.ServiceDate)=
> (/* Expression = Relevant Year based on current date */
> CASE (DATEPART(Q, GETDATE())-1)
> WHEN 0 THEN (YEAR(GETDATE())-1) ELSE YEAR(GETDATE()) END
> ))
>
>
> "Ken D." <KenD@.discussions.microsoft.com> wrote in message
> news:40BBC4EC-8E33-40D5-92F9-87C472F36879@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)