Thursday, April 11, 2013

[T-SQL] Not able improve the performace of the query used in SSRS report

[T-SQL] Not able improve the performace of the query used in SSRS report

Not able improve the performace of the query used in SSRS report

Posted: 10 Apr 2013 11:20 PM PDT

I have used the below query in SSRS report but it is not at all rndring data.Can you plese help/guide me to improve the prformance of the query:-- Snapshot ValidationWITH Snapshot_CTE( flk_leadid , flk_leadsnapshotid , flk_fiscalperiodstart , flk_fiscalperiodend , flk_subject , flk_name , flk_companyname , flk_campaignid , flk_reportingproductfamilyid , flk_countryid , flk_stateid , flk_statuscode , flk_pricenettotal , flk_salesteamterritoryleadid , flk_territoryid , flk_leadownerterritoryid , ownerid , flk_createdon , flk_closeddate , snapshotcount , leadcount)As( select fleadsnapshot.flk_leadid , fleadsnapshot.flk_leadsnapshotid , fleadsnapshot.flk_fiscalperiodstart , fleadsnapshot.flk_fiscalperiodend , fleadsnapshot.flk_subject , fleadsnapshot.flk_name , fleadsnapshot.flk_companyname , fleadsnapshot.flk_campaignid , fleadsnapshot.flk_reportingproductfamilyid , fleadsnapshot.flk_countryid , fleadsnapshot.flk_stateid , fleadsnapshot.flk_statuscode , fleadsnapshot.flk_pricenettotal , fleadsnapshot.flk_salesteamterritoryleadid , fleadsnapshot.flk_territoryid , fleadsnapshot.flk_leadownerterritoryid , fleadsnapshot.ownerid , fleadsnapshot.flk_createdon , fleadsnapshot.flk_closeddate , SnapshotWithMaxCreatedOn.snapshotcount , 1 as leadcount from Filteredflk_leadsnapshot fleadsnapshot inner join ( select fleadsnapshot.flk_leadid , max(fleadsnapshot.createdon) as maxcreatedon , COUNT(fleadsnapshot.flk_leadid) as snapshotcount from Filteredflk_leadsnapshot fleadsnapshot where fleadsnapshot.flk_leadid is not null group by fleadsnapshot.flk_leadid ) as SnapshotWithMaxCreatedOn on SnapshotWithMaxCreatedOn.flk_leadid = fleadsnapshot.flk_leadid and SnapshotWithMaxCreatedOn.maxcreatedon = fleadsnapshot.createdon)SELECT SnapshotValidation.* FROM(-- No Snapshotselect 'No Snapshot' as [Error Type] , 1 as [Error Sequence] , LeadWithoutSnapshot.leadcount as [Lead Count] , flead.leadid as [Lead ID] , flead.[subject] as [Lead Name] , flead.createdon as [Lead Create Date] , flead.modifiedon as [Lead Modified Date] , flead.flk_closeddate as [Lead Close Date] , 0 as [Snapshot Count] , NULL as [Snapshot ID] , NULL as [Snapshot Name] , NULL as [Snapshot Fiscal Start] , NULL as [Snapshot Fiscal End]from FilteredLead flead inner join ( select COUNT(1) as leadcount, flead.leadid from FilteredLead flead where flead.leadid not in ( select distinct flead.leadid from FilteredLead flead inner join Filteredflk_leadsnapshot fsnapshot on fsnapshot.flk_leadid = flead.leadid ) group by flead.leadid ) as LeadWithoutSnapshot on LeadWithoutSnapshot.leadid = flead.leadid UNION ALL-- Fiscal Period Mismatchselect 'Fiscal Period Mismatch' as [Error Type] , 2 as [Error Sequence] , fsnapshot.leadcount as [Lead Count] , flead.leadid as [Lead ID] , flead.[subject] as [Lead Name] , flead.createdon as [Lead Create Date] , flead.modifiedon as [Lead Modified Date] , flead.flk_closeddate as [Lead Close Date] , fsnapshot.snapshotcount as [Snapshot Count] , fleadsnapshot.flk_leadsnapshotid as [Snapshot ID] , fleadsnapshot.flk_subject as [Snapshot Name] , fleadsnapshot.flk_fiscalperiodstart as [Snapshot Fiscal Start] , fleadsnapshot.flk_fiscalperiodend as [Snapshot Fiscal End]from FilteredLead flead inner join Snapshot_CTE fsnapshot on fsnapshot.flk_leadid = flead.leadid inner join Filteredflk_leadsnapshot fleadsnapshot on fleadsnapshot.flk_leadid = flead.leadidwhere -- lead close date is null but snapshot fiscal end date has value (Lead E, G) (flead.flk_closeddate is null and fsnapshot.flk_fiscalperiodend is not null) or -- snapshot fiscal start date is not correct saturday depending on lead created on (Lead F, M, N) (fsnapshot.snapshotcount = 1 and (convert(date, fsnapshot.flk_fiscalperiodstart) <> convert(date, dateadd(dd, -datepart(dw, flead.createdon), flead.createdon)))) or -- lead close date is not null but snapshot fiscal end date has no value (Lead K) (flead.flk_closeddate is not null and fsnapshot.flk_fiscalperiodend is null) or -- snapshot fiscal end date is not correct friday depending on lead closed on (Lead M, O) (convert(date, fsnapshot.flk_fiscalperiodend) <> convert(date, dateadd(dd, 6 - datepart(dw, flead.flk_closeddate), flead.flk_closeddate))) UNION ALL-- Attribute Mismatchselect 'Attribute Mismatch' as [Error Type] , 3 as [Error Sequence] , fsnapshot.leadcount as [Lead Count] , flead.leadid as [Lead ID] , flead.[subject] as [Lead Name] , flead.createdon as [Lead Create Date] , flead.modifiedon as [Lead Modified Date] , flead.flk_closeddate as [Lead Close Date] , fsnapshot.snapshotcount as [Snapshot Count] , fleadsnapshot.flk_leadsnapshotid as [Snapshot ID] , fleadsnapshot.flk_subject as [Snapshot Name] , fleadsnapshot.flk_fiscalperiodstart as [Snapshot Fiscal Start] , fleadsnapshot.flk_fiscalperiodend as [Snapshot Fiscal End]from FilteredLead flead inner join Snapshot_CTE fsnapshot on fsnapshot.flk_leadid = flead.leadid inner join Filteredflk_leadsnapshot fleadsnapshot on fleadsnapshot.flk_leadid = flead.leadidwhere (fsnapshot.flk_subject <> flead.[subject]) or (fsnapshot.flk_name<>flead.fullname) or (fsnapshot.flk_companyname <>flead.companyname) or (fsnapshot.flk_campaignid <>flead.campaignid) or (fsnapshot.flk_reportingproductfamilyid <>flead.flk_reportingproductfamilyid) or (fsnapshot.flk_countryid <>flead.flk_countryid) or (fsnapshot.flk_stateid <> flead.flk_stateid) or (fsnapshot.flk_statuscode <> flead.statecode) or (fsnapshot.flk_pricenettotal <>flead.flk_pricenettotal) or (fsnapshot.flk_salesteamterritoryleadid <>flead.flk_salesteamterritoryleadid) or (fsnapshot.flk_territoryid <> flead.flk_territoryid) or (fsnapshot.flk_leadownerterritoryid <> flead.flk_leadownerterritoryid) or (fsnapshot.ownerid <> flead.ownerid) or (fsnapshot.flk_createdon <>flead.createdon) or (fsnapshot.flk_closeddate <> flead.flk_closeddate) UNION ALLselect 'Multiple Snapshots' as [Error Type] , 4 as [Error Sequence] , 1 as [Lead Count] , flead.leadid as [Lead ID] , flead.[subject] as [Lead Name] , flead.createdon as [Lead Create Date] , flead.modifiedon as [Lead Modified Date] , flead.flk_closeddate as [Lead Close Date] , flsnapshot.snapshotcount as [Snapshot Count] , fleadsnapshot.flk_leadsnapshotid as [Snapshot ID] , fleadsnapshot.flk_subject as [Snapshot Name] , fleadsnapshot.flk_fiscalperiodstart as [Snapshot Fiscal Start] , fleadsnapshot.flk_fiscalperiodend as [Snapshot Fiscal End] from FilteredLead flead inner join ( select fsnapshot.flk_leadid, COUNT(1) as snapshotcount from ( select fsnapshotouter.flk_leadid , ( select COUNT(1) from Filteredflk_leadsnapshot fsnapshotinner where fsnapshotinner.flk_leadid = fsnapshotouter.flk_leadid and convert(date, fsnapshotinner.flk_fiscalperiodstart) = convert(date, fsnapshotouter.flk_fiscalperiodstart) ) as SnapshotCount from Filteredflk_leadsnapshot fsnapshotouter ) as fsnapshot where fsnapshot.SnapshotCount > 1 group by fsnapshot.flk_leadid ) as flsnapshot on flsnapshot.flk_leadid = flead.leadid inner join Filteredflk_leadsnapshot fleadsnapshot on fleadsnapshot.flk_leadid = flead.leadid UNION ALLselect 'Incomplete Snapshot' as [Error Type] , 5 as [Error Sequence] , 0 as [Lead Count] , flead.leadid as [Lead ID] , flead.[subject] as [Lead Name] , flead.createdon as [Lead Create Date] , flead.modifiedon as [Lead Modified Date] , flead.flk_closeddate as [Lead Close Date] , 0 as [Snapshot Count] , fleadsnapshot.flk_leadsnapshotid as [Snapshot ID] , fleadsnapshot.flk_subject as [Snapshot Name] , fleadsnapshot.flk_fiscalperiodstart as [Snapshot Fiscal Start] , fleadsnapshot.flk_fiscalperiodend as [Snapshot Fiscal End]from( select fleadinner.leadid, COUNT(fleadinner.leadid) as snapshotcount from Filteredflk_leadsnapshot fsnapshotinner inner join FilteredLead fleadinner on fleadinner.leadid = fsnapshotinner.flk_leadid where fsnapshotinner.flk_leadid = fleadinner.leadid and ( (fleadinner.flk_closeddate is null and fsnapshotinner.flk_fiscalperiodend is null) or (fleadinner.flk_closeddate is not null and fsnapshotinner.flk_fiscalperiodend is null) ) group by fleadinner.leadid having COUNT(fleadinner.leadid) > 1) as fleadouterinner joinFilteredLead flead on flead.leadid = fleadouter.leadidinner joinFilteredflk_leadsnapshot fleadsnapshot on fleadsnapshot.flk_leadid = fleadouter.leadid) as SnapshotValidationorder by SnapshotValidation.[Error Sequence], SnapshotValidation.[Lead Name], SnapshotValidation.[Snapshot Fiscal Start]

How to script table data with either TSQL or Powershell?

Posted: 10 Apr 2013 05:34 PM PDT

Hi,I can use GUI of the MS to script out data from a table. Is this possible using either Powershell or TSQL?Thanks.

Migration from MSDE (SQL2K) to SQLEXPRESS (SQL2K8) - Query Performance Degradation

Posted: 10 Apr 2013 02:31 AM PDT

I have migrated some databases from MSDE (SQL2K) to SQLEXPRESS (SQL2K8)Just using the backup and restore functions provided and made no other changes.I notice a query in particular has gone from taking 2 seconds in MSDE to taking 988 seconds in SQLEXPRESS. Hmmmm!Can anyone advise on why this would be happening?Query below... can't provide any sample data unfortunately but if you need any more info please let me know - not sure what would be needed?!?!(TBL_INSTANCES contains 12946 rows and TBL_RELATIONSHIPS contains 18137 rows)SELECT [Build1].dbo.TBL_TYPE_DEF.TYPE_NAME, [Build1].dbo.TBL_INSTANCES.DOC_NAME,[Build1].dbo.TBL_INSTANCES.DOC, [Build1].dbo.TBL_INSTANCES.SEC, [Build1].dbo.TBL_INSTANCES.VER,[Build1].dbo.TBL_RELATIONSHIPS.JUSTIFICATION FROM [Build1].dbo.TBL_INSTANCES INNER JOIN [Build1].dbo.TBL_TYPE_DEF ON [Build1].dbo.TBL_INSTANCES.TYPE_ID = [Build1].dbo.TBL_TYPE_DEF.TYPE_ID LEFT OUTER JOIN [Build1].dbo.TBL_RELATIONSHIPS ON [Build1].dbo.TBL_INSTANCES.DOC = [Build1].dbo.TBL_RELATIONSHIPS.PARENT_DOC AND [Build1].dbo.TBL_INSTANCES.SEC = [Build1].dbo.TBL_RELATIONSHIPS.PARENT_SEC AND [Build1].dbo.TBL_INSTANCES.VER = [Build1].dbo.TBL_RELATIONSHIPS.PARENT_VER WHERE (([Build1].dbo.TBL_INSTANCES.DOC + [Build1].dbo.TBL_INSTANCES.SEC + ISNULL(CONVERT(VARCHAR(10), [Build1].dbo.TBL_INSTANCES.VER), '')) NOT IN (SELECT PARENT_DOC + PARENT_SEC + ISNULL(CONVERT(VARCHAR(10), PARENT_VER), '') FROM [Build1].dbo.TBL_RELATIONSHIPS AS TBL_RELATIONSHIPS_1 WHERE ((PARENT_DOC + PARENT_SEC + ISNULL(CONVERT(VARCHAR(10), PARENT_VER), '')) IN (SELECT DOC+SEC+ISNULL(CONVERT(VARCHAR(10), VER),'') FROM [Build1].dbo.TBL_INSTANCES AS TBL_INSTANCES_1 WHERE (TYPE_ID IN (SELECT TYPE_ID FROM [Build1].dbo.TBL_TYPE_DEF AS TBL_TYPE_DEF_1 WHERE (TYPE_ID NOT IN (SELECT CHILD_TYPE FROM [Build1].dbo.TBL_RELATIONSHIPS_ALLOWED)))))) AND (CHILD_DOC IS NOT NULL))) AND ([Build1].dbo.TBL_INSTANCES.TYPE_ID IN (SELECT TYPE_ID FROM [Build1].dbo.TBL_TYPE_DEF AS TBL_TYPE_DEF_1 WHERE (TYPE_ID NOT IN (SELECT CHILD_TYPE FROM [Build1].dbo.TBL_RELATIONSHIPS_ALLOWED AS TBL_RELATIONSHIPS_ALLOWED_1)))) ORDER BY [Build1].dbo.TBL_TYPE_DEF.TYPE_ID,DOC,SEC,VER I am a total newbie to query optimization, so any help, guidance or direction appreciatedThanks in advance!Stacey

DATABASEPROPERTYEX and the IsSubscribed property

Posted: 10 Apr 2013 02:15 AM PDT

Hi All,When trying to examine if a database is a subscriber, I am trying to use DATABASEPROPERTYEX like:select DATABASEPROPERTYEX ( 'SubscriberDB' , 'IsSubscribed' )but it always returns 0, regardless of if the DB is a subscriber or not.Is there something i'm missing?Cheers.Rin

Replace Data in a String Based on Data in Table In line Query

Posted: 10 Apr 2013 04:29 PM PDT

Hi All,I trying to update a String based on the Inline View.String:-1 * [SAL] - [BON] - [COMM]In Line Query:Select * from TABLE Values ( SAL , COMM ) in tableNew String (Updated): -1 * [SAL] - 0 - [COMM]Please need help.

t-sql trimming before and after stored proc name

Posted: 10 Apr 2013 04:02 AM PDT

Hi, I have requirement to get only stored proc name and trim everything before and after stored procedure name. Here is the code i tried. so output only need to show "abc_SessionValuesUpdate"Any help will be great...thanks...:-)declare @col varchar(20)select @col = 'CREATE Procedure abc_SessionValuesUpdate AS SET NOCOUNT ON BEGIN UPDATE SessionValues SET Value = @Value WHERE ID = @ID AND Name = @Name IF @@ROWCOUNT = 0 INSERT INTO SessionValues values (@ID,@Name,@Value) END 'Select substring( LEFT(@Col,charindex('abc',@Col)+1),charindex('abc_',@Col)+1,len(LEFT(@Col,charindex('abc',@Col)-1))+1 )

inbuilt function : replicate, len, ltrim, rtrim and convert

Posted: 10 Apr 2013 07:47 AM PDT

can someone explain me why we use replicate in our query and also len, ltrim, rtrim and convert with useful example

How to Calculate a column value, based on previous column value

Posted: 10 Apr 2013 06:18 AM PDT

Hello All, Could you please help me here with this sql query, drop table #tempo1 create table #tempo1 (ActNo int, PlanName varchar(20), ProcessMonth varchar(8)) insert into #tempo1 values (100,'Keeper','2012-Jul') insert into #tempo1 values (101,'Keeper','2012-Jul') insert into #tempo1 values (102,'Valuer','2012-Jul') insert into #tempo1 values (103,'Keeper','2012-Jul') insert into #tempo1 values (104,'Valuer','2012-Jul') insert into #tempo1 values (105,'Keeper','2012-Jul') insert into #tempo1 values (110,'Keeper','2012-Jul') insert into #tempo1 values (100,'Keeper','2012-Oct') insert into #tempo1 values (101,'Valuer','2012-Oct') insert into #tempo1 values (102,'Valuer','2012-Oct') insert into #tempo1 values (103,'Keeper','2012-Oct') insert into #tempo1 values (104,'Keeper','2012-Oct') insert into #tempo1 values (105,'Valuer','2012-Oct') insert into #tempo1 values (106,'Valuer','2012-Oct') output will show below threecolumns, first two is straight , but the fourth column shoud be calculate based on second column (if secondvalue is same as fourth then just add literal 'Cnt 2' otherwie 'Cnt 1' so that i.e., if secondcolumn value is 'Keeper' and 4th column value is also 'Keeper' then show as 'Keeper Cnt2' sameway with Valuer as well, if it is different in july and oct then just oct column value with 'Cnt 1' literal ), for exmple AcctNo, JulyPtcpt, OctPtcpt 100, 'Keeper', 'Keeper Cnt 2' ---> (Both July and Oct same so added Cnt 2 at the end ) 101, 'Keeper','Valuer Cnt 1' ---> (Both July and Oct not same, so added Cnt 1 at the end ) 102, 'Valuer','Valuer Cnt 2' 103, 'Keeper','Keeper Cnt 2' 104, 'Valuer','Keeper Cnt 1' 105, 'Keeper','Valuer Cnt 1' 106, '' ,'Valuer Cnt 1' ---> because Not exist in prior month just leave blank or just leave with Cnt1 110, 'Keeper','' --> not exist in oct so leave blank or Just leave it as Cnt1 please help me here, i am stuck at the processmonth separation for jul/oct Kindly assist me here, milan
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog