Board index » off-topic » CDS order of records slightly different than query

CDS order of records slightly different than query


2008-05-15 10:59:19 PM
off-topic6
I have a CDS that is ordering the records from the query slightly different than the query order. There is no index on the cds.
Using the profiler I can grab the query that's being executed against the database and run it from the database management tool. The order of that result set is different than what the cds shows. It's not by much though, the records are within a few rows of where they should be.
Is this a known issue?
D2006 Update 2
SQL Server Express 2005
 
 

Re:CDS order of records slightly different than query

Preston wrote:
Quote
I have a CDS that is ordering the records from the query slightly
different than the query order. There is no index on the cds.
Unless you set TDataSetProvider.Options.poRetainServerOrder true (not
the default), you can't count on these being the same.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Want to help make Delphi and InterBase better? Use QC!
qc.borland.com -- Vote for important issues
 

Re:CDS order of records slightly different than query

"Craig Stuntz [TeamB]" < XXXX@XXXXX.COM [a.k.a. acm.org]>wrote:
Quote
Unless you set TDataSetProvider.Options.poRetainServerOrder true (not
the default), you can't count on these being the same.
Any idea why the default is off or why this problem even exists. Is it a performance issue? If so, will turning it on slow things down?
 

{smallsort}

Re:CDS order of records slightly different than query

Preston wrote:
Quote
Any idea why the default is off or why this problem even exists.
I don't know why the default is what it is. My guess is that people
expect the CDS index feature to work. I don't think that turning it on
will hurt performance, but as with all performance issues, I wouldn't
suggest attempting to optimize things which have not been shown to be
problems yet.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Want to help make Delphi and InterBase better? Use QC!
qc.borland.com -- Vote for important issues
 

Re:CDS order of records slightly different than query

Quote
Is this a known issue?
Sure it's known, but it is worse than you believe, because it doesn't
affect only the order but the number of rows returned -or at least shown-:
qc.borland.com/wc/qcmain.aspx
 

Re:CDS order of records slightly different than query

"Craig Stuntz [TeamB]" < XXXX@XXXXX.COM [a.k.a. acm.org]>wrote:
Quote
Preston wrote:

>Any idea why the default is off or why this problem even exists.

I don't know why the default is what it is. My guess is that people
expect the CDS index feature to work. I don't think that turning it on
will hurt performance, but as with all performance issues, I wouldn't
suggest attempting to optimize things which have not been shown to be
problems yet.

I too have noticed the sorting differences. Dashes are ignored in the CDS index sort. The CDS sort appears to be based upon Windows dictionary sorting (as is MS Access / Jet). MS SQL Server does binary sort by default, but can be configured to do Windows dictionary sort. However, it would be nice if the CDS would provide an option for a binary sort for compatibility with SQL Server (I prefer the binary sort for my application). Any chance of that happening?