SQL Server Performance

Queries based on derived tables: slow running

Discussion in 'Getting Started' started by cjp, Aug 28, 2008.

  1. cjp New Member

    In our medical research work, I feequently have to use derived tables in the form of views when I build queries: a typical example (that I used yesterday for teaching purposes) is where I join the demographic table (one row per patient) to the prescriptions table (many rows per patient) with a second join from this to a lookup that contains the drug codes of interest (one row per drug).
    For practical reasons, the demographic, prescription, diagnostic and lab tests databases are held separately (otherwise I would be backing up 150 GB data every time I made a small change to the demographic table). The result of this is that I have to run the query in, say, the demographic database and then build views to anything else I need.
    This kind of query often runs very slowly, even on powerful kit and with efficient indexes. I have been reading that there is an issue in SQLS 2005 that can produce slow running in this kind of query. Apparently, Microsoft have included a fix for this behaviour in their cumulative updates but I also understand that applying the fixes from the cumulative updates can produce problems. I'm a little out of my depth here since I don't fully understand the language Microsoft use.
    Can anyone advise me about this? I want Microsoft to issue SP 3 but, failing this, I need to know whether it is safe to apply the fix for this issue.
  2. preethi Member

    When Microsoft releases a patch to public, I try them on a sandbox. If that works fine to the best of my understanding, I used to check over the internet. If no one complains about it, it is something I can take the risk and work. Ofcourse I will take the precausions of backup and the date of installtion (Next few days are not heavy)to ensure, no one is affected.
  3. moh_hassan20 New Member

    you can enhance view performance by using indexed views
    check: http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx
  4. satya Moderator

    As you are saying that it is fixed on one of the CU package then better to test it for further assesment, as referred by Preeti which is one of the best practice.
    If you see the main problem is due to indexes then run the PROFILER trace to capture and then submit to database tuning advisor for other recommendations.
  5. cjp New Member

    Many thanks to you all. I would like to add some detail - I may be misunderstanding some of what you are saying.
    a) - I have read the link on indexing views and have attempted to make an index but the process fails because (I think) the table to which I have made the view is in a different database. Essentially, I can't use schemabinding if the table I am viewing is in a different database and I can't make an index unless I specify schemabinding...
    I tried making the view and then indexing it after it existed but I ran into the same problems - there seems to be not much one can do if the view is to a table in a different database.
    b) I think that the two articles in the MS CU list that might be relevant are 50002496 and 50002579 - but I don't fully understand what is being said.
    Do these two fixes sound appropriate? Thanks. Chris
  6. Adriaan New Member

    BOL on the CREATE VIEW syntax clearly states "All referenced objects must be in the same database." if you want to use the WITH SCHEMABINDING option.
    You might get away with creating a view in your current db that refers to the table(s) on the remote database, and referring to that in your view, but I'm pretty sure SQL will see through that and pop up the same message of refusal.
    Did you try OPENQUERY or similar?
  7. cjp New Member

    Thanks, Adriaan - yes, I accept your observations - the second approach does, indeed, produce the same difficulty.
    Do you know if SQLS 2005 uses the index on the underlying table if it is in another dbase and I build a view to it?
    I am beginning to think that I will need to hold all the source tables in the same database but this is logistically sub-optimal.
    By the way, what is OPENQUERY?
  8. Adriaan New Member

    Check out Books Online that is part of the standard SQL Server installation - this should always be your first step before posting here.
  9. cjp New Member

    Thanks - I will ensure that I do this in future.
    I have checked out OPENQUERY in BOL and, unfortunately, it doesn't work because (or so it seems) the database I am viewing is on the same server as the database issuing the view command.
    In the end, I can fix this by incorporating all my tables into the same database - this should ensure that all indexes are fully available. It's not ideal from a backup perspective but it is not a major problem (I can think of ways of managing this).
    Many thanks to all who are offering ideas - greatly appreciated.
  10. moh_hassan20 New Member

    [quote user="cjp"]In the end, I can fix this by incorporating all my tables into the same database - this should ensure that all indexes are fully available. It's not ideal from a backup perspective but it is not a major problem (I can think of ways of managing this).[/quote]
    if you copy your tables, you have to truncate and update these tables when data changes.
    why not to use the new enhancement in sql 2005: SYNONYM , instead copying table
    you gain extra advantages:
    - you use the index of the original tables
    - any data update is reflected
    - no performance overhead (as i found)
    use pubs

    FOR AdventureWorks.Production.Product

    CREATE SYNONYM ProductListPriceHistory
    FOR AdventureWorks.Production.ProductListPriceHistory

    select p. ProductID , p.Name , PLH.ListPrice , PLH.ModifiedDate
    from product p
    join ProductListPriceHistory PLH on p.ProductID = PLH.ProductID
    where p.ProductID =707
    try to get execution plan, you will find the indexes in the AdventureWorks are used by optimizer
    SYNONYM was the missing feature , and now it is in hand in sql 2005
  11. cjp New Member

    Many thanks for the suggestion about SYNONYM. I will try this - sounds like a very interesting idea.
  12. satya Moderator

  13. cjp New Member

    Thanks for this idea - very interesting. The method closely resembles that for Common Table Expressions (which I use frequently), so I will experiment. The first of the linked articles suggests that using derived tables results in the query engine trying to run the procedure in memory - I can see the advantages of this. However, when the derived table is too large to fit into memory, there will (presumably) be the need to create a temporary table to take data so as to free up memory.
    Regarding BOL, I do use it...The difficulty is that I have no formal training and do not come from a computing/science/maths background and, therefore, do not always understand the technical terminology. This is why I tend to post rather basic questions here - I'm trying to plug the gaps in my underlying knowledge.
    That said, I take the point!
    Thanks again for all your help.

Share This Page