Proxy table usage ignores where clause and uses slow cursor?

[Former Member]

Note after review: Does this have to do with SQLA processing date strings differently than sql server would, so it fails a check and decides to not do a pass thru query?

I've setup a proxy table to sql server. The processing of this query took actually several hours and yet - on sql server (or SQLA without a proxy table) the query is generally less than a second.

We have sql (in C preprocessor code) as:

EXEC             
SELECT  Count(*)
INTO    :Match_Count
FROM    M_Claimant
WHERE   Unique_Id In (Select Unique_Id From M_Claimant Where lastnamekey1 = :lastname_key_1 AND
lastnamekey2 = :lastname_key_2 AND
firstnamekey1 = :firstname_key_1 AND
firstnamekey2 = :firstname_key_2 AND
DateOfBirth = :DateOfBirth AND
left(Member_Number,:MemberNumberUniqueLength) = :Member_Number AND
                        left(Member_Number,3)  'DEL' AND
Plan_ID = :Plan_UniqueID);

and this comes over to sql server completely devoid the where clause that is in the original query. The cursor then runs for about two hours as it is a large table.

Its a bit confusing why SQLA is using a cursor here and not passing the query through with the where clause. Any suggestions?

(Note the same thing happens even if simplified)

EXEC SQL 
SELECT  Count(*)
INTO    :Match_Count
FROM    M_Claimant
WHERE   lastnamekey1 = :lastname_key_1 AND
lastnamekey2 = :lastname_key_2 AND
firstnamekey1 = :firstname_key_1 AND
firstnamekey2 = :firstname_key_2 AND
DateOfBirth = :DateOfBirth AND
Plan_ID = :Plan_UniqueID;

Here is the log

I. 02/04 12:22:15. The capability check of 211 failed
I. 02/04 12:22:15. The Original Statement is 
I. 02/04 12:22:15.  select Count(*) from
I. 02/04 12:22:15.   M_Claimant where
I. 02/04 12:22:15.   M_Claimant.Unique_Id = any(select M_Claimant.Unique_Id from M_Claimant where M_Claimant.lastnamekey1 = :lastname_key_1 and
I. 02/04 12:22:15.     M_Claimant.lastnamekey2 = :lastname_key_2 and
I. 02/04 12:22:15.     M_Claimant.firstnamekey1 = :firstname_key_1 and
I. 02/04 12:22:15.     M_Claimant.firstnamekey2 = :firstname_key_2 and
I. 02/04 12:22:15.     M_Claimant.DateOfBirth = :DateOfBirth and
I. 02/04 12:22:15.     M_Claimant.Sex_Code = :SexCode and
I. 02/04 12:22:15.     "left"(M_Claimant.Member_Number,:MemberNumberUniqueLength) = :Member_Number and
I. 02/04 12:22:15.     "left"(M_Claimant.Member_Number,3)  'DEL' and
I. 02/04 12:22:15.     M_Claimant.Plan_ID = :Plan_UniqueID)
I. 02/04 12:22:15. The Virtual Statement is 
I. 02/04 12:22:15.  select Count(*) from vt_1 where
I. 02/04 12:22:15.   vt_1.expr_1 = any(select vt_2.expr_1 from vt_2 where vt_2.expr_2 = :lastname_key_1 and
I. 02/04 12:22:15.     vt_2.expr_3 = :lastname_key_2 and
I. 02/04 12:22:15.     vt_2.expr_4 = :firstname_key_1 and
I. 02/04 12:22:15.     vt_2.expr_5 = :firstname_key_2 and
I. 02/04 12:22:15.     vt_2.expr_6 = :DateOfBirth and
I. 02/04 12:22:15.     vt_2.expr_7 = :SexCode and
I. 02/04 12:22:15.     "left"(vt_2.expr_8,:MemberNumberUniqueLength) = :Member_Number and
I. 02/04 12:22:15.     "left"(vt_2.expr_8,3)  'DEL' and
I. 02/04 12:22:15.     vt_2.expr_9 = :Plan_UniqueID)
I. 02/04 12:22:15. The Remote Statement for vt_1 is 
I. 02/04 12:22:15.  select
I. 02/04 12:22:15.   M_Claimant.Unique_Id from M_Claimant
I. 02/04 12:22:15. The Remote Statement for vt_2 is 
I. 02/04 12:22:15.  (
I. 02/04 12:22:15. select M_Claimant.Unique_Id,M_Claimant.lastnamekey1,
I. 02/04 12:22:15.   M_Claimant.lastnamekey2,
I. 02/04 12:22:15.   M_Claimant.firstnamekey1,
I. 02/04 12:22:15.   M_Claimant.firstnamekey2,
I. 02/04 12:22:15.   M_Claimant.DateOfBirth,
I. 02/04 12:22:15.   M_Claimant.Sex_Code,
I. 02/04 12:22:15.   M_Claimant.Member_Number,
I. 02/04 12:22:15.   M_Claimant.Plan_ID from M_Claimant)
I. 02/04 12:22:15. Execute (testdb): 
I. 02/04 12:22:15. SELECT t1.Unique_ID FROM testdb.CLAIMS.M_CLAIMANT t1  
I. 02/04 12:22:15. Execute (testdb): 
I. 02/04 12:22:15. SELECT t2.Unique_ID , t2.LastNameKey1 , t2.LastNameKey2 , t2.FirstNameKey1 , t2.FirstNameKey2 , t2.DateOfBirth , t2.Sex_Code , t2.Member_Number , t2.PLAN_ID FROM testdb.CLAIMS.M_CLAIMANT t2  

[Former Member]

Hi Breck - the query you recommended is basically the query that is sent to SQLA. This is an older version (9) but I can surely try with a more recent version too (12)

[Former Member]

The issue is that the query rewrite introduces the use of the left builtin but the capability bit for left is not set for MS SQL Server. As a result, the query gets executed in no passthru. However, I believe newer versions of SQL Server do support left so you might be able to get away with executing:

ALTER SERVER server CAPABILITY 'left' ON

Remember to disconnect and reconnect to make sure the remote connection gets refreshed.

[Former Member]

Hi Karim, why is this query getting rewritten though, its a very basic query with no join condition specirfied? (see the simpler form second query I included in the original post that causes the same issue)

[Former Member]

ok - I tried this option and that query is working now. nice : ) But the same thing immediately happens again this time with code 80 I. 02/04 13:44:24. The capability check of 80 failed I. 02/04 13:44:24. The Original Statement is I. 02/04 13:44:24. update M_Active set I. 02/04 13:44:24. M_Active.ProcessingTime = :ProcessingTime from M_Active where I. 02/04 13:44:24. M_Active.Date_Entered = current date and M_Active.Source = :Source and M_Active.DataStartOffset = :DataStartOffSet

(more below)

[Former Member]

The Virtual Statement is update vt_2 set vt_2.expr_1 = :ProcessingTime where vt_2.expr_2 = current date and vt_2.expr_3 = :Source and vt_2.expr_4 = :DataStartOffSet The Remote Statement for vt_1 is update M_Active set M_Active.ProcessingTime = :? The Remote Statement for vt_2 is select M_Active.ProcessingTime, M_Active.Date_Entered,M_Active.Source,M_Active.DataStartOffset from M_Active Execute cursor CIS_0 (testdb):

and the cursor runs for quite as m_active has 9 million plus rows.

[Former Member]

Capability bit 80 is "current constants". Are you sure your version of MS SQL Server will allow you to push current date over? If so, then issue another ALTER SERVER to set 'current constants' ON. Be careful though, turning on current constants will also push other "CURRENT blah" things that SQL Server may not support.

[Former Member]

By the way, to complete the thought, turning on current constants will force SA to push current date, current time, current timestamp, current UTC timestamp and current timezone whenever possible. So again, be careful when you turn this bit on.

[Former Member]

hmm.. ok.. well - is there another way to work around this? The query seems simple enough - so Im not understanding why theres an issue passing this query over, can you elaborate? What other side effects would this have. When you say it pushes current date, etc over, what would that effect? Thanks for all your help so far!!!

[Former Member]

@Adam,

The remote data access layer has a large set of capability bits that it checks before deciding what portions of the query can be send over to the remote and what portions must be handled locally by SA. For each remote class (saodbc, mssodbc, etc.) the default set of capability bits is set such that remote data access to that remote works for as many versions as possible. Hence for MS SQL Server, the capability bits for left, current constants and others are not set because not all version of SQL server have the support for left etc. (continued below).

[Former Member]

Hence, it is up to the customer to tweak the capability bits IF NEEDED to better represent the capabilities of the particular version of SQL Server (etc.) that is being used. In your case, turning on the capability bit for left was fine since your version of SQL Server supports left. Once you turn that capability bit on, SA knows that it is safe to push left() to SQL Server rather than compensating for its usage locally. The more you can push to the remote, the better the overall performance. (continued below)

[Former Member]

Now, in the case of the current constants capability bit, that bit tells SA that it is alright to push current date, current time, current timestamp, current UTC timestamp and current timezone to SQL Server. The current query that is giving you grief makes use of current date. So you have to decide if you want to turn the current constants capability bit on and push current date to SQL Server. To make this decision you have to ask yourself a few questions like can my version of SQL Server support current date etc.? (continued)

[Former Member]

If your version of SQL Server supports current date but not the other current constants, then it is probably not safe to turn on this capability bit. You might then consider breaking the query up so that you first store the value of current date in a variable and then use that variable in the query instead of current date. HTH

[VolkerBarth]

@Karim: I'm thankful that my suggestions (cf. my newer answers) seem to cope with the official recommendation...

[VolkerBarth]

And yet another answer:

If you want to find out whether a particular statement will be compatible for a remote server, the FORWARD TO statement is handy.

E.g. you can check with the following query whether your server will support CURRENT DATE:

FORWARD TO rem { SELECT CURRENT DATE from dbo.sysobjects }

In my case, it returns a syntax error at "CURRENT".

However (and as stated in my previous posts), the following works:

FORWARD TO rem { SELECT CURRENT_TIMESTAMP from dbo.sysobjects }

[Former Member]

in this case this interestingly enough passes - FORWARD TO opusbilling2development { update M_Active SET ProcessingTime = 1 WHERE Date_Entered = fn_Current_Date() and Source = 10000 } but sqla in processing determines this is not a pass through query because of the function and decides to do a join checking every single row of the 9 million+ strong table.

[VolkerBarth]

@Adam: See my comment to the other answer.

[VolkerBarth]

Breck has pointed out on his blog what might happen when the false capatibility bits are set for MS SQL Server:)

[VolkerBarth]

LEFT is even supported with MS SQL 2000, cf. the docs. RIGHT and SUBSTRING do exist, too. So I guess (as in the previous question), the default capability bits should be adapted for MS SQL.

I'm somewhat puzzled by the "left(Member_Number,3) 'DEL'" part of your query - is there a missing operator?

---

For your second question (below Karim's answer), the error will raise as CURRENT DATE is no MS SQL constant, and AFAIK most of the other CURRENT ... constants don't exist in MS SQL, either. Only the particular MS-compatible (and SQL 2008 compatible) variants of CURRENT TIMESTAMP and CURRENT USER (i.e. CURRENT_TIMESTAMP and CURRENT_USER) do exist.

Therefore it might not work that well to set that capability bit.

However, if you could use both date and time (instead of only current date), switching to CURRENT_TIMESTAMP or the similar GETDATE() function should work. We have used GETDATE() in MS-/SA-compatible code for years.

[Former Member]

unfortunately we need just the date here. Im changing all of the locations that used CURRENT DATE to use a UDF on sql server to get the date and we'll see what snags I hit next : ) If I set those bits - what is the behavior? SQLA defines some constants in the query to pass these values over that could 'collide' with names on sql server? Is there a link I can read more about this at (ya.. I googled a little but not sure of other details in this context so results are quite far off)?

[Former Member]

ok so now I included a function to call - thinking it would pass through to sql server - but it looks as if it evaluates locally.

I. 02/07 13:07:07. The capability check of Must be resolved locally failed I. 02/07 13:07:07. The Original Statement is I. 02/07 13:07:07. update M_Active set I. 02/07 13:07:07. M_Active.ProcessingTime = 1 from M_Active where I. 02/07 13:07:07. M_Active.Date_Entered = fn_Current_Date(*) and M_Active.Source = 10000 so.. at a loss here if this can be accomplished by a function or if I must use the same sql function in every location to get just the date?

[VolkerBarth]

@Adam: What MS SQL Server version are you using? AFAIK, up to MS SQL 2005, there is no pure "DATE" datatype, there's just datetime and smalldatetime, and both contain a time portion. So you couldn't even use a cast like "CAST(CURRENT_TIMESTAMP to DATE)" or the like.

[VolkerBarth]

@Adam: I'm no iAnywhere engineer, so I can't tell the internals. But I guess the function is executed locally (and as a consequence, preventing full passthrough mode), as SA doesn't know that your function is existing on the MS side. As such, it may think it has to compute it locally. - That being said, I don't know at all if you can make your MS function known to SA as some kind of user-defined capability.