View in queries

  • I have the following query i want to run:

    select CASE WHEN Invoice_FK IS NOT NULL THEN 'Invoice ' + ' Invoice_ID:' + CONVERT(VARCHAR,INvoice_fk) END

      , Amount as Amount

      , st.add_datetime as [DateCreated]

    from vw_site_transaction st

    where site_fk in (select site_id from tblSite where cust_fk = 104536)

    The view contains approximately 1010020 rows and is a union of several queries. In this example the sub query returns one value. This query takes along time to execute (>1-2mins). Site_fk is index in all tables involved in the view and on tblSite. The execution plan shows that the query is scanning the whole index (pk index) of one the tables in the view instead of scanning the site_fk index and then doing a bookmark lookup. But when I run the following query it completes in about 1 second.

    select null as Cust_FK

      ,CASE WHEN Invoice_FK IS NOT NULL THEN 'Invoice ' + ' Invoice_ID:' + CONVERT(VARCHAR,INvoice_fk) END

       

      , Amount as Amount

      , st.add_datetime as [DateCreated]

    from vw_site_transaction st

    where site_fk in (select 35357 from tblSite where cust_fk = 104536)

    There is a different execution plan for these two queries. Is there a way to to force the query to use the second execution plan or is there any other way to to speed up the execution. I've tried index and table hints but it doesn't seem to make any difference.

  • I think the difference is SQL Server is smart enough to know you have a literal in your IN clause.    My suggestion is to avoid the IN clause and to join the tblSite to the vw_site_transaction view and then just limit the records to cust_fk = 104536.  The IN clause is not the fastest operator in SQL's bag.

     

    c

    If the phone doesn't ring...It's me.

  • I tried it that way to begin with but it gave me the same problem. I have tired the traditional join method where the join is specifed in the where clause and the INNER JOIN approach. It make no difference.

    select CASE WHEN Invoice_FK IS NOT NULL THEN 'Invoice ' + ' Invoice_ID:' + CONVERT(VARCHAR,INvoice_fk) END

      , Amount as Amount

      , st.add_datetime as [DateCreated]

    from vw_site_transaction st INNER JOIN (select site_id from tblSite where cust_fk = 104536) s

     on st.site_fk = s.site_id

  • As sugegsted try a join, or try replacing the IN with an EXISTS:

    select CASE WHEN Invoice_FK IS NOT NULL THEN 'Invoice ' + ' Invoice_ID:' + CONVERT(VARCHAR,INvoice_fk) END

      , Amount as Amount

      , st.add_datetime as [DateCreated]

    from vw_site_transaction st

    where exists (

      Select *

      from tblSite As s

      where s.site_id = st.site_fk and

      cust_fk = 104536

    )

  • Thanks, but still no joy.

    I could dynamically create the SQL statement by creating a cursor from,  Select *

      from tblSite where cust_fk = 104536 and then created the IN clause.

    This seems like cheating to me but if no one has any ideas then i will have to do it that way.

  • Why are you using an subquery?

    select CASE WHEN Invoice_FK IS NOT NULL THEN 'Invoice ' + ' Invoice_ID:' + CONVERT(VARCHAR,INvoice_fk) END

      , Amount as Amount

      , st.add_datetime as [DateCreated]

    from vw_site_transaction st INNER JOIN (select site_id from tblSite where cust_fk = 104536) s

     on st.site_fk = s.site_id

    Try:

    select CASE WHEN Invoice_FK IS NOT NULL THEN 'Invoice ' + ' Invoice_ID:' + CONVERT(VARCHAR,INvoice_fk) END

      , Amount as Amount

      , st.add_datetime as [DateCreated]

    from vw_site_transaction st INNER JOIN tblSite on vw_site_transaction.site_id = tblsite.site_id where cust_fk = 104536

     

    If the phone doesn't ring...It's me.

  • Thats what i tried original and the reason im was using a subquery is because i want SQL to execute the subquery first so that it doesn't scan the whole of vw_site_transcation. Im not sure if SQL does this but I thought i might as well give it a whirl.

  • My suggestion then is to add tblSite to the view in question and see what the execution plan is.  You may also want to make it a stored procedure that accepts the cust_fk as a parameter then have the SQL limited to that parameter.  SQL should be able to precompile it properly.

    If the phone doesn't ring...It's me.

  • I''m sad to say i've given up using a normal query and have had to resort to dynamically created the sql statment. This returns the resultset in an instant. 

    DECLARE @Cust_ID INT

    Set @Cust_id = 138553

    SET CONCAT_NULL_YIELDS_NULL OFF

    DECLARE @SQL VARCHAR(2000)

    DECLARE @INClause VARCHAR(200)

    DECLARE @Site_ID INT

    --BUILD THE SELECT STATEMENT

    SET @SQL = 'select CASE WHEN Invoice_FK IS NOT NULL THEN ' + '''' + 'INvoice ' + ''''

     + ' + ' + '''' + 'Invoice_ID:' + '''' + ' + CONVERT(VARCHAR,INvoice_fk) END

        , Amount as Amount

        , st.add_datetime as [DateCreated]

      from vw_site_transaction st

      where site_fk in ('

    --BUILD THE IN CLAUSE

     DECLARE c_Sites CURSOR FOR

     SELECT Site_ID

     FROM tblSite

     WHERE Cust_fk = @Cust_id

    OPEN c_Sites

    FETCH NEXT FROM c_Sites INTO @Site_ID

    WHILE @@FETCH_STATUS = 0

    BEGIN

     SET @INClause = @INClause + CONVERT(Varchar,@Site_id) + ','

      FETCH NEXT FROM c_Sites INTO @Site_ID

    END

    CLOSE c_Sites

    DEALLOCATE c_Sites

    --REMOVE TRAILING COMMA

     SET @INClause = LEFT(@INClause,LEN(@INClause)-1) + ')'

    --COMPLETE THE SQL STATEMENT

     SET @SQL = @SQL  + @INClause

    execute (@sql)

  • Hello,

    I still think it should be possible to do it without dynamic SQL, but have no other ideas than those already presented... However, if dynamic SQL will be your solution, you could avoid at least the cursor. Hopefully I didn't make any typo in the SQL, I don't have data to test it on - but we use similar statements on our tables, so it should be OK.

    --BUILD THE IN CLAUSE

    SET @INClause = ''

    SELECT @INClause = @INClause + CONVERT(Varchar,Site_id) + ','

    FROM tblSite

    WHERE Cust_fk = @Cust_id

    --REMOVE TRAILING COMMA

     SET @INClause = LEFT(@INClause,LEN(@INClause)-1) + ')'

    HTH, Vladan

  • I have just experience the exact same problem and came up with the same solution. But I don't think that it will be a viable solution for us... just a short term patch to a big time problem.

    The strangest thing is that it seemed all the queries with this problem seemed to work not 2 days ago.

    Now they are lagging indefinitely.

    My view table contains ~ 10 million rows.

    My lookup table contains ~ 1 million, but the restrictions quickly pair it down to < 10 rows.

    I've tried forcing with an INNER LOOP JOIN to the view but it is inconsistent.

    I don't know much about WA_sys indexes, but maybe they might help. Anybody with further input?

    thanks,

    -jmr

  • I eventually had to abandon the dynamic sql because we are using ADO recordsets and they don't like it when dynamic SQL or temp tables are used.

    Instead a create a new view that joined each union query in the view to the site table. This seemd to sort it out. However, if the business rules change i have to remember to change both views rather than just the one.

  • My view doesn't actually contain any UNION clauses.

    It has 20 tables.

    All With JOINS OR LEFT JOINS. A few of the tables are large, most are small lookups. I feel that it should just do loop joins down the line until it gets the final recordset. (Due to a missing field it does do one book mark look up) but the end resultset is so small maybe < 10 rows  that a bookmark lookup is not an issue.)

    Not sure if I can effectively split it up since the key that I woul have to include in each separate view is on the biggest table anyways.

     

    I'll have to fool with it for a while.

    Thanks for the reponse.. any other ideas still welcome.

    -jmr

  • Did you try to index the view itself?

    Check "Indexed view" in BOL. It might give you significant performance gains. A lot of conditions apply to create indexed views, but they say the result worths paying...

    Gabriela

  • The original point of the view was to encapsulate business logic, and as a result, it includes some left joins and other properties that don't allow for an indexed view.

    I suppose that I'll have to break it out into a number of smaller views to take gain performance.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply