September 21, 2010 at 8:44 pm
how can i create a stored proc or function which can be used just like a view and perform just like querying a table
September 22, 2010 at 4:55 am
The best way to fix your problem is to rewrite your initial query without the view and to simplify it as much as possible.
The more complicated a query is, the more chances you'll have SQL server will generate a not so optimal plan, which can lead to horrible run time differences.
You can either rewrite the query and remove as many joins as possible or if you are mixing big and small tables, try to get a result of all the small tables into a temporary table and join it with the big table(s).
September 22, 2010 at 7:34 am
You may find this useful: http://www.developer.com/db/article.php/3598891/User-Defined-Functions-Powerful-Alternatives-to-Views-and-Stored-Procedures.htm
Francis
September 22, 2010 at 10:18 am
I still wonder why is the view perofrming better on some db's which are actually bigger than the problem database.
September 22, 2010 at 10:43 am
I have tried table valued function instead of view but still the same performance, i am not getting paralellism yet.
September 22, 2010 at 1:43 pm
I think the problem in the last part of the query is that it is using nested loop at the inner join, how do i avoid that? where as the db that is perofrming good is not using nested loop but it is using paralelism, thats how i interepreted.
September 22, 2010 at 2:02 pm
Make sure both databases have the same paramterization option (I assume that is FORCED)
Did you try to force parallelism like:
select a.empno,sdate,enddate,place
ecode1,ecode2,ecode3,ecode4,ecode5
into #dev2
from employee a inner join #empdts b
on a.empno=b.empno
inner join #dev c
on a.empno = c.empno
where a.sdate between b.efdt and b.exdt
OPTION (MAXDOP 0);
Francis
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply