SQL Server Performance

Complex Queries

Discussion in 'General Developer Questions' started by vsnreddi, Dec 7, 2005.

  1. vsnreddi New Member

    Hi..
    Is there any way to re-write this query. The query is correct one, but i am not able to get the data faster.
    I am combining the same qry with other qry(with different tables) using union.


    select 'Description' LineDesc,
    li.bldivi Division,
    li.blfaci Facility,
    li.blagnb AgreementNo,
    li.blponr,
    agh.bhagcn CustNo,
    agh.bhacnm CustName,
    agh.bhaotp house,
    dwr.bmarcs Reason,
    dwdesc.cttx40 DownReason,
    dwli.stpnca Value,
    dwli.stfvdt FromDate,
    dwli.stlvdt ToDate,
    fa.cffacn Name,
    hl.stivno No,
    hl.stivdt InvDate,
    xt.tltx60 Text
    From
    stagli li,
    staghe agh,
    staghd dwli,
    stagdn dwr,
    csytab dwdesc,
    cfacil fa,
    staghl hl,
    ssytxl xt,
    (select stagnb, stponr, max(strgdt + strgtm) datetime from staghl
    group by stagnb, stponr) iv
    Where li.blcono = agh.bhcono
    and li.blagnb = agh.bhagnb
    and li.bldivi = agh.bhdivi
    and li.blfaci = agh.bhfaci
    and agh.bhcono = 1
    and dwli.stitds = 'down period'
    and dwli.stcono = li.blcono
    and dwli.stdivi = li.bldivi
    and dwli.stfaci = li.blfaci
    and dwli.stagnb = li.blagnb
    and dwli.stponr = li.blponr
    and dwr.bmcono = li.blcono
    and dwr.bmdivi = li.bldivi
    and dwr.bmfaci = li.blfaci
    and dwr.bmagnb = li.blagnb
    and dwr.bmponr = li.blponr
    and dwdesc.ctstco = 'ARCC'
    and dwdesc.ctstky = dwr.bmarcs
    and dwdesc.ctcono = agh.bhcono
    and fa.cfcono = agh.bhcono
    and fa.cfdivi = agh.bhdivi
    and fa.cffaci = agh.bhfaci
    and hl.stcono = li.blcono
    and hl.stagnb = li.blagnb
    and hl.stponr = li.blponr
    and (hl.strgdt+hl.strgtm) = iv.datetime
    and hl.stagnb = iv.stagnb
    and hl.stponr = iv.stponr
    and xt.tlcono = li.blcono
    and xt.tltxid = li.blpotx
    and xt.tllino = 1
    and li.blcono = 1




    SURYA

    Be pure to do good.[Vivekananda]
  2. Adriaan New Member

    Use JOIN syntax. Check if the fields that you're joining on, and fields that you filter in, have adequate indexes.

    Your derived table should be a correlated subquery in the WHERE part.
  3. vsnreddi New Member

    I have used join syntax only. All the field joining condition correct, indexes also there. The resulted output are approved one.

    SURYA

    Be pure to do good.[Vivekananda]
  4. Adriaan New Member

    No, your query doesn't have JOIN syntax: you don't actually use the keyword JOIN with an ON clause. Keep up with the times, they are a-changing: no more joining in the WHERE clause please.

    And I would still recommend changing the derived table into a correlated subquery in the WHERE clause.
  5. vsnreddi New Member

    You mean to say like this

    inner join stagli on li.blcono = agh.bhcono and li.blagnb = agh.bhagnb and li.bldivi = agh.bhdivi and li.blfaci = agh.bhfaci
    inner join staghd on dwli.stcono = li.blcono and dwli.stdivi = li.bldivi and dwli.stfaci = li.blfaci and dwli.stagnb = li.blagnb and dwli.stponr = li.blponr
    inner join stagdn on dwr.bmcono = li.blcono and dwr.bmdivi = li.bldivi and dwr.bmfaci = li.blfaci and dwr.bmagnb = li.blagnb and dwr.bmponr = li.blponr



    SURYA

    Be pure to do good.[Vivekananda]
  6. Adriaan New Member

    Could you rewrite that as a complete FROM clause with the aliases in place?

    Also, run the query in Query Analyzer to see if SQL can make heads-and-tails of it.
  7. dineshasanka Moderator

  8. ranjitjain New Member

    Look for the execution plans and check which indexes are being used and if you find any Scan then modify the data access method by modifying where clause to have Index Seek.
    Also as suggested by adriaan, have indexes on joining columns.
  9. vsnreddi New Member

    I have done the same, the performance the qry is increases little bit.

    ------------------------------------------

    quote:Originally posted by Adriaan

    Could you rewrite that as a complete FROM clause with the aliases in place?

    Also, run the query in Query Analyzer to see if SQL can make heads-and-tails of it.

    SURYA

    Be pure to do good.[Vivekananda]
  10. vsnreddi New Member

    quote:Originally posted by ranjitjain

    Look for the execution plans and check which indexes are being used and if you find any Scan then modify the data access method by modifying where clause to have Index Seek.
    Also as suggested by adriaan, have indexes on joining columns.
    -------
    I have cheked execution plans and indexes on joining columns. the qry is ok with that.

    The possible reasons are, the data is more(crores) in all tables and joining more tables.
    executing one more qry using union command.

    Can i insert the data in temp tables ?

    SURYA

    Be pure to do good.[Vivekananda]
  11. ranjitjain New Member

    If you are using UnION in your select then you can get further imrovement by replacing it with UION ALL as it returns result much faster as it excludes the check for duplicates.
    Use UION ALL only if you are sure and dont want to display any duplicates.

    You can even try using temp tables. But if the data in table is huge and you are just working with piece of data then only go for it.
    Else you will have to again create index on temp table.
    But if the rows which you are going to insert in temp table is less then you can even check for table variables as it offers more performance than temp tables.

    You can experiment with all of this.
    Run your query by adding this at top and check in messages pane to find the read count whether any improvement or not

    SET STATISTICS IO ON
  12. surendrakalekar New Member

    Inserting into temp table will not increase the performance.
    Try to use subquery for those tables which is having less records.
    or
    You can try the below

    (select fa.cffacn Name from cfacil fa With (NOLOCK) where ........)
    (Select tltx60 Text from ssytxl xt With (NOLOCK) where .....)
    --Try to use subquery for staghl table as well
    use " With (Nolock) " if you have 10 millions of rows



    Surendra Kalekar

  13. vsnreddi New Member

    quote:Originally posted by surendrakalekar

    Inserting into temp table will not increase the performance.
    Try to use subquery for those tables which is having less records.
    or
    You can try the below

    (select fa.cffacn Name from cfacil fa With (NOLOCK) where ........)
    (Select tltx60 Text from ssytxl xt With (NOLOCK) where .....)
    --Try to use subquery for staghl table as well
    use " With (Nolock) " if you have 10 millions of rows



    Surendra Kalekar

    ----------------------
    i wll try this and let u know..

    SURYA

    Be pure to do good.[Vivekananda]
  14. vsnreddi New Member

    Hi Ranjit,
    I tried all with this, Now the Qry returning the output faster compartive earlier.
    ->Stored the required information in hash table.(minimum req data in large table)
    ->joining the hash table with other ones.
    ->Used union all , the data is getting without duplicates, but have to chk future data.

    I am trying to get the otherway what Surendra Kalekar suggested...




    SURYA



  15. ranjitjain New Member

    You can also read about using table variable which is more faster than creating and dropping and using #temptables as for table type if data is very less than SQL holds it in memory and does the processing
  16. Adriaan New Member

    quote:Originally posted by ranjitjain

    You can also read about using table variable which is more faster than creating and dropping and using #temptables as for table type if data is very less than SQL holds it in memory and does the processing
    Depends on the amount of data you will be putting into the table variable. And don't forget that you can add indexes to temp tables, not to table variables ...
  17. vsnreddi New Member

    Thanks you all.

    I have prepared set of queries and sending for approval.Getting the data faster, after posting the qry..

    Included all possible conditions what we have discussed...

    01. using temp tables (hash tables),indexes on hash tables,
    02. using subqueries for those tables which is having less records.

    Once again Thanks to Adriaan,dineshasanka,Ranjit jain,Surendra Kalekar





    SURYA



  18. ranjitjain New Member

    Good to know that after discussing your problem here,<br />You finally made to have fast running Procedure.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />
  19. surendrakalekar New Member

    Hi Surya,
    Can you post your modified code?


    Surendra Kalekar

  20. vsnreddi New Member

    Once it is approved i wll post it.
    Thanks..


    quote:Originally posted by surendrakalekar

    Hi Surya,
    Can you post your modified code?


    Surendra Kalekar




    SURYA



  21. Madhivanan Moderator

    >>And don't forget that you can add indexes to temp tables, not to table variables ...

    I hope you meant this

    Alter table @t............

    Madhivanan

    Failing to plan is Planning to fail
  22. Adriaan New Member

    quote:Originally posted by Madhivanan

    >>And don't forget that you can add indexes to temp tables, not to table variables ...

    I hope you meant this

    Alter table @t............

    Madhivanan

    Failing to plan is Planning to fail
    No, I meant this:

    CREATE TABLE #TMP (Col1 INT)
    CREATE CLUSTERED INDEX ixTMP ON #TMP (Col1)

    You can do anything with a temporary table that you can also do with regular user table. You can have a PK, indexes and (default) constraints. I tried triggers, but they're not allowed on temporary objects. Haven't tried relationships - probably not allowed either, perhaps FK is supported. Does anyone know?

    If you try creating an index on a table variable, you get a syntax error - like this:

    DECLARE @TMP TABLE (Col1 INT)
    CREATE CLUSTERED INDEX ixTMP ON @TMP (Col1)
    ---
    Server: Msg 170, Level 15, State 1, Line 2
    Line 2: Incorrect syntax near '@TMP'.
  23. Madhivanan Moderator

    Well. If you define a Primary key then there is change that it can use Clustered Index<br /><br />DECLARE @TMP TABLE (Col1 INT primary key Clustered)<br /><br />The major difficulty on Table variable is that you cannt use Alter Statement [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  24. Adriaan New Member

    Thanks for pointing out the PK option in the table variable. YOu can add DEFAULT constraints on columns as well, like this:
    DECLARE @TMP TABLE (Col1 INT PRIMARY KEY CLUSTERED, Col2 VARCHAR(10) DEFAULT(''))

    Another point of interest ... A table variable will be created as a temporary table, if SQL Server estimates that the amount of data to be inserted into the table will exceed a certain threshold.
  25. vsnreddi New Member

    Thanks madhivanan
    i did samething only.


    quote:Originally posted by Madhivanan

    >>And don't forget that you can add indexes to temp tables, not to table variables ...

    I hope you meant this

    Alter table @t............

    Madhivanan

    Failing to plan is Planning to fail



    SURYA



  26. ranjitjain New Member

    Good point Madhivanan,
    I never tried it and never thought of it but really a good one to keep that in my mind for future.

Share This Page