View Running Slow w/ Unqualified Order By

Suppose I have a view named "Stuff".  When selecting from the the view
like this,

   select * from stuff order by Id

the query runs very slow.  However when I qualify the Id column with
one of the aliases in
the underlying query of the view like this

   select * from stuff order by s.Id

the query runs 10-12 times faster. Why is that?

Cheers,
Aeden
0
aeden
12/22/2009 4:53:32 AM
sqlserver.programming 1873 articles. 0 followers. Follow

1 Replies
1028 Views

Similar Articles

[PageSpeed] 7

Hi
I think that it because of SQL Server does not need to get to the datapages 
('touching' the table) to return the data (RID) but  traverse trou the index 
pages to to get the data (the column ID to satisfy the query IS already 
stored in the index)

Have you compare bot execution plans?



<aeden.jameson@gmail.com> wrote in message 
news:b6e1bc20-a544-4b63-b449-8a12d7c006cf@h14g2000pri.googlegroups.com...
> Suppose I have a view named "Stuff".  When selecting from the the view
> like this,
>
>   select * from stuff order by Id
>
> the query runs very slow.  However when I qualify the Id column with
> one of the aliases in
> the underlying query of the view like this
>
>   select * from stuff order by s.Id
>
> the query runs 10-12 times faster. Why is that?
>
> Cheers,
> Aeden 


0
Uri
12/22/2009 5:15:55 AM
Reply:

Similar Artilces:

running total 03-15-08
i need to have a running total in a query. example date item qty needed onhand available 03/18/08 1 500 2000 1500 03/19/08 1 1500 2000 0 3/20/08 1 250 2000 -250 so the available column changes based on the available of the prior line. i want this to continue until the item changes. is this possible. thanks Use a subquery to get the sum of the previous values for the same item. You will want type an expression like this into the Field row of your qu...

Ordering to Lowest Cost Vendor
GP 8.0, SQL Hi, we are a retailer with two sites under one company. Each site has it's own vendors. We want to use Purchase Order Generator to order from the site-specific vendor with the lowest cost. I tried changing the items so that PO Generator orders using vendor with lowest cost. However, this selects the lowest cost vendor among all the vendors for an item, and there doesn't appear to be a way to designate a vendor as site specific (other than using a naming convention which we have done). Here's the problem: We are a two-store retail chain set up under a single com...

Multiple search change calendar view
Hi Here's my problem : I go 2 calendar My view is day/week/month with preview When I do more than one search with the little search (not advanced) box, go to another folder then go back to calendar, view is changed to "active appointements". How can i prevent this ? Thanks Fred ...

Dynamics Currency Problem
Hi, Bit of a strange problem with Dynamics. We have Dynamics set to run on two Citrix servers. One for our UK users, and one for our US users. All of a sudden the smart list exports for our US users are appearing in Pound Sterling as apposed to Dollars. I assume that the currency is taken from the regional settings, is this correct? If so, then is it the local settings of the users PC or is it the profile regional settings on the citrix server? I hope someone can shed some light on this as i am lost as to why this has happened to all US users all of a sudden? Many thanks in advance ...

Win2k Clients slow to log on after Exchange 2k3 SP2 install
I have a feeling my issue could be the result of the combination of Exchange Server 2003 SP2 install and Windows Server 2003 SP1 install on the same day, but still I'm not sure why this would happen. The server itself still runs as normal - slow, but it's been that way since day 1. The workstations however logged on within a few seconds yesterday, but today they are taking almost a minute, sometimes more. The machines themselves are relatively old, but not too old for the OS. My hub is only 10MBps but again, they were much faster yesterday. I have 8 workstations connected to my ...

Need to be able to view BOMs for buy items
We have subassemblies that we have designed which go into our primary finished good. We don't make them as they involve processes which we cannot perform in-house. We do own the subassembly and as such need to keep drawings and specs for the subassembly. The easiest way to track the setup of the subassembly is to maintain a BOM. However it is important that the item be treated as a buy item for production purposes. The costs need to reflect the costs we must pay to the vendor for production. As such I had previously defined this item as a Make item so that I could enter the BOM. Now...

retrieving connection string w/ ConfigurationManager
I am trying to store and retrieve connection string info from config file and I am having trouble retrieving the connection string info. I stored the info with the following: Configuration config = null; config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None ); config.ConnectionStrings.ConnectionStrings.Add( new ConnectionStringSettings( "MyConfigurationString", "Server=local;Database=Test;Password=password;User Id=john;" ) ); config.Save(); I then remove the Add method and use the following try to retrie...

need to create view to see last weeks documents by created date
I need to create a standard view so I can see all the documents uploaded within last two weeks or month or one week You should be able to do this using the Created column I would think. -- Daniel A. Galant Imagine what we could be... if we could just imagine. "Imtiaz Bukhari" <Imtiaz Bukhari@discussions.microsoft.com> wrote in message news:FD684759-BB73-488D-B9FC-9113B9711410@microsoft.com... > I need to create a standard view so I can see all the documents uploaded > within last two weeks or month or one week In article <FD684759-BB73-488D-B9FC...

Ability to choose default setting for "Include Purchase orders"
I would like to see the ability to choose the default setting for the "Include Purchase Orders" option that is found in the "Post Closed Batches" window. To get to this window, click Journal, click Post Close Batches. Not everyone posts closed batches to QuickBooks and this is an extra step that could be avoided if we were allowed to choose the default for this setting. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the mess...

Running a select from the immediate window
I want to create and test my queries in the immediate window before adding them to my subs. In the immediate window i was able to create a table. DoCmd.RunSQL "create table test ([ff1] text (5));" I appended records using the same syntax DoCmd.RunSQL "..." Now i want to query out all the records. DoCmd.RunSQL "SELECT * from test;" Does not work. What is the correct syntax to run a select in the immediate window? thanks -- Message posted via http://www.accessmonster.com You can't. DoCmd.RunSQL only works with action queries: Ins...

Not Authorized to view this page
Hi Guys, I have recently installed CRM 1.2 onto a clients server running sbs2003. Since I needed sharepoint to run, I moved sharepoint to a different website. I created a new CRM website and installed it. It worked corectly. I have now had to change the ip address of the server and websites. I can log onto sharepoint without a problem, but CRM keeps giving me a 'You are not authorized to view this page' error. I also cannot log into workflow manager, 'I am told that the server localhost is not responding.' Can anyone give me a solution to solve this? Please real...

use same cell w/in a function in a entire column.
I want to be able to create a entire column in which every cell in the column pulls a data point from the same cell. If I high light and paste from my initial, lets say B1 to b2 through b100. It changes the A1 data in each cell to the next to a2 a3 a4 a5 a6 a7 a8 ect.. i know this is simple but thanks anyway. Todd Put a $ in front of the row reference (ex: a$1 instead of a1) to keep the row from advancing as you fill down. (Similarly you could put a $ in from of the column reference to keep the column from advancing as you fill right). Check Excel's help on 'cell and range re...

Run-time Error 7
Hi: Every time that I open Microsoft Excel 2002 (opening a blank spreadsheet), I get a Visual Basic dialog box that says "Run-Time Error 7". It does not seem to be a problem, really. I just press the "End" button in this VBA dialogue box and Excel opens just fine. It's more of a nuisance than a problem. Does anyone know how to get rid of this box so that it doesn't pop-up? Thanks! childofthe1980s Hi There's a poorly written addin residing either in Excel's startup folder (see Tools > Options for location) or installed (See Tools > Addin for...

Strange behavior of the alphabetic sort order
An article in the KB (#168234 XL97: Hyphens and Apostrophes Are Sorted After Alphabetic Letters ) explains very well how the sort should work in the last versions of Excel. But it sounds like if Excel's sorting behaves differently, which get me in trubbles: Here are 3 lists A, B and C all sorted by Excel (every elements are separated by a comma for reading purpose).The third list does not work as expected since the hyphen character is not ignored at all. Does anybody have an explanation ? List A:1,-,+,a List B:11,1-,1+,1a List C:111,1+1,1-1,1a1 The Excel 2003 help file adds a li...

Unable to view my movies in Media Center
I have ripped movies into Media Center. But now, can not access through Media Center. I can only view them by going to the drive they are stored on. However, all files are now TS files, so cannot view movies with Media Center only Nero. When I select open with Media Center files do not covert and movie will not play with Media Center. I got message prior to this error my computer had updates and to install. I installed them, which caused errors. Subsequently removed updates, and everythig works fine except the errror with my movies. Any ideas Try 'VideoReDo' :- ...

Advanced Find or a View Question
Is there a field to find out the current user logged in from the Accounts itself. The owner is the owner who owns the account. I am trying to find the current user logged in so I can give a filtered view based on it. Thanks Hi: Michael has posted a great example of how this can be done here: http://www.stunnware.com/crm2/topic.aspx?id=AJAX1 -- John. Microsoft Principal Architect This message sent from Windows Vista! <srini.venkatesan@gmail.com> wrote in message news:1161904149.549945.253590@m73g2000cwd.googlegroups.com... > Is there a field to find out the current user logged...

some records fonts view by red color
Hi Dears I have form, there are many records, I need some records fonts view by red color. thanks Take a look at conditional formatting. SAMI wrote: >Hi Dears > >I have form, there are many records, > >I need some records fonts view by red color. > >thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200704/1 StrayBullet wrote: >Take a look at conditional formatting. > >>Hi Dears >> >[quoted text clipped - 3 lines] >> >>thanks Hi StrayBullet Not by focus I need allwy...

How do I view today's date line on Gantt Chart?
I want to see a date line running vertically on the Gantt chart. This date line should keep track of today's date and keep on moving as per the date when the project plan is opened/viewed. Hi, Curious, by default it is there. If it isn't: Format Gridlines Select Current Date and choose a non-blank presentation. HTH -- Jan De Messemaeker Microsoft Project Most Valuable Professional +32 495 300 620 For availability check: http://users.online.be/prom-ade/Calendar.pdf "Suraps" <Suraps@discussions.microsoft.com> wrote in message news:BEEDCE87-E...

saved publisher file opened with incorrect page order
I created book pages with two-page spread master pages and separate master page for first page. I flowed text, added objects and pictures. I saved often over 3 days. I now have 38 pages completed. When I opened the document today, the pages were out of order. (page 13 appears as page one (single page); other pages are not all in order. I have tried: inserting new blank pages, moving pages, etc. but am stymied. I have not resaved the document today so that I am left with document I first opened today. Any suggestions would be helpful. -- Ndlc Have you tried re-setting the public...

Saving a sort in a custom view
I'm trying to save a particular sort in a custom view and it's not working. Does anyone know of a limitation when saving custom views? Maybe sorting is not something that can be saved as part of a custom view. Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ From XL Help (custom views): > What can be stored in custom views? The stored settings include column > widths, display options, window size and position on the screen, window > splits...

No sig file w/o Word 2003? What to do?
Based on searching the forum I found one of the helpful experts mentio that to attach the right signature file to OL 2003 messages, you mus have Tools > Option > Mail Format "Use Microsoft Office Word 2003 t edit e-mail messages" checked. Without that checked, the signatur files for my "default" account will be used for all account. OK, I'm not going to comment on that "feature" :mad: But, here's the situ: No MS Word 2003 - I use WS Word 2000, I bought OL 03 separately No need for a signature in my default (home) account A strong need for a sig...

AD Properties and Outlook Views.
Hi, We have an issue whereby all our user details are hidden in Exchange using the user template in Exchange System Manager. This works fine for hiding private information when viewing user properties in Outlook however if someone right clicks on the user and selects "add to contacts" all this information is pulled into the contact form. Does anyone know where this can be locked down or do we need to secure the actual attributes in AD? Thanks for any help in advance. Cheers Leon Hi Leon, I have the exact same problem here. Did you find a solution ? "LeonG" wrote: >...

How can you arrange, multiple mailboxes on the folder list view?
Do you know how to arrange the appearance of multiple mailboxes on the folder list? If so please let me know, thanks, this is for Outlook 2002 I don't think it's possible -- the Folder List is always arranged alphabetically. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:1c79801c42238$e9f4e430$a301280a@phx.gbl, Sean wrote: > Do you know how to arrange the appearance of multiple > mailboxes on the folder list? If so please let me know, > th...

How to set default views to Hotmail Calendar?
Hi everyone, I have Outlook Live subscription (Outlook Connector with Outlook 2003). Every time I click "Calendar" in Outlook it takes me to the local "Calendar" folder. Same with "Contacts". Then I have to uncheck the local Calendar and check Hotmail Calendar to view. If I restart Outlook, I will have to do it again. How do I configure Outlook to automatically view Hotmail Calendar? Thanks! pkoli...@hotmail.com wrote: > Hi everyone, > > I have Outlook Live subscription (Outlook Connector with Outlook 2003). > > Every time I click "Calen...

Restrict Free/Busy View
I have a couple of executives at my company that don't want people knowing their availability. They want the ability of others to see their "free/busy" information blocked. I can't seem to find any documentation or instructions on how to do this. Does anyone have any ideas? I would appreciate any assistance on this. Thanks in advance. On Wed, 10 Aug 2005 12:39:38 -1000, Tech LA <dusty@scservicesla.com> wrote: > I have a couple of executives at my company that don't want people > knowing > their availability. They want the ability of others to see...