August 18, 2009 at 12:27 pm
Hi,
Please see below the query, "WHERE rowranking > 0 and rowranking < 26" is the problematic part, if i exclude this part from the query i get the result set in 2-3 seconds, but using "WHERE rowranking > 0 and rowranking < 26" it is taking forever and i get the PageIoLatch_SH in the activity monitor.
SELECT *
FROM
( select * , ROW_NUMBER() over(ORDER BY lead_stage DESC , Lead_LeadId DESC ) AS rowranking
from vListLead
WHERE Lead_AssignedUserId=523 AND lead_status = N'In Progress'
) as A
WHERE rowranking > 0 and rowranking < 26
Can someone help me identifying the issue and solution?
August 18, 2009 at 12:43 pm
Will this do what you need:
select top 25 * , ROW_NUMBER() over(ORDER BY lead_stage DESC , Lead_LeadId DESC ) AS rowranking
from vListLead
WHERE Lead_AssignedUserId=523 AND lead_status = N'In Progress'
order by lead_stage desc, lead_leadid desc;
- 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 18, 2009 at 1:00 pm
No, because "WHERE rowranking > 0 and rowranking < 26" is used to implement the paging on the web page, each page displays 26 items at a time.
August 18, 2009 at 1:06 pm
do you get the same performance issues if you use between instead of the >< operators?
August 18, 2009 at 1:10 pm
Unfortunately, but yes same issue with BETWEEN.
August 18, 2009 at 1:18 pm
For that kind of situation, your best bet is usually to insert the data into a table that has a session ID in it, and the row numbers, and then have each web page session query that table. That way, you're not recalculating the data each time the page changes. Also has the advantage+disadvantage that data changes don't cause the pages to change. Some people call that a "perm temp 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 18, 2009 at 1:21 pm
GSquared (8/18/2009)
Will this do what you need:
select top 25 * , ROW_NUMBER() over(ORDER BY lead_stage DESC , Lead_LeadId DESC ) AS rowrankingfrom vListLead
WHERE Lead_AssignedUserId=523 AND lead_status = N'In Progress'
order by lead_stage desc, lead_leadid desc;
Even though i can't use this but i tried this query just to check the performance, and its slow too.
even i tried without the ROW_NUMBER and its slow
select top 25 *
from vListLead
WHERE Lead_AssignedUserId=523 AND lead_status = N'In Progress'
order by lead_stage desc, lead_leadid desc
looks like sorting is causing some issues.
August 18, 2009 at 1:24 pm
GSquared (8/18/2009)
For that kind of situation, your best bet is usually to insert the data into a table that has a session ID in it, and the row numbers, and then have each web page session query that table. That way, you're not recalculating the data each time the page changes. Also has the advantage+disadvantage that data changes don't cause the pages to change. Some people call that a "perm temp table".
Agreed, but this is a third party application and we cannot make any changes on the front end. We can only play with the database. And in this particular case query itself is performing slow so there must be something wrong at the database or hardware level causing the PageIoLatch_SH waits.
August 18, 2009 at 1:26 pm
This would be a database change.
If, on the other hand, sorting is causing the problem, have you looked into your execution plan? You're probably missing an index that'll make a huge difference in performance.
- 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 18, 2009 at 2:09 pm
Thanks GSquared you da man.
I checked the plan previously and i thought it is fine....but now i looked it more closely and figured out a missing INCLUDED column in the index.
Now query is returning under a second.
Thanks guys.
August 18, 2009 at 2:38 pm
You're welcome.
- 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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply