November 21, 2013 at 6:25 am
Can any one spot anything in the given below query that can be done to improve the performance of query..... Your urgent response is highly appreciated.
The first query is:
select top 100 jord.ordno, custno, upper(cname) as cname, cphone, pono, odate, jordlikb.sdate,
ordstat, repno, verfno, ostat, upper(proj_name) as proj_name, distno,
assocno, item_status,
jordftpxlate.vendor as vno, jordftpxlate.seq as seq, jordlikb.qty, jordftpxlate.item,
upper(jordftpxlate.vname) as vname, jordftpxlate.cmsordno, sbsvendno, jordlikb.cmsinvdt,
jordftpxlate.sbspono, jordlikb.price
from JORD
left join jordftpxlate on jord.ordno = jordftpxlate.ordno
left join jordlikb on jord.ordno = jordlikb.ordno and jordlikb.seq = 0
left join jordlistat on jord.ordno = jordlistat.ordno and jordlistat.seq = 0 and jordlistat.transno = 10
order by jord.ordno desc
On our server, normally this query will take less than 10 seconds. But during the course of the day, it can take over 1 minute to run.
The four tables that are being affected by this query have the following record counts:
Table NameRecord Count
JORD88,174
JORDFTPXLATE197,172
JORDLIKB147,116
JORDLISTAT1,259,504
The JORD file has the following indexes:
1. Ordno
2. Ordno Descending
3. Distno+Assocno
4. Distno+Assocno+Ordno
It has the following fields
NameTypeLength
OrdnoInt4
CustnoCharacter8
CnameCharacter30
CphoneCharacter14
PonoCharacter30
OdateDate
OrdstatCharacter30
RepnoInt4
VerfnoInt4
OstatCharacter30
Proj_nameCharacter30
DistnoCharacter4
AssocnoCharacter2
…
JORDFTPXLATE
1. ordno+seq
2. sbspono+ordno+seq
JORDFTPXLATE has the following fields.
NameTypeLength
OrdnoInt4
SeqShortInt2
ItemCharacter15
QtyInteger4
VendorInteger4
VnameCharacter30
CMSInvnoCharacter24
CMSOrdnoCharacter24
SBSPonoCharacter12
CMSInvdtDate
JORDLIKB
1. seq
2. cmsordno
3. ordno+seq
4. ordno
5. vpo
JORDLIKB has the following fields
NameTypeLength
OrdnoInteger4
SeqShortInt2
Bean_fmtShortint2
QtyInteger4
ItemCharacter12
ItsubnumCharacter8
IdescCharacter100
AdescCharacter100
…
JORDLISTAT
1. ID
2. SEQ
3. ordno+seq+transno
4. transno
JORDLISTAT has the following fields
NameTypeLength
IdInteger4 (autoinc+unique)
OrdnoInteger4
SeqShortint2
TransnoInteger4
Df1Character30
Df2Character30
Df3Character30
MessageCharacter55
TsTimestamp
ApplrownoInteger4
November 21, 2013 at 6:35 am
Please post table definitions, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2013 at 7:12 am
I have already done that
November 21, 2013 at 7:19 am
I don't see an execution plan, I don't see complete index definitions and I only see part of the table definitions. Please read the article I referenced, trying to help on performance problems with bits of hard-to-read information is not easy. If you need urgent help, then you should give enough information that it's easy to help you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply