SQL Server Performance

Query taking more time - urgent

Discussion in 'T-SQL Performance Tuning for Developers' started by ramesh, Feb 11, 2004.

  1. ramesh New Member

    Hi,<br /><br />One of my query(below) is taking 17 seconds. Can anyone suggest me how can modify the query which can reduce the execution time.<br /><br />SELECT isum_lecid,<br />left(idet_lecid,<img src='/community/emoticons/emotion-11.gif' alt='8)' />,<br />substring(idet_lecid,9,10),<br />substring(idet_lecid,19,2),<br />substring(idet_lecid,21,2),<br />idet_statusflg<br />FROM<br />tbllecissuedetail, <br />tbllecissuesummary <br />WHERE<br />idet_currentownerid=1 AND <br />upper(idet_transferrequestflg) ='O' AND<br />upper(Idet_RedeemedFlg) = 'N' AND<br />idet_issueid=isum_lecid<br />ORDER BY<br />tbllecissuedetail.idet_seqid,<br />tbllecissuedetail.idet_lecid<br /><br />idet_lecid is say L000ENGS0000000010104<br /><br />Regards,<br />Ramesh
  2. Luis Martin Moderator

    I can't help with code, but did you update statistics or defragment recently?


    Luis Martin
    Moderator
    SQL-Server-Performance.com
  3. ramesh New Member

    Martin,

    I don't have much idea on SQL Server administration part. I am not sure about the updating statistics or defragment.

    Can you help me out to know what these are and what should i do now?

    Cheers
    Ramesh
  4. Luis Martin Moderator

    To mantein performance along the time, you should deframent indexs and update statistics.
    I suggest to create a job, on non working times, to update statistics every day.
    To do that, use Enterprise Manager, Managment, SQL server agent, jobs, in tool bar you can find a Wizard (litte magic stick) click and select Managment, Data Base Mainteinance Plan Wizard, follow instructions, choose database, ckeck Update Statistics used by Query Optimizer, live 10% as default, schedule to time you want to run,( Remember I suggest every day), go on with instruccions, in my case I don't backup in same job.

    Other suggestion is to create same job, once a week (ie, on sunday) changing 10% to 100% this job will be longer than daily job, but update statistics full.

    With defragmentation I suggest to see in this forum (Contribute Scripts) and you will find a store procedure to degrag indexs.
    With this store procedure you can create a new job to run once a week to keep all index without fragmentation.

    Hope That Help.


    Luis Martin
    Moderator
    SQL-Server-Performance.com
  5. ramesh New Member

    Thanks Martin.

    Let me check and come back.

    Regards,
    Ramesh
  6. gaurav_bindlish New Member

    Is the join on column idet_issueid=isum_lecid?

    Looking at the exeution plan may help....

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  7. bambola New Member

    what about indexes on these tables? you should at least have them on the columns you join and order by...

  8. ramesh New Member

    I have clustered indexes on both the tables as

    idet_lecid on tblLECIssueDetail and
    isum_Lecid on tblLECIssueSummary

    Ramesh
  9. bambola New Member

    idet_issueid = isum_lecid

    are this the column you join? if so, you would (at least) need to add an index on idet_issueid and another one on [idet_seqid, idet_lecid]

  10. ramesh New Member

    I think we can create only ONE clustered index for a table.

    As mentioned already,

    idet_lecid on tblLECIssueDetail and
    isum_Lecid on tblLECIssueSummary

    are the primary keys of the tables and also selected as the clustered indexes.

    Ramesh
  11. pavankan New Member

    But you can create a non-clustered index



    quote:Originally posted by ramesh

    I think we can create only ONE clustered index for a table.

    As mentioned already,

    idet_lecid on tblLECIssueDetail and
    isum_Lecid on tblLECIssueSummary

    are the primary keys of the tables and also selected as the clustered indexes.

    Ramesh
  12. pavankan New Member

    You can use the EM to create and run 'Maintenance plans'

    Right click on the db in EM. All tasks > Maintenance plan
    It brings up an easy to use wizard. You can set up 'update statistics' and rebuild indexes among other things that it can do.





    quote:Originally posted by LuisMartin

    To mantein performance along the time, you should deframent indexs and update statistics.
    I suggest to create a job, on non working times, to update statistics every day.
    To do that, use Enterprise Manager, Managment, SQL server agent, jobs, in tool bar you can find a Wizard (litte magic stick) click and select Managment, Data Base Mainteinance Plan Wizard, follow instructions, choose database, ckeck Update Statistics used by Query Optimizer, live 10% as default, schedule to time you want to run,( Remember I suggest every day), go on with instruccions, in my case I don't backup in same job.

    Other suggestion is to create same job, once a week (ie, on sunday) changing 10% to 100% this job will be longer than daily job, but update statistics full.

    With defragmentation I suggest to see in this forum (Contribute Scripts) and you will find a store procedure to degrag indexs.
    With this store procedure you can create a new job to run once a week to keep all index without fragmentation.

    Hope That Help.


    Luis Martin
    Moderator
    SQL-Server-Performance.com
  13. ChrisFretwell New Member

    I would look at adding a non-clustered index to your join fields. If in doubt, run your query through the index tuning wizard. It will likely suggest the same thing.
    And if you can, rewrite using the ansi join syntax so

    FROM
    tbllecissuedetail join tbllecissuesummary on idet_issueid=isum_lecid
    WHERE
    idet_currentownerid=1 AND
    upper(idet_transferrequestflg) ='O' AND
    upper(Idet_RedeemedFlg) = 'N'

    It makes it easier to see what fields would benefit from an index.

    If your collation isn't case sensitive, then you dont need to upper case the other fields in your where clause. Its a minor thing that can help performance.

    Chris
  14. ramesh New Member

    Thanks All for your suggestions.

    I have made the following changes.
    I have created a job using Database Maintenance Plan wizard and from Optimizations option, i have selected the Reorganize data and Indexes and ran the job.

    After running the job, the queries started to run fast.

    Regards,
    Ramesh
  15. frankwxn New Member

    upper(idet_transferrequestflg) ='O' AND
    upper(Idet_RedeemedFlg) = 'N' AND

    this upper function slows your query down, my sql server is not case sensitive, so I do not use upper function
    But if you need to use it
    try this one

    ( idet_transferrequestflg ='O' or idet_transferrequestflg ='o' ) And
    ( Idet_RedeemedFlg = 'N' AND Idet_RedeemedFlg = 'n' )
  16. ramesh New Member

    Frankwxn,

    i have performed the test on the production server, there is no difference in the execution time even after the query modification that you have suggested. Hope this information is useful.

    Ramesh
  17. frankwxn New Member

    sorry I typed wrong ,
    ( idet_transferrequestflg ='O' or idet_transferrequestflg ='o' ) And
    ( Idet_RedeemedFlg = 'N' or Idet_RedeemedFlg = 'n' )

    did you compare the execution plan ,

    execution time maybe the same, but the execution plan may be different , because the Upper function will cause the sql query not going to use the index on RedeemedFlg , or idet_transferrequestflg .

    But I am not exactly sure.

Share This Page