August 8, 2008 at 11:24 am
Hi,
Here is my traditional query (i have asked so many questions about it ...it must be famous in forum by now):D
I have achieved almost the required performance. SO now the most of time is taken by sorting.....can you please have a look at the attached execution plan and suggest how could we reduce the sorting time........btw all the indexes on my tables have "sort in temp table" option set to false......will that make any difference??
exec sp_executesql N'
SELECT TOP 50
(SELECT TOP 1 Name FROM Product l INNER JOIN LicenseProduct lp on l.id = lp.currentproductid WHERE LicenseId = License.Id ORDER BY l.ReleaseNumber, l.Created DESC) AS ProductName,
(SELECT TOP 1 ProductNumber FROM Product l INNER JOIN LicenseProduct lp on l.id = lp.currentproductid WHERE LicenseId = License.Id ORDER BY l.ReleaseNumber, l.Created DESC) AS ProductNumber,
License.Id AS LicenseId,
License.SerialNumber,
License.ServiceExpires,
License.FirstName,
License.LastName,
License.CompanyName,
License.EmailAddress AS LicenseEmail,
u.LoweredUsername AS AccountEmail,
License.Activations,
License.ReplacedBy,
License.Enabled
FROM
License WITH( NOLOCK )
LEFT OUTER JOIN
MyIpswitch_UserLicense ul WITH( NOLOCK ) ON License.Id = ul.LicenseId
LEFT OUTER JOIN
MyIpswitch_User u WITH( NOLOCK ) ON ul.UserId = u.UserId
WHERE
License.SerialNumber LIKE @SerialNumber + ''%''
ORDER BY License.SerialNumber',N'@SerialNumber varchar(127)',@SerialNumber='2B'
August 8, 2008 at 11:28 am
I don't understand most of your Order By clause. It looks like you're sorting on variables, but they shouldn't change throughout the query, so they shouldn't be in the Order By clause. Or am I missing something here?
Is SerialNumber the clustered index on the table, or at least the leading edge of an index on the table?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 8, 2008 at 11:36 am
Its a dynamic search query.......and parameters are passed to this query using sp_executesql
the order by clause is .......order by serialnumber
serialnumber is column of License Table and is indexed (non clustered)
August 8, 2008 at 12:25 pm
In that case, you're probably getting the best sort speed you're going to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 8, 2008 at 12:29 pm
does the "sort in temp DB" option of index effects sorting performance??
August 8, 2008 at 12:32 pm
usman.tanveer (8/8/2008)
exec sp_executesql N'SELECT TOP 50
(SELECT TOP 1 Name FROM Product l INNER JOIN LicenseProduct lp on l.id = lp.currentproductid WHERE LicenseId = License.Id ORDER BY l.ReleaseNumber, l.Created DESC) AS ProductName,
(SELECT TOP 1 ProductNumber FROM Product l INNER JOIN LicenseProduct lp on l.id = lp.currentproductid WHERE LicenseId = License.Id ORDER BY l.ReleaseNumber, l.Created DESC) AS ProductNumber,
License.Id AS LicenseId,
License.SerialNumber,
License.ServiceExpires,
License.FirstName,
License.LastName,
License.CompanyName,
License.EmailAddress AS LicenseEmail,
u.LoweredUsername AS AccountEmail,
License.Activations,
License.ReplacedBy,
License.Enabled
FROM
License WITH( NOLOCK )
LEFT OUTER JOIN
MyIpswitch_UserLicense ul WITH( NOLOCK ) ON License.Id = ul.LicenseId
LEFT OUTER JOIN
MyIpswitch_User u WITH( NOLOCK ) ON ul.UserId = u.UserId
WHERE
License.SerialNumber LIKE @SerialNumber + ''%''
ORDER BY License.SerialNumber',N'@SerialNumber varchar(127)',@SerialNumber='2B'
I see three queries here that are sorting, which may or may not be causing the problem. I also don't see why you are using dynamic SQL, unless what you are showing is the results of building the query. Still - not sure that you really need dynamic SQL.
With that said, you might be able to improve performance by removing the two correlated sub-queries. You could modify the query to:
exec sp_executesql N'
;WITH Product (LicenseId, Name, ProductNumber, rn)
AS (SELECT LicenseId
,Name
,ProductNumber
,row_number() OVER(Partition By LicenseId ORDER BY l.ReleaseNumber, l.Created desc)
FROM Product l
INNER JOIN LicenseProduct lp on l.id = lp.currentproductid
)
SELECT TOP 50
p.ProductName,
p.ProductNumber,
License.Id AS LicenseId,
License.SerialNumber,
License.ServiceExpires,
License.FirstName,
License.LastName,
License.CompanyName,
License.EmailAddress AS LicenseEmail,
u.LoweredUsername AS AccountEmail,
License.Activations,
License.ReplacedBy,
License.Enabled
FROM
License WITH( NOLOCK )
LEFT OUTER JOIN
Product p ON p.LicenseId = License.Id AND p.rn = 1
LEFT OUTER JOIN
MyIpswitch_UserLicense ul WITH( NOLOCK ) ON License.Id = ul.LicenseId
LEFT OUTER JOIN
MyIpswitch_User u WITH( NOLOCK ) ON ul.UserId = u.UserId
WHERE
License.SerialNumber LIKE @SerialNumber + ''%''
ORDER BY License.SerialNumber', N'@SerialNumber varchar(127)', @SerialNumber = '2B'
This is not tested (of course) - but I think it will return what you are looking for. You may have to adjust the OVER clause to make sure you are getting the expected results.
The only real option to eliminate the sort would be to remove the ORDER BY and sort the results in the client. Not sure if that is even possible for you - or whether or not that will improve your performance.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 8, 2008 at 12:34 pm
usman.tanveer (8/8/2008)
does the "sort in temp DB" option of index effects sorting performance??
Yes, that's why it is there. However, I believe that this is only used when rebuilding the indexes (though I could be wrong).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 8, 2008 at 12:43 pm
rbarryyoung (8/8/2008)
usman.tanveer (8/8/2008)
does the "sort in temp DB" option of index effects sorting performance??Yes, that's why it is there. However, I believe that this is only used when rebuilding the indexes (though I could be wrong).
Barry, you are almost right - all that means is that the LAST time the index was rebuilt the 'sort in temp DB' option was used. This setting is not used if you issue a rebuild statement that does not have that option set. In other words, it is not a default setting or used as a default setting.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 8, 2008 at 12:43 pm
When you create or rebuild an index, by setting the SORT_IN_TEMPDB option to ON you can direct the SQL Server 2005 Database Engine to use tempdb to store the intermediate sort results that are used to build the index. Although this option increases the amount of temporary disk space that is used to create an index, the option could reduce the time that is required to create or rebuild an index when tempdb is on a set of disks different from that of the user database.
- Books Online
It affects creation/rebuild of indexes, but not use of them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 8, 2008 at 1:18 pm
Thanks for the clarification guys.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 12, 2008 at 8:48 am
If you want to get technical, any time you see a spool operation in a query plan, its sorting in TempDb. It has nothing to do with the SORT_IN_TEMPDB option you set when rebuilding the index.
Its basically spooling a sort table in TempDb either because it decides that selecting the data in a certain way makes a join operation easier, or an insert / update of another index easier.
To avoid an "expensive" sort, place a covering index on the table in the order the data is being selected by the query. This generally improves query performance at the cost of insert performance against that table.
September 5, 2008 at 7:20 am
You may not actually have a problem. If the majority of the resources are used in the ORDER BY clause and the ordered column is using a non-clustered index, it seems to me that it just means that everything else is working efficiently. Something has to be taking the most processing time.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply