January 22, 2007 at 7:56 am
Hello
I have a query which accesses one table and two views. A "SELECT *" on the table/views is very fast (under one second) and the number of records returned is <200 on each table/view. Also when I use real tables instead of views my query is very fast (under one second).
But when I use the table/views the query is very, very slow (about 30 seconds).
Is this a problem of the optimizer? Do I have to make temporary tables or is there a way to tell SQL Server not to do so much optimization between the views but just take the views "as they are"?
You can have a look at the query on http://mdgrosse.net/perf.sql.txt . I had similar problems before at another customer. There I could solve the problem by reformulating the query - but I didn't understand the underlying problem which caused this sudden loss of performance.
thx
Michael
January 22, 2007 at 8:33 am
It would really help if we saw the execution plan of th query.
January 22, 2007 at 11:24 am
January 22, 2007 at 5:54 pm
You have many scans on the "par" table, in different spots in the plan.
is that a large table?
---------------------------------------
elsasoft.org
January 23, 2007 at 4:03 am
No, this table has only one record (accounting stuff: the client and the current year).
January 23, 2007 at 1:19 pm
well, it is a pretty complicated join.
you might be able to improve perf by using a few temp tables instead, to decrease the number of joins.
---------------------------------------
elsasoft.org
January 23, 2007 at 2:58 pm
Ok - heres the biggie:
When I change all "LEFT JOIN" in "LEFT HASH JOIN" (only in my query, I don't touch the views) the whole query needs under one second (the original query needs with the current data over a minute).
Does the "LEFT HASH JOIN" brings any disadvantages?
Can anybody explain this to me? Are there any books where I can learn such things. I'm a bit worried about my "technique": rewriting a query until it is fast without really knowing what I am doing.
thx
Michael
January 23, 2007 at 3:03 pm
January 23, 2007 at 3:27 pm
here's a good explanation of hash join:
http://blogs.msdn.com/craigfr/archive/2006/08/10/687630.aspx
Craig Freedman's blog is excellent for learning about the inner workings of the optimizer.
---------------------------------------
elsasoft.org
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply