|
Home > Archive > MS SQL Server > December 2006 > SP in a View
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| Hi ,
Is there any way to use a Sored Procedure in a View.
What I mean is
' To execute the stored procedure and output some columns that could be
called by the view'
Thanks
| |
| Tibor Karaszi 2006-12-13, 7:12 pm |
| You could use a hack with OPENQUERY or OPENROWSET. I don't recommend that, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"Dutt" <Mr.Dutt@gmail.com> wrote in message
news:1166008494.370157.140690@j44g2000cwa.googlegroups.com...
> Hi ,
> Is there any way to use a Sored Procedure in a View.
> What I mean is
> ' To execute the stored procedure and output some columns that could be
> called by the view'
> Thanks
>
| |
| Uri Dimant 2006-12-13, 7:12 pm |
| Dutt
CREATE PROC usp_myproc
AS
SELECT OrderId,OrderDate FROM Orders
GO
--usage
EXEC usp_myproc
Why would you want to call it by view, can you explain a little bit?
"Dutt" <Mr.Dutt@gmail.com> wrote in message
news:1166008494.370157.140690@j44g2000cwa.googlegroups.com...
> Hi ,
> Is there any way to use a Sored Procedure in a View.
> What I mean is
> ' To execute the stored procedure and output some columns that could be
> called by the view'
> Thanks
>
| |
|
| Uri,
Here I'm concerned with performance tuning.
I'm using almost 8 joins(inner and left outer) in the SP.The
performance is a bit bad.
For a SP, since the execution plan is stored,its fast,isnt it....
So,what i think is i create SPs for 3 or 4 joins and connect all the
result sets through 'VIEWS'.
I hope u can understand me now.
Thanks
Dutt
Uri Dimant wrote:[color=darkred
]
> Dutt
>
> CREATE PROC usp_myproc
> AS
> SELECT OrderId,OrderDate FROM Orders
> GO
>
> --usage
> EXEC usp_myproc
>
> Why would you want to call it by view, can you explain a little bit?
>
>
>
>
> "Dutt" <Mr.Dutt@gmail.com> wrote in message
> news:1166008494.370157.140690@j44g2000cwa.googlegroups.com...
| |
|
| Uri,
Here I'm concerned with performance tuning.
I'm using almost 8 joins(inner and left outer) in the SP.The
performance is a bit bad.
For a SP, since the execution plan is stored,its fast,isnt it....
So,what i think is i create SPs for 3 or 4 joins ,use OUTPUT parameters
in it and connect all the result sets through 'VIEWS'.
I hope u can understand me now.
Thanks
Dutt
Uri Dimant wrote:[color=darkred
]
> Dutt
>
> CREATE PROC usp_myproc
> AS
> SELECT OrderId,OrderDate FROM Orders
> GO
>
> --usage
> EXEC usp_myproc
>
> Why would you want to call it by view, can you explain a little bit?
>
>
>
>
> "Dutt" <Mr.Dutt@gmail.com> wrote in message
> news:1166008494.370157.140690@j44g2000cwa.googlegroups.com...
| |
| Uri Dimant 2006-12-13, 7:12 pm |
| Dutt
> I'm using almost 8 joins(inner and left outer) in the SP.The
> performance is a bit bad.
> For a SP, since the execution plan is stored,its fast,isnt it....
> So,what i think is i create SPs for 3 or 4 joins and connect all the
> result sets through 'VIEWS'.
No, I think you are on the wrong way. If you notice that perfomance is bad ,
try to invsetigate an execution plan , perhaps the optimizer uses a wrong
/or not using at all indexes
"Dutt" <Mr.Dutt@gmail.com> wrote in message
news:1166009956.577089.47590@f1g2000cwa.googlegroups.com...
> Uri,
>
> Here I'm concerned with performance tuning.
> I'm using almost 8 joins(inner and left outer) in the SP.The
> performance is a bit bad.
> For a SP, since the execution plan is stored,its fast,isnt it....
> So,what i think is i create SPs for 3 or 4 joins and connect all the
> result sets through 'VIEWS'.
> I hope u can understand me now.
>
> Thanks
> Dutt
> Uri Dimant wrote:
>
| |
| Tibor Karaszi 2006-12-13, 7:12 pm |
| > I'm using almost 8 joins(inner and left outer) in the SP.The
> performance is a bit bad.
> For a SP, since the execution plan is stored,its fast,isnt it....
The main reason for execution time is probably not producing the execution plan, it is probably due
to inefficient data access. My suggestion is that you attack this problem by looking at the
execution plan, analyze and consider creating supporting indexes for the query.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"Dutt" <Mr.Dutt@gmail.com> wrote in message
news:1166009956.577089.47590@f1g2000cwa.googlegroups.com...
> Uri,
>
> Here I'm concerned with performance tuning.
> I'm using almost 8 joins(inner and left outer) in the SP.The
> performance is a bit bad.
> For a SP, since the execution plan is stored,its fast,isnt it....
> So,what i think is i create SPs for 3 or 4 joins and connect all the
> result sets through 'VIEWS'.
> I hope u can understand me now.
>
> Thanks
> Dutt
> Uri Dimant wrote:
>
| |
|
| Hi both,
In the execution plan itself I found that the JOINS are taking more
time.
Anyhow,I'll try again .
ThankYou friends
Dutt
Tibor Karaszi wrote:[color=darkred
]
>
> The main reason for execution time is probably not producing the execution plan, it is probably due
> to inefficient data access. My suggestion is that you attack this problem by looking at the
> execution plan, analyze and consider creating supporting indexes for the query.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Dutt" <Mr.Dutt@gmail.com> wrote in message
> news:1166009956.577089.47590@f1g2000cwa.googlegroups.com...
| |
| Uri Dimant 2006-12-13, 7:12 pm |
| How did you know it? What kind of JOIN do you see at execution plan?
"Dutt" <Mr.Dutt@gmail.com> wrote in message
news:1166014736.195956.305080@l12g2000cwl.googlegroups.com...
> Hi both,
>
> In the execution plan itself I found that the JOINS are taking more
> time.
> Anyhow,I'll try again .
>
> ThankYou friends
> Dutt
> Tibor Karaszi wrote:
>
| |
| Tibor Karaszi 2006-12-13, 7:12 pm |
| > In the execution plan itself I found that the JOINS are taking more
> time.
Which is why you would attach this issue like any SQL related performance issue. Look at the
queries, plans and how the queries uses the indexes you have. Perhaps you just need to create
indexes on (some of) the columns that you joins over, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"Dutt" <Mr.Dutt@gmail.com> wrote in message
news:1166014736.195956.305080@l12g2000cwl.googlegroups.com...
> Hi both,
>
> In the execution plan itself I found that the JOINS are taking more
> time.
> Anyhow,I'll try again .
>
> ThankYou friends
> Dutt
> Tibor Karaszi wrote:
>
|
|
|
|
|