asked on

How we can optimize this SQL query.

SELECT NewID(), Transactions.Transactions, Matters.Matters, Matters.MatterID, Matters.ShortDesc,
 Matters.AreaOfLaw, Matters.MatterType, Matters.ClientSort, Professionals.Professionals, Professionals.Initials, 
 Professionals.Office, Professionals.ProfClass, Professionals.ProfDept, Professionals.ProfType, 
 Professionals.ProfName, TaskCodes.TaskCodes, TaskCodes.CodeId, Transactions.MattersProjects, 
 MattersProjects.ProjectDesc, MattersProjects.ProjectAmount, ActivityCodes.ActivityCodeId ,
  ActivityCodes.ActivityCodeDesc, '4a0d2393-2e80-4a88-ac38-688b6f956852',
	 '77bdcf50-bb5e-479b-af19-8058295c8252', GetDate()   FROM FeeAnalysis INNER JOIN Transactions 
	 ON Transactions.Transactions = FeeAnalysis.Transactions LEFT OUTER JOIN MattersProjects 
	 ON Transactions.MattersProjects = MattersProjects.MattersProjects LEFT OUTER JOIN ActivityCodes
	  ON Transactions.ActivityCodes = ActivityCodes.ActivityCodes LEFT OUTER JOIN Professionals
	  ON Transactions.Professionals = Professionals.Professionals LEFT OUTER JOIN TaskCodes 
	  ON Transactions.TaskCodes = TaskCodes.TaskCodes LEFT OUTER JOIN StmnLedger 
	  ON Transactions.StmnLedger = StmnLedger.StmnLedger INNER JOIN Matters 
	  ON Transactions.Matters = Matters.Matters INNER JOIN SecurityILSKey 
	  ON SecurityILSKey.ILSKey = Matters.ILSKey 
	  AND SecurityILSKey.Professionals = '4a0d2393-2e80-4a88-ac38-688b6f956852' 
	  LEFT OUTER JOIN MatterTypes ON Matters.MatterType = MatterTypes.MatterTypesDesc
	   INNER JOIN Components ON Transactions.Components = Components.Components

Open in new window

PortletPaul

The first place to look is the execution plan. Please provide this (attach as a .sqlplan file).

ASKER

Please find the file.
SQLQuery2.sql

PortletPaul

That is not an execution plan, it is a series of 3 queries.

Save an Execution Plan in XML Format

ASKER

Please have a look.
SQL.sqlplan
Execution-plan.xml

PortletPaul

Thanks, but it looks good.
Mostly Index seeks, some Index scans but the row counts are very small. even the most "costly" item is seek on an clustered index.

I really do not see optimization potential in that query. (But of course I cannot comment on the functionality, so I don't know if you could achieve equivalent functionality with a simpler query).

Are you experiencing slowness? Why have you asked for this?

ASKER

Yes,By the query i am just launched the report window on that time it will take more time.That is why i am asking.

Nitin Sontakke

Couple of things:

1/ Did you try executing this query with SET STATISTICS TIME ON; Let us know how much time does it take. Better still paste time stats here.

2/ Is this query part of a stored procedure? If not, are you open to explore the sp route to a script route (as in more than one select statement)?

If yes, I suggest two (or three) tables variables to hold data of SecurityILSKey, Matters and (optionally) Transactions table rows. This should significantly reduce the data upfront (obviously i am unaware of rows involved).

Let me know your views!

ASKER

1/ I was trying with SET STATISTICS TIME ON.It is not fruitful solution.
2/This is inline query which written in my code not SP.

Nitin Sontakke

1/ SET STATISTICS TIME ON is NOT a solution. It ONLY tells how long it took to execute the query. The aim is to see that number. If that number is few hundred milliseconds, 200 or 300, then there is no point in investigating further (or less fruitful). This means the perceived delay is not this query.

2/ I can see that. The question was are you OPEN to a stored procedure / script block. Some scenarios doesn't allow that freedom. Hence the question.

ASKER