I am using GUIDs as my primary keys in tables. For performance reasons &
best/recommended practices with ElevateDB, does anyone know if they be case
sensitive or case insensitive?


jr

James,

GUIDs are treated the same as a VARCHAR(38), so they can be case
sensitive or case insensitive depending on the collation you specify for
the column.

--
Fernando Dias
[Team Elevate]

From a performance perspective, which are faster: sensitive or insensitive with ElevateDB?

Thanks

jr





Fernando Dias wrote:

James,

GUIDs are treated the same as a VARCHAR(38), so they can be case
sensitive or case insensitive depending on the collation you specify for
the column.

--
Fernando Dias
[Team Elevate]

James


Sensitive.

The difference is that sensitive uses the table itself and insensitive writes out a temporary table to disk containing just the rows you've selected.

Roy Lambert [Team Elevate]

Roy,

> The difference is that sensitive uses the table itself and insensitive
> writes out a temporary table to disk containing just the rows you've
> selected.

I could very well be wrong, but your answer is correct if it concerns result
sets, but not in case of index collation.

Best regards,
Fons

Fons


Can you expand on that because I don't understand your comment.

Roy Lambert

Roy,

> Can you expand on that because I don't understand your comment.

Sure. The question "I am using GUIDs as my primary keys in tables. For
performance reasons & best/recommended practices with ElevateDB, does anyone
know if they be case sensitive or case insensitive?" was answered by you
with "Sensitive. The difference is that sensitive uses the table itself and
insensitive writes out a temporary table to disk containing just the rows
you've selected.". And I think you mix up two separate things.

With SELECT statements it can be a sensitive or an insensitive result set.
One of the differences between them two is that the latter uses a temporay
table and is therefore performance wise slower. Just like you stated, but
the question was related to keys. Reading the manual I couldn't find
anything about this regarding keys. The collation determines the sensitivity
and thus whether the query is optimized or not, but this doesn't mean it
uses a temp file. At least, the manual doesn't mention this at all.

But, like I stated in my previous post, I could be totally wrong.

Best regards,
Fons

Fons


I'll have to do some checking / wait for Tim's response. I wasn't aware that the collation could influence wether it was a sensitive result set or not. I thought it simply influenced optimisation.

Roy Lambert

Roy,

Collation can influence whether it is a sensitive result-set/cursor or
not, and thus also optimisation,  because you can only get a sensitive
result-set/cursor if you specify a collation that does match an existing
index, otherwise you will always get an insensitive cursor and also
unoptimized queries.

However, the question was about *case* sensitiveness not about
*cursor/result-set* sensitiveness.

I don't know if there is any significant difference in performance using
one or the other (case sensitive or case insensitive collations), I
suspect there isn't, but it's only an opinion based on my own experiences.

--
Fernando Dias
[Team Elevate]

Fernando

Two points:


1. I took

"From a performance perspective, which are faster: sensitive or insensitive with ElevateDB?"

to be referring to the result set not the collation.

2. Since you've got me worried I just tried
"select * from contacts where _Surname COLLATE DAN = 'smith'"

and I get a sensitive result set even though the column and index are declared as ANSI_CI


Roy Lambert