Comparison in Where or Having Clauses NOT working

  • I have NO idea what is going on.

    Last week we upgraded from SQL 2000 to SQL 2005 and multiple Stored procedures quit working. They just run and run and run never ending.

    The only common thing I can see is the Where (or have) statement. It seems to HANG on a comparison such as Not Equal <>

    Last week I found one

    WHERE (vw_Job_Master_JCD_Open_sJobs.Costs_TD + vw_Job_Master_JCD_Open_sJobs.Committed_Cost)>[tbl_Job_JCD_Projections].[Cost_at_Completion]

    I changed the Where to a Case within the select and it suddenly worked. Is there something I'm missing in the Where or Having Clause having to do with comparisons?????

    I could really use some help,

    Thanks,

    Declare @dtWeekEnding_Date smalldatetime;

    Declare @strWeekEnding_Date nvarchar(12);

    /* Set WeekEning Date Begin */

    Set @dtWeekEnding_Date =

    (

    Select dt_WeekEnding_Current From tbl_WeekEnding_PR

    );

    Set @strWeekEnding_Date = '''' + Convert(nvarchar(12), @dtWeekEnding_Date,101) + '''';

    /* Set WeekEning Date End */

    Select

    PR_Employee_Mst.Employee_LName + ', ' + PR_Employee_Mst.Employee_FName As Employee_LFName

    , tbl_TimeCard_Upload_PR.Employee_MEI_ID AS Employee_MEI_ID

    , tbl_TimeCard_Upload_PR.Job_Co AS Job_Co

    , tbl_TimeCard_Upload_PR.Job_Div AS Job_Div

    , tbl_TimeCard_Upload_PR.Job_No AS Job_No

    , tbl_TimeCard_Upload_PR.JCD AS JCD

    , tbl_TimeCard_Upload_PR.Pay_Type_ID AS Pay_Type_ID

    From

    dbo.tbl_TimeCard_Upload_PR

    Left Join

    OpenQuery(Alpha,'

    Select

    JCTMST.CCONO As Job_Co

    , JCTMST.CDVNO As Job_Div

    , JCTMST.CJBNO As Main_Job_No

    , JCTMST.CSJNO As Sub_Job_No

    , JCTMST.CJCDI As JCT_JCD

    , LTrim(RTrim(JCTMST.CJCS1)) As JCD_Seg1

    , LTrim(RTrim(JCTMST.CJCS2)) As JCD_Seg2

    , LTrim(RTrim(JCTMST.CJCS3)) As JCD_Seg3

    From JCTMST

    Where (JCTMST.CCSTY = ''L'')

    Order By

    JCTMST.CCONO

    , JCTMST.CDVNO

    , JCTMST.CJBNO

    , JCTMST.CSJNO

    , JCTMST.CJCDI

    ') As Job_Mst

    On tbl_TimeCard_Upload_PR.Job_Co = Job_Mst.Job_Co

    And tbl_TimeCard_Upload_PR.Job_Div = Job_Mst.Job_Div

    And tbl_TimeCard_Upload_PR.Main_Job_No = Job_Mst.Main_Job_No

    And IsNull(tbl_TimeCard_Upload_PR.Sub_Job_No, '') = LTrim(RTrim(IsNull(Job_Mst.Sub_Job_No, '')))

    And LTrim(RTrim(IsNull(tbl_TimeCard_Upload_PR.JCD_Seg1, ''))) = LTrim(RTrim(IsNull(Job_Mst.JCD_Seg1, '')))

    And LTrim(RTrim(IsNull(tbl_TimeCard_Upload_PR.JCD_Seg2, ''))) = LTrim(RTrim(IsNull(Job_Mst.JCD_Seg2, '')))

    And LTrim(RTrim(IsNull(tbl_TimeCard_Upload_PR.JCD_Seg3, ''))) = LTrim(RTrim(IsNull(Job_Mst.JCD_Seg3, '')))

    Left Join

    OPENQUERY(Alpha,'

    SELECT

    PR_Employee_Mst.MCONO as Co_No

    , LTrim(RTrim(PR_Employee_Mst.MFN25)) As Employee_FName

    , LTrim(RTrim(PR_Employee_Mst.MLN25)) As Employee_LName

    , LTrim(RTrim(PR_Employee_Mst.MNM25)) As Employee_Name

    , PR_Employee_Mst.MEENO As Employee_MEI_ID

    FROM

    PRTMST As PR_Employee_Mst

    Where

    (

    (PR_Employee_Mst.MCONO = 1)

    And (PR_Employee_Mst.DivisionNumber = 0)

    And (PR_Employee_Mst.StatusCode = ''A'')

    )

    ') AS PR_Employee_Mst

    On tbl_TimeCard_Upload_PR.Employee_MEI_ID = PR_Employee_Mst.Employee_MEI_ID

    Where

    (tbl_TimeCard_Upload_PR.WeekEnding = @dtWeekEnding_Date)

    And (tbl_TimeCard_Upload_PR.PR_Status <> 'U')

    And (Job_Mst.JCT_JCD Is Null)

    /* And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'CU') Pay Type to show in list */

    /* And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'EA') Pay Type to show in list */

    And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'HL')

    /* And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'NC') Pay Type to show in list */

    /* And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'OC') Pay Type to show in list */

    /* And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'OV') Pay Type to show in list */

    /* And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'PD') Pay Type to show in list */

    /* And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'PP') Pay Type to show in list */

    /* And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'PS') Pay Type to show in list */

    /* And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'PT') Pay Type to show in list */

    /* And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'RT') Pay Type to show in list */

    And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'SO')

    And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'SR')

    /* And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'SU') Pay Type to show in list */

    And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'TO')

    And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'TR')

    /* And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'UP') Pay Type to show in list */

    /* And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'VA') Pay Type to show in list */

    /* And (tbl_TimeCard_Upload_PR.Pay_Type_ID <> 'VU') Pay Type to show in list */

    Order By

    PR_Employee_Mst.Employee_LName + ', ' + PR_Employee_Mst.Employee_FName

    , tbl_TimeCard_Upload_PR.Employee_MEI_ID

    , tbl_TimeCard_Upload_PR.Job_Co

    , tbl_TimeCard_Upload_PR.Job_Div

    , tbl_TimeCard_Upload_PR.Job_No

    , tbl_TimeCard_Upload_PR.Main_Job_No

    , tbl_TimeCard_Upload_PR.Sub_Job_No

    , tbl_TimeCard_Upload_PR.JCD

    , tbl_TimeCard_Upload_PR.Pay_Type_ID

  • Did you update all statistics after the upgrade.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I didn't do the upgrade... But I'm guessing no.

    Will that impact the Where will doing a comparison?

    How do you do an update to statistics so I can direct him in the right direction?

  • Then that's a likely cause. Old-format stats can affect just about all queries.

    Look up UPDATE STATISTICS. Suggest all tables WITH FULLSCAN.

    Also make sure that he's done the recommended consistency checks after update, changed the page verify, changed the compat mode of possible, etc.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I'll pass that along,

    Also something I noticed. Looking at the Where the second on 'PR_STATUS' is a data type "nchar(1)".

    All of the 'Pay_Type_ID's are nvarchar(2). When I deleted that single line in the where OR changing the datatype to nvarchar(1) (test db of course) it ran fine.

    I'm going to do that (have to based on time constraints)?

  • No, nvarchar and nchar don't require conversions. Now if one had been varchar or char you should make the change, but both being unicode is fine.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I wonder why it works when I change the nchar(1) to nvarchar(1)

  • Ken at work (3/12/2012)


    I'll pass that along,

    Also something I noticed. Looking at the Where the second on 'PR_STATUS' is a data type "nchar(1)".

    All of the 'Pay_Type_ID's are nvarchar(2). When I deleted that single line in the where OR changing the datatype to nvarchar(1) (test db of course) it ran fine.

    I'm going to do that (have to based on time constraints)?

    In your WHERE statement you compare Pay_Type_ID with two-letter codes, changing it to nvarchar(1) will mean that you don't need any of the above WHERE conditions...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • I'm not sure I follow.

    Pay_Type_ID is a two letter code; PR_Status is a one letter code. I can change PR_Status from nchar(1) to nvarchar(1) and oddly enough it executes.

  • First step: Fix your statistics, it's well known that SQL 2005 does not use the SQL 2000 statistics format well.

    Once that's done, if you still have problems look at the individual queries.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Bingo,

    I spoke to the admin. He just ran update statistics (and some other thing???) and yeah!!! It worked.

    Thank you,

    Now we'll have to schedule the other db's and run the same updates there as well.

    Thanks again,

  • Thank you for your help but, of course, I have a new questions.

    I believe the admin ran the Update Stats, yesterday and everything seemed to work. I have another stored procedure (LONG) that DOES work. But instead of taking several seconds (up to 30 or so) now it takes nearly 3 minutes to execute. There are two linked Servers in the sp. I ran those select statements and they came up fast.

    The front end is MS-Access 2007 so... it's timing out.

    Might there be something else we missed?

    As you might expect this is a core procedure that needs to run not just a data-mining report, sigh...

  • There are some query performance regressions in SQL 2005, usually involving 'questionable' SQL. Maybe post it (I suggest a new thread so that people see that it's a new question) along with execution plans and we'll see what can be done

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I have no doubt the SQL may be questionable since I'm still fairly new.

    So in addition to that you're saying that SQL 2005 may run somewhat slower on some queries. Swell!

    I'm a little reluctant to post the entire query since it's a bit long. I'll see if I can find anything and until then I'll extend the Timeout, sigh.

    Otherwise I'll consider posting it and end up feeling a bit guilty.

    Thanks for all of your help,

  • Ken at work (3/13/2012)


    So in addition to that you're saying that SQL 2005 may run somewhat slower on some queries. Swell!

    A very small percentage of queries (and I mean very small). Usually from changes to the optimiser that result in a different plan being generated and fixable in every case I've seem (which admittedly hasn't been many). It's not something that happens every system and it's not something that one should expect when upgrading.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply