June 20, 2009 at 4:01 am
Hello,
I hope someone can help me. I has been 5 long hours since I started giving a try to optmize this query. But this really isn't one easy task to me.
This is the query:
exec sp_executesql N'
select b.field1, b.field2, c.field3
from view1 a
join table1 b on a.field1=b.field1
join table2 c on a.field1 = c.field1
where a.field4>=@P1 and a.field<=@P2
and b.field2 not in
(select b.field2 from view1 a join table1 b on a.field1=b.field_1
where a.field4>=@P3 and a.field4<=@P4 group by b.field2)
group by b.field1,b.field2,field3',
N'@P1 datetime,@P2 datetime,@P3 datetime,@P4 datetime','Jun 1 2008 12:00:00:000AM','Jun 18 2008 12:00:00:000AM','Jun 1 2009 12:00:00:000AM','Jun 18 2009 12:00:00:000AM'
[/code]
The view is huge while the 2 tables are very small. I don't have the execution plan right now because this query takes more than 20 minutes to run and I'm very tired to execute it again. If I remove the subquery it takes less than 2 seconds!
I tried to make a the view indexed, but didn't work. Seem a kind of bug possibly fixed in SQL Server 2005 SP3. I didn't find a related hotfix and installing SP3 now isn't an option.
My main expertise is administration I'm not good with developing, so I got stuck.
I appreciate any help.
Thanks in advance.
Thank you Paul and Florian for orientation. I'am attaching the extra information.
The query actually returns 7 rows. If I remove the subquery it runs pretty fast, but returns extra 125 rows unwanted. I'm not the application developer. I just admin database servers, but I'm in charge of solve this performance problem or, at least, give some orientation how workaround to get the same results with less time. This issue came after the application upgrade, the view was a lot simple before upgrade and the query used to runs in less than 1 minute.
The view has 3264200 rows and with the query's where clause it returns around 42000 rows. Table1 and table2 have less than 300 rows.
June 20, 2009 at 4:28 am
leomar,
It's really hard to offer a solution here, particularly since the query appears to reference a view which could be anything! The general construction of the query is potentially problematic - the NOT IN with the subquery is likely to be the problem, but without more details it's really impossible to say.
Please help us to help you by posting the definitions for the tables and views concerned, including index definitions, and some idea of the data sizes involved. An explanation of what the query is designed to do together with some sample expected output would be a bonus.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 20, 2009 at 4:35 am
In addition:
To help with performance issues an execution plan is always helpful. Maybe have a look to Gail's article "How to post Performance Problems":
http://www.sqlservercentral.com/articles/SQLServerCentral/66909
June 22, 2009 at 7:16 am
I just want to echo what everyone else has said. Without at least an execution plan, it's just not possible to say what's going on.
I can suggest that you try using a LEFT JOIN and check for null values instead of the NOT IN statement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 22, 2009 at 8:57 am
Hello Grant,
I attached the files yesterday. They're in my original post.
June 22, 2009 at 3:31 pm
How many rows are in the table Loja_Venda? Exec plan gives estimated 2.3 rows, actual 15 million, and that's for the results of an index scan with a predicate on it. The plan's using nested loops because SQL thinks there's a small number of rows. If there are actually several million, those nested loops are going to perform terribly.
First things first, rebuild the indexes on Loja_Venda and see if that changes the exec plan (it'll update stats and hopefully fix the estimate). If it does, post the new plan.
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
June 23, 2009 at 6:36 am
Hello Gail,
Table LOJA_VENDA has 6+ million. The plan was generated with all indexes defragmented, so I think indexing is not the problem.
June 23, 2009 at 6:54 am
Then please do a statistics update, preferably with full scan. I wasn't asking to remove fragmentation, an index rebuild updates stats and, from what I can see, stats are way off on that table. That, or there's something really odd going on there.
When you say 6+ million, how many more than 6 million? As I said, the plan's showing that 15 million rows were returned from that table.
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply