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)

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

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

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

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