March 12, 2012 at 6:57 am
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
March 12, 2012 at 7:01 am
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
March 12, 2012 at 7:06 am
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?
March 12, 2012 at 7:11 am
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
March 12, 2012 at 7:19 am
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)?
March 12, 2012 at 7:27 am
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
March 12, 2012 at 7:43 am
I wonder why it works when I change the nchar(1) to nvarchar(1)
March 12, 2012 at 8:01 am
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...
March 12, 2012 at 8:05 am
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.
March 12, 2012 at 8:09 am
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
March 12, 2012 at 8:21 am
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,
March 13, 2012 at 6:35 am
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...
March 13, 2012 at 6:52 am
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
March 13, 2012 at 8:34 am
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,
March 13, 2012 at 8:45 am
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply