June 3, 2009 at 1:38 am
I have a table with 16 million records (Table name is "Question") and i need to get records based on some criteria and need to sort data on datetime column to display it. But query performs very slow when I do ordering at DateTime and works fine when I order it at ID.
Query is given below.
Kindly help me out !
SELECT *
FROM (
SELECTq.id
,q.[text]
,q.listingDescription
,q.pageUrl
,q.[timestamp]
,q.lastDiscussedTimestamp
,q.discussionCount
,q.expertCount
,q.viewCount
,q.categoryID
,scat.[name] as SupportCategoryName
,scat.pageUrl as SupportCategoryPageUrl
,scat.imageUrl as SupportCategoryImageUrl
,comp.[name] as CompanyName, comp.pageUrl as CompanyPageUrl
,comp.imageUrl as CompanyImageUrl
,prod.[name] as ProductName
,prod.pageUrl as ProductPageUrl
,prod.imageUrl as ProductImageUrl
,CategoryName = Case When q.categoryID is null Then null
Else (Select name from Category Where category.ID = q.categoryID) End
,PostedBy = Case When q.VisitorID is null Then
(Select ''
+ [name] +'' from [User] Where [User].ID = q.UserID) Else
(Select nick from Visitor Where Visitor.ID = q.VisitorID) End
/* Main issue is with this ROW_NUMBER when we order by timestamp*/
,ROW_NUMBER() OVER(ORDER BY q.TimeStamp desc) as RowNum
/* The query works fine and gives results in a flash when i order by at ID
,ROW_NUMBER() OVER(ORDER BY q.ID desc) as RowNum
*/
FROMQuestion Q
JOINQuestionKeyword qk ON QK.QuestionID = Q.ID
LEFT OUTER JOIN SPT_Category scat ON q.SupportCategoryID = scat.ID AND scat.status=1
LEFT OUTER JOIN SPT_Company comp ON q.CompanyID = comp.ID AND comp.status=1
LEFT OUTER JOIN SPT_Product prod ON q.productID = prod.ID AND prod.status=1
Whereq.status=1
AND qk.keywordID=33672
) as QuestionDiscussion
WHERE RowNum BETWEEN 1 AND 20
Musab
http://www.sqlhelpline.com
June 3, 2009 at 1:50 am
Hope this helps you,
http://www.windows-tech.info/15/1c5bec6464df5582.php
"Don't limit your challenges, challenge your limits"
June 3, 2009 at 2:55 am
Dear thankyou very much for such a quick response and your valuable information but unfortunately i have tried all these and i have index seeks not a single index scan or table scan is there all the covering indexes are there but still the query takes too much.
please help me out in this regard.
Musab
http://www.sqlhelpline.com
June 3, 2009 at 3:33 am
Are you sure the index containing TimeStamp column is being used?
June 3, 2009 at 4:53 am
A query with ordering on DateTime filed will perform slower than query with ordering on int filed, as to sort on datetime value, sql server has perform internal conversion on that field which need extra efforts and time at server side.
There may be two solution,
1) Instead of ordering on DateTime, do ordering on Date only. OR
2) Add a new field 'DateID' which will increase with new day (as new entry into table); and use this new field for ordering.
eg,
date: 01-Jun-09
DateID: 1
date: 02-Jun-09
DateID: 2
date: 03-Jun-09
DateID: 3
You can use 'After insesrt' trigger to update this field.
"Don't limit your challenges, challenge your limits"
June 3, 2009 at 5:14 am
Hi,
Please could you save the graphical "Actual Execution Plan" as a .sqlplan zip it up and attach it.
Also could we get index definitions for for the tables in your query.
Without seeing more info there isn't much more I can help with thanks 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 3, 2009 at 7:09 am
INT will sort faster then DATETIME but not radically faster. Have you updated the statistics on the index recently, maybe with a full scan. Is there a good data distribution on the index? Are you possibly getting a key lookup when using the date index and need a covering index possibly using include columns? Posting the execution plan is the best way to begin to answer some of these questions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 4, 2009 at 11:24 pm
i am attaching the execution plan and this plan has a Index Scan in it as it only uses a DateTime index as im not enforcing it to use any specific index and the covering index is also not there. if i use the covering index then it does an index seek but the result takes the same time
Musab
http://www.sqlhelpline.com
June 4, 2009 at 11:29 pm
I have defragged all the indexes and maintenance is done regularly and no index has a scan density less than 90% so what else is missing ? what would be the best way to sort on datetime and get the most optimum result i cant afford if the query takes longer than second, and taking a complete one second is a worst case so im seriously in a trouble
Musab
http://www.sqlhelpline.com
June 4, 2009 at 11:38 pm
Then try to change your indexing from datetime to int.. as desc in my last reply and suggested by Grant Fritchey.
"Don't limit your challenges, challenge your limits"
June 5, 2009 at 12:52 am
dear Thankyou very much for your valuable suggestion but it will be a worst case when i will do it as it requires a column to be added and data population will require much time and will disturb the traffic of the web site.
I need a better solution which can boost the performance without changing the structure of the table, I can add remove the indexes and if anything other. Thanks once again but it would be much better to give me any other workaround !
Musab
http://www.sqlhelpline.com
June 5, 2009 at 1:38 am
Hi musab, it's ok.
Actually, i m not in favor of indexing on datetime filed... i would rather change it at design time only. Though scenario like urs also needs to be consideration..
I would also like to know abut d solution.. i m trying w/ myside (s i told u i do not prefer to do it, and hence dont hv such kind of dealing) n hope to get anything soon. 🙂
"Don't limit your challenges, challenge your limits"
June 5, 2009 at 1:45 am
Thankyou very much its really nice to hear from you.
Musab
http://www.sqlhelpline.com
June 5, 2009 at 5:54 am
First, if you can post the actual execution plan instead of the estimated, it'll make a huge difference in trying to figure out what's going on.
Next, I'm not sure it'll affect the performance, positively or negatively, but I'd rewrite this section of the query to use JOIN's instead of what you have:
,CategoryName = CASE WHEN q.categoryID IS NULL THEN NULL
ELSE (SELECT name
FROM Category
WHERE category.ID = q.categoryID
)
END
,PostedBy = CASE WHEN q.VisitorID IS NULL
THEN (SELECT '' + [name] + ''
FROM [User]
WHERE [User].ID = q.UserID
)
ELSE (SELECT nick
FROM Visitor
WHERE Visitor.ID = q.VisitorID
)
END
You should be able to to do outer joins on these criteria and then use the values or not through a COALESCE in the SELECT statement. Don't use COALESCE in the WHERE clause or the JOINS.
Based on the output list from the key lookup, I don't think a covering index is possible. I realize that the cluster is the PK. Would it be possible to, as an experiment, set up the table with a non-clustered pk and try a cluster on the timestamp field? I'm not sure about that one, but it could work.
Finally, is the intent of the query to get the top 20 records sorted by the TimeStamp column? If so, instead of the ROW_NUMBER function and a sub-select, why not a more straight forward TOP 20 with an ORDER BY clause? Also, if you're selecting the datetime value DESC, did you create the index in that same order? It will make a difference.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 8, 2009 at 10:10 pm
Dear Fritchey thankyou very much for your time,
I have tried the left joins and its not that much beneficial as main issue is of timestamp you can and about the row_num actually this is q query for paging and i need sequence results so cant do just top 20.
And the index is also of the same order. and about pk the problem is that im using FTS and i cant change the pk so need ID for pk as main searching will be affected it i change this.
I am also attaching the Original Plan you can check and give me any other suggestion.
Musab
http://www.sqlhelpline.com
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply