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 |
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. 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)? 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? @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. @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. |
Breck has pointed out on his blog what might happen when the false capatibility bits are set for MS SQL Server:) |
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:
In my case, it returns a syntax error at "CURRENT". However (and as stated in my previous posts), the following works:
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. @Adam: See my comment to the other answer. |