Showing posts with label orders. Show all posts
Showing posts with label orders. Show all posts

Tuesday, March 20, 2012

Data Reinitialized When Filtered Column Changes

Hello,
We have SQL2005 Server, SQL Mobile 2005 CF2.0 application. It seems that
when we mark Orders as complete on the server, it redownloads all
OrderDetails from the server down to the PDA, even though there are no
changes to this table on the server, and there are changes on the PDA that
should get uploaded.
We have 2 tables Orders and OrderDetails. We want only non-complete Orders
and their OrderDetails to filter down to the pda.
So we have a base filter setup on our Orders table like this:
SELECT <published_columns> FROM
[dbo].[Orders] WHERE [Orders].[Completed] = 0 AND
[Orders].[OwnerContactID]=HOST_NAME()
Then we add a join filter to get OrderDetails for those Orders:
SELECT <published_columns> FROM [dbo].[Orders] INNER JOIN
[dbo].[OrderDetails] ON [Orders].[OrderID] = [OrderDetails].[OrderID]
Everthing works great most of the time, the problem is, when someone on the
server marks an Order as Complete, but there are still changes to
OrderDetails on the PDA, it generates conflict on OrderDetails. It does try
to upload from the PDA for OrderDetails, but they are filtered into a
conflict table, and the server wins. The OrderDetails table has not been
touched on the server, so I don't understand this. It's like the server
"sets" all the columns in OrderDetails when Orders.Completed is changed to
TRUE, and then the changes from the PDA are regarded as a conflict.
I don't think I understand how this works, and there probably is a reason is
for this. Any help or suggestions of a better way to do this, filter
Non-Complete items down without reinitializing the child data when that
filtered data changes, please let me know!
Thanks!
Are you using column level tracking? It seems that with column level
tracking this should work.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"nkleinjan" <nkleinjan@.discussions.microsoft.com> wrote in message
news:07D5DD6F-F0DF-4B4F-B1FB-C7CC3DE4F7D3@.microsoft.com...
> Hello,
> We have SQL2005 Server, SQL Mobile 2005 CF2.0 application. It seems that
> when we mark Orders as complete on the server, it redownloads all
> OrderDetails from the server down to the PDA, even though there are no
> changes to this table on the server, and there are changes on the PDA that
> should get uploaded.
> We have 2 tables Orders and OrderDetails. We want only non-complete Orders
> and their OrderDetails to filter down to the pda.
> So we have a base filter setup on our Orders table like this:
> SELECT <published_columns> FROM
> [dbo].[Orders] WHERE [Orders].[Completed] = 0 AND
> [Orders].[OwnerContactID]=HOST_NAME()
> Then we add a join filter to get OrderDetails for those Orders:
> SELECT <published_columns> FROM [dbo].[Orders] INNER JOIN
> [dbo].[OrderDetails] ON [Orders].[OrderID] = [OrderDetails].[OrderID]
> Everthing works great most of the time, the problem is, when someone on
> the
> server marks an Order as Complete, but there are still changes to
> OrderDetails on the PDA, it generates conflict on OrderDetails. It does
> try
> to upload from the PDA for OrderDetails, but they are filtered into a
> conflict table, and the server wins. The OrderDetails table has not been
> touched on the server, so I don't understand this. It's like the server
> "sets" all the columns in OrderDetails when Orders.Completed is changed to
> TRUE, and then the changes from the PDA are regarded as a conflict.
> I don't think I understand how this works, and there probably is a reason
> is
> for this. Any help or suggestions of a better way to do this, filter
> Non-Complete items down without reinitializing the child data when that
> filtered data changes, please let me know!
> Thanks!
>
>

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