How Can I Improve This Query , Taking Too Much Time

Question

1.00/5 (1 vote)

See more:

Hi

this query is taking to much time to run and i want to reduce the time .... any better solutions

SQL

SET NOCOUNT ON;

	--CREATE INDEX campaign_ids
	--ON ReportingAcxiom ([campaign],[campaign_id],[cell_id]);

	create table #ecamp
	(
	[campaign_id] [nvarchar](100) NULL,
	[Campaign_desc] [nvarchar](300) NULL,
	[Campaign_start_date] datetime NULL,
	[cell_id] [nvarchar](100) NULL,
	[filename] [nvarchar](300) NULL
	);

	create table #ecampMatch
	(
	[email_source] [nvarchar](100) NULL,
	[campaign] [nvarchar](300) NULL,
	[date_time] datetime NULL,
	[cell_id] [nvarchar](100) NULL,
	[filename] [nvarchar](300) NULL
	);

	insert into #ecampMatch(email_source, campaign, date_time, cell_id, filename)
	select distinct email_source, campaign, date_time, cell_id, filename
	from master
	where campaign in (select distinct campaign from ReportingAcxiom);

	INSERT INTO #ecamp
        (campaign_id, Campaign_desc, Campaign_start_date, cell_id, filename)
	SELECT e.email_source as'campaign_id', e.campaign as 'Campaign_desc', e.date_time as 'Campaign_start_date', e.cell_id, e.filename
	FROM #ecampMatch e INNER JOIN
	ReportingAcxiom ON e.campaign = ReportingAcxiom.campaign
	and e.email_source = ReportingAcxiom.campaign_id
	and e.cell_id = ReportingAcxiom.cell_id;

	SET NOCOUNT OFF;

	with a as (
	select campaign_id, Campaign_desc, CONVERT(VARCHAR(19), max(Campaign_start_date), 120) as 'Campaign_start_date', cell_id, filename as 'cell_name', '' as 'list_name', '' as 'list_id', '' as 'source_code'
	from #ecamp
	group by campaign_id, Campaign_desc, cell_id, filename
	)

	select campaign_id, Campaign_desc, CONVERT(VARCHAR(19), max(Campaign_start_date), 120) as 'Campaign_start_date',CONVERT(VARCHAR(19), dateadd(m, 1, cast(Campaign_start_date as datetime)), 120) as 'Campaign_end_date', cell_id, cell_name, '' as 'list_name', '' as 'list_id', '' as 'source_code'
	from a
	group by campaign_id, Campaign_desc, Campaign_start_date, cell_id, cell_name;

Thanks

Posted 19-Feb-15 5:52am

saad88

Updated 19-Feb-15 6:09am

Wendelius

v3

Add a Solution

2 solutions

Solution 1

Well you could try the following indices:

SQL

CREATE INDEX ReportingAcxiom_campaign ON ReportingAcxiom (campaign);
CREATE INDEX ReportingAcxiom_campaign ON ReportingAcxiom (campaign_id, cell_id, campaign);
CREATE INDEX master_campaign ON master (campaign);
CREATE INDEX ecampMatch_campaign ON #ecampMatch  (campaign, email_source, cell_id);

Posted 19-Feb-15 6:17am

Wendelius

Comments

saad88 19-Feb-15 12:33pm

Hi Mika

i have non clustered index on campaign in master table .... also i know its stupid to ask but can u please incorporate these in query i wrote above , i just made it but not working .... or can u mention where i need them in query , i know i can create them all at same time ..... but can i use like create index and then drop after i finish .... what u say

saad88 19-Feb-15 12:35pm

or should i do it like i have done it for reportingAcxion , at the start of the query
and write drop statements at the ends

Wendelius 19-Feb-15 12:45pm

You should create the three first indexes in the database and let them be. Do not include them in your query. The indexes will live by themselves in the database. Typically indexes are created just once and never dropped.

An exception is the last index. It should be created after filling the temp table so:

...
insert into #ecampMatch(email_source, campaign, date_time, cell_id, filename)
select distinct email_source, campaign, date_time, cell_id, filename
from master
where campaign in (select distinct campaign from ReportingAcxiom);

CREATE INDEX ecampMatch_campaign ON #ecampMatch (campaign, email_source, cell_id);
...

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-02-19T06:52:00

I'm adding another solution since a completely different approach would be to squeeze the logic into a single statement. If I didn't make too many mistakes, it should look something like

SQL

with a as (
select campaign_id,
    Campaign_desc,
    CONVERT(VARCHAR(19),
    max(Campaign_start_date), 120) as 'Campaign_start_date',
    cell_id,
    filename as 'cell_name',
    '' as 'list_name',
    '' as 'list_id',
    '' as 'source_code'
from (SELECT e.email_source as'campaign_id',
        e.campaign as 'Campaign_desc',
        e.date_time as 'Campaign_start_date',
        e.cell_id,
        e.filename
    FROM (      select distinct email_source, campaign, date_time, cell_id, filename
            from master
        where   campaign in (select distinct campaign
                    from ReportingAcxiom)
        ) e
        INNER JOIN ReportingAcxiom ON e.campaign = ReportingAcxiom.campaign
        and e.email_source = ReportingAcxiom.campaign_id
        and e.cell_id = ReportingAcxiom.cell_id) ecamp
group by campaign_id, Campaign_desc, cell_id, filename
)
select campaign_id,
    Campaign_desc,
    CONVERT(VARCHAR(19),
    max(Campaign_start_date), 120) as 'Campaign_start_date',
    CONVERT(VARCHAR(19),
    dateadd(m, 1, cast(Campaign_start_date as datetime)), 120) as 'Campaign_end_date',
    cell_id,
    cell_name,
    '' as 'list_name',
    '' as 'list_id',
    '' as 'source_code'
from a
group by campaign_id, Campaign_desc, Campaign_start_date, cell_id, cell_name;

If you want to test you can still leave the three first indexes in the database but you don't need the temp tables or temp table index anymore