New post
AllAccepted answers
Robert Cemper · Jul 14, 2018

As by your concrete question

Caché

InterSystems Caché® is a high-performance database that powers transaction processing applications around the world. It is used for everything from mapping a billion stars in the Milky Way, to processing a billion equity trades in a day, to managing smart energy grids.

Ensemble

InterSystems Ensemble® is a seamless platform for rapid connectivity and the development of new connectable applications. Ensemble users typically complete projects twice as fast compared to previous generations of integration products.

InterSystems IRIS

InterSystems IRIS Data Platform™ sets a new level of performance for rapidly developing and deploying important applications. All of the needed tools and capabilities are provided in a reliable, unified platform spanning data management, interoperability, transaction processing, and analytics.

more

Robert Cemper · Jul 13, 2018

I'm not aware of a "customized" collation.

But this might be an appropriate workaround:

- for your property, you define an additional calculated property that results out of your  customized collation
- for this new calculated property,  you define COLLATION = EXACT to avoid default surprises (SQLUPPER !!!)

If you index it, you should get what you expected without impact to the rest of your table

Robert Cemper · Jul 8, 2018

Very interesting result.

You fall in timeout even with CallbackComment IS NULL   !!!!!

so single quotes can't be part of the game. 
So this should also work

SELECT 
LENGTH(CallbackComment)
FROM SQ.CBPhoneResult_View
WHERE PhoneDateODBC = '2018-04-09' 
AND CallbackComment IS NOT NULL

and this too

SELECT 
LENGTH(CallbackComment), CallbackComment
FROM SQ.CBPhoneResult_View
WHERE PhoneDateODBC = '2018-04-09' 
AND CallbackComment IS NOT NULL

This will indicate that some empty (NULL)  element is causing the troubles.

It might make sense to initialize all NULL CallbackComments with something.

Robert Cemper · Jul 8, 2018

You can do an additional check directly in the table that holds CallbacKComment

From the Query Plan I  assume it is 

  • SQ. CB_Contact

 something like 

SELECT ID,
LENGTH(CallbackComment), CallbackComment 
FROM SQ.CB_Contact
WHERE CallbackComment IS NOT NULL

this should lead you to the critical point and someone with enough 
privileges could take a look at the stored data if it is really a single quote or something else

Robert Cemper · Jul 8, 2018

As it stops also with the REPLACE let'S do 2 other checks:

SELECT 
CallbackComment
FROM SQ.CBPhoneResult_View
WHERE PhoneDateODBC = '2018-04-09' 
AND CallbackComment IS NULL

this verifies that the day plays no role

SELECT 
LENGTH(CallbackComment)
FROM SQ.CBPhoneResult_View
WHERE PhoneDateODBC = '2018-04-09' 
AND NOT CallbackComment [ ''''

Now we exclude all single quotes
If this one fails means that we see a single quote but it might be some other character

I also changed to LENGTH as  it should not interfere with single quotes

[ is the contains operator

Robert Cemper · Jul 8, 2018

OK. You found 801 entries  with ~200 k global  access.
and the query works as expected

So 

SELECT COUNT(*) 
FROM SQ.CBPhoneResult_View
WHERE PhoneDateODBC = '2018-04-10'

for Tuesday Apr.10 it should return 801  

What is the count for Monday  2018-04-09 ?  I 'd expect more

SELECT
 COUNT(*)
FROM SQ.CBPhoneResult_View
WHERE PhoneDateODBC = '2018-04-09'
Robert Cemper · Jul 8, 2018

As you see the query plan is significantly shorter.
Your original refers to 3 other tables.
To find out why and how to improve would require all table/class  & view definitions

  • SQ.CBPhoneResult_View
  • SQ.CBPH_Phone
  • SQ. CBPH_Result      your 2nd query ends here  
  • SQ. CB_Test      access to  CallbackComment starts here
  • SQ. CB_Order
  • SQ. CB_Contact

Without the related class definition it is hard to say how these tables link to each other

But you really should run it from the terminal prompt to see your result at least once:

Do you have developer access to your Caché  at all? 
Can you see the class definitions in Studio ? 

Robert Cemper · Jul 8, 2018

OK,now your timeout is clear.
with a Relative Cost of  ~2 millions, your query requires some support to speed up.
You just see the first 6 empty results.

First reason: you run over ALL records in  ...PhoneMaster
with an inner loop in  ...Testmaster on IndexCall,OrdeCode, TestCode,...Result_Index

So your timeout is not surprising.

I'd suggest creating an Index on PhoneDateODBC to speed up your query. >>  lower  Relative Cost
with 2 million I'm quite sure that you even timeout over ODBC.

So I'm back to my earlier suggestion:
Let your query run from terminal prompt,
have a coffee or two and maybe it is completed then.

This is not your fault.
Blame the designer of that ugly VIEW

Robert Cemper · Jul 8, 2018

OK.
You use a Caché version before 2015.1 that doesn't know $TRANSLATE

It's then 

SELECT
 REPLACE(CallbackComment  ,'''','?')
FROM SQ.CBPhoneResult_View
WHERE PhoneDateODBC = '2018-04-09'

I expect you will run into a timeout again.
So please click to "Show Query Plan" and let us see what's going on.

Additionally, on left border click to Views;
select  SQ.CBPhoneResult_View and get an Image similar to this

important part:  VIEW TEXT

Robert Cemper · Jul 8, 2018

OK. now the picture gets clear.

 the key problem looks as if a single ' is interpreted as a String delimiter.
so instead of naked CallbackComment 
You may try

$TRANSLATE(CallbackComment  ,'''','?')

(it's really 4single quotes, no typo)
So you replace the single quote by a non-conflicting character BEFORE the string is passed to ODBC   
This would prove that the single quote is the cause of trouble.