Showing posts with label thiscreate. Show all posts
Showing posts with label thiscreate. Show all posts

Monday, March 19, 2012

function within view

I have a view built like this
CREATE VIEW XXX
AS
select * from XXX_Calculate()
the XXX_calculate() function is built like this
CREATE FUNCTION XXX_Calculate ()
RETURNS @.Result TABLE (XXXID bigint NOT NULL, XXX2ID bigint NOT NULL)
....
it contains cursors which insert values to the @.Result table.
The problem is whenever a user calls this view the function is beiing
executed again so the retrieval is slow...
Is there a hint to have it behave like normal view?
Thanx in advance.
Sorry about my poor English...P Platan:
At first i'm trying to to use cursors at all.
Because as you see it works very slow, i would offer you to think how to be
avoid usin cursor.
If your example is as well as your view', I dont see whay do you need view
at all. On many programs that use sql you can use SELECT * fron function().
I would offer you to use store procedure instead of view. because on store
procedure you can set the function result on one temporary table and use it
as you can in the store procedure. Also all other software who work with sql
server can use store procedure as well as view.
"P Platan" <pplat@.exnds.com> wrote in message
news:ulItixdQGHA.4536@.TK2MSFTNGP10.phx.gbl...
>I have a view built like this
> CREATE VIEW XXX
> AS
> select * from XXX_Calculate()
> the XXX_calculate() function is built like this
> CREATE FUNCTION XXX_Calculate ()
> RETURNS @.Result TABLE (XXXID bigint NOT NULL, XXX2ID bigint NOT NULL)
> ....
> it contains cursors which insert values to the @.Result table.
> The problem is whenever a user calls this view the function is beiing
> executed again so the retrieval is slow...
> Is there a hint to have it behave like normal view?
> Thanx in advance.
> Sorry about my poor English...
>|||I use view because it resides in another database from the one that the
function calls.
To be more specific
In the old datbase schema we had a basic table with 150 categories as fields
I the new implementation we want to normalize it and have them 'vertical'.
In order not to transfer lots of data to the other db and not load triggers
in the basic table which is accessed very heavily we created the view to the
new db which 'verticals' the categories-fields of the basic table.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uNX6wjeQGHA.5248@.TK2MSFTNGP09.phx.gbl...
>P Platan:
> At first i'm trying to to use cursors at all.
> Because as you see it works very slow, i would offer you to think how to
> be avoid usin cursor.
> If your example is as well as your view', I dont see whay do you need view
> at all. On many programs that use sql you can use SELECT * fron
> function().
> I would offer you to use store procedure instead of view. because on store
> procedure you can set the function result on one temporary table and use
> it as you can in the store procedure. Also all other software who work
> with sql server can use store procedure as well as view.
> "P Platan" <pplat@.exnds.com> wrote in message
> news:ulItixdQGHA.4536@.TK2MSFTNGP10.phx.gbl...
>

Sunday, February 26, 2012

Fun of INSTEAD OF UPDATE trigger

Hi All,
Here I have a view like this:
create view vw_Lcustkeycode as
select *, convert(bit,0) as IsArchived from dbo.Lcustkeycode
union all
select *, convert(bit,1) as IsArchived from
DataEntryArchive.dbo.aLcustkeycode
I created an INSTEAD OF UPDATE trigger like:
CREATE TRIGGER tr_update_2cols on vw_Lcustkeycode INSTEAD OF UPDATE
AS
BEGIN
if update(UserName) and update(DateModified) begin
update d
set d.UserName=i.UserName, d.DateModified=i.DateModified
from dbo.Lcustkeycode d
inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
where i.IsArchived=0
update d
set d.UserName=i.UserName, d.DateModified=i.DateModified
from DataEntryArchive.dbo.aLcustkeycode d
inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
where i.IsArchived=1
end
END
When I run following update in Query Analyzer (notice the lcustkeycode_id is
primary key so only 1 row should be affected):
update vw_LCustKeycode
set username='jamma', datemodified=getdate()
where lcustkeycode_id=111060167
It said:
(2 row(s) affected)
(1 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
I check the data and they are correct and really only 1 row was updated, but
why it said so many rows were affected? I find there are no other trigers
sitting there except my instead of trigger.
Can anyone here explain this strange behaviour?
Thanks,
JamesJames Ma wrote:
> Hi All,
> Here I have a view like this:
> create view vw_Lcustkeycode as
> select *, convert(bit,0) as IsArchived from dbo.Lcustkeycode
> union all
> select *, convert(bit,1) as IsArchived from
> DataEntryArchive.dbo.aLcustkeycode
> I created an INSTEAD OF UPDATE trigger like:
> CREATE TRIGGER tr_update_2cols on vw_Lcustkeycode INSTEAD OF UPDATE
> AS
> BEGIN
> if update(UserName) and update(DateModified) begin
> update d
> set d.UserName=i.UserName, d.DateModified=i.DateModified
> from dbo.Lcustkeycode d
> inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
> where i.IsArchived=0
> update d
> set d.UserName=i.UserName, d.DateModified=i.DateModified
> from DataEntryArchive.dbo.aLcustkeycode d
> inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
> where i.IsArchived=1
> end
> END
> When I run following update in Query Analyzer (notice the
> lcustkeycode_id is primary key so only 1 row should be affected):
> update vw_LCustKeycode
> set username='jamma', datemodified=getdate()
> where lcustkeycode_id=111060167
> It said:
> (2 row(s) affected)
> (1 row(s) affected)
> (8 row(s) affected)
> (0 row(s) affected)
> (8 row(s) affected)
> I check the data and they are correct and really only 1 row was
> updated, but why it said so many rows were affected? I find there are
> no other trigers sitting there except my instead of trigger.
> Can anyone here explain this strange behaviour?
> Thanks,
> James
Look in Profiler and see what it's doing (look at SP:StmtCompleted
events in addition to SQL:StmtCompleted).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks for your quick reply. Just now I closed Query Analyzer and entered it
again, then the results become:
(1 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
Seems fine now. I can't explain what happened just now. Even when I set
nocount off, it retuned.
(2 row(s) affected)
(8 row(s) affected)
But now everything is fine.
"David Gugick" wrote:

> James Ma wrote:
> Look in Profiler and see what it's doing (look at SP:StmtCompleted
> events in addition to SQL:StmtCompleted).
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Hello, James
The extra "n row(s) affected" are probably a side effect of the "Show
execution plan" in Query Analyzer. Retry your query with this option on
and off to see if that's the problem.
Razvan