Click here to Skip to main content
15,923,142 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
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

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)[^]
 
Share this answer
 
Check the solution here

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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900