CDS order of records slightly different than query

Preston

unread,

May 15, 2008, 10:59:19 AM5/15/08

to

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]

unread,

May 15, 2008, 11:06:10 AM5/15/08

to

Preston wrote:

> 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 : http://blogs.teamb.com/craigstuntz
Want to help make Delphi and InterBase better? Use QC!
http://qc.borland.com -- Vote for important issues

Preston

unread,

May 15, 2008, 11:32:05 AM5/15/08

to

"Craig Stuntz [TeamB]" <craig_...@nospam.please [a.k.a. acm.org]> wrote:

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

Craig Stuntz [TeamB]

unread,

May 15, 2008, 11:36:52 AM5/15/08

to

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.

delfo

unread,

May 16, 2008, 3:32:48 AM5/16/08

to

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

http://qc.borland.com/wc/qcmain.aspx?d=55581

Roy

unread,

Jul 10, 2008, 9:53:06 AM7/10/08

to

"Craig Stuntz [TeamB]" <craig_...@nospam.please [a.k.a. acm.org]> wrote:

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