Performance drops when view is built using joins

[arunrathna]

I find that whenever I use a join in a view, the performance while retrieving records from the view gets worse (like 25 times slower than normal retrieval). If I use unions in the view to produce a similar result that the view with join would produce, the view is much faster. But I could not use unions to do all kinds of joins that I need. Please let me know if it is advisable to use joins in views.

Thanx,
Rathna.

 

[JimHorton]

Rathna,
Have you indexed the fields involved in the join? This may seem obvious, but it's often overlooked. If it's a multi field join, then make composite indexes over the fields on both sides. Depending on how often this query is run, it's very often worth the disk space and small update performance hit to have the index. Disk space is usually cheaper than time.
--Jim

 

[v0520]

Dont forget when you do a join, you are "materializing" the entire cursor before you will get results. That is to say if you have 500,000 rows in one table and join it with another table with 1,0000 rows and then join it with another table with 1,0000 rows, it will take time.

If it needs to be fast (as in a search), perhaps the best strategy is to issue a query first on the main search table and then retrieve the associated data later as you are displaying the results.

I had the same problem and it was because of the volume of the data we have (about 70 million rows total on our database).

Gilbert M. Vanegas
Programmer Analyst III
County of San Bernardino - ISD
Email : gvanegas@isd.sbcounty.gov

 

[Guest_imported]

Try to optimize your SQL with the product SQL Expert from leccotech.com

 

[arunrathna]

Thanx for ur answers. Inspite of all efforts on indexes and other means, I do find that joins in views slow down the query a lot. The reason should be as we are "materializing" the entire cursor before you will get results. But I managed to achieve the same with views using unions and groups bys. So temporaririly, the problem was solved.
UDB still has major problems with implementation on views with outerjoins and temptables (built using &quot;with&quot; clause). When I build a view like that (with a temp table and a outer join), I am able to give a select * on the view, but when I refer a column name as select <col name> from <view name>, it gives a SQL internal error. Hope this problem is solved in the next release.
Thanx,
Rathna.