SQL Server Performance Forum – Threads Archive

Query taking more time – urgent

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

---

I can’t help with code, but did you update statistics or defragment recently?
Luis Martin
Moderator
SQL-Server-Performance.com

---

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

---

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

---

Thanks Martin. Let me check and come back. Regards,
Ramesh

---

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

---

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

---

I have clustered indexes on both the tables as idet_lecid on tblLECIssueDetail and
isum_Lecid on tblLECIssueSummary Ramesh

---

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]

---

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

---

But you can create a non-clustered index

---

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.

---

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

---

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

---

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’ )

---

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

---

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.

---

]]>