Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scottpt
Posting Yak Master
186 Posts |
 Posted - 2009-03-18 : 08:42:35
|
| I have a view that is not performing well. The data is correct.The part of the view not working is below. If I comment out "w.Module = 'AP' " it works if I leave it in it take 1100% longer.select * FROM APSetup s (NOLOCK) CROSS JOIN WrkRelease w JOIN APTran t ON w.BatNbr = t.BatNbr JOIN Account a (NOLOCK) ON a.Acct = t.Acct JOIN APDoc d ON d.BatNbr = t.BatNbr AND d.VendId = t.VendId AND d.RefNbr = t.RefNbr AND (d.DocType <> 'VC' OR d.Status <> 'T') JOIN Batch b ON b.BatNbr = t.BatNbr AND b.Module = w.Module WHERE w.Module = 'AP' AND a.SummPost <> 'Y' AND s.GLPostOpt <> 'S' |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-03-18 : 08:53:18
|
Try thisSELECT *FROM APSetup AS s WITH (NOLOCK)INNER JOIN WrkRelease AS w ON w.Module = 'AP'INNER JOIN APTran AS t ON t.BatNbr = w.BatNbrINNER JOIN Account AS a WITH (NOLOCK) ON a.Acct = t.Acct AND a.SummPost <> 'Y' INNER JOIN APDoc AS d ON d.BatNbr = t.BatNbr AND d.VendId = t.VendId AND d.RefNbr = t.RefNbr AND NOT (d.DocType = 'VC' AND d.Status = 'T')INNER JOIN Batch AS b ON b.BatNbr = t.BatNbrWHERE b.Module = w.Module AND s.GLPostOpt <> 'S'
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
pootle_flump
1064 Posts |
Posted - 2009-03-18 : 08:54:34
|
| Is Module indexed? Have you compared the two execution plans? I'm thinking maybe inappropriate lookups but it is impossible to say based on the info.How many rows in APSetup BTW? |
|
|
|
scottpt
Posting Yak Master
186 Posts |
Posted - 2009-03-18 : 09:08:13
|
| *APsetup has 1 row.*Adding a index to WrkRelease had no affect.*Even With WrkRelease having 0 row this query takes 15 minutes to execute. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-03-18 : 09:18:25
|
Hav you tried my suggestion. CROSS JOIN can give wrong result due to bad statistics.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
pootle_flump
1064 Posts |
Posted - 2009-03-18 : 09:19:28
|
quote:
Originally posted by scottpt
*APsetup has 1 row.*Adding a index to WrkRelease had no affect.*Even With WrkRelease having 0 row this query takes 15 minutes to execute.
Ta. I didn't mean add one, I was asking if there was one. I would have been surprised if adding one improved things.Did you get the two execution plans? |
|
|
|
scottpt
Posting Yak Master
186 Posts |
Posted - 2009-03-18 : 09:40:10
|
quote:
Originally posted by Peso
Hav you tried my suggestion. CROSS JOIN can give wrong result due to bad statistics.
E 12°55'05.63"N 56°04'39.26"
Ran that query it still took 7:45 to run. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-03-18 : 10:19:59
|
7 minutes 45 seconds is almost half the time of original 15 minutes.Post a link to your execution plan and we can investige it.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
scottpt
Posting Yak Master
186 Posts |
Posted - 2009-03-18 : 16:28:56
|
| Fixed.I changed WHERE w.Module = 'AP' To WrkRelease w jOIN spt_fIXER xyz ON w.MODULE=XYZ.module Where spt_fixer contains one column MODULE with one row 'AP'. These will return the same data sets. From these results my guess is that the where clause is causing SQL to come up with a inefficient query plan for the view. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-03-18 : 16:32:25
|
Without seeing the executin plans any guess is good.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
Performance issue WTF? (SOLOMON)