Tuesday, September 24, 2013

[T-SQL] Seeking Explanation for Query Performance difference...

[T-SQL] Seeking Explanation for Query Performance difference...

Seeking Explanation for Query Performance difference...

Posted: 23 Sep 2013 10:10 PM PDT

Just after some advice if possible.I had a Stored Procedure which had a join to a Scalar Valued Function, returning 40 rows in its final data set. It was doing 300 thousand reads and taking 9 seconds to return its data.Soooooooo….I ripped the Function out of the Join and populated the results of the Function into a Local Temporary Table at the top of the Procedure. Reads came down to 1982, took less than 1 second to run. Now I know Scalar Functions are poor in SELECT and WHERE clauses due to the row by row processing and executes the Function, but I didn't see why this would be a problem in the Join?!Soooooooo…I tested one other thing, I swapped the Local Temporary Table out for a Table Variable and ran the query again. This time the performance was back to how it was originally with the ridiculous amount of reads being performed. Cache and Buffers dropped each time. STATISTICS IO showed the massively reduced reads when using the Local Temp Table.The Execution Plan highlighted the difference, the Local Temporary Table version was using Parallelism and the Table Variable/Function versions were not. Both procedures were over the cost Threshold for Parallelism so not sure why one chose to use it and not the other?!Any ideas?Cheers guys

Query performance

Posted: 23 Sep 2013 07:25 PM PDT

Hi,I have a query which takes more than 2 min whenver it runs.Is there any other way to wrtie this query so that performance can be improved[code="sql"]SELECT A.OFFICEID,A.PROJECTID,A.PROPOSALID,A.SOLUTIONID,A.UNITID,A.PRICEITEMID,A.ESTIMATIONGROUPID,A.SRNO,A.PRICEITEMNAME,A.LOCALPURCHASETYPEID,A.PRI_PRICE,A.Sec_Price,A.PRI_PRICEWOR,A.SEC_PRICEWOR,A.PRIMARYCURRENCYID,A.SECONDARYCURRENCYID,A.DELETEFLAG,A.DATEADDED,A.ADDEDBY,A.DateChanged,A.ChangedBy,A.Quantity,A.SrNoDetail,A.Pri_PriceWM,A.Sec_PriceWM ,A.Code,A.MOrITypeFROM ESTIMATIONOUTPUTPRICE A WITH (NOLOCK),VW_ESTIMATIONOUTPUTPRICE_ETL_COMPANY B WITH (NOLOCK), UNIT C WITH (NOLOCK)WHERE A.OFFICEID = B.OFFICEID and A.PROJECTID = B.PROJECTID and A.PROPOSALID = B.PROPOSALID and A.SOLUTIONID = B.SOLUTIONID and A.UNITID = B.UNITID andB.OFFICEID = C.OFFICEID andB.PROJECTID = C.PROJECTID andB.PROPOSALID = C.PROPOSALID andB.SOLUTIONID = C.SOLUTIONID andB.UNITID = C.UNITID and C.ISARCHIVED=0 [/code]

Query

Posted: 23 Sep 2013 08:35 PM PDT

Dear All I have one scnario. One column i am having Following records 1a2a2b.......10a10bBut i want following output through Query122....1010.

inconsistently wrong query results

Posted: 23 Sep 2013 08:50 PM PDT

Hi All - I have a count/grouping problem and for the life of me can't make sense of it. Involved in the query is one table (plus a time-dimension table as a variation) which holds info on enquiries made, each enquiry has its int key and an FK referencing the table which contains info on cases, it's a straight forward one-to many relationship. What I want the query to do is count distinct cases and count enquiries per month. It shouldn't be hard, but I get inconsistent and partly wrong results. Below is the sql with result sets. The first two snipets are variations on achiving the count/group operations in one query, and the third bit looking up each month seperately and puts it in one result set, where the months are in the same order as in the two queries above. The last result set is also the correct one.I have checked the data and there are no abnormalities, so I must be to do with just not understanding the way sql count and groups. Any help would be most welcome, thanx. [code="sql"]SELECT TOP (100) PERCENT COUNT(DISTINCT CaseID) AS Cases, COUNT( distinct CallID) AS Enquiries, RIGHT(CONVERT(varchar(10), CallDatAdd, 5), 5) as [Month-Year]FROM tbldCallsWHERE (CallDatAdd BETWEEN CONVERT(DATETIME, '2013-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2013-06-30 00:00:00', 102)) GROUP BY RIGHT(CONVERT(varchar(10), CallDatAdd, 5), 5)order by [Month-Year] asc[/code][code="plain"]Cases Enquiries Month-Year723 945 01-13646 866 02-13773 937 03-13675 829 04-13758 928 05-13632 782 06-13[/code][code="sql"]SELECT COUNT(DISTINCT tbldCalls.CaseID) AS Cases, COUNT(tbldCalls.CallID) AS Enquiries, dimension_time.Month_TextFROM dimension_time INNER JOIN tbldCalls ON CONVERT(date, dimension_time.Day_Timestamp) = CONVERT(date, tbldCalls.CallDatAdd)WHERE (tbldCalls.CallDatAdd BETWEEN CONVERT(DATETIME, '2013-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2013-06-30 00:00:00', 102))GROUP BY dimension_time.Month_Text, dimension_time.Month_KeyORDER BY dimension_time.Month_Key asc[/code][code="plain"]Cases Enquiries Month_Text723 945 Jan646 866 Feb773 937 Mar675 829 Apr758 928 May632 782 Jun[/code][code="sql"]SELECT count(distinct CaseID) as Cases, count (callid) as EnquiriesFROM tbldCallsWHERE (CallDatAdd between CONVERT(DATETIME, '2013-01-01 00:00:00', 102) and CONVERT(DATETIME, '2013-01-31 00:00:00', 102))Union allSELECT count(distinct CaseID) as FebCases, count (callid) as EnquiriesFROM tbldCallsWHERE (CallDatAdd between CONVERT(DATETIME, '2013-02-01 00:00:00', 102) and CONVERT(DATETIME, '2013-02-28 00:00:00', 102))Union allSELECT count(distinct CaseID) as MarchCases, count (callid) as EnquiriesFROM tbldCallsWHERE (CallDatAdd between CONVERT(DATETIME, '2013-03-01 00:00:00', 102) and CONVERT(DATETIME, '2013-03-31 00:00:00', 102))Union allSELECT count(distinct CaseID) as AprilCases, count (callid) as EnquiriesFROM tbldCallsWHERE (CallDatAdd between CONVERT(DATETIME, '2013-04-01 00:00:00', 102) and CONVERT(DATETIME, '2013-04-30 00:00:00', 102))Union allSELECT count(distinct CaseID) as MayCases, count (callid) as EnquiriesFROM tbldCallsWHERE (CallDatAdd between CONVERT(DATETIME, '2013-05-01 00:00:00', 102) and CONVERT(DATETIME, '2013-05-31 00:00:00', 102))Union allSELECT count(distinct CaseID) as JuneCases, count (callid) as EnquiriesFROM tbldCallsWHERE (CallDatAdd between CONVERT(DATETIME, '2013-06-01 00:00:00', 102) and CONVERT(DATETIME, '2013-06-30 00:00:00', 102))[/code][code="plain"]Cases Enquiries680 888618 825773 937630 772678 831632 782[/code]

