Little performance tuning on this query. 1

[HestonJames]

Morning Chaps,

As ever I'm after a little advice on the optimal way to achieve a task. I have the following query at the moment:

SELECT thinktank.thinktank_ID
     , ThinkTank.Name
     , ThinkTank.Installed
     , RemoteAddress.Address
     , RemoteAddress.DateTime
  FROM ThinkTank
INNER
  JOIN ( SELECT ThinkTank_ID
              , MAX(DateTime) AS max_date
           FROM RemoteAddress
         GROUP
             BY ThinkTank_ID ) AS m
    ON m.ThinkTank_ID = ThinkTank.ThinkTank_ID
INNER
  JOIN RemoteAddress
    ON RemoteAddress.ThinkTank_ID = m.ThinkTank_ID
   AND RemoteAddress.DateTime = m.max_date
ORDER BY ThinkTank.Installed DESC,
		ThinkTank.Name

Now this works an absolute charm and pulls records from the ThinkTank table along with the last entry for each ThinkTank from the RemoteAddress table. This query is very slick and runs in about 0.01 of a seconds which is great.

Now, I want to pull the last records for each ThinkTank from another table also, which has substantially more data in it (millions of rows) and this has a bit more of a hit on performance, here is my query:

SELECT thinktank.thinktank_ID
     , ThinkTank.Name
     , ThinkTank.Installed
     , RemoteAddress.Address
     , RemoteAddress.DateTime
[COLOR=red]
	 , l.LastLog
[/color]
  FROM ThinkTank
INNER
  JOIN ( SELECT ThinkTank_ID
              , MAX(DateTime) AS max_date
           FROM RemoteAddress
         GROUP
             BY ThinkTank_ID ) AS m
    ON m.ThinkTank_ID = ThinkTank.ThinkTank_ID
INNER
  JOIN RemoteAddress
    ON RemoteAddress.ThinkTank_ID = m.ThinkTank_ID
   AND RemoteAddress.DateTime = m.max_date
[COLOR=red]
INNER JOIN (
			Select	ThinkTank_ID,
					MAX(LogDateTime) As LastLog
			From	LoggingModel.dbo.MessageLog
			Group By	ThinkTank_ID
			) As l
		ON l.ThinkTank_ID = ThinkTank.ThinkTank_ID
[/color]
ORDER BY ThinkTank.Installed DESC,
		ThinkTank.Name

Now this little puppy runs at 35 seconds which isn't much use to me ;-) My question is, before I start to play with indexing again (I have loads of other queries which work off that table which I don't want to upset by changing indexes) is this the most efficient way of doing about what I'm doing?

Many thanks guys, appreciate the help.

Heston

 

[gmmastros]

How long does it take to run this part by itself?

Select   ThinkTank_ID, MAX(LogDateTime) As LastLog
From     LoggingModel.dbo.MessageLog            
Group By ThinkTank_ID

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Oh yeah. I forgot to mention... That is exactly how I would have written the query. (for whatever that's worth).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Hi George,

I'm glad to know that you'd have written it the same way, that's certainly encouraging, shows I've perhaps learned a little something from you over the past couple of months on here ;-)

I've run that separate element of the query and it takes about the same amount of time as the entire query so that is definitely where the bottleneck occurs.

Heston

 

[gmmastros]

Can you post the results of this?

-- Shows indexes on the table
sp_helpindex 'LoggingModel'


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Hi George,

I actually ran sp_helpindex 'MessageLog' so as only give the index of the table concerned, hope that's ok?

index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PerformanceIndex nonclustered located on PRIMARY LogClass_ID, LogDateTime, ThinkTank_ID, Device_ID, Media_ID
PK_MessageLog clustered, unique, primary key located on PRIMARY MessageLog_ID

Cheers my man,

Heston

 

[gmmastros]

Oh. Right. It's early here and I may not be thinking clearly.

Anyway.... I think the only way you can make this faster is by adding another index on the MessageLog table.

This index should include ThinkTank_ID and LogDateTime.

This one index will probably make the query run in less than a second.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Hey George,

Thanks for that, I'll look into grabbing a backup of my production DB, making those mods and testing on the dev server before I roll it out, just to be sure it doesnt effect the performance anywhere else in the application and then provided its all good I'll roll it out

Heston

 

[HestonJames]

Hey George,

I got around to trying to add this index to production this morning and get the following error thrown by my SSMS when trying to save the table.

/*
Friday, April 10, 200911:10:38 AM
User: #########
Server: #######
Database: ###########
Application:
*/

'MessageLog' table
- Unable to create index 'LastDataPerformance'.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Is this just because the database is doing other things? should I shut down the front end applications before trying to add the index to the table?

furthermore, in the 'sort by' part of the index, should I be using Ascending or Descending? I've only ever used Ascending before however I'm wondering if with this use case where we're looking for the new records first maybe Descending might be better?

Cheers,

Heston

 

[gmmastros]

I suggest you try creating the index in a query window instead of the GUI designer. Mostly because I think timeouts are handled differently.

I have a dirty little secret. My database is tiny. It's only a couple hundred megs. So things like 'descending' on indexes have never really made a difference for me. As such, I'm afraid I don't know the answer to your second question. Sorry.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Hey George,

Thanks for the feedback, that is a dirty little secret ;-) with that said since I've had my maintenance tasks running my DB is now down to around 700Mb instead the few GB is was origionaly However the table in question here is by far the biggest with around 6,000,000 rows at present and growing hourly.

I'll play around with the different options for ASC/DESC and see if that makes any difference to how it performs, I know very little about indexing, I'll let you know how things go on that front.

Scripting that CREATE INDEX did the job, with the new index on the table that query becomes more performant at around 3 seconds which is certainly better and means its not in the realms of acceptability.

I'd be keen to try using the DESC sort order on that datetime column to see what difference it makes, assuming my index is called 'LastDataPerformance' how would I script the alteration to make that sort order DESC?

Thanks George, you're a super star.

Heston

 

[gmmastros]

how would I script the alteration to make that sort order DESC?

I don't think you can really alter an index like this. I suggest you drop the index and re-create it.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Hey George,

Thanks for that tip. I dropped the index and recreated it with the DESC sort order but it doesn't appear to make any difference to the performance so I've put it back to the standard ASC order on the index. Perhaps that type of index only comes into play when we're ORDERING BY or something, who knows.

Thanks again for your help mate,

Heston

 

[gmmastros]

You're welcome. Anytime.

Is 3 seconds acceptable?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Yeah it should be o.k for now, it's called as part of a scheduled background task so isn't of any huge consequence, I'll keep my eye on it though, if the performance starts to slip as the DB grows then we'll have to look at a slightly better solution but this should be good for now.

We're due a server expansion at any time so I think we're fighting against the physics of the hardware to a certain extend at the moment ;-)

Heston

 

[gmmastros]

I see. Cool.

Even better though, if you can get your performance acceptable on slower hardware, it'll be even better on faster hardware.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Yeah that's very true indeed it's difficult to sport bottlenecks on a speedy machine.

Do you have any other suggestions on how to drop that query execution time then? off the top of your head?

Heston

 

[gmmastros]

Not really. I suspect the only way to improve performance is through indexes. Have you ever used the 'Database Engine Tuning Advisor' (DTA)? This is a very powerful tool that you can use to help tune your database (through indexes and statistics).

If you are using SQL Server Management Studio...

Load your query in a query window.
Click Query -> Analyze Query in Database Engine Tuning Advisor

After it loads, you can click start analysis and it may suggest indexes to speed up your query. You need to be a little careful bacause adding too many indexes to a table will slow down inserts, updates and deletes. Usually a couple indexes are fine, though.

Sometimes DTA will not be able to help, but there still may be things you can do to speed up a query. Consider DTA as a good starting point.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom