Hi Jan, As mentioned a few days ago, Dynamics Ax 4.0 SP-1 uses placeholders a lot more then Dynamics Ax 3.0 (also in forms). Espessially when interacting with transaction tables it is important to get the right accessplan - therefore your should try to force use of literals. This can easilly be tested in a small job :)
Best regards, Karsten Wollesen, thy: data Dynamics Ax Technology courses e: [EMAIL PROTECTED] w: http://www.thydata.dk/services ▪ http://www.x-masterclass.com --- In [email protected], "Dahlsgaard Jan" <[EMAIL PROTECTED]> wrote: > > I'm afraid there's not a lot of news. One of the statements we're having problems with is this: > SELECT <complete fieldlist> > FROM VENDTRANS A > WHERE (DATAAREAID='esb') > ORDER BY A.DATAAREAID DESC,A.ACCOUNTNUM DESC,A.TRANSDATE DESC > OPTION(FAST 1) > (opening the vendtrans form, approx. 98000 records of 2400000 meets the where-clause) > A cluster index exists on the three fields in the order by. > If I take a look at the executionplans, apparently sqlserver2000 only need this index, and are able to "read backwards" in the index. Sqlserver2005 needs a sort to give me the rows, and the sort takes 2/3's of the time spend. > If I remove the order by, the fast part of the resultset seems to perform pretty much the same, but getting the entire resultset, takes about 50% longer on 2005 than 2000. > My 2 sqlservers are installed on the same win2003 server, and the databases are exactly identical and all indexes recreated and all statistics are updated. > > I'm very disapointed. > > ________________________________ > > Fra: [email protected] [mailto:development- [EMAIL PROTECTED] På vegne af abid husayn > Sendt: 22. marts 2007 11:18 > Til: [email protected] > Emne: RE: [development-axapta] Slow performance with SQLServer2005 > > > > Hi > > How did you get on with your SQL performance issues? > > Regards > > Abid > > mit <[EMAIL PROTECTED] <mailto:mit%40hafnet.dk> > wrote: Hi > > Try to rebuild SQL statistiscs. > > Use Dynamics "long running queries" to determine which plan is used by the > SQL server. > > Regards > > Michael Troelsen > > EG Utility A/S > > _____ > > From: [email protected] <mailto:development- axapta%40yahoogroups.com> > [mailto:[email protected] <mailto:development- axapta%40yahoogroups.com> ] On Behalf Of Dahlsgaard Jan > Sent: 12. marts 2007 12:36 > To: [email protected] <mailto:development-axapta% 40yahoogroups.com> > Subject: [development-axapta] Slow performance with SQLServer2005 > > Hi > > This weekend we tried installing a sqlserver2005 on a new server, and > copied our sqlserver2000 database to this server (using backup/restore). > We have been using Axapta 3.0 KR2 for a while, but we now experience > very poor performance. Some forms used to open in seconds, now takes > several minutes. The tables used on the tested forms have cluster > indexes, so this should not be the issue. The sqlserver2005 seems to be > running fine, with no performance problems, but accessing data from > axapta is extremely slow. Any good ideas as to what is the problem ? > > Regards > Jan S Dahlsgaard > Fujitsu DK > > [Non-text portions of this message have been removed] > > -- > This message has been scanned for viruses and > dangerous content by the <http://www.secpoint.com <http://www.secpoint.com> > SecPoint Protector > > -- > This message has been scanned for viruses and > dangerous content by the SecPoint Protector > for more information please see http://www.secpoint.com <http://www.secpoint.com> > > [Non-text portions of this message have been removed] > > > > > > > --------------------------------- > All New Yahoo! Mail - Tired of unwanted email come-ons? Let our SpamGuard protect you. > > [Non-text portions of this message have been removed] > > > > > > > [Non-text portions of this message have been removed] >
