CDS order of records slightly different than query

Preston

Delphi Developer

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

Craig Stuntz [TeamB]

Delphi Developer

2008-05-15 11:06:10 PM

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

Preston

Delphi Developer

2008-05-15 11:32:05 PM

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}

Craig Stuntz [TeamB]

Delphi Developer

2008-05-15 11:36:52 PM

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

delfo

Delphi Developer

2008-05-16 03:32:48 PM

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

Roy

Delphi Developer

2008-07-10 09:53:06 PM

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?