Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kapland
Starting Member
5 Posts |
 Posted - 2013-09-26 : 09:25:23
|
| I have a code that runs without any issues. Within the code, I select the record number using the statement: ROW_NUMBER() OVER(ORDER BY VMFV.Policy_No) as RecordNo. When I try and remove this statement by either deleting it or commenting it out, the code runs on and on without stopping. Yesterday I let it run for an hour and began to get some results very slowly, however my query normally runs in 30-90 seconds, so I cant have it taking this long, since the idea of removing the record number is to make it run faster. Any ideas? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-26 : 09:40:12
|
quote:
Originally posted by Kapland
I have a code that runs without any issues. Within the code, I select the record number using the statement: ROW_NUMBER() OVER(ORDER BY VMFV.Policy_No) as RecordNo. When I try and remove this statement by either deleting it or commenting it out, the code runs on and on without stopping. Yesterday I let it run for an hour and began to get some results very slowly, however my query normally runs in 30-90 seconds, so I cant have it taking this long, since the idea of removing the record number is to make it run faster. Any ideas?
Removing a row_number column in and of itself should not slow down a query. There is something else that is causeing the query to slow down. It may be that the logic of the query and the number of rows returned have changed, or it could be that the environment (number of rows in the table, the load on the server, or any number of other things) have changed.Are you able to post the entire query? |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-09-26 : 13:09:15
|
| Removing row_number generation part in fact may speed up the response timeMadhivananFailing to plan is Planning to fail |
|
|
|
Kapland
Starting Member
5 Posts |
Posted - 2013-09-27 : 08:10:20
|
| Unfortunately, I cannot post the entire query, since it is over 500 lines long. I have the row number statement embedded in another select statement, but when I remove the top one or both, I get the same issues. As in select ... Extract.RecordNo... from (select...ROW_NUMBER() OVER(ORDER BY VMFV.Policy_No) as RecordNo...) as Extract So when I comment just the first one or both, I get the same issue. This is the only change that I'm making in the code to go from running smoothly to running endlessly. Yesterday after about 2 hours I had almost half my query... |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-27 : 08:15:36
|
quote:
Originally posted by Kapland
Unfortunately, I cannot post the entire query, since it is over 500 lines long. I have the row number statement embedded in another select statement, but when I remove the top one or both, I get the same issues. As in select ... Extract.RecordNo... from (select...ROW_NUMBER() OVER(ORDER BY VMFV.Policy_No) as RecordNo...) as Extract So when I comment just the first one or both, I get the same issue. This is the only change that I'm making in the code to go from running smoothly to running endlessly. Yesterday after about 2 hours I had almost half my query...
Turn on query plan (Control-M or Query->Include Actual Execution Plan) and then run the query with and without the change(s). Compare the query plans. The graphical query plans will show you how SQL Server is getting you the results along with a lot of useful information about each step it is taking - number of rows returned, relative cost etc. |
|
|
|
Kapland
Starting Member
5 Posts |
Posted - 2013-09-27 : 09:20:09
|
| Just tried that, was denied permission:"Msg 262, Level 14, State 4, Line 1SHOWPLAN permission denied in database" |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-27 : 09:53:18
|
quote:
Originally posted by Kapland
Just tried that, was denied permission:"Msg 262, Level 14, State 4, Line 1SHOWPLAN permission denied in database"
Are you able to ask someone who has the permissions to do this for you, or have them grant you the permissions? Alternatively, if you have another dev environment where you have full permissions you can try it there.It is virutally impossible for anyone on the forum to diagnose the problem with the limited information that you are able to post. And, without the ability to see what the query optimizer is doing, it would be hard for you to figure it out as well.Regardless, what I still suspect is that it is not just the changes you indicated that are present in the code. Check again to verify that there aren't any other changes to the enviornment and/or code.Also, when you make a change to a stored procedure and run it, the query plan has to be regenerated - which can add to the time taken for results to be returned when you run it for the first time. So run the query a few times to get a more realistic measure of the execution time. |
|
|
|
Kapland
Starting Member
5 Posts |
Posted - 2013-09-27 : 09:58:00
|
| OK, I'll try that, thanks for your help. |
|
|
|
|
|
|
|
|
Removing a row Number Column