How can I improve the performance of this query using indexes?

Question

0.00/5 (No votes)

See more:

Here's my query:

SQL

SELECT	'Id' = el.id,
				'DB Time Stamp' = convert(nvarchar(50), el.db_datetime),
				'Local Time Stamp' = convert(nvarchar(50), el.local_datetime),
				'Event' = e.name,
				'Sub Campaign' = sc.title,
				'Company' = a.company_name,
				'Contact' = c.first_name + ' ' + c.last_name,
				'User' = u.fullname,
				'Parameter 1' = el.param1,
				'Parameter 2' = el.param2,
				'Parameter 3' = el.param3,
				'Parameter 4' = el.param4,
				'Parameter 5' = el.param5,
				'Parameter 6' = el.param6
				
	FROM		event_log		el WITH(NOLOCK)
	JOIN		[events]		e  WITH(NOLOCK) ON el.event_id = e.id
	LEFT JOIN	subcampaigns	sc WITH(NOLOCK) ON el.subcampaign_id = sc.id
	LEFT JOIN	accounts		a  WITH(NOLOCK) ON el.account_id = a.id
	LEFT JOIN	contacts		c  WITH(NOLOCK) ON el.contact_id = c.id
	LEFT JOIN	users			u  WITH(NOLOCK) ON el.[user_id] = u.id

	ORDER BY	el.id DESC;

I did the following:

1. Added composite index using event_log id, including columns: local_datetime,db_datetime, param1-param6
2. I added foreign key indexes in tables events, subcampaigns, accounts, contacts, users
3. I added individual indexes for subcampaign_id, account_id, contact_id, user_id inside my event_log table.

There is no improvement in the performance (runtime). I still get 50 seconds turn over.

Posted 24-Jun-14 22:10pm

SovereignX

Add a Solution

Comments

_Asif_ 25-Jun-14 4:31am

Did you check Query Execution Plan, what does it say? To me the problem seems to be the data not the query as you are not applying any filters. If lets say event_log contains 1 M rows than all 1 M rows will be processed here

2 solutions

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)

Vedat Ozan Oner · Answer 1 · 2014-06-24T22:38:00

Actually, I won't give you the direct answer, but I'll tell you what I would do in such a case. Start with joining two tables (event_log, events). No conversion, no other tables, no ordering. Then step by step add other tables, then apply other criteria, ordering, conversion, etc. Which change decreases your query performance, focus on that change and try to see how to improve it. (or ask here :))

for example:
1- test this:

SQL

SELECT  el.id, el.db_datetime, el.local_datetime, e.name
FROM event_log el
JOIN [events] e ON el.event_id = e.id

2- if it is ok, then test this:

SQL

SELECT  el.id, el.db_datetime, el.local_datetime, e.name, sc.title
FROM event_log el
JOIN [events] e ON el.event_id = e.id
JOIN subcampaigns sc ON el.subcampaign_id = sc.id

...

step by step, improve your query.

Another option is to check execution plan. You can do this by right-clicking on the query within SSMS. Here is a good info on the subject:
Displaying Graphical Execution Plans (SQL Server Management Studio)[^]

tyagimanoj12 · Answer 2 · 2014-06-24T22:52:00

Solution 3

Check the solution here

http://webxperts.co.in/home/articles[^]

Posted 24-Jun-14 22:52pm

tyagimanoj12