Phone number question - detecting and replacing

Posted: 23 Sep 2013 07:58 AM PDT

Hello,I have a website/database app with a text column that stores a user's bio where users are not allowed to embed their phone number (e.g. similar to a dating website). I need to write a query that detects phone numbers and replaces them with XXXs. Thanks,MattFor example, I have written a query that detects records with phone numbers in (xxx) xxx-xxxx format, but the replacing is tough. Maybe RegExp?select userid, bio from userswhere bio like ('%([0-9][0-9][0-9])%')

Incomprehensible error in OVER clause with self-JOIN and GROUP BY

Posted: 23 Sep 2013 02:38 AM PDT

I've encountered a very weird error when using an OVER clause, which i cannot explain. I'd be interested if anyone else can tell me why this happened.I was running the following code:[code="sql"]SELECT ParentProjects.RollupAllProjectsCommittedTotalCost * CASE WHEN ROW_NUMBER() OVER (PARTITION BY ParentProjects.ProjectID ORDER BY ParentProjects.ProjectID) = 1 THEN 1 ELSE 0 ENDFROM Projects INNER JOIN Projects AS ParentProjects ON parentProjects.projectID = Projects.ParentProjectIDGROUP BY Projects.ProjectID, ParentProjects.ProjectName, ParentProjects.RollupAllProjectsCommittedTotalCost[/code]and i got the following error:[quote]Msg 8120, Level 16, State 1, Line 1Column 'Projects.ProjectID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.[/quote]Note that the column "Projects.ProjectID" is [b]NOT[/b] in the select list and it [b]IS[/b] in the GROUP BY clause, exactly the opposite of what the error claims!Now, if if replace the PARTITION BY element and use an equivalent one from the primary Projects table (instead of the self join column), the code runs just fine:[code="sql"]SELECT ParentProjects.RollupAllProjectsCommittedTotalCost * CASE WHEN ROW_NUMBER() OVER (PARTITION BY Projects.TopLevelParentProjectID ORDER BY Projects.ProjectID) = 1 THEN 1 ELSE 0 ENDFROM Projects INNER JOIN Projects AS ParentProjects ON parentProjects.projectID = Projects.ParentProjectIDGROUP BY Projects.ProjectID, ParentProjects.ProjectName, ParentProjects.RollupAllProjectsCommittedTotalCost,Projects.TopLevelParentProjectID[/code]Can anyone explain to me why this is happening?!?

CLUSTERED INDEX SCAN (EmpNonPrjTime)

Posted: 23 Sep 2013 05:15 AM PDT

I have a report that calls procedure.Procedure is simple select from VIEW:...[code="sql"]FROM vwNzEmpNonPrjTimeWHERE co_code = @L_CO_CODE AND ('**all**' IN (@L_ORG) OR org_code IN (SELECT * FROM getValuesAsTable(@L_ORG, ','))) AND ('**all**' IN (@L_EMPLOYEE) OR EmployeeNumber IN (SELECT * FROM getValuesAsTable(@L_EMPLOYEE, ',')))[/code]@L_ORG and @L_EMPLOYEE are input parameters for "Multi Value" SSRS parameters.Inside stored porcedure I cannot use IN @var method.I have to convert comma delimited strings into temp table.but this seems to create a problem with execution code.If I change procedure code and have:WHERE org_code = @L_ORGAND EmployeeNumber = @L_EMPLOYEEcode executes in 1 sec, no clustered index scan.As sooon as I change it back to the code at the topI get clustered index scan and it executes more than 10 sec.By the way, SELECT * FROM getValuesAsTable(@L_EMPLOYEE, ','))shouldn't be a problem. It runs in 200 miliseconds even with a lot of values.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog