SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Removing a row Number Column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kapland
Starting Member

USA
5 Posts

Posted - 09/26/2013 :  09:25:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3710 Posts

Posted - 09/26/2013 :  09:40:12  Show Profile  Reply with Quote
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?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 09/26/2013 :  13:09:15  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Removing row_number generation part in fact may speed up the response time

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kapland
Starting Member

USA
5 Posts

Posted - 09/27/2013 :  08:10:20  Show Profile  Reply with Quote
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...
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3710 Posts

Posted - 09/27/2013 :  08:15:36  Show Profile  Reply with Quote
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.
Go to Top of Page

Kapland
Starting Member

USA
5 Posts

Posted - 09/27/2013 :  09:20:09  Show Profile  Reply with Quote
Just tried that, was denied permission:
"Msg 262, Level 14, State 4, Line 1
SHOWPLAN permission denied in database"
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3710 Posts

Posted - 09/27/2013 :  09:53:18  Show Profile  Reply with Quote
quote:
Originally posted by Kapland

Just tried that, was denied permission:
"Msg 262, Level 14, State 4, Line 1
SHOWPLAN 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.
Go to Top of Page

Kapland
Starting Member

USA
5 Posts

Posted - 09/27/2013 :  09:58:00  Show Profile  Reply with Quote
OK, I'll try that, thanks for your help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000