July 11, 2007 at 9:45 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 11, 2007 at 10:07 pm
I think you'll need to post the structure of the 2 views to get any solid advice from this forum.
But... you can look at indexes on join fields and WHERE clauses, both in your query and within the views themselves.
You mentioned that placing the filter inside the views adds performance. Perhaps the views can be rewritten as table functions, but I'm not positive that approach would get the desired results by itself. But it's worth a try as a last resort and it wouldn't take too much to write and test.
Regards,
Rubes
July 11, 2007 at 10:23 pm
I have checked already the indexes of the tables, the tables are linked properly and all the table joins are based on fields contained (all of them for all the tables) in clustered indexes. The problem is the two views contain (both of them) something close to 10.000 records (none of them indexed unfortunately ...) , so joining them together gives a 100.000.000 rows result which filtered on a pretty complex filter leads to ~ 50.000 rows.
July 11, 2007 at 11:45 pm
I'm curious as to why you would get 100,000,000 rows after the join. Many to many I suppose.
Otherwise, I'm out of ideas without seeing the structure of the views.
Regards,
Rubes
July 13, 2007 at 10:38 am
Yes, table, view, and index DDL will help. Also, you say there are no parameterized queries in SQL Server. What do you mean by that? Of course you can use parameters in a SQL query. If your core tables only have 10,000 rows, the query should not be taking minutes. Posting your code will help lots as it sounds like you may have written a triangular JOIN here.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply