April 11, 2013 at 6:20 am
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 Validation
WITH 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 Snapshot
select
'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]
, NULLas [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 Mismatch
select
'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_fiscalperiodendas [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.leadid
where
-- 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 Mismatch
select
'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_fiscalperiodendas [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.leadid
where
(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 ALL
select
'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_fiscalperiodendas [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 ALL
select
'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_fiscalperiodendas [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 fleadouter
inner join
FilteredLead flead on flead.leadid = fleadouter.leadid
inner join
Filteredflk_leadsnapshot fleadsnapshot on fleadsnapshot.flk_leadid = fleadouter.leadid
) as SnapshotValidation
order by SnapshotValidation.[Error Sequence], SnapshotValidation.[Lead Name], SnapshotValidation.[Snapshot Fiscal Start]
April 11, 2013 at 6:53 am
Can you provide the table definitons. And any data if poss
April 11, 2013 at 7:40 am
As previously stated having ddl and sample data is the best way to help. There is a large amount of information here though so that task may be a bit daunting.
You need to break this apart into pieces and figure out which of your queries are the slow ones. I see lots of nonSARGable predicates, there are also a number of predicates that can be simplified.
For example you have:
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
)
)
Why bother with checking fleadinner.flk_closeddate? If it is NULL or NOT NULL you want the same thing. This can be simplified to:
WHERE fsnapshotinner.flk_leadid = fleadinner.leadid
AND fsnapshotinner.flk_fiscalperiodend IS NULL
The query that has this:
WHERE (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)
This is likely to be one of your slowest because it has no choice but to scan ALL of those columns. :w00t:
This process is likely to be far more complicated than an online forum can provide. Your query needs a lot of optimization and the only way to do that is with tables, indexes and data.
For anyone else stopping by I ran the query through a formatter so it is more legible.
WITH 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 Snapshot
SELECT '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 Mismatch
SELECT '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.leadid
WHERE
-- 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 Mismatch
SELECT '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.leadid
WHERE (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 ALL
SELECT '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 ALL
SELECT '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 fleadouter
INNER JOIN FilteredLead flead ON flead.leadid = fleadouter.leadid
INNER JOIN Filteredflk_leadsnapshot fleadsnapshot ON fleadsnapshot.flk_leadid = fleadouter.leadid
) AS SnapshotValidation
ORDER BY SnapshotValidation.[Error Sequence]
,SnapshotValidation.[Lead Name]
,SnapshotValidation.[Snapshot Fiscal Start]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 11, 2013 at 9:24 pm
Okay, this can be simplified but it isn't going to be easy. Some of this code is doing a lot more work than is needed. If you would like to see how this code can be modified we will need a lot of assistance from you.
We will need the DDL (CREATE TABLE statement) for each of the tables involved in the query. You can reduce this to only the columns needed by the query and the indexes necessary to support it, We will need the indexes currently defind on the tables (as CREATE INDEX statements).
We will need SAMPLE data, not LIVE data. The data you provide needs to be representative of the problem domain. This needs to be provided for all the tables as a series of INSERT INTO statements. Just enough data in each table to properly test our queries.
Last, and MOST important, we need the expected results based on the sample data. In this case I would assume it is the output of the current SQL code provided.
Please make sure that if you provide the requested information that you test it all in a Sandbox (empty) database to be sure all the scripts run without errors.
The following is an example of the changes that could be made. I was able to make some changes to the initial CTE:
WITH 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
flss.flk_leadid,
flss.flk_leadsnapshotid,
flss.flk_fiscalperiodstart,
flss.flk_fiscalperiodend,
flss.flk_subject,
flss.flk_name,
flss.flk_companyname,
flss.flk_campaignid,
flss.flk_reportingproductfamilyid,
flss.flk_countryid,
flss.flk_stateid,
flss.flk_statuscode,
flss.flk_pricenettotal,
flss.flk_salesteamterritoryleadid,
flss.flk_territoryid,
flss.flk_leadownerterritoryid,
flss.ownerid,
flss.flk_createdon,
flss.flk_closeddate,
smco.snapshotcount,
1 AS leadcount
FROM
dbo.Filteredflk_leadsnapshot flss
cross apply (select max(flss1.createdon) AS maxcreatedon, COUNT(flss1.flk_leadid) AS snapshotcount
FROM Filteredflk_leadsnapshot flss1
WHERE flss1.flk_leadid = flss.flk_leadid) smco(maxcreatedon, snapshotcount)
WHERE smco.maxcreatedon = flss.createdon
)
... -- rest of code here.
Whether this will work or not, I have no idea as I have no tables or data to test it against or to compare the output with the original code.
April 11, 2013 at 9:29 pm
If provided all requested, we should be able to test and compare changes to original code in small workable chunks, breaking the code down piece by piece.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply