April 13, 2005 at 10:25 am
I am attempting to resolve some performance issues that revolve around a view that was built to union 2 tables in SQL 7.
at a high level the 2 tables have existing clients and new clients and perform various joins to get ancillary information about the clients.
When queries are going against an ID that is only in the first table, but not possible in the 2nd (field is selected like 0 as ID), the performance is horrible. When the query goes after names ( possible match in both tables), it performs fairly well. The individual queries perform fairly well when I go against ID.
I can split them up into 2 separate views, and when I know ahead of time which table I need to access, use the appropriate view. This works well, but requires much more work in code modification. Does anyone have any other ideas of things I might try to get the view to perform better? (Again, please keep in mind SQL 7, although we are scheduled to finally go to 2000 soon)
April 14, 2005 at 3:16 am
It sounds like you have an indexing issue.
Could you post the DDL of your tables, views and indexes for that query?
Also don't forget UNION is doing a distinct sort while UNION ALL doesn't
Bye
Gabor
April 14, 2005 at 6:44 am
Thanks, unfortunately we are talking many tables and join conditions, so I do not want to post the DDL. If I run each part of the union individually, it works fine.. In fact, so far that has been my fix, creating 2 views and calling the appropriate one if I know which half is to be used. My problem is how to tune the combined view. Are there any things you can do in a query of this type to make sure it chooses the right query plan?
April 14, 2005 at 7:31 am
Shure!
You can check the access plan within your Query Analyzer.
So you can see whether it is using an index or a table scan
Bye
Gabor
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply