July 11, 2007 at 11:39 pm
What I have:
A query containing a join of two vies, like :
SELECT ... FROM view1 LEFT OUTER JOIN view2 ON linking_criteria WHERE ClientID = code
This query is really slow, taking minutes no matter of client (this is normal IMHO) I have to make it a lot faster but I really don't know how, because:
1. SCHEMABINDING is not an option , the database is in a 24x7 development cycle (serving ~ 100 clients), there are at least 2-3 structural changes daily, so using indexed views is a dream in this moment ...
2. moving the filter (ClientID = code) inside the views can lead to very good performance enhancement, but the problem in this case is that will lead to a growing complexity (I will need a different view for any client, so only for this query I should create 200 views (100 view1 + 100 view2). I am not the lazy type, I would do this but I have 12 queries like this, calling different views, so the real work will be huge .... And I already have a lot of let's say "individualized views"
3. I know that we don't have parameterized queries in SQL Server ?
The problem lies on a SQL Server 2000 SP3a (don't laugh guys, it's not my fault...) Standard Edition / Win 2003 SP1 and except the "solution" with building tons of individualized views I really don't have any idea how to fix this. Probably I will do this, but I would like to have some other ideas except mines on this topic ....
July 12, 2007 at 3:04 am
July 13, 2007 at 7:48 am
You are not in as bad of shape as you think you are. Sorry about the whole sp3a thing though...Tell your company that MS has stopped support on that version.
As much as views can hinder performance, they are really little more than sub queries and can be written to optimize performance. I cannot give you much help without specifics on your table schema and the SQL for the views, but if you spend some time making the usual optimization effort, you will see benefit.
Start with running your query and checking the execution plans. Fix table scans right away by indexing and (if necessary hints). Also remember that you can get views to sort by using a TOP 100 PERCENT and an ORDER BY. If your views are optimized and then ordered correctly, the optimization engine will happily understand the order of the data and treat it accordingly.
July 16, 2007 at 1:18 pm
One trick that I use it to copy the query twice into a single query window.
The top one will stay the same. Make your changes to the bottom one. Do a Ctrl-L to see the query plan. It will tell you percentage wise which query is faster.
The downside of this is that it only works if the changes are in the query. If you are comparing table changes or index changes, then you will need 2 tables to compare before and after results.
Hope I helped.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply