Hey all,
I've got a very long stored proc that runs intensive updates on a particular table. The locks are always escalated from Intent eXclusive to eXclusive. After some reading online, I've decided to implement this (http://support.microsoft.com/default.aspx?scid=kb;en-us;323630#kb2) . The idea is to start a transaction with another spid, and hold an incompatible lock on that table so the stored procedure that I'm running isn't able to escalate the lock. The solution works, but it unfortunately means that I've to lock this table with an update lock for the whole stored proc, which I would rather not do.
Is it possible to spawn another stored proc/function/transaction under another spid from within my stored proc ? I'm hoping the answer to my question isn't here (http://www.dbforums.com/t994076.html).
Is it maybe possible to open another connection within my stored proc ? On a similar note, would it be possible to communicate somehow between connections without using a table ?
Thanks,
-KilkaThere are many ways to do this, but Transact-SQL is a bit limited in this area. It can be done, but it is brute force and ugly at best.
Have you investigated DTS? At least in my experience, it handles this kind of processing much better than Transact-SQL can.
-PatP|||I've worked with DTS and I know the only way it could potentially help me was if I used scripting (activeX or something) to acheive the same thing.
I'm trying to keep everything in a scheduled stored proc. If at all possible, I want to do everything in T-sql for performance reasons. This is something that takes hours to run, so any little performance hit has a big impact.
Showing posts with label stored. Show all posts
Showing posts with label stored. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Precompiling checking problem
i have a stored procedure i want to save
the commands all execute fine if i execute them one at a time, but when i
try to create a sp out of the script, it fails
problem... my sp adds a coulmn to a table in the begining, does some
processing and uses that column, then drops it.
the process that saves the sp tries to validate each command individually
instead of as a progression so obviously a select statement with the new
column is not valid on its own and it wont process the script.
is their any directive in QA that can turn off this checking?
i have a work around, build the query dynamically, but this is a pain as far
as im concerned.
any suggestions?
not sure I understand completely, BUT I think your problem may be resolved
by placing GO statements after each step in the sproc.
try that (if you havent already) and script the thing out and paste it in
here if that still does not work..
that we we can all have a look at it and get through the guesswork.
Greg Jackson
PDX, Oregon
|||example:
table1 has column1, column2, column3
i create a script to make a stored procedure in query analyzer:
create proc test1 as
alter table to add column4
update table1 to set column4 = to something
update table1 to set other columns to something
alter table to drop column4
go
1) execute each internal line seperately, each runs without error
2) execute script to create the proc, ERROR cause it checks each command
before creating the proc and since the second line sets column4 to
something, and column4 is not there right now, its an invalid command and
the proc isnt created
3) cant put go's between the commands in a stored procedure and even if you
could, the system still wont save the procedure cause line2 is still invalid
at this moment in time.
|||Claude Hebert wrote:
> example:
> table1 has column1, column2, column3
> i create a script to make a stored procedure in query analyzer:
> create proc test1 as
> alter table to add column4
> update table1 to set column4 = to something
> update table1 to set other columns to something
> alter table to drop column4
>
Without trying to understand what you are doing, the problem is that
each of those statements must be in its own batch. You have to use
dynamic sql to do what you want:
create table dbo.test1 (col1 int)
go
insert into dbo.test1 values (1)
create proc dbo.test2 as
begin
EXEC ('alter table dbo.test1 add column4 int')
select * from dbo.test1
EXEC ('update dbo.test1 set column4 = 5')
select * from dbo.test1
EXEC ('alter table dbo.test1 drop column column4')
select * from dbo.test1
end
go
exec test2
drop proc dbo.test2
create table dbo.test1
David Gugick
Imceda Software
www.imceda.com
|||i know that works, and this is a very simple case...
because of the size of our project, the number of procedures, the number of
lines in those procedures that would have to be moved into EXEC statements,
i was trying to find another way...
i was just hopping there was a set something that i could do in QA to allow
the code to run without all the EXEC's
im guessing at this point, the answer is NO
|||Hi Greg
GO is a batch separator for the client, it is not a SQL statement. GO tells
the CLIENT to send a set of commands to SQL Server separately (that is what
a batch is).
GO is not possible inside a stored procedure, which always executes within a
single batch. In fact, if you are trying to create a stored proc in the
Query Analyzer, as soon as a GO is entered, that is the end of the
procedure.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eG51le%23VFHA.2616@.TK2MSFTNGP14.phx.gbl...
> not sure I understand completely, BUT I think your problem may be resolved
> by placing GO statements after each step in the sproc.
> try that (if you havent already) and script the thing out and paste it in
> here if that still does not work..
> that we we can all have a look at it and get through the guesswork.
>
> Greg Jackson
> PDX, Oregon
>
|||yep my bad.
I didnt read carefully enough. I though he was talking about a SQL Script.
GAJ
the commands all execute fine if i execute them one at a time, but when i
try to create a sp out of the script, it fails
problem... my sp adds a coulmn to a table in the begining, does some
processing and uses that column, then drops it.
the process that saves the sp tries to validate each command individually
instead of as a progression so obviously a select statement with the new
column is not valid on its own and it wont process the script.
is their any directive in QA that can turn off this checking?
i have a work around, build the query dynamically, but this is a pain as far
as im concerned.
any suggestions?
not sure I understand completely, BUT I think your problem may be resolved
by placing GO statements after each step in the sproc.
try that (if you havent already) and script the thing out and paste it in
here if that still does not work..
that we we can all have a look at it and get through the guesswork.
Greg Jackson
PDX, Oregon
|||example:
table1 has column1, column2, column3
i create a script to make a stored procedure in query analyzer:
create proc test1 as
alter table to add column4
update table1 to set column4 = to something
update table1 to set other columns to something
alter table to drop column4
go
1) execute each internal line seperately, each runs without error
2) execute script to create the proc, ERROR cause it checks each command
before creating the proc and since the second line sets column4 to
something, and column4 is not there right now, its an invalid command and
the proc isnt created
3) cant put go's between the commands in a stored procedure and even if you
could, the system still wont save the procedure cause line2 is still invalid
at this moment in time.
|||Claude Hebert wrote:
> example:
> table1 has column1, column2, column3
> i create a script to make a stored procedure in query analyzer:
> create proc test1 as
> alter table to add column4
> update table1 to set column4 = to something
> update table1 to set other columns to something
> alter table to drop column4
>
Without trying to understand what you are doing, the problem is that
each of those statements must be in its own batch. You have to use
dynamic sql to do what you want:
create table dbo.test1 (col1 int)
go
insert into dbo.test1 values (1)
create proc dbo.test2 as
begin
EXEC ('alter table dbo.test1 add column4 int')
select * from dbo.test1
EXEC ('update dbo.test1 set column4 = 5')
select * from dbo.test1
EXEC ('alter table dbo.test1 drop column column4')
select * from dbo.test1
end
go
exec test2
drop proc dbo.test2
create table dbo.test1
David Gugick
Imceda Software
www.imceda.com
|||i know that works, and this is a very simple case...
because of the size of our project, the number of procedures, the number of
lines in those procedures that would have to be moved into EXEC statements,
i was trying to find another way...
i was just hopping there was a set something that i could do in QA to allow
the code to run without all the EXEC's
im guessing at this point, the answer is NO
|||Hi Greg
GO is a batch separator for the client, it is not a SQL statement. GO tells
the CLIENT to send a set of commands to SQL Server separately (that is what
a batch is).
GO is not possible inside a stored procedure, which always executes within a
single batch. In fact, if you are trying to create a stored proc in the
Query Analyzer, as soon as a GO is entered, that is the end of the
procedure.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eG51le%23VFHA.2616@.TK2MSFTNGP14.phx.gbl...
> not sure I understand completely, BUT I think your problem may be resolved
> by placing GO statements after each step in the sproc.
> try that (if you havent already) and script the thing out and paste it in
> here if that still does not work..
> that we we can all have a look at it and get through the guesswork.
>
> Greg Jackson
> PDX, Oregon
>
|||yep my bad.
I didnt read carefully enough. I though he was talking about a SQL Script.
GAJ
Precision Problem
Hi,
I can't seem to get a stored procedure to return decimal places. Here are the steps to re-create the problem:
--first create the following procedure
CREATE PROCEDURE test_precision
AS
BEGIN
RETURN 5.2
END
--then run the following code
DECLARE @.x DECIMAL(18,2)
EXECUTE @.x = test_precision
SELECT @.x
I would like this code to return 5.20, but instead it returns 5.00. Any assistance would be greatly appreciated. Thanks.if you are using SQL 2005 that is the problem. Your stored Procedure would work correctly in SQL 2000.
The work around
declare @.x FLOAT(18,2)
give that a try.|||ooops!! allow me to take that back - wrong thought process. sorry.
you need to edit your proc to read 5.20sql
Friday, March 23, 2012
Power user question
I want to create a user on SQL Server 2005 that needs to be able to
create databases, logins, and users, as well as create stored procs,
triggers, tables, etc. This user will be accessed from a vb.net
application. Is there anything less powerful than the 'sa' user that
will do the trick, or some kind of roll-my-own permissions? I really
haven't played around too much with assigning SQL Server permissions
before.
Cheers,
MarcusYou can grant the server role Database Creators to the user, and then grant
the fixed database role db_ddladmin for each existing database.
"Marcus" wrote:
> I want to create a user on SQL Server 2005 that needs to be able to
> create databases, logins, and users, as well as create stored procs,
> triggers, tables, etc. This user will be accessed from a vb.net
> application. Is there anything less powerful than the 'sa' user that
> will do the trick, or some kind of roll-my-own permissions? I really
> haven't played around too much with assigning SQL Server permissions
> before.
> Cheers,
> Marcus
>
create databases, logins, and users, as well as create stored procs,
triggers, tables, etc. This user will be accessed from a vb.net
application. Is there anything less powerful than the 'sa' user that
will do the trick, or some kind of roll-my-own permissions? I really
haven't played around too much with assigning SQL Server permissions
before.
Cheers,
MarcusYou can grant the server role Database Creators to the user, and then grant
the fixed database role db_ddladmin for each existing database.
"Marcus" wrote:
> I want to create a user on SQL Server 2005 that needs to be able to
> create databases, logins, and users, as well as create stored procs,
> triggers, tables, etc. This user will be accessed from a vb.net
> application. Is there anything less powerful than the 'sa' user that
> will do the trick, or some kind of roll-my-own permissions? I really
> haven't played around too much with assigning SQL Server permissions
> before.
> Cheers,
> Marcus
>
Power user question
I want to create a user on SQL Server 2005 that needs to be able to
create databases, logins, and users, as well as create stored procs,
triggers, tables, etc. This user will be accessed from a vb.net
application. Is there anything less powerful than the 'sa' user that
will do the trick, or some kind of roll-my-own permissions? I really
haven't played around too much with assigning SQL Server permissions
before.
Cheers,
MarcusYou can grant the server role Database Creators to the user, and then grant
the fixed database role db_ddladmin for each existing database.
"Marcus" wrote:
> I want to create a user on SQL Server 2005 that needs to be able to
> create databases, logins, and users, as well as create stored procs,
> triggers, tables, etc. This user will be accessed from a vb.net
> application. Is there anything less powerful than the 'sa' user that
> will do the trick, or some kind of roll-my-own permissions? I really
> haven't played around too much with assigning SQL Server permissions
> before.
> Cheers,
> Marcus
>
create databases, logins, and users, as well as create stored procs,
triggers, tables, etc. This user will be accessed from a vb.net
application. Is there anything less powerful than the 'sa' user that
will do the trick, or some kind of roll-my-own permissions? I really
haven't played around too much with assigning SQL Server permissions
before.
Cheers,
MarcusYou can grant the server role Database Creators to the user, and then grant
the fixed database role db_ddladmin for each existing database.
"Marcus" wrote:
> I want to create a user on SQL Server 2005 that needs to be able to
> create databases, logins, and users, as well as create stored procs,
> triggers, tables, etc. This user will be accessed from a vb.net
> application. Is there anything less powerful than the 'sa' user that
> will do the trick, or some kind of roll-my-own permissions? I really
> haven't played around too much with assigning SQL Server permissions
> before.
> Cheers,
> Marcus
>
POWER function
Hello,
I'm trying to follow a specification here:
W1 = (W0^0.333 + G * D/1000)^3 (the ^ symbol means 'to the power')
In my stored procedure I have used the following test data with unexpected results. Can anyone tell me if I'm using POWER properly?
@.Value = POWER(((POWER(150,(1/3))+ 2)* 10/1000),3)
Thanks
I think you are adding parentheses that are forcing a different order of arguments than the original. Here is what the original equation looks like with parentheses:
W1 = ( W0^(1/3) + ( G * D/1000) )^3
Your equation is written like this:
Val = ( (150^(1/3) + 2) * 10/1000 ) ^3|||Thanks Darrell, you're right about the parentheses. However, even with the correction and replacing '1/3' with 0.3333 (which makes a difference) - the results are still out - we expect the @.Value to be 150+ and it's only 125+. But you think the nested POWER function is OK? Or should I do this in 2 seperate statements?|||
Do you have to multiply G with D before dividing it by 1000? Maybe you miss a parenthesis with G*D.
W1 = ( W0^(1/3) + ((G * D)/1000) )^3
|||Thanks, but tried that with no improvement. I've read elsewhere that "SQL server does not have a datatype for fractions and instead uses an approximate datatype such as float, where rounding will occur. Float has a maximum precision of 15 digits." Does this mean that trying to get a cube root with POWER is indeterminate? Has anyone by any chance written a function for getting cube roots? At the moment POWER is giving me 5 as a cube root of 150 - which is way out.
set
@.value=POWER(150,1.00/3.00)|||Here are the relevant links and by Microsoft docs there is no indication the POWER function in SQL Server is none deterministic and yes it is FLOAT dependent. The links below will take you in the right direction. Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms173773.aspx
http://msdn2.microsoft.com/en-us/library/ms174276.aspx
http://msdn2.microsoft.com/en-us/library/ms177516.aspx
Caddre,
I'm having a 'duh' moment! Thanks for that- making the value being acted on convertible to float (150.00 instead of 150) gave the result we were looking for.
|||
ashaig:
Caddre,
I'm having a 'duh' moment! Thanks for that- making the value being acted on convertible to float (150.00 instead of 150) gave the result we were looking for.
I am glad I could help and you are not alone most people don't know that most are FLOAT dependent.
|||Try this one, you can always change the precision to max.
POWER(CONVERT(float, 150), 0.333)
Wednesday, March 21, 2012
Posting an entire XML document to SQL Server
How do I post an entire XML document to SQL Server in a such a way that when
it gets posted, I can process it with a Stored Procedure?
My specific application is using BizTalk 2004 to send an entire document to
SQL Server without using the "updategram" schema. I just want to send an
entire document via a BizTalk 2004 SQL Send Port.
Thanks,
Mike Jansen
Sr. Software Developer
Prime ProData, Inc.
North Canton, Ohio USA
(mjansen) (at) (primepro-com)
In SQL Server 2000, define your stored proc with a parameter of type NTEXT
or TEXT and use your favorite provider to pass the XML (in an encoding that
is compatible with either Unicode (NTEXT) or your server code page (TEXT)).
Best regards
Michael
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:uinFg5SvEHA.4020@.TK2MSFTNGP10.phx.gbl...
> How do I post an entire XML document to SQL Server in a such a way that
> when
> it gets posted, I can process it with a Stored Procedure?
> My specific application is using BizTalk 2004 to send an entire document
> to
> SQL Server without using the "updategram" schema. I just want to send an
> entire document via a BizTalk 2004 SQL Send Port.
> Thanks,
> Mike Jansen
> Sr. Software Developer
> Prime ProData, Inc.
> North Canton, Ohio USA
> (mjansen) (at) (primepro-com)
>
it gets posted, I can process it with a Stored Procedure?
My specific application is using BizTalk 2004 to send an entire document to
SQL Server without using the "updategram" schema. I just want to send an
entire document via a BizTalk 2004 SQL Send Port.
Thanks,
Mike Jansen
Sr. Software Developer
Prime ProData, Inc.
North Canton, Ohio USA
(mjansen) (at) (primepro-com)
In SQL Server 2000, define your stored proc with a parameter of type NTEXT
or TEXT and use your favorite provider to pass the XML (in an encoding that
is compatible with either Unicode (NTEXT) or your server code page (TEXT)).
Best regards
Michael
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:uinFg5SvEHA.4020@.TK2MSFTNGP10.phx.gbl...
> How do I post an entire XML document to SQL Server in a such a way that
> when
> it gets posted, I can process it with a Stored Procedure?
> My specific application is using BizTalk 2004 to send an entire document
> to
> SQL Server without using the "updategram" schema. I just want to send an
> entire document via a BizTalk 2004 SQL Send Port.
> Thanks,
> Mike Jansen
> Sr. Software Developer
> Prime ProData, Inc.
> North Canton, Ohio USA
> (mjansen) (at) (primepro-com)
>
Postage Calculation goes where?
When a customer proceeds to the checkout of the store I'm working on, the customer's ID is sent to a database stored procedure which returns the details of the order based on the shopping cart and customer account details stored in the database.
Should I have the stored procedure calculate the postage and packing for the order or should I wait till the order details are returned to the asp.net code, do some calculations, and then update the order?
Having this code in the asp.net page would not only require a trip to the server to do the update but also a trip to retrieve p&p information used for the calculations. These extra trips are my concern.
Any thoughts?::These extra trips are my concern.
Why? Are you working for Amazon?
How many hundred CHECKOUTS do you have per minute?
IMHO this is a non-issue, at least with some caching.
Monday, March 12, 2012
Possible?: Count(*) returned by EXEC
Hi all,
I have a stored procdure which does a select and returns the records
directly -i.e. Not in output parameters e.g:
CREATE PROCEDURE up_SelectRecs(@.ProductName nvarchar(30)) AS
SELECT *
FROM MyTable
WHERE [Name]=@.ProductName
In another stored procedure I need to do the following:
SELECT COUNT(*)
FROM MyTable
WHERE [Name]=@.ProductName
As the select queries are actually a lot more complex that this, I'd
rather not duplicate the select code in 2 sp's to save the maintenance
effort - I'm looking for a way to execute the first procedure from the
second and just count the records returned - something like:
SELECT Count(*)
FROM EXEC up_SelectRecs @.ProductName
Any way to achieve this?
Thanks all
--James"James" <Jamesmitchard@.yahoo.co.uk> wrote in message
news:19d01a84.0501261535.1d7c6dd7@.posting.google.c om...
> Hi all,
> I have a stored procdure which does a select and returns the records
> directly -i.e. Not in output parameters e.g:
> CREATE PROCEDURE up_SelectRecs(@.ProductName nvarchar(30)) AS
> SELECT *
> FROM MyTable
> WHERE [Name]=@.ProductName
> In another stored procedure I need to do the following:
> SELECT COUNT(*)
> FROM MyTable
> WHERE [Name]=@.ProductName
> As the select queries are actually a lot more complex that this, I'd
> rather not duplicate the select code in 2 sp's to save the maintenance
> effort - I'm looking for a way to execute the first procedure from the
> second and just count the records returned - something like:
> SELECT Count(*)
> FROM EXEC up_SelectRecs @.ProductName
> Any way to achieve this?
> Thanks all
> --James
See here:
http://www.sommarskog.se/share_data.html
If you have SQL 2000 (you didn't mention which version you have), a
table-valued UDF would probably work well in your case:
select * from dbo.MyFunc(@.ProductName)
select count(*) from dbo.MyFunc(@.ProductName)
Simon
Possible to specifying a list as a parameter to a stored procedure ?
I have been converting a VB 6 applications database queries into SQL Server 2000 stored procedures and have come up against a problem where lists are used in search conditions...For example a list of accounts are selected based on their account currency ID being equal to 1, 5, or 7. In the VB 6 query the string looks like...
SELECT tblAccount.txtName FROM tblAccount WHERE (tblAccount.intCurrencyId IN(1, 5, 7))
The list could contain a single value or upto 20 values. Is it possible to pass the currency list (i.e "1, 5, 7, ...") as a parameter to the stored procedure?
Any help much appreciated!The answer is, "maybe".
It depends on your needs for performance. Please take a look at this discussion for more details.
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=15403
Essentially, if you pass the list as a comma delimited string, then you will either need to parse it inside the query or use it in a dynamic SQL query within the SP. Your other choice is to take all 20 objects as single parameters to your SP. Your IN statement would then be a large set of OR statements for each of the 20 items.
I hope this helps,
CC|||I have to write a lot of stored procedures for reports. I always declare my parameters like
level1 varchar(255)
I then look at the incoming value. I use charindex to find ';' or ',' If I find either I know I have to use "in" in the where clause and format the values correctly.
IF CHARINDEX(';',@.LEVEL1)>0
BEGIN
SET @.LEVEL1=REPLACE('('+''''+REPLACE(@.LEVEL1,';',''''+ ','+'''')+''''+')',' ','')
END
If it is prompt is equal to '%' for all I make my where clause a like, if it is a single value I use equal. The trick to making this so flexible is to use dynamic sql. If you don't know what the parameter will be before hand it seems to be the best way.
' AND ISNULL(T1.DIVISION,'+''''+'NONE'+''''+')' +
case when CHARINDEX(',',@.LEVEL1)>0 then 'in '+@.LEVEL1
else
CASE @.LEVEL1 when '%' THEN ' LIKE '+''''+@.LEVEL1+''''+'+'+''''+'%'+''''
ELSE '='+''''+@.LEVEL1+'''' END
END
Sorry for the formating. It looks better in the actual file|||Oh and I just realized something. If the incoming value is '%' then do not add a condition for it in the dynamic where clause. It makes zero sense to add anything to a where clause if you don't need to.
SELECT tblAccount.txtName FROM tblAccount WHERE (tblAccount.intCurrencyId IN(1, 5, 7))
The list could contain a single value or upto 20 values. Is it possible to pass the currency list (i.e "1, 5, 7, ...") as a parameter to the stored procedure?
Any help much appreciated!The answer is, "maybe".
It depends on your needs for performance. Please take a look at this discussion for more details.
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=15403
Essentially, if you pass the list as a comma delimited string, then you will either need to parse it inside the query or use it in a dynamic SQL query within the SP. Your other choice is to take all 20 objects as single parameters to your SP. Your IN statement would then be a large set of OR statements for each of the 20 items.
I hope this helps,
CC|||I have to write a lot of stored procedures for reports. I always declare my parameters like
level1 varchar(255)
I then look at the incoming value. I use charindex to find ';' or ',' If I find either I know I have to use "in" in the where clause and format the values correctly.
IF CHARINDEX(';',@.LEVEL1)>0
BEGIN
SET @.LEVEL1=REPLACE('('+''''+REPLACE(@.LEVEL1,';',''''+ ','+'''')+''''+')',' ','')
END
If it is prompt is equal to '%' for all I make my where clause a like, if it is a single value I use equal. The trick to making this so flexible is to use dynamic sql. If you don't know what the parameter will be before hand it seems to be the best way.
' AND ISNULL(T1.DIVISION,'+''''+'NONE'+''''+')' +
case when CHARINDEX(',',@.LEVEL1)>0 then 'in '+@.LEVEL1
else
CASE @.LEVEL1 when '%' THEN ' LIKE '+''''+@.LEVEL1+''''+'+'+''''+'%'+''''
ELSE '='+''''+@.LEVEL1+'''' END
END
Sorry for the formating. It looks better in the actual file|||Oh and I just realized something. If the incoming value is '%' then do not add a condition for it in the dynamic where clause. It makes zero sense to add anything to a where clause if you don't need to.
Friday, March 9, 2012
Possible to pass table as parametr to stored procedure??
In one stored procedure i create temporary table and fill it with data,
after this i call to other stored procedure to which i want pass table as
parameter.
How can i do it '
Message posted via http://www.webservertalk.comNo there is not way without looping through a resultset and giving the
seperate fields as variables or as an "array".
http://vyaskn.tripod.com/passing_ar..._procedures.htm
But you could create a (gobal) temp table from another procedure and call
this temp table at the destination procedure.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"JB via webservertalk.com" <forum@.nospam.webservertalk.com> schrieb im Newsbeitrag
news:c902681f62414d1cb7c0a8dca5cea43e@.SQ
webservertalk.com...
> In one stored procedure i create temporary table and fill it with data,
> after this i call to other stored procedure to which i want pass table as
> parameter.
> How can i do it '
> --
> Message posted via http://www.webservertalk.com|||See if this helps: http://www.sommarskog.se/share_data.html
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"JB via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:c902681f62414d1cb7c0a8dca5cea43e@.SQ
webservertalk.com...
> In one stored procedure i create temporary table and fill it with data,
> after this i call to other stored procedure to which i want pass table as
> parameter.
> How can i do it '
> --
> Message posted via http://www.webservertalk.com|||Hi
Or you can create a Table using the Table Data Type and pass that between
SP's.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OJND4SbTFHA.2128@.TK2MSFTNGP15.phx.gbl...
> No there is not way without looping through a resultset and giving the
> seperate fields as variables or as an "array".
> http://vyaskn.tripod.com/passing_ar..._procedures.htm
>
> But you could create a (gobal) temp table from another procedure and call
> this temp table at the destination procedure.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "JB via webservertalk.com" <forum@.nospam.webservertalk.com> schrieb im
> Newsbeitrag news:c902681f62414d1cb7c0a8dca5cea43e@.SQ
webservertalk.com...
>|||Thanks for replay.
In my sp i define:
DECLARE @.myTable TABLE(
[col1] [int] ,
[col2] [bit] ,
..........
..........
if i understand i can do the same and to declare as @.@.myTable
and from now i can use this tabel in all proc?
Message posted via http://www.webservertalk.com|||thanks however it not help me. (i've read it before).
Message posted via http://www.webservertalk.com|||can u give a sample? i don't really undersatand how to achieve it
Message posted via http://www.webservertalk.com|||You cannot pass a table variable as parameter to stored procedures. But if
you look at the article I posted in my previous post, you will find a way
out
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"JB via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:053b03bed2fe474fb8510840a324d760@.SQ
webservertalk.com...
> can u give a sample? i don't really undersatand how to achieve it
> --
> Message posted via http://www.webservertalk.com|||O yes.. u r right my mistake, sorry
Thank u very much.
Message posted via http://www.webservertalk.com|||Hi
See if it helps you
Use Northwind
CREATE PROC mysp2
AS
SELECT * FROM #Test
GO
CREATE PROC mysp1--Main stored procedure
@.Ord INT
AS
CREATE TABLE #Test
(
cust CHAR(5)
)
INSERT INTO #Test SELECT Customerid FROM Orders WHERE OrderId=@.Ord
EXEC mysp2 --We will use the #Test temp table within mysp2
--Usage
EXEC mysp1 10248
DROP PROC mysp1,mysp2
"JB via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:fcd2f7925c90404592916ac6c168bcc5@.SQ
webservertalk.com...
> thanks however it not help me. (i've read it before).
> --
> Message posted via http://www.webservertalk.com
after this i call to other stored procedure to which i want pass table as
parameter.
How can i do it '
Message posted via http://www.webservertalk.comNo there is not way without looping through a resultset and giving the
seperate fields as variables or as an "array".
http://vyaskn.tripod.com/passing_ar..._procedures.htm
But you could create a (gobal) temp table from another procedure and call
this temp table at the destination procedure.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"JB via webservertalk.com" <forum@.nospam.webservertalk.com> schrieb im Newsbeitrag
news:c902681f62414d1cb7c0a8dca5cea43e@.SQ
webservertalk.com...
> In one stored procedure i create temporary table and fill it with data,
> after this i call to other stored procedure to which i want pass table as
> parameter.
> How can i do it '
> --
> Message posted via http://www.webservertalk.com|||See if this helps: http://www.sommarskog.se/share_data.html
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"JB via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:c902681f62414d1cb7c0a8dca5cea43e@.SQ
webservertalk.com...
> In one stored procedure i create temporary table and fill it with data,
> after this i call to other stored procedure to which i want pass table as
> parameter.
> How can i do it '
> --
> Message posted via http://www.webservertalk.com|||Hi
Or you can create a Table using the Table Data Type and pass that between
SP's.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OJND4SbTFHA.2128@.TK2MSFTNGP15.phx.gbl...
> No there is not way without looping through a resultset and giving the
> seperate fields as variables or as an "array".
> http://vyaskn.tripod.com/passing_ar..._procedures.htm
>
> But you could create a (gobal) temp table from another procedure and call
> this temp table at the destination procedure.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "JB via webservertalk.com" <forum@.nospam.webservertalk.com> schrieb im
> Newsbeitrag news:c902681f62414d1cb7c0a8dca5cea43e@.SQ
webservertalk.com...
>|||Thanks for replay.
In my sp i define:
DECLARE @.myTable TABLE(
[col1] [int] ,
[col2] [bit] ,
..........
..........
if i understand i can do the same and to declare as @.@.myTable
and from now i can use this tabel in all proc?
Message posted via http://www.webservertalk.com|||thanks however it not help me. (i've read it before).
Message posted via http://www.webservertalk.com|||can u give a sample? i don't really undersatand how to achieve it
Message posted via http://www.webservertalk.com|||You cannot pass a table variable as parameter to stored procedures. But if
you look at the article I posted in my previous post, you will find a way
out
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"JB via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:053b03bed2fe474fb8510840a324d760@.SQ
webservertalk.com...
> can u give a sample? i don't really undersatand how to achieve it
> --
> Message posted via http://www.webservertalk.com|||O yes.. u r right my mistake, sorry
Thank u very much.
Message posted via http://www.webservertalk.com|||Hi
See if it helps you
Use Northwind
CREATE PROC mysp2
AS
SELECT * FROM #Test
GO
CREATE PROC mysp1--Main stored procedure
@.Ord INT
AS
CREATE TABLE #Test
(
cust CHAR(5)
)
INSERT INTO #Test SELECT Customerid FROM Orders WHERE OrderId=@.Ord
EXEC mysp2 --We will use the #Test temp table within mysp2
--Usage
EXEC mysp1 10248
DROP PROC mysp1,mysp2
"JB via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:fcd2f7925c90404592916ac6c168bcc5@.SQ
webservertalk.com...
> thanks however it not help me. (i've read it before).
> --
> Message posted via http://www.webservertalk.com
Possible to overuse WITH (NOLOCK)?
I'm working with a process that is initially invoked from VB, but runs
through a set of stored procedures. The first four levels of the calls are
simply gathering data that the other levels below will need and aggregating
some of it. This data is used to determine if further processing is needed or
not, and if so, calls the next level. W/o going into too much detail, since
the upper levels are simply data gathering, I've been rather liberal in my
application of WITH (NOLOCK) on virtually every table that is used in the
look up.
Here's the reason why, initially the process took two hours to run and it
didn't fully complete. Since my users are not in a Snickers commercial, I can
hardly expect them to wait that long for this process. So I need to make it
as fast as possible. I've gone through and changed all my CURSORS to select
loops (mucho improvement) and also dumped #tempTables in favor of table
variables (more improvement)... and then I added WITH (NOLOCK) on every
normal (non temp or derrived - ie inner selects) table in all queries... I
notice more improvement again. I also rearranged a few queries where some
calculations were done breaking out the data into two parts and changed a
LEFT JOIN to an INNER JOIN....
IS the ANY thing else I can do to squeeze some performance out of this
monster? I haven't run it through profiler yet, but that's the next obvious
choice.
The other half of the query is: is it possible to go too far with WITH
(NOLOCK)? Or is what I've done reasonable?
=chris
On Tue, 28 Feb 2006 13:16:26 -0800, CAnderson wrote:
>The other half of the query is: is it possible to go too far with WITH
>(NOLOCK)? Or is what I've done reasonable?
Hi Chris,
I'll start here.
If performance gain is your sole target, you can use this hint freely.
But if you want correct reports, beware. As another poster in this
groups once said it: WITH (NOLOCK) can give you incorrect results at a
blinding speed.
SQL Server will normally lock data that has been changed but not yet
committed. Other queries have to wait for this lock to be released
before they can access the data. With WITH (NOLOCK), you ignore the
lock, which means that you'll read the uncommitted data. This saves lots
of time if there are locks, and it even saves some time if there are no
locks since you bypass the overhead of checking for locks.
But the downside is that you can read uncommitted data. Suppose that I
update a column to one billion dollars negative. Some sanity check in a
trigger will probably catch this and rollback my transaction - but if
your report runs in the periode between my submitting the update and the
trigger rolling it back, your report will be off by a billion dollars.
Another example - suppose a transaction is debiting your account and
crediting mine. Your report runs before my account is credited, but
after yours is debited. Now, the totals on the left-hand side of your
report won't match those on the right-hand side and all bookkeepers,
accountants and controllers in your company will go crazy.
>I've gone through and changed all my CURSORS to select
>loops (mucho improvement) and also dumped #tempTables in favor of table
>variables (more improvement)...
(snip)
>IS the ANY thing else I can do to squeeze some performance out of this
>monster?
Revisit your code. Try to get rid of all cursors, all select loops, all
temp tables and all table variables. SQL Server is optimized for
declarative, set-based processing. All procedural, row-based code (both
cursor and select loop; both temp table and table variable) will almost
always be slower than one single or a short batch of set-based queries.
Check if all your queries use indexes. Add indexes where necessary.
Remove unused indexes. Pay special attention to your choice of clustered
index.
If you need more specific help than this, you'll need to give more
specific information. Check out www.aspfaq.com/5006.
Hugo Kornelis, SQL Server MVP
|||CAnderson [MVP] wrote:
> I'm working with a process that is initially invoked from VB, but runs
> through a set of stored procedures. The first four levels of the
> calls are simply gathering data that the other levels below will need
> and aggregating some of it. This data is used to determine if further
> processing is needed or not, and if so, calls the next level. W/o
> going into too much detail, since the upper levels are simply data
> gathering, I've been rather liberal in my application of WITH
> (NOLOCK) on virtually every table that is used in the look up.
> Here's the reason why, initially the process took two hours to run
> and it didn't fully complete. Since my users are not in a Snickers
> commercial, I can hardly expect them to wait that long for this
> process. So I need to make it as fast as possible. I've gone through
> and changed all my CURSORS to select loops (mucho improvement) and
> also dumped #tempTables in favor of table variables (more
> improvement)... and then I added WITH (NOLOCK) on every normal (non
> temp or derrived - ie inner selects) table in all queries... I
> notice more improvement again. I also rearranged a few queries where
> some calculations were done breaking out the data into two parts and
> changed a LEFT JOIN to an INNER JOIN....
> IS the ANY thing else I can do to squeeze some performance out of this
> monster? I haven't run it through profiler yet, but that's the next
> obvious choice.
> The other half of the query is: is it possible to go too far with WITH
> (NOLOCK)? Or is what I've done reasonable?
> =chris
I think th eapproach you should reall ybe taking here is performance
tuning the SQL running in this long running batch. After you've
diagnosed all the SQL and you know things are running as fast as
possible, then start looking at hints as a possible way to improve
performance. As Hugo clearly demonstrates, not all business requirements
can tolerate a NOLOCK hint. Your business needs to decide if this is
tolerable or not.
Regarding your comment: "dumped #tempTables in favor of table variables
(more improvement)". I don't know what your temp/table vars look like,
but temp tables have some major performance advantages with larger data
sets because you can create indexes to support the queries run off the
tables. Also, if you're looping through the temp tables, pulling one row
at a time, use a SELECT TOP 1 to pull in the data for the row. If you're
joining to the temp tables or deleting from them, an index will likely
help.
David Gugick - SQL Server MVP
Quest Software
|||Thanks David & Hugo... pretty much confirmed what I had already suspected. As
far as reading uncomitted data when using nolock, that's not a problem as the
data isn't updated until the very last step. I really wish I had the time to
go back and re-engineer the process properly in VB code rather than in SQL,
but it's a 5yr old process and if I change it now the account managers will
have a fit, and so will the client as they've been waiting long enough as it
is for this "to work" - it works as originaly built, but not like they want
it to be... ah, clients... where would we be w/o them. What I'm finding is
that 6 times out of 10 it's lightning fast... it's the 4 times that takes the
longest (the 4 times will take more time than the 6 did total.)
I'm not sure I could explain the process w/o giving any trade secrets (drat
those NDA's) or w/o making heads explode as I try to explain the industry, so
I won't bore people w/ the details.
Yes, idealy I wish I could do it in batches of select statement but the
business rules are getting in the way - I truly regret building this the way
I did 5 yrs ago... If I only knew then what I know now... but hindsight is
20/20 right?
At any rate, thanks for your help, I'm going to expore the possibility of
converting the process into VB code, and if I can do it in one day (the
project manager is out today) then I might attempt it. Otherwise, it'll just
have to go as it is for now.
Cheers,
Chris
|||On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:
>Thanks David & Hugo... pretty much confirmed what I had already suspected. As
>far as reading uncomitted data when using nolock, that's not a problem as the
>data isn't updated until the very last step.
Hi Chris,
Not by that process, it isn't. But are you equally sure that no other
users are accessing and changing the data at the same time?
(snip)
>At any rate, thanks for your help, I'm going to expore the possibility of
>converting the process into VB code, and if I can do it in one day (the
>project manager is out today) then I might attempt it. Otherwise, it'll just
>have to go as it is for now.
Good luck. And let us know if you need further assistance!
Hugo Kornelis, SQL Server MVP
|||"Hugo Kornelis" wrote:
> On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:
>
> Hi Chris,
> Not by that process, it isn't. But are you equally sure that no other
> users are accessing and changing the data at the same time?
>
Positive... The nature of the data being manipulated, as well as standard
business practicess in the industry, practicaly require that only one person
is going to be accessing and updating the data at any given time - even if
the process wasn't being automated.
> (snip)
>
> Good luck. And let us know if you need further assistance!
> --
> Hugo Kornelis, SQL Server MVP
>
I was able to port much of the business logic over to VB, leaving SQL to
just select queries and two action queries.... there was "some" improvement,
but it still isn't where I need to it be... I found several cases where I
was running through some logic where I didn't need to and short-circuited it
on that condition. And then I find out (from the QA dept no less) that the
Proj Manager has decided to ship it as it is with a note that says we are
working on the performance issue. Which means I can now take my time to do
this right rather than slapping it together like I did. Will wonders never
cease.
-Chris
through a set of stored procedures. The first four levels of the calls are
simply gathering data that the other levels below will need and aggregating
some of it. This data is used to determine if further processing is needed or
not, and if so, calls the next level. W/o going into too much detail, since
the upper levels are simply data gathering, I've been rather liberal in my
application of WITH (NOLOCK) on virtually every table that is used in the
look up.
Here's the reason why, initially the process took two hours to run and it
didn't fully complete. Since my users are not in a Snickers commercial, I can
hardly expect them to wait that long for this process. So I need to make it
as fast as possible. I've gone through and changed all my CURSORS to select
loops (mucho improvement) and also dumped #tempTables in favor of table
variables (more improvement)... and then I added WITH (NOLOCK) on every
normal (non temp or derrived - ie inner selects) table in all queries... I
notice more improvement again. I also rearranged a few queries where some
calculations were done breaking out the data into two parts and changed a
LEFT JOIN to an INNER JOIN....
IS the ANY thing else I can do to squeeze some performance out of this
monster? I haven't run it through profiler yet, but that's the next obvious
choice.
The other half of the query is: is it possible to go too far with WITH
(NOLOCK)? Or is what I've done reasonable?
=chris
On Tue, 28 Feb 2006 13:16:26 -0800, CAnderson wrote:
>The other half of the query is: is it possible to go too far with WITH
>(NOLOCK)? Or is what I've done reasonable?
Hi Chris,
I'll start here.
If performance gain is your sole target, you can use this hint freely.
But if you want correct reports, beware. As another poster in this
groups once said it: WITH (NOLOCK) can give you incorrect results at a
blinding speed.
SQL Server will normally lock data that has been changed but not yet
committed. Other queries have to wait for this lock to be released
before they can access the data. With WITH (NOLOCK), you ignore the
lock, which means that you'll read the uncommitted data. This saves lots
of time if there are locks, and it even saves some time if there are no
locks since you bypass the overhead of checking for locks.
But the downside is that you can read uncommitted data. Suppose that I
update a column to one billion dollars negative. Some sanity check in a
trigger will probably catch this and rollback my transaction - but if
your report runs in the periode between my submitting the update and the
trigger rolling it back, your report will be off by a billion dollars.
Another example - suppose a transaction is debiting your account and
crediting mine. Your report runs before my account is credited, but
after yours is debited. Now, the totals on the left-hand side of your
report won't match those on the right-hand side and all bookkeepers,
accountants and controllers in your company will go crazy.
>I've gone through and changed all my CURSORS to select
>loops (mucho improvement) and also dumped #tempTables in favor of table
>variables (more improvement)...
(snip)
>IS the ANY thing else I can do to squeeze some performance out of this
>monster?
Revisit your code. Try to get rid of all cursors, all select loops, all
temp tables and all table variables. SQL Server is optimized for
declarative, set-based processing. All procedural, row-based code (both
cursor and select loop; both temp table and table variable) will almost
always be slower than one single or a short batch of set-based queries.
Check if all your queries use indexes. Add indexes where necessary.
Remove unused indexes. Pay special attention to your choice of clustered
index.
If you need more specific help than this, you'll need to give more
specific information. Check out www.aspfaq.com/5006.
Hugo Kornelis, SQL Server MVP
|||CAnderson [MVP] wrote:
> I'm working with a process that is initially invoked from VB, but runs
> through a set of stored procedures. The first four levels of the
> calls are simply gathering data that the other levels below will need
> and aggregating some of it. This data is used to determine if further
> processing is needed or not, and if so, calls the next level. W/o
> going into too much detail, since the upper levels are simply data
> gathering, I've been rather liberal in my application of WITH
> (NOLOCK) on virtually every table that is used in the look up.
> Here's the reason why, initially the process took two hours to run
> and it didn't fully complete. Since my users are not in a Snickers
> commercial, I can hardly expect them to wait that long for this
> process. So I need to make it as fast as possible. I've gone through
> and changed all my CURSORS to select loops (mucho improvement) and
> also dumped #tempTables in favor of table variables (more
> improvement)... and then I added WITH (NOLOCK) on every normal (non
> temp or derrived - ie inner selects) table in all queries... I
> notice more improvement again. I also rearranged a few queries where
> some calculations were done breaking out the data into two parts and
> changed a LEFT JOIN to an INNER JOIN....
> IS the ANY thing else I can do to squeeze some performance out of this
> monster? I haven't run it through profiler yet, but that's the next
> obvious choice.
> The other half of the query is: is it possible to go too far with WITH
> (NOLOCK)? Or is what I've done reasonable?
> =chris
I think th eapproach you should reall ybe taking here is performance
tuning the SQL running in this long running batch. After you've
diagnosed all the SQL and you know things are running as fast as
possible, then start looking at hints as a possible way to improve
performance. As Hugo clearly demonstrates, not all business requirements
can tolerate a NOLOCK hint. Your business needs to decide if this is
tolerable or not.
Regarding your comment: "dumped #tempTables in favor of table variables
(more improvement)". I don't know what your temp/table vars look like,
but temp tables have some major performance advantages with larger data
sets because you can create indexes to support the queries run off the
tables. Also, if you're looping through the temp tables, pulling one row
at a time, use a SELECT TOP 1 to pull in the data for the row. If you're
joining to the temp tables or deleting from them, an index will likely
help.
David Gugick - SQL Server MVP
Quest Software
|||Thanks David & Hugo... pretty much confirmed what I had already suspected. As
far as reading uncomitted data when using nolock, that's not a problem as the
data isn't updated until the very last step. I really wish I had the time to
go back and re-engineer the process properly in VB code rather than in SQL,
but it's a 5yr old process and if I change it now the account managers will
have a fit, and so will the client as they've been waiting long enough as it
is for this "to work" - it works as originaly built, but not like they want
it to be... ah, clients... where would we be w/o them. What I'm finding is
that 6 times out of 10 it's lightning fast... it's the 4 times that takes the
longest (the 4 times will take more time than the 6 did total.)
I'm not sure I could explain the process w/o giving any trade secrets (drat
those NDA's) or w/o making heads explode as I try to explain the industry, so
I won't bore people w/ the details.
Yes, idealy I wish I could do it in batches of select statement but the
business rules are getting in the way - I truly regret building this the way
I did 5 yrs ago... If I only knew then what I know now... but hindsight is
20/20 right?
At any rate, thanks for your help, I'm going to expore the possibility of
converting the process into VB code, and if I can do it in one day (the
project manager is out today) then I might attempt it. Otherwise, it'll just
have to go as it is for now.
Cheers,
Chris
|||On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:
>Thanks David & Hugo... pretty much confirmed what I had already suspected. As
>far as reading uncomitted data when using nolock, that's not a problem as the
>data isn't updated until the very last step.
Hi Chris,
Not by that process, it isn't. But are you equally sure that no other
users are accessing and changing the data at the same time?
(snip)
>At any rate, thanks for your help, I'm going to expore the possibility of
>converting the process into VB code, and if I can do it in one day (the
>project manager is out today) then I might attempt it. Otherwise, it'll just
>have to go as it is for now.
Good luck. And let us know if you need further assistance!
Hugo Kornelis, SQL Server MVP
|||"Hugo Kornelis" wrote:
> On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:
>
> Hi Chris,
> Not by that process, it isn't. But are you equally sure that no other
> users are accessing and changing the data at the same time?
>
Positive... The nature of the data being manipulated, as well as standard
business practicess in the industry, practicaly require that only one person
is going to be accessing and updating the data at any given time - even if
the process wasn't being automated.
> (snip)
>
> Good luck. And let us know if you need further assistance!
> --
> Hugo Kornelis, SQL Server MVP
>
I was able to port much of the business logic over to VB, leaving SQL to
just select queries and two action queries.... there was "some" improvement,
but it still isn't where I need to it be... I found several cases where I
was running through some logic where I didn't need to and short-circuited it
on that condition. And then I find out (from the QA dept no less) that the
Proj Manager has decided to ship it as it is with a note that says we are
working on the performance issue. Which means I can now take my time to do
this right rather than slapping it together like I did. Will wonders never
cease.
-Chris
Possible to overuse WITH (NOLOCK)?
I'm working with a process that is initially invoked from VB, but runs
through a set of stored procedures. The first four levels of the calls are
simply gathering data that the other levels below will need and aggregating
some of it. This data is used to determine if further processing is needed o
r
not, and if so, calls the next level. W/o going into too much detail, since
the upper levels are simply data gathering, I've been rather liberal in my
application of WITH (NOLOCK) on virtually every table that is used in the
look up.
Here's the reason why, initially the process took two hours to run and it
didn't fully complete. Since my users are not in a Snickers commercial, I ca
n
hardly expect them to wait that long for this process. So I need to make it
as fast as possible. I've gone through and changed all my CURSORS to select
loops (mucho improvement) and also dumped #tempTables in favor of table
variables (more improvement)... and then I added WITH (NOLOCK) on every
normal (non temp or derrived - ie inner selects) table in all queries... I
notice more improvement again. I also rearranged a few queries where some
calculations were done breaking out the data into two parts and changed a
LEFT JOIN to an INNER JOIN....
IS the ANY thing else I can do to squeeze some performance out of this
monster? I haven't run it through profiler yet, but that's the next obvious
choice.
The other half of the query is: is it possible to go too far with WITH
(NOLOCK)? Or is what I've done reasonable?
=chrisOn Tue, 28 Feb 2006 13:16:26 -0800, CAnderson wrote:
>The other half of the query is: is it possible to go too far with WITH
>(NOLOCK)? Or is what I've done reasonable?
Hi Chris,
I'll start here.
If performance gain is your sole target, you can use this hint freely.
But if you want correct reports, beware. As another poster in this
groups once said it: WITH (NOLOCK) can give you incorrect results at a
blinding speed.
SQL Server will normally lock data that has been changed but not yet
committed. Other queries have to wait for this lock to be released
before they can access the data. With WITH (NOLOCK), you ignore the
lock, which means that you'll read the uncommitted data. This saves lots
of time if there are locks, and it even saves some time if there are no
locks since you bypass the overhead of checking for locks.
But the downside is that you can read uncommitted data. Suppose that I
update a column to one billion dollars negative. Some sanity check in a
trigger will probably catch this and rollback my transaction - but if
your report runs in the periode between my submitting the update and the
trigger rolling it back, your report will be off by a billion dollars.
Another example - suppose a transaction is debiting your account and
crediting mine. Your report runs before my account is credited, but
after yours is debited. Now, the totals on the left-hand side of your
report won't match those on the right-hand side and all bookkeepers,
accountants and controllers in your company will go crazy.
>I've gone through and changed all my CURSORS to select
>loops (mucho improvement) and also dumped #tempTables in favor of table
>variables (more improvement)...
(snip)
>IS the ANY thing else I can do to squeeze some performance out of this
>monster?
Revisit your code. Try to get rid of all cursors, all select loops, all
temp tables and all table variables. SQL Server is optimized for
declarative, set-based processing. All procedural, row-based code (both
cursor and select loop; both temp table and table variable) will almost
always be slower than one single or a short batch of set-based queries.
Check if all your queries use indexes. Add indexes where necessary.
Remove unused indexes. Pay special attention to your choice of clustered
index.
If you need more specific help than this, you'll need to give more
specific information. Check out www.aspfaq.com/5006.
Hugo Kornelis, SQL Server MVP|||CAnderson [MVP] wrote:
> I'm working with a process that is initially invoked from VB, but runs
> through a set of stored procedures. The first four levels of the
> calls are simply gathering data that the other levels below will need
> and aggregating some of it. This data is used to determine if further
> processing is needed or not, and if so, calls the next level. W/o
> going into too much detail, since the upper levels are simply data
> gathering, I've been rather liberal in my application of WITH
> (NOLOCK) on virtually every table that is used in the look up.
> Here's the reason why, initially the process took two hours to run
> and it didn't fully complete. Since my users are not in a Snickers
> commercial, I can hardly expect them to wait that long for this
> process. So I need to make it as fast as possible. I've gone through
> and changed all my CURSORS to select loops (mucho improvement) and
> also dumped #tempTables in favor of table variables (more
> improvement)... and then I added WITH (NOLOCK) on every normal (non
> temp or derrived - ie inner selects) table in all queries... I
> notice more improvement again. I also rearranged a few queries where
> some calculations were done breaking out the data into two parts and
> changed a LEFT JOIN to an INNER JOIN....
> IS the ANY thing else I can do to squeeze some performance out of this
> monster? I haven't run it through profiler yet, but that's the next
> obvious choice.
> The other half of the query is: is it possible to go too far with WITH
> (NOLOCK)? Or is what I've done reasonable?
> =chris
I think th eapproach you should reall ybe taking here is performance
tuning the SQL running in this long running batch. After you've
diagnosed all the SQL and you know things are running as fast as
possible, then start looking at hints as a possible way to improve
performance. As Hugo clearly demonstrates, not all business requirements
can tolerate a NOLOCK hint. Your business needs to decide if this is
tolerable or not.
Regarding your comment: "dumped #tempTables in favor of table variables
(more improvement)". I don't know what your temp/table vars look like,
but temp tables have some major performance advantages with larger data
sets because you can create indexes to support the queries run off the
tables. Also, if you're looping through the temp tables, pulling one row
at a time, use a SELECT TOP 1 to pull in the data for the row. If you're
joining to the temp tables or deleting from them, an index will likely
help.
David Gugick - SQL Server MVP
Quest Software|||Thanks David & Hugo... pretty much confirmed what I had already suspected. A
s
far as reading uncomitted data when using nolock, that's not a problem as th
e
data isn't updated until the very last step. I really wish I had the time to
go back and re-engineer the process properly in VB code rather than in SQL,
but it's a 5yr old process and if I change it now the account managers will
have a fit, and so will the client as they've been waiting long enough as it
is for this "to work" - it works as originaly built, but not like they want
it to be... ah, clients... where would we be w/o them. What I'm finding is
that 6 times out of 10 it's lightning fast... it's the 4 times that takes th
e
longest (the 4 times will take more time than the 6 did total.)
I'm not sure I could explain the process w/o giving any trade secrets (drat
those NDA's) or w/o making heads explode as I try to explain the industry, s
o
I won't bore people w/ the details.
Yes, idealy I wish I could do it in batches of select statement but the
business rules are getting in the way - I truly regret building this the way
I did 5 yrs ago... If I only knew then what I know now... but hindsight is
20/20 right?
At any rate, thanks for your help, I'm going to expore the possibility of
converting the process into VB code, and if I can do it in one day (the
project manager is out today) then I might attempt it. Otherwise, it'll just
have to go as it is for now.
Cheers,
Chris|||On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:
>Thanks David & Hugo... pretty much confirmed what I had already suspected.
As
>far as reading uncomitted data when using nolock, that's not a problem as t
he
>data isn't updated until the very last step.
Hi Chris,
Not by that process, it isn't. But are you equally sure that no other
users are accessing and changing the data at the same time?
(snip)
>At any rate, thanks for your help, I'm going to expore the possibility of
>converting the process into VB code, and if I can do it in one day (the
>project manager is out today) then I might attempt it. Otherwise, it'll jus
t
>have to go as it is for now.
Good luck. And let us know if you need further assistance!
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" wrote:
> On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:
>
> Hi Chris,
> Not by that process, it isn't. But are you equally sure that no other
> users are accessing and changing the data at the same time?
>
Positive... The nature of the data being manipulated, as well as standard
business practicess in the industry, practicaly require that only one person
is going to be accessing and updating the data at any given time - even if
the process wasn't being automated.
> (snip)
>
> Good luck. And let us know if you need further assistance!
> --
> Hugo Kornelis, SQL Server MVP
>
I was able to port much of the business logic over to VB, leaving SQL to
just select queries and two action queries.... there was "some" improvement
,
but it still isn't where I need to it be... I found several cases where I
was running through some logic where I didn't need to and short-circuited it
on that condition. And then I find out (from the QA dept no less) that the
Proj Manager has decided to ship it as it is with a note that says we are
working on the performance issue. Which means I can now take my time to do
this right rather than slapping it together like I did. Will wonders never
cease.
-Chris
through a set of stored procedures. The first four levels of the calls are
simply gathering data that the other levels below will need and aggregating
some of it. This data is used to determine if further processing is needed o
r
not, and if so, calls the next level. W/o going into too much detail, since
the upper levels are simply data gathering, I've been rather liberal in my
application of WITH (NOLOCK) on virtually every table that is used in the
look up.
Here's the reason why, initially the process took two hours to run and it
didn't fully complete. Since my users are not in a Snickers commercial, I ca
n
hardly expect them to wait that long for this process. So I need to make it
as fast as possible. I've gone through and changed all my CURSORS to select
loops (mucho improvement) and also dumped #tempTables in favor of table
variables (more improvement)... and then I added WITH (NOLOCK) on every
normal (non temp or derrived - ie inner selects) table in all queries... I
notice more improvement again. I also rearranged a few queries where some
calculations were done breaking out the data into two parts and changed a
LEFT JOIN to an INNER JOIN....
IS the ANY thing else I can do to squeeze some performance out of this
monster? I haven't run it through profiler yet, but that's the next obvious
choice.
The other half of the query is: is it possible to go too far with WITH
(NOLOCK)? Or is what I've done reasonable?
=chrisOn Tue, 28 Feb 2006 13:16:26 -0800, CAnderson wrote:
>The other half of the query is: is it possible to go too far with WITH
>(NOLOCK)? Or is what I've done reasonable?
Hi Chris,
I'll start here.
If performance gain is your sole target, you can use this hint freely.
But if you want correct reports, beware. As another poster in this
groups once said it: WITH (NOLOCK) can give you incorrect results at a
blinding speed.
SQL Server will normally lock data that has been changed but not yet
committed. Other queries have to wait for this lock to be released
before they can access the data. With WITH (NOLOCK), you ignore the
lock, which means that you'll read the uncommitted data. This saves lots
of time if there are locks, and it even saves some time if there are no
locks since you bypass the overhead of checking for locks.
But the downside is that you can read uncommitted data. Suppose that I
update a column to one billion dollars negative. Some sanity check in a
trigger will probably catch this and rollback my transaction - but if
your report runs in the periode between my submitting the update and the
trigger rolling it back, your report will be off by a billion dollars.
Another example - suppose a transaction is debiting your account and
crediting mine. Your report runs before my account is credited, but
after yours is debited. Now, the totals on the left-hand side of your
report won't match those on the right-hand side and all bookkeepers,
accountants and controllers in your company will go crazy.
>I've gone through and changed all my CURSORS to select
>loops (mucho improvement) and also dumped #tempTables in favor of table
>variables (more improvement)...
(snip)
>IS the ANY thing else I can do to squeeze some performance out of this
>monster?
Revisit your code. Try to get rid of all cursors, all select loops, all
temp tables and all table variables. SQL Server is optimized for
declarative, set-based processing. All procedural, row-based code (both
cursor and select loop; both temp table and table variable) will almost
always be slower than one single or a short batch of set-based queries.
Check if all your queries use indexes. Add indexes where necessary.
Remove unused indexes. Pay special attention to your choice of clustered
index.
If you need more specific help than this, you'll need to give more
specific information. Check out www.aspfaq.com/5006.
Hugo Kornelis, SQL Server MVP|||CAnderson [MVP] wrote:
> I'm working with a process that is initially invoked from VB, but runs
> through a set of stored procedures. The first four levels of the
> calls are simply gathering data that the other levels below will need
> and aggregating some of it. This data is used to determine if further
> processing is needed or not, and if so, calls the next level. W/o
> going into too much detail, since the upper levels are simply data
> gathering, I've been rather liberal in my application of WITH
> (NOLOCK) on virtually every table that is used in the look up.
> Here's the reason why, initially the process took two hours to run
> and it didn't fully complete. Since my users are not in a Snickers
> commercial, I can hardly expect them to wait that long for this
> process. So I need to make it as fast as possible. I've gone through
> and changed all my CURSORS to select loops (mucho improvement) and
> also dumped #tempTables in favor of table variables (more
> improvement)... and then I added WITH (NOLOCK) on every normal (non
> temp or derrived - ie inner selects) table in all queries... I
> notice more improvement again. I also rearranged a few queries where
> some calculations were done breaking out the data into two parts and
> changed a LEFT JOIN to an INNER JOIN....
> IS the ANY thing else I can do to squeeze some performance out of this
> monster? I haven't run it through profiler yet, but that's the next
> obvious choice.
> The other half of the query is: is it possible to go too far with WITH
> (NOLOCK)? Or is what I've done reasonable?
> =chris
I think th eapproach you should reall ybe taking here is performance
tuning the SQL running in this long running batch. After you've
diagnosed all the SQL and you know things are running as fast as
possible, then start looking at hints as a possible way to improve
performance. As Hugo clearly demonstrates, not all business requirements
can tolerate a NOLOCK hint. Your business needs to decide if this is
tolerable or not.
Regarding your comment: "dumped #tempTables in favor of table variables
(more improvement)". I don't know what your temp/table vars look like,
but temp tables have some major performance advantages with larger data
sets because you can create indexes to support the queries run off the
tables. Also, if you're looping through the temp tables, pulling one row
at a time, use a SELECT TOP 1 to pull in the data for the row. If you're
joining to the temp tables or deleting from them, an index will likely
help.
David Gugick - SQL Server MVP
Quest Software|||Thanks David & Hugo... pretty much confirmed what I had already suspected. A
s
far as reading uncomitted data when using nolock, that's not a problem as th
e
data isn't updated until the very last step. I really wish I had the time to
go back and re-engineer the process properly in VB code rather than in SQL,
but it's a 5yr old process and if I change it now the account managers will
have a fit, and so will the client as they've been waiting long enough as it
is for this "to work" - it works as originaly built, but not like they want
it to be... ah, clients... where would we be w/o them. What I'm finding is
that 6 times out of 10 it's lightning fast... it's the 4 times that takes th
e
longest (the 4 times will take more time than the 6 did total.)
I'm not sure I could explain the process w/o giving any trade secrets (drat
those NDA's) or w/o making heads explode as I try to explain the industry, s
o
I won't bore people w/ the details.
Yes, idealy I wish I could do it in batches of select statement but the
business rules are getting in the way - I truly regret building this the way
I did 5 yrs ago... If I only knew then what I know now... but hindsight is
20/20 right?
At any rate, thanks for your help, I'm going to expore the possibility of
converting the process into VB code, and if I can do it in one day (the
project manager is out today) then I might attempt it. Otherwise, it'll just
have to go as it is for now.
Cheers,
Chris|||On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:
>Thanks David & Hugo... pretty much confirmed what I had already suspected.
As
>far as reading uncomitted data when using nolock, that's not a problem as t
he
>data isn't updated until the very last step.
Hi Chris,
Not by that process, it isn't. But are you equally sure that no other
users are accessing and changing the data at the same time?
(snip)
>At any rate, thanks for your help, I'm going to expore the possibility of
>converting the process into VB code, and if I can do it in one day (the
>project manager is out today) then I might attempt it. Otherwise, it'll jus
t
>have to go as it is for now.
Good luck. And let us know if you need further assistance!
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" wrote:
> On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:
>
> Hi Chris,
> Not by that process, it isn't. But are you equally sure that no other
> users are accessing and changing the data at the same time?
>
Positive... The nature of the data being manipulated, as well as standard
business practicess in the industry, practicaly require that only one person
is going to be accessing and updating the data at any given time - even if
the process wasn't being automated.
> (snip)
>
> Good luck. And let us know if you need further assistance!
> --
> Hugo Kornelis, SQL Server MVP
>
I was able to port much of the business logic over to VB, leaving SQL to
just select queries and two action queries.... there was "some" improvement
,
but it still isn't where I need to it be... I found several cases where I
was running through some logic where I didn't need to and short-circuited it
on that condition. And then I find out (from the QA dept no less) that the
Proj Manager has decided to ship it as it is with a note that says we are
working on the performance issue. Which means I can now take my time to do
this right rather than slapping it together like I did. Will wonders never
cease.
-Chris
Possible to overuse WITH (NOLOCK)?
I'm working with a process that is initially invoked from VB, but runs
through a set of stored procedures. The first four levels of the calls are
simply gathering data that the other levels below will need and aggregating
some of it. This data is used to determine if further processing is needed or
not, and if so, calls the next level. W/o going into too much detail, since
the upper levels are simply data gathering, I've been rather liberal in my
application of WITH (NOLOCK) on virtually every table that is used in the
look up.
Here's the reason why, initially the process took two hours to run and it
didn't fully complete. Since my users are not in a Snickers commercial, I can
hardly expect them to wait that long for this process. So I need to make it
as fast as possible. I've gone through and changed all my CURSORS to select
loops (mucho improvement) and also dumped #tempTables in favor of table
variables (more improvement)... and then I added WITH (NOLOCK) on every
normal (non temp or derrived - ie inner selects) table in all queries... I
notice more improvement again. I also rearranged a few queries where some
calculations were done breaking out the data into two parts and changed a
LEFT JOIN to an INNER JOIN....
IS the ANY thing else I can do to squeeze some performance out of this
monster? I haven't run it through profiler yet, but that's the next obvious
choice.
The other half of the query is: is it possible to go too far with WITH
(NOLOCK)? Or is what I've done reasonable?
=chrisOn Tue, 28 Feb 2006 13:16:26 -0800, CAnderson wrote:
>The other half of the query is: is it possible to go too far with WITH
>(NOLOCK)? Or is what I've done reasonable?
Hi Chris,
I'll start here.
If performance gain is your sole target, you can use this hint freely.
But if you want correct reports, beware. As another poster in this
groups once said it: WITH (NOLOCK) can give you incorrect results at a
blinding speed.
SQL Server will normally lock data that has been changed but not yet
committed. Other queries have to wait for this lock to be released
before they can access the data. With WITH (NOLOCK), you ignore the
lock, which means that you'll read the uncommitted data. This saves lots
of time if there are locks, and it even saves some time if there are no
locks since you bypass the overhead of checking for locks.
But the downside is that you can read uncommitted data. Suppose that I
update a column to one billion dollars negative. Some sanity check in a
trigger will probably catch this and rollback my transaction - but if
your report runs in the periode between my submitting the update and the
trigger rolling it back, your report will be off by a billion dollars.
Another example - suppose a transaction is debiting your account and
crediting mine. Your report runs before my account is credited, but
after yours is debited. Now, the totals on the left-hand side of your
report won't match those on the right-hand side and all bookkeepers,
accountants and controllers in your company will go crazy.
>I've gone through and changed all my CURSORS to select
>loops (mucho improvement) and also dumped #tempTables in favor of table
>variables (more improvement)...
(snip)
>IS the ANY thing else I can do to squeeze some performance out of this
>monster?
Revisit your code. Try to get rid of all cursors, all select loops, all
temp tables and all table variables. SQL Server is optimized for
declarative, set-based processing. All procedural, row-based code (both
cursor and select loop; both temp table and table variable) will almost
always be slower than one single or a short batch of set-based queries.
Check if all your queries use indexes. Add indexes where necessary.
Remove unused indexes. Pay special attention to your choice of clustered
index.
If you need more specific help than this, you'll need to give more
specific information. Check out www.aspfaq.com/5006.
--
Hugo Kornelis, SQL Server MVP|||CAnderson [MVP] wrote:
> I'm working with a process that is initially invoked from VB, but runs
> through a set of stored procedures. The first four levels of the
> calls are simply gathering data that the other levels below will need
> and aggregating some of it. This data is used to determine if further
> processing is needed or not, and if so, calls the next level. W/o
> going into too much detail, since the upper levels are simply data
> gathering, I've been rather liberal in my application of WITH
> (NOLOCK) on virtually every table that is used in the look up.
> Here's the reason why, initially the process took two hours to run
> and it didn't fully complete. Since my users are not in a Snickers
> commercial, I can hardly expect them to wait that long for this
> process. So I need to make it as fast as possible. I've gone through
> and changed all my CURSORS to select loops (mucho improvement) and
> also dumped #tempTables in favor of table variables (more
> improvement)... and then I added WITH (NOLOCK) on every normal (non
> temp or derrived - ie inner selects) table in all queries... I
> notice more improvement again. I also rearranged a few queries where
> some calculations were done breaking out the data into two parts and
> changed a LEFT JOIN to an INNER JOIN....
> IS the ANY thing else I can do to squeeze some performance out of this
> monster? I haven't run it through profiler yet, but that's the next
> obvious choice.
> The other half of the query is: is it possible to go too far with WITH
> (NOLOCK)? Or is what I've done reasonable?
> =chris
I think th eapproach you should reall ybe taking here is performance
tuning the SQL running in this long running batch. After you've
diagnosed all the SQL and you know things are running as fast as
possible, then start looking at hints as a possible way to improve
performance. As Hugo clearly demonstrates, not all business requirements
can tolerate a NOLOCK hint. Your business needs to decide if this is
tolerable or not.
Regarding your comment: "dumped #tempTables in favor of table variables
(more improvement)". I don't know what your temp/table vars look like,
but temp tables have some major performance advantages with larger data
sets because you can create indexes to support the queries run off the
tables. Also, if you're looping through the temp tables, pulling one row
at a time, use a SELECT TOP 1 to pull in the data for the row. If you're
joining to the temp tables or deleting from them, an index will likely
help.
David Gugick - SQL Server MVP
Quest Software|||Thanks David & Hugo... pretty much confirmed what I had already suspected. As
far as reading uncomitted data when using nolock, that's not a problem as the
data isn't updated until the very last step. I really wish I had the time to
go back and re-engineer the process properly in VB code rather than in SQL,
but it's a 5yr old process and if I change it now the account managers will
have a fit, and so will the client as they've been waiting long enough as it
is for this "to work" - it works as originaly built, but not like they want
it to be... ah, clients... where would we be w/o them. What I'm finding is
that 6 times out of 10 it's lightning fast... it's the 4 times that takes the
longest (the 4 times will take more time than the 6 did total.)
I'm not sure I could explain the process w/o giving any trade secrets (drat
those NDA's) or w/o making heads explode as I try to explain the industry, so
I won't bore people w/ the details.
Yes, idealy I wish I could do it in batches of select statement but the
business rules are getting in the way - I truly regret building this the way
I did 5 yrs ago... If I only knew then what I know now... but hindsight is
20/20 right?
At any rate, thanks for your help, I'm going to expore the possibility of
converting the process into VB code, and if I can do it in one day (the
project manager is out today) then I might attempt it. Otherwise, it'll just
have to go as it is for now.
Cheers,
Chris|||On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:
>Thanks David & Hugo... pretty much confirmed what I had already suspected. As
>far as reading uncomitted data when using nolock, that's not a problem as the
>data isn't updated until the very last step.
Hi Chris,
Not by that process, it isn't. But are you equally sure that no other
users are accessing and changing the data at the same time?
(snip)
>At any rate, thanks for your help, I'm going to expore the possibility of
>converting the process into VB code, and if I can do it in one day (the
>project manager is out today) then I might attempt it. Otherwise, it'll just
>have to go as it is for now.
Good luck. And let us know if you need further assistance!
--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" wrote:
> On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:
> >Thanks David & Hugo... pretty much confirmed what I had already suspected. As
> >far as reading uncomitted data when using nolock, that's not a problem as the
> >data isn't updated until the very last step.
> Hi Chris,
> Not by that process, it isn't. But are you equally sure that no other
> users are accessing and changing the data at the same time?
>
Positive... The nature of the data being manipulated, as well as standard
business practicess in the industry, practicaly require that only one person
is going to be accessing and updating the data at any given time - even if
the process wasn't being automated.
> (snip)
> >At any rate, thanks for your help, I'm going to expore the possibility of
> >converting the process into VB code, and if I can do it in one day (the
> >project manager is out today) then I might attempt it. Otherwise, it'll just
> >have to go as it is for now.
> Good luck. And let us know if you need further assistance!
> --
> Hugo Kornelis, SQL Server MVP
>
I was able to port much of the business logic over to VB, leaving SQL to
just select queries and two action queries.... there was "some" improvement,
but it still isn't where I need to it be... I found several cases where I
was running through some logic where I didn't need to and short-circuited it
on that condition. And then I find out (from the QA dept no less) that the
Proj Manager has decided to ship it as it is with a note that says we are
working on the performance issue. Which means I can now take my time to do
this right rather than slapping it together like I did. Will wonders never
cease.
-Chris
through a set of stored procedures. The first four levels of the calls are
simply gathering data that the other levels below will need and aggregating
some of it. This data is used to determine if further processing is needed or
not, and if so, calls the next level. W/o going into too much detail, since
the upper levels are simply data gathering, I've been rather liberal in my
application of WITH (NOLOCK) on virtually every table that is used in the
look up.
Here's the reason why, initially the process took two hours to run and it
didn't fully complete. Since my users are not in a Snickers commercial, I can
hardly expect them to wait that long for this process. So I need to make it
as fast as possible. I've gone through and changed all my CURSORS to select
loops (mucho improvement) and also dumped #tempTables in favor of table
variables (more improvement)... and then I added WITH (NOLOCK) on every
normal (non temp or derrived - ie inner selects) table in all queries... I
notice more improvement again. I also rearranged a few queries where some
calculations were done breaking out the data into two parts and changed a
LEFT JOIN to an INNER JOIN....
IS the ANY thing else I can do to squeeze some performance out of this
monster? I haven't run it through profiler yet, but that's the next obvious
choice.
The other half of the query is: is it possible to go too far with WITH
(NOLOCK)? Or is what I've done reasonable?
=chrisOn Tue, 28 Feb 2006 13:16:26 -0800, CAnderson wrote:
>The other half of the query is: is it possible to go too far with WITH
>(NOLOCK)? Or is what I've done reasonable?
Hi Chris,
I'll start here.
If performance gain is your sole target, you can use this hint freely.
But if you want correct reports, beware. As another poster in this
groups once said it: WITH (NOLOCK) can give you incorrect results at a
blinding speed.
SQL Server will normally lock data that has been changed but not yet
committed. Other queries have to wait for this lock to be released
before they can access the data. With WITH (NOLOCK), you ignore the
lock, which means that you'll read the uncommitted data. This saves lots
of time if there are locks, and it even saves some time if there are no
locks since you bypass the overhead of checking for locks.
But the downside is that you can read uncommitted data. Suppose that I
update a column to one billion dollars negative. Some sanity check in a
trigger will probably catch this and rollback my transaction - but if
your report runs in the periode between my submitting the update and the
trigger rolling it back, your report will be off by a billion dollars.
Another example - suppose a transaction is debiting your account and
crediting mine. Your report runs before my account is credited, but
after yours is debited. Now, the totals on the left-hand side of your
report won't match those on the right-hand side and all bookkeepers,
accountants and controllers in your company will go crazy.
>I've gone through and changed all my CURSORS to select
>loops (mucho improvement) and also dumped #tempTables in favor of table
>variables (more improvement)...
(snip)
>IS the ANY thing else I can do to squeeze some performance out of this
>monster?
Revisit your code. Try to get rid of all cursors, all select loops, all
temp tables and all table variables. SQL Server is optimized for
declarative, set-based processing. All procedural, row-based code (both
cursor and select loop; both temp table and table variable) will almost
always be slower than one single or a short batch of set-based queries.
Check if all your queries use indexes. Add indexes where necessary.
Remove unused indexes. Pay special attention to your choice of clustered
index.
If you need more specific help than this, you'll need to give more
specific information. Check out www.aspfaq.com/5006.
--
Hugo Kornelis, SQL Server MVP|||CAnderson [MVP] wrote:
> I'm working with a process that is initially invoked from VB, but runs
> through a set of stored procedures. The first four levels of the
> calls are simply gathering data that the other levels below will need
> and aggregating some of it. This data is used to determine if further
> processing is needed or not, and if so, calls the next level. W/o
> going into too much detail, since the upper levels are simply data
> gathering, I've been rather liberal in my application of WITH
> (NOLOCK) on virtually every table that is used in the look up.
> Here's the reason why, initially the process took two hours to run
> and it didn't fully complete. Since my users are not in a Snickers
> commercial, I can hardly expect them to wait that long for this
> process. So I need to make it as fast as possible. I've gone through
> and changed all my CURSORS to select loops (mucho improvement) and
> also dumped #tempTables in favor of table variables (more
> improvement)... and then I added WITH (NOLOCK) on every normal (non
> temp or derrived - ie inner selects) table in all queries... I
> notice more improvement again. I also rearranged a few queries where
> some calculations were done breaking out the data into two parts and
> changed a LEFT JOIN to an INNER JOIN....
> IS the ANY thing else I can do to squeeze some performance out of this
> monster? I haven't run it through profiler yet, but that's the next
> obvious choice.
> The other half of the query is: is it possible to go too far with WITH
> (NOLOCK)? Or is what I've done reasonable?
> =chris
I think th eapproach you should reall ybe taking here is performance
tuning the SQL running in this long running batch. After you've
diagnosed all the SQL and you know things are running as fast as
possible, then start looking at hints as a possible way to improve
performance. As Hugo clearly demonstrates, not all business requirements
can tolerate a NOLOCK hint. Your business needs to decide if this is
tolerable or not.
Regarding your comment: "dumped #tempTables in favor of table variables
(more improvement)". I don't know what your temp/table vars look like,
but temp tables have some major performance advantages with larger data
sets because you can create indexes to support the queries run off the
tables. Also, if you're looping through the temp tables, pulling one row
at a time, use a SELECT TOP 1 to pull in the data for the row. If you're
joining to the temp tables or deleting from them, an index will likely
help.
David Gugick - SQL Server MVP
Quest Software|||Thanks David & Hugo... pretty much confirmed what I had already suspected. As
far as reading uncomitted data when using nolock, that's not a problem as the
data isn't updated until the very last step. I really wish I had the time to
go back and re-engineer the process properly in VB code rather than in SQL,
but it's a 5yr old process and if I change it now the account managers will
have a fit, and so will the client as they've been waiting long enough as it
is for this "to work" - it works as originaly built, but not like they want
it to be... ah, clients... where would we be w/o them. What I'm finding is
that 6 times out of 10 it's lightning fast... it's the 4 times that takes the
longest (the 4 times will take more time than the 6 did total.)
I'm not sure I could explain the process w/o giving any trade secrets (drat
those NDA's) or w/o making heads explode as I try to explain the industry, so
I won't bore people w/ the details.
Yes, idealy I wish I could do it in batches of select statement but the
business rules are getting in the way - I truly regret building this the way
I did 5 yrs ago... If I only knew then what I know now... but hindsight is
20/20 right?
At any rate, thanks for your help, I'm going to expore the possibility of
converting the process into VB code, and if I can do it in one day (the
project manager is out today) then I might attempt it. Otherwise, it'll just
have to go as it is for now.
Cheers,
Chris|||On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:
>Thanks David & Hugo... pretty much confirmed what I had already suspected. As
>far as reading uncomitted data when using nolock, that's not a problem as the
>data isn't updated until the very last step.
Hi Chris,
Not by that process, it isn't. But are you equally sure that no other
users are accessing and changing the data at the same time?
(snip)
>At any rate, thanks for your help, I'm going to expore the possibility of
>converting the process into VB code, and if I can do it in one day (the
>project manager is out today) then I might attempt it. Otherwise, it'll just
>have to go as it is for now.
Good luck. And let us know if you need further assistance!
--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" wrote:
> On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:
> >Thanks David & Hugo... pretty much confirmed what I had already suspected. As
> >far as reading uncomitted data when using nolock, that's not a problem as the
> >data isn't updated until the very last step.
> Hi Chris,
> Not by that process, it isn't. But are you equally sure that no other
> users are accessing and changing the data at the same time?
>
Positive... The nature of the data being manipulated, as well as standard
business practicess in the industry, practicaly require that only one person
is going to be accessing and updating the data at any given time - even if
the process wasn't being automated.
> (snip)
> >At any rate, thanks for your help, I'm going to expore the possibility of
> >converting the process into VB code, and if I can do it in one day (the
> >project manager is out today) then I might attempt it. Otherwise, it'll just
> >have to go as it is for now.
> Good luck. And let us know if you need further assistance!
> --
> Hugo Kornelis, SQL Server MVP
>
I was able to port much of the business logic over to VB, leaving SQL to
just select queries and two action queries.... there was "some" improvement,
but it still isn't where I need to it be... I found several cases where I
was running through some logic where I didn't need to and short-circuited it
on that condition. And then I find out (from the QA dept no less) that the
Proj Manager has decided to ship it as it is with a note that says we are
working on the performance issue. Which means I can now take my time to do
this right rather than slapping it together like I did. Will wonders never
cease.
-Chris
Possible to make a procedure "sleep" or run periodically?
Is it possible to put a "sleep" command in a stored procedure.
I would like to write a procedure that periodically checks some things in my
database, but I don't want them to suck up a lot of resources. If I could
write a procedure that runs in a loop with a 5 minute sleep command, that
would do it.
Or is this the wrong approach in SQL Server? I realize I could set up a
separate scheduled task that executes a procedure using ISQL. Is this a
better method?
Rick Harrison.I don't know enough about the particual need to know if there is a better
way or is this is a good way... I've done things like this in the past using
the WAITFOR command. You'll find info in BOL...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Rick Harrrison" <rick@.knowware.com> wrote in message
news:uGKLBEQxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Is it possible to put a "sleep" command in a stored procedure.
> I would like to write a procedure that periodically checks some things in
my
> database, but I don't want them to suck up a lot of resources. If I could
> write a procedure that runs in a loop with a 5 minute sleep command, that
> would do it.
> Or is this the wrong approach in SQL Server? I realize I could set up a
> separate scheduled task that executes a procedure using ISQL. Is this a
> better method?
> Rick Harrison.
>|||Use SQL Server Agent to schedule the stored procedure?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rick Harrrison" <rick@.knowware.com> wrote in message
news:uGKLBEQxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Is it possible to put a "sleep" command in a stored procedure.
> I would like to write a procedure that periodically checks some things in
my
> database, but I don't want them to suck up a lot of resources. If I could
> write a procedure that runs in a loop with a 5 minute sleep command, that
> would do it.
> Or is this the wrong approach in SQL Server? I realize I could set up a
> separate scheduled task that executes a procedure using ISQL. Is this a
> better method?
> Rick Harrison.
>|||Hi Rick,
Thank you for using MSDN Newsgroup! I think Aaron and Brian have point out ways to run the
stored procedure (SP) on a schedule basis.
Based on my experience, it's better to use scheduled job IF you want to run your SP in a 5
minutes loop (it does handle that gracefully). The WAITFOR statement is also a good method
to implement this, however, it can only work after a specified time interval has passed or a
specified time is reached, unless you use an explicit loop in your SP.
Additionally, the stored procedure will remain suspended until the WAITFOR completes, this
may affect the performance. However, it can easily be embedded in your script and is not
Agent related. So it depends on your scenario and the needs you want to meet.
Rick, does this answer your question? If there is anything more I can do to assist you, please
feel free to post it in the group.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks! WAITFOR is what I was looking for. I did searches using "sleep" and
"pause", but I didn't think of "wait" or "delay".
Rick.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:Osu%23o1QxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> I don't know enough about the particual need to know if there is a better
> way or is this is a good way... I've done things like this in the past
using
> the WAITFOR command. You'll find info in BOL...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Rick Harrrison" <rick@.knowware.com> wrote in message
> news:uGKLBEQxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> > Is it possible to put a "sleep" command in a stored procedure.
> >
> > I would like to write a procedure that periodically checks some things
in
> my
> > database, but I don't want them to suck up a lot of resources. If I
could
> > write a procedure that runs in a loop with a 5 minute sleep command,
that
> > would do it.
> >
> > Or is this the wrong approach in SQL Server? I realize I could set up a
> > separate scheduled task that executes a procedure using ISQL. Is this a
> > better method?
> >
> > Rick Harrison.
> >
> >
>|||Thanks! Somehow I had overlooked this feature. I knew it was possible to
schedule maintenance plans, which I do, but I didn't know you could run any
procedure on a schedule.
Rick.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OEAHM7QxDHA.1088@.tk2msftngp13.phx.gbl...
> Use SQL Server Agent to schedule the stored procedure?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Rick Harrrison" <rick@.knowware.com> wrote in message
> news:uGKLBEQxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> > Is it possible to put a "sleep" command in a stored procedure.
> >
> > I would like to write a procedure that periodically checks some things
in
> my
> > database, but I don't want them to suck up a lot of resources. If I
could
> > write a procedure that runs in a loop with a 5 minute sleep command,
that
> > would do it.
> >
> > Or is this the wrong approach in SQL Server? I realize I could set up a
> > separate scheduled task that executes a procedure using ISQL. Is this a
> > better method?
> >
> > Rick Harrison.
> >
> >
>|||Both techniques will be useful for me. Thank you.
""Billy Yao [MSFT]"" <v-binyao@.online.microsoft.com> wrote in message
news:FTG3OcWxDHA.424@.cpmsftngxa07.phx.gbl...
> Hi Rick,
> Thank you for using MSDN Newsgroup! I think Aaron and Brian have point out
ways to run the
> stored procedure (SP) on a schedule basis.
> Based on my experience, it's better to use scheduled job IF you want to
run your SP in a 5
> minutes loop (it does handle that gracefully). The WAITFOR statement is
also a good method
> to implement this, however, it can only work after a specified time
interval has passed or a
> specified time is reached, unless you use an explicit loop in your SP.
> Additionally, the stored procedure will remain suspended until the WAITFOR
completes, this
> may affect the performance. However, it can easily be embedded in your
script and is not
> Agent related. So it depends on your scenario and the needs you want to
meet.
> Rick, does this answer your question? If there is anything more I can do
to assist you, please
> feel free to post it in the group.
> Best regards,
> Billy Yao
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
>
I would like to write a procedure that periodically checks some things in my
database, but I don't want them to suck up a lot of resources. If I could
write a procedure that runs in a loop with a 5 minute sleep command, that
would do it.
Or is this the wrong approach in SQL Server? I realize I could set up a
separate scheduled task that executes a procedure using ISQL. Is this a
better method?
Rick Harrison.I don't know enough about the particual need to know if there is a better
way or is this is a good way... I've done things like this in the past using
the WAITFOR command. You'll find info in BOL...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Rick Harrrison" <rick@.knowware.com> wrote in message
news:uGKLBEQxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Is it possible to put a "sleep" command in a stored procedure.
> I would like to write a procedure that periodically checks some things in
my
> database, but I don't want them to suck up a lot of resources. If I could
> write a procedure that runs in a loop with a 5 minute sleep command, that
> would do it.
> Or is this the wrong approach in SQL Server? I realize I could set up a
> separate scheduled task that executes a procedure using ISQL. Is this a
> better method?
> Rick Harrison.
>|||Use SQL Server Agent to schedule the stored procedure?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rick Harrrison" <rick@.knowware.com> wrote in message
news:uGKLBEQxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Is it possible to put a "sleep" command in a stored procedure.
> I would like to write a procedure that periodically checks some things in
my
> database, but I don't want them to suck up a lot of resources. If I could
> write a procedure that runs in a loop with a 5 minute sleep command, that
> would do it.
> Or is this the wrong approach in SQL Server? I realize I could set up a
> separate scheduled task that executes a procedure using ISQL. Is this a
> better method?
> Rick Harrison.
>|||Hi Rick,
Thank you for using MSDN Newsgroup! I think Aaron and Brian have point out ways to run the
stored procedure (SP) on a schedule basis.
Based on my experience, it's better to use scheduled job IF you want to run your SP in a 5
minutes loop (it does handle that gracefully). The WAITFOR statement is also a good method
to implement this, however, it can only work after a specified time interval has passed or a
specified time is reached, unless you use an explicit loop in your SP.
Additionally, the stored procedure will remain suspended until the WAITFOR completes, this
may affect the performance. However, it can easily be embedded in your script and is not
Agent related. So it depends on your scenario and the needs you want to meet.
Rick, does this answer your question? If there is anything more I can do to assist you, please
feel free to post it in the group.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks! WAITFOR is what I was looking for. I did searches using "sleep" and
"pause", but I didn't think of "wait" or "delay".
Rick.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:Osu%23o1QxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> I don't know enough about the particual need to know if there is a better
> way or is this is a good way... I've done things like this in the past
using
> the WAITFOR command. You'll find info in BOL...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Rick Harrrison" <rick@.knowware.com> wrote in message
> news:uGKLBEQxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> > Is it possible to put a "sleep" command in a stored procedure.
> >
> > I would like to write a procedure that periodically checks some things
in
> my
> > database, but I don't want them to suck up a lot of resources. If I
could
> > write a procedure that runs in a loop with a 5 minute sleep command,
that
> > would do it.
> >
> > Or is this the wrong approach in SQL Server? I realize I could set up a
> > separate scheduled task that executes a procedure using ISQL. Is this a
> > better method?
> >
> > Rick Harrison.
> >
> >
>|||Thanks! Somehow I had overlooked this feature. I knew it was possible to
schedule maintenance plans, which I do, but I didn't know you could run any
procedure on a schedule.
Rick.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OEAHM7QxDHA.1088@.tk2msftngp13.phx.gbl...
> Use SQL Server Agent to schedule the stored procedure?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Rick Harrrison" <rick@.knowware.com> wrote in message
> news:uGKLBEQxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> > Is it possible to put a "sleep" command in a stored procedure.
> >
> > I would like to write a procedure that periodically checks some things
in
> my
> > database, but I don't want them to suck up a lot of resources. If I
could
> > write a procedure that runs in a loop with a 5 minute sleep command,
that
> > would do it.
> >
> > Or is this the wrong approach in SQL Server? I realize I could set up a
> > separate scheduled task that executes a procedure using ISQL. Is this a
> > better method?
> >
> > Rick Harrison.
> >
> >
>|||Both techniques will be useful for me. Thank you.
""Billy Yao [MSFT]"" <v-binyao@.online.microsoft.com> wrote in message
news:FTG3OcWxDHA.424@.cpmsftngxa07.phx.gbl...
> Hi Rick,
> Thank you for using MSDN Newsgroup! I think Aaron and Brian have point out
ways to run the
> stored procedure (SP) on a schedule basis.
> Based on my experience, it's better to use scheduled job IF you want to
run your SP in a 5
> minutes loop (it does handle that gracefully). The WAITFOR statement is
also a good method
> to implement this, however, it can only work after a specified time
interval has passed or a
> specified time is reached, unless you use an explicit loop in your SP.
> Additionally, the stored procedure will remain suspended until the WAITFOR
completes, this
> may affect the performance. However, it can easily be embedded in your
script and is not
> Agent related. So it depends on your scenario and the needs you want to
meet.
> Rick, does this answer your question? If there is anything more I can do
to assist you, please
> feel free to post it in the group.
> Best regards,
> Billy Yao
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
>
Possible to lock a row within a stored procedure in SQL Server 2000?
Hi All,
I have a table that holds pregenerated member IDs.
This table is used to assign an available member id to web site
visitors who choose to register with the site
So, conceptually the process has been, from the site (in ASP), to:
- select the top record from the members table where the assigned flag
= 0
- update the row with details about the new member and change the
assigned flag to 1
- return the selected member id to the web page
Now I'm dealing with the idea that there may be brief, high traffic
periods of registration, so I'm trying to build a method (stored
procedure?) that will ensure the same member id isn't returned by the
select statement if more than 1 request to register happens at the
same instant.
So, my question is, is there a way, once a record has been selected,
to exclude that record from other select requests, within the bounds
of a stored procedure?
ie:
- select statement is executed and row is instantly locked; any other
select statement running at that exact moment will receive a different
row returned and sill similarly lock it, ad nauseum for as many
simultaneous select statements as take place
- row is updated with details and flag is updated to indicate the
member id is no longer unassigned
- row is released for general purposes etc
If what I'm suggesting above isn't practical, can anyone help me
identify a different way of achieving the same result?
Any help immensely, immensely appreciated!
Much warmth,
MurrayM Wells <planetquirky@.planetthoughtful.org> wrote in message news:<j1o220p3uauokindou45q9l305eq8au783@.4ax.com>...
> Hi All,
> I have a table that holds pregenerated member IDs.
> This table is used to assign an available member id to web site
> visitors who choose to register with the site
> So, conceptually the process has been, from the site (in ASP), to:
> - select the top record from the members table where the assigned flag
> = 0
> - update the row with details about the new member and change the
> assigned flag to 1
> - return the selected member id to the web page
> Now I'm dealing with the idea that there may be brief, high traffic
> periods of registration, so I'm trying to build a method (stored
> procedure?) that will ensure the same member id isn't returned by the
> select statement if more than 1 request to register happens at the
> same instant.
> So, my question is, is there a way, once a record has been selected,
> to exclude that record from other select requests, within the bounds
> of a stored procedure?
> ie:
> - select statement is executed and row is instantly locked; any other
> select statement running at that exact moment will receive a different
> row returned and sill similarly lock it, ad nauseum for as many
> simultaneous select statements as take place
> - row is updated with details and flag is updated to indicate the
> member id is no longer unassigned
> - row is released for general purposes etc
> If what I'm suggesting above isn't practical, can anyone help me
> identify a different way of achieving the same result?
> Any help immensely, immensely appreciated!
> Much warmth,
> Murray
It's not clear from your description why you need to generate the IDs
in advance - a simpler approach might be to use an IDENTITY column,
and insert directly into the members table for a new registration. You
can then return the system-generated value to the client using
scope_identity(). This assumes, of course, that the membership ID is
simply a number, with no other meaning:
create table dbo.Members (
MemberID int identity(1,1) primary key,
FirstName varchar(50) not null,
LastName varchar(50) not null
)
go
insert into dbo.Members (FirstName, LastName)
values ('Murray', 'Wells')
select 'Your ID is: ' + cast(scope_identity() as char(2))
go
If you do need some control over the value of the membership ID, then
one of these approaches might be suitable:
http://groups.google.com/groups?q=s...uewin.ch&rnum=1
If this doesn't help, you may want to post the structure (CREATE
TABLE) of your members table, along with some sample data, and explain
exactly what you want to return to the client.
Simon|||On 4 Feb 2004 23:44:05 -0800, sql@.hayes.ch (Simon Hayes) wrote:
>M Wells <planetquirky@.planetthoughtful.org> wrote in message news:<j1o220p3uauokindou45q9l305eq8au783@.4ax.com>...
>> Hi All,
>>
[ here there be snippage ]
>It's not clear from your description why you need to generate the IDs
>in advance - a simpler approach might be to use an IDENTITY column,
>and insert directly into the members table for a new registration. You
>can then return the system-generated value to the client using
>scope_identity(). This assumes, of course, that the membership ID is
>simply a number, with no other meaning:
>create table dbo.Members (
>MemberID int identity(1,1) primary key,
>FirstName varchar(50) not null,
>LastName varchar(50) not null
>)
>go
>insert into dbo.Members (FirstName, LastName)
>values ('Murray', 'Wells')
>select 'Your ID is: ' + cast(scope_identity() as char(2))
>go
>If you do need some control over the value of the membership ID, then
>one of these approaches might be suitable:
>http://groups.google.com/groups?q=s...uewin.ch&rnum=1
>If this doesn't help, you may want to post the structure (CREATE
>TABLE) of your members table, along with some sample data, and explain
>exactly what you want to return to the client.
Hi Simon,
Thank you very much for your help with this!
Unfortunately, the need to use pre-generated member ids is a foregone
issue; it's not something I have any control over.
I looked at the two examples in the link you provided. The first makes
sense to me, but I can't formulate a query that makes it do exactly
what I want. And I don't yet know enough about locking in SQL Server
to know if the locking hint (UPDLOCK) does what I hope it will do.
To give you a little extra detail, my test table definition is:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblmembers]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblmembers]
GO
CREATE TABLE [dbo].[tblmembers] (
[recid] [int] IDENTITY (1, 1) NOT NULL ,
[memid] [numeric](18, 0) NULL ,
[memname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[mememail] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[activated] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblmembers] WITH NOCHECK ADD
CONSTRAINT [PK_tblmembers] PRIMARY KEY CLUSTERED
(
[recid]
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [IX_tblmembers_memid] ON
[dbo].[tblmembers]([memid]) ON [PRIMARY]
GO
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES('1','1000001','John Smith','john@.smith.com','1')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES('2','1000002','Jen Smith','smith@.jen.com','1')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES('3','1000003','','','0')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES('4','1000004','','','0')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES('5','1000005','','','0')
So, the basic concept is that when a membership registration form is
submitted, I want to go and get the next unassigned member id
(activated=0) change the activated flag to 1 and update memname and
mememail fields with parameters passed from the form. I then need to
return the value in that record's memid field back to the confirmation
page.
I can do all of this, but I haven't been able to satisfy myself that
there won't be a problem if I get two simultaneous registration
requests.
In essence, I'm trying to figure a way, given the above sample data,
that if there are two simultaneous registration requests, one of them
is returned the row with the member id of 1000003 and one of them is
returned the row with the member id of 1000004. And, it probably goes
without saying, that if there are 3 simultaneous requests, the third
one would be returned the row with the member id of 1000005, and so
on. In other words, no two registrations should return the same member
id.
Given the examples you provided in the link, I was trying to do do
something similar to:
declare @.rowid numeric
UPDATE tblmembers set @.rowid=selmem.recid, activated = 1 where recid =
(SELECT TOP 1 recid from tblmembers where activated = 0) as selmem
return @.rowid
But, of course, this is invalid syntax, since it seems you can't
provide an alias to a subquery in an UPDATE statement as I have
attempoted to do above.
However, the concept was to perform the select and update in the same
statement, return the recid of the selected / updated row, and then
use that recid value to perform another update query etc to provide
the member name and email details.
I'm sorry if this only confuses matters, but my hope is that it
explains a little better what I'm hoping to achieve...
Thank you, again, for your help!
Much warmth,
Murray|||"M Wells" <planetquirky@.planetthoughtful.org> wrote in message
news:unq420d09pagnsd386892b6snq0vf03n8o@.4ax.com...
> On 4 Feb 2004 23:44:05 -0800, sql@.hayes.ch (Simon Hayes) wrote:
> >M Wells <planetquirky@.planetthoughtful.org> wrote in message
news:<j1o220p3uauokindou45q9l305eq8au783@.4ax.com>...
> >> Hi All,
> >>
> [ here there be snippage ]
> >It's not clear from your description why you need to generate the IDs
> >in advance - a simpler approach might be to use an IDENTITY column,
> >and insert directly into the members table for a new registration. You
> >can then return the system-generated value to the client using
> >scope_identity(). This assumes, of course, that the membership ID is
> >simply a number, with no other meaning:
> >create table dbo.Members (
> >MemberID int identity(1,1) primary key,
> >FirstName varchar(50) not null,
> >LastName varchar(50) not null
> >)
> >go
> >insert into dbo.Members (FirstName, LastName)
> >values ('Murray', 'Wells')
> >select 'Your ID is: ' + cast(scope_identity() as char(2))
> >go
> >If you do need some control over the value of the membership ID, then
> >one of these approaches might be suitable:
>http://groups.google.com/groups?q=s...l=en&lr=&ie=UTF
-8&oe=UTF-8&selm=40044e65%241_2%40news.bluewin.ch&rnum=1
> >If this doesn't help, you may want to post the structure (CREATE
> >TABLE) of your members table, along with some sample data, and explain
> >exactly what you want to return to the client.
>
> Hi Simon,
> Thank you very much for your help with this!
> Unfortunately, the need to use pre-generated member ids is a foregone
> issue; it's not something I have any control over.
> I looked at the two examples in the link you provided. The first makes
> sense to me, but I can't formulate a query that makes it do exactly
> what I want. And I don't yet know enough about locking in SQL Server
> to know if the locking hint (UPDLOCK) does what I hope it will do.
> To give you a little extra detail, my test table definition is:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblmembers]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblmembers]
> GO
> CREATE TABLE [dbo].[tblmembers] (
> [recid] [int] IDENTITY (1, 1) NOT NULL ,
> [memid] [numeric](18, 0) NULL ,
> [memname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [mememail] [varchar] (255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [activated] [bit] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblmembers] WITH NOCHECK ADD
> CONSTRAINT [PK_tblmembers] PRIMARY KEY CLUSTERED
> (
> [recid]
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE INDEX [IX_tblmembers_memid] ON
> [dbo].[tblmembers]([memid]) ON [PRIMARY]
> GO
>
> INSERT tblmembers(recid,memid,memname,mememail,activated)
> VALUES('1','1000001','John Smith','john@.smith.com','1')
> INSERT tblmembers(recid,memid,memname,mememail,activated)
> VALUES('2','1000002','Jen Smith','smith@.jen.com','1')
> INSERT tblmembers(recid,memid,memname,mememail,activated)
> VALUES('3','1000003','','','0')
> INSERT tblmembers(recid,memid,memname,mememail,activated)
> VALUES('4','1000004','','','0')
> INSERT tblmembers(recid,memid,memname,mememail,activated)
> VALUES('5','1000005','','','0')
> So, the basic concept is that when a membership registration form is
> submitted, I want to go and get the next unassigned member id
> (activated=0) change the activated flag to 1 and update memname and
> mememail fields with parameters passed from the form. I then need to
> return the value in that record's memid field back to the confirmation
> page.
> I can do all of this, but I haven't been able to satisfy myself that
> there won't be a problem if I get two simultaneous registration
> requests.
> In essence, I'm trying to figure a way, given the above sample data,
> that if there are two simultaneous registration requests, one of them
> is returned the row with the member id of 1000003 and one of them is
> returned the row with the member id of 1000004. And, it probably goes
> without saying, that if there are 3 simultaneous requests, the third
> one would be returned the row with the member id of 1000005, and so
> on. In other words, no two registrations should return the same member
> id.
> Given the examples you provided in the link, I was trying to do do
> something similar to:
> declare @.rowid numeric
> UPDATE tblmembers set @.rowid=selmem.recid, activated = 1 where recid =
> (SELECT TOP 1 recid from tblmembers where activated = 0) as selmem
> return @.rowid
> But, of course, this is invalid syntax, since it seems you can't
> provide an alias to a subquery in an UPDATE statement as I have
> attempoted to do above.
> However, the concept was to perform the select and update in the same
> statement, return the recid of the selected / updated row, and then
> use that recid value to perform another update query etc to provide
> the member name and email details.
> I'm sorry if this only confuses matters, but my hope is that it
> explains a little better what I'm hoping to achieve...
> Thank you, again, for your help!
> Much warmth,
> Murray
I think this is what you're looking for, assuming that your definition of
the 'next' row is the one with the lowest value for memid within the set of
rows which have an activated value of 0:
declare @.memid int,
@.memname varchar(50),
@.mememail varchar(255)
set @.memname = 'John Doe'
set @.mememail = 'john@.doe.com'
begin tran
select @.memid = min(memid)
from tblmembers with(updlock)
where activated = 0
update tblmembers
set activated = 1, memname = @.memname, mememail = @.mememail
where memid = @.memid
commit
select @.memid
Since you're returning the memid, but updating the other columns, you can't
use the UPDATE syntax I suggested in the link - you'll have to use the
locking hint, which needs to be inside a transaction. Note that I haven't
put any error handling in the code above, but you should definitely put it
in your real code. Here is a helpful resource:
http://www.sommarskog.se/error-handling-II.html
One other point is that having both the recid and memid columns seems to be
redundant - the natural primary key of the table appears to be memid (and
mememail may also be a candidate key), so it's not clear what purpose recid
serves, although I appreciate that you may not have complete control over
the schema, and that you may have simplified your real data here.
Simon|||[ here there be snippage ]
>I think this is what you're looking for, assuming that your definition of
>the 'next' row is the one with the lowest value for memid within the set of
>rows which have an activated value of 0:
>declare @.memid int,
> @.memname varchar(50),
> @.mememail varchar(255)
>set @.memname = 'John Doe'
>set @.mememail = 'john@.doe.com'
>begin tran
>select @.memid = min(memid)
>from tblmembers with(updlock)
>where activated = 0
>update tblmembers
>set activated = 1, memname = @.memname, mememail = @.mememail
>where memid = @.memid
>commit
>select @.memid
>
>Since you're returning the memid, but updating the other columns, you can't
>use the UPDATE syntax I suggested in the link - you'll have to use the
>locking hint, which needs to be inside a transaction. Note that I haven't
>put any error handling in the code above, but you should definitely put it
>in your real code. Here is a helpful resource:
>http://www.sommarskog.se/error-handling-II.html
>One other point is that having both the recid and memid columns seems to be
>redundant - the natural primary key of the table appears to be memid (and
>mememail may also be a candidate key), so it's not clear what purpose recid
>serves, although I appreciate that you may not have complete control over
>the schema, and that you may have simplified your real data here.
Hi Simon,
Thank you, thank you, thank you!
This seems to be doing exactly what I need it to do!
One question, though -- the min() function in the select statement
seems to slow the execution of the stored procedure considerably.
Is there anything wrong with simply using:
select Top 1 @.memid = memid from tblmembers with(updlock)
where activated = 0
My own testing _seems_ to establish this as being somewhat faster...
Again, thank you, thank you, thank you!
Much warmth,
Murray|||M Wells (planetquirky@.planetthoughtful.org) writes:
> This seems to be doing exactly what I need it to do!
> One question, though -- the min() function in the select statement
> seems to slow the execution of the stored procedure considerably.
> Is there anything wrong with simply using:
> select Top 1 @.memid = memid from tblmembers with(updlock)
> where activated = 0
> My own testing _seems_ to establish this as being somewhat faster...
Permit me to bump in here. I was about to suggest something last night
that was akin to what Simon proposed. But then I was struck of a sense
of doubt whether it would work or not. You see, the standard question
is about get the next value to insert. This pre-generated thing changes
the playing rules a bit. So I left your message unanswered and went to
bed.
Problem is that it is now about 24 hourse since I saw your question the
first time, which means that also today the bed is waiting for me. But
I can aleast provide a tip on how to test this. Add this statement to the
batch between the SELECT and the UPDATE:
WAITFOR DELAY '00:00:10'
Then run the batch from two different windows in Query Analyzer, and
see if you get the expected result.
Also, change the design of the table, so that the clustered index is
on memberid (you would do best in dropping recid; it serves no purpose),
and add a non-clustered index on activated. This may be good for
performance.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Thu, 5 Feb 2004 23:46:45 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:
>M Wells (planetquirky@.planetthoughtful.org) writes:
[ here there be snippage ]
>Permit me to bump in here. I was about to suggest something last night
>that was akin to what Simon proposed. But then I was struck of a sense
>of doubt whether it would work or not. You see, the standard question
>is about get the next value to insert. This pre-generated thing changes
>the playing rules a bit. So I left your message unanswered and went to
>bed.
(Laugh) Hi Erland -- you're more than welcome to bump in! Thank you
for adding your thoughts...
>Problem is that it is now about 24 hourse since I saw your question the
>first time, which means that also today the bed is waiting for me. But
>I can aleast provide a tip on how to test this. Add this statement to the
>batch between the SELECT and the UPDATE:
> WAITFOR DELAY '00:00:10'
>Then run the batch from two different windows in Query Analyzer, and
>see if you get the expected result.
Thank you for this suggestion -- I was wondering how to test if it
works, and it seems to do just that.
>Also, change the design of the table, so that the clustered index is
>on memberid (you would do best in dropping recid; it serves no purpose),
>and add a non-clustered index on activated. This may be good for
>performance.
The suggestion re the memberid field makes a lot of sense, but the
activated field is currently a BIT type. I was under the impression
you can't put indexes on BIT fields?
I've been wondering if there would be any benefit in changing this to
a TINYINT type so I can put an index on it. Storage isn't an issue,
however speed is.
Thanks, again, for your input -- and hope you got some good sleep.
Much warmth,
Murray|||M Wells (planetquirky@.planetthoughtful.org) writes:
> The suggestion re the memberid field makes a lot of sense, but the
> activated field is currently a BIT type. I was under the impression
> you can't put indexes on BIT fields?
Did you say which version of SQL Server you are using? It's correct that
in SQL7 and earlier, you cannot have bit column in indexes. However, in
SQL2000 this restriction is lifted.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 6 Feb 2004 09:14:36 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:
>M Wells (planetquirky@.planetthoughtful.org) writes:
>> The suggestion re the memberid field makes a lot of sense, but the
>> activated field is currently a BIT type. I was under the impression
>> you can't put indexes on BIT fields?
>Did you say which version of SQL Server you are using? It's correct that
>in SQL7 and earlier, you cannot have bit column in indexes. However, in
>SQL2000 this restriction is lifted.
Hi Erland,
Hmmm. I'm using SQL2000, but I don't see bit columns when I create
indexes using the Manage Indexes / Keys dialogue...
Do I need to create indexes for bit columns via SQL statements?
Much warmth,
Murray|||On Fri, 06 Feb 2004 14:09:45 GMT, M Wells
<planetquirky@.planetthoughtful.org> wrote:
>On Fri, 6 Feb 2004 09:14:36 +0000 (UTC), Erland Sommarskog
><sommar@.algonet.se> wrote:
[ here there be snippage ]
>Hi Erland,
>Hmmm. I'm using SQL2000, but I don't see bit columns when I create
>indexes using the Manage Indexes / Keys dialogue...
>Do I need to create indexes for bit columns via SQL statements?
Hi Erland,
No need to reply to the above -- I discovered that, yes, I can create
indexes on bit columns via SQL...
However, if I can be forgiven for one last question: given one or more
bit columns in the WHERE clause of a select statement, should I
explicitly CAST the value I'm looking for as a bit?
I came across this tip while surfing for information on bit columns,
but wasn't certain if this remained relevant fro SQL2K...
Many thanks to both you and Simon for all of your help!!
Much warmth,
Murray|||"M Wells" <planetquirky@.planetthoughtful.org> wrote in message
news:3sf720plrd91o572otgvri3pga73nrrej7@.4ax.com...
> On Fri, 06 Feb 2004 14:09:45 GMT, M Wells
> <planetquirky@.planetthoughtful.org> wrote:
> >On Fri, 6 Feb 2004 09:14:36 +0000 (UTC), Erland Sommarskog
> ><sommar@.algonet.se> wrote:
> [ here there be snippage ]
> >Hi Erland,
> >Hmmm. I'm using SQL2000, but I don't see bit columns when I create
> >indexes using the Manage Indexes / Keys dialogue...
> >Do I need to create indexes for bit columns via SQL statements?
> Hi Erland,
> No need to reply to the above -- I discovered that, yes, I can create
> indexes on bit columns via SQL...
> However, if I can be forgiven for one last question: given one or more
> bit columns in the WHERE clause of a select statement, should I
> explicitly CAST the value I'm looking for as a bit?
> I came across this tip while surfing for information on bit columns,
> but wasn't certain if this remained relevant fro SQL2K...
> Many thanks to both you and Simon for all of your help!!
> Much warmth,
> Murray
As far as I'm aware, there shouldn't be any need to explicitly CAST the
value to a bit, but I haven't used bit columns much, so I can't say for
sure.
To answer your previous question:
"Is there anything wrong with simply using:
select Top 1 @.memid = memid from tblmembers with(updlock)
where activated = 0"
The issue here is that TOP without ORDER BY doesn't really mean anything -
you will get one row returned, but in theory you could get any row where
activated = 0. In practice, if you have a clustered index on the table, then
you'll probably get rows back in the order of that index, but there's no
guarantee.
So if your business rule requires you to get the lowest possible memid, then
you would need to do this:
select Top 1 @.memid = memid
from tblmembers with(updlock)
where activated = 0
order by memid asc -- this defines what TOP means
This is effectively the same as my query, of course.
Simon|||M Wells (planetquirky@.planetthoughtful.org) writes:
> However, if I can be forgiven for one last question: given one or more
> bit columns in the WHERE clause of a select statement, should I
> explicitly CAST the value I'm looking for as a bit?
Yes.
This is because of the conversion rules in SQL Server. If you don't use
cast(), SQL Server will convert the bit column to integer. And whenever
a column is not used in its original shape, SQL Server can no longer
seek an index with this column.
It could still opt to scan the index, but it would have to scan all
pages for the index. However, for you repro, I got a table scan when
I did not use cast(). This may be due to the small size of the table.
However, when I used cast(), SQL Server used an Index Seek.
I think that even with a large members table, this method should be
really fast, although there is a cost for updating the index when you
activate the customer.
I also did some concurrency studies, and I feel fairly confident that
the method with UPDLOCK is safe.
Here is a repro, so you can see exactly what I ran:
CREATE TABLE [dbo].[tblmembers] (
[memid] [numeric](18, 0) NOT NULL ,
[memname] [varchar] (50) NULL,
[mememail] [varchar] (255) NULL ,
[activated] [bit] NOT NULL,
CONSTRAINT PK_tblmembers PRIMARY KEY CLUSTERED (memid)
)
GO
CREATE INDEX isactivated ON tblmembers(activated)
GO
INSERT tblmembers(memid,memname,mememail,activated)
VALUES('1000001','John Smith','john@.smith.com','1')
INSERT tblmembers(memid,memname,mememail,activated)
VALUES('1000002','Jen Smith','smith@.jen.com','1')
INSERT tblmembers(memid,memname,mememail,activated)
VALUES('1000003','','','0')
INSERT tblmembers(memid,memname,mememail,activated)
VALUES('1000004','','','0')
INSERT tblmembers(memid,memname,mememail,activated)
VALUES('1000005','','','0')
go
declare @.memid int,
@.memname varchar(50),
@.mememail varchar(255)
set @.memname = 'John Doe'
set @.mememail = 'john@.doe.com'
begin tran
select @.memid = min(memid)
from tblmembers with(updlock)
where activated = convert(bit, 0)
update tblmembers
set activated = 1, memname = @.memname, mememail = @.mememail
where memid = @.memid
commit
select @.memid
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 6 Feb 2004 23:06:17 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:
[ here there be snippage ]
I just wanted to give a heartfelt thanks to both of you (Simon and
Erland) for all of your help with this.
I can't imagine a better testimony for the internet than the fact that
people like you two (and many others) go out of your way to share your
knowledge and expertise with a complete stranger who is struggling to
get something done.
Thank you both, again!
Much warmth,
Murray
http://www.planetthoughtful.org
possible to link 2 stored procedures to produce only 1 recordset?
Hello everybody!
trying to do the following:
-create a report in access project
-got 3 stored procedures which return data that shall be shown on report
-need one recordset as datasource (or can i use more than one here?)
Problem:
Data was unrelated before, now needs to be on same report, that's why until now i have 3 different pretty complex stored procedures returning a recordset each.
I could of course copy and paste the whole 3 into 1 new stored proc, but when one changes i had to change the newly created one too (which might get messy when doing a lot of maintenance and changes on the others)
Can create a stored procedure that simply integrates those 3 into one recordset something like this (in pseudo-code):
CREATE PROCEDURE IntegrateSPs AS
INTEGRATE
SP1,SP2,SP3
INTO myRecordset
Anything like this possible?
thx in advance,
KumaHow about this:
create a new "wrapper" procedure. that does the something like:
create #temp (with a buch of fields)
insert into #temp exec proc1
insert into #temp exec proc2
select *
from #temp|||How about this:
create a new "wrapper" procedure. that does the something like:
create #temp (with a buch of fields)
insert into #temp exec proc1
insert into #temp exec proc2
select *
from #temp
Well that would work nicely if the results of each sproc had the same number of columns and the same datatypes, and that each sproc only returns 1 result set...
Otherwise it won't...
Can you show me the result sets of each?|||Example:
SPs look somewhat like this (but huger with more calculation involved)
CREATE PROCEDURE SP1
@.PersonIDStart nvarchar (10),
@.PersonIDEnd nvarchar (10)
AS
SELECT
a + b + c AS fld1,
d + e + f AS fld2
FROM tbl
WHERE PersonID BETWEEN @.PersonIDStart AND @.PersonIDEnd
SP1 would return:
PersonID, Fld1, Fld2, Fld3
SP2:
PersonID, Fld4, Fld5, Fld6
SP3:
PersonID, Fld7, Fld8, Fld9
Of course there are more fields in each recordset. All FldX-fields are smallints.
PersonID is text.
SP1 to SP3 would get the same parameters passed for PersonID and retrieve a number of recordsets accordingly.
Reports will be created for each PersonID with all values from SP1 to SP3 for this PersonID on one sheet.
like this:
Results for PersonID: XYZ123
SP1 SP2 SP3
DimA 4 8 1
DimB 6 8 3
DimC 9 2 7
If i were to put data in a temp table or any go-between permanent table, it had to be one record per PersonID with all the data from the three SPs in it.
SPs are set up to retrieve recordsets only (SELECT). Would i be able to make them dump those into a table without altering them completely (i need the recordset approach elsewhere) and without having to duplicate them into INSERT SPs?
thx
Kuma|||You could use something like:CREATE PROCEDURE p_Wrapper
@.piPersonStart INT
, @.piPersonEnd INT
AS
CREATE TABLE #t1 (personid INT, f1 INT, f2 INT, f3 INT)
INSERT INTO #t1 EXECUTE sp1 @.piPersonStart, @.piPersonEnd
CREATE TABLE #t2 (personid INT, f4 INT, f5 INT, f6 INT)
INSERT INTO #t2 EXECUTE sp2 @.piPersonStart, @.piPersonEnd
CREATE TABLE #t3 (personid INT, f7 INT, f8 INT, f9 INT)
INSERT INTO #t3 EXECUTE sp3 @.piPersonStart, @.piPersonEnd
SELECT *
FROM #t1
FULL JOIN #t2 ON (#t2.personid = #t1.personid)
FULL JOIN #t3 ON (#t3.personid = #t1.personid)
RETURN-PatP|||thx a lot.
hoped for something even simpler, but i can live with that.
Think I'll forego the CREATE TABLE thingy and make permanent tables that I empty after I'm finished. Dont like this temp table stuff.
glad i got the syntax for the INSERT statement!
ty again
Kuma|||Just hope the sproc is executed at the same time then...
I would suggest a table variable if you don't like temp tables...
My question to you then, is how BIG is the result set?
Because if it's not then temp is not a problem...
still I'd use a table variable...|||Just beware if you use permanent tables that you can only allow one user at a time to run the p_Wrapper procedure. Temp tables or table variables dodge that bullet.
-PatP|||thx for the info in the last two posts.
table variable sounds very good. i'll try that.
didnt think about the the issue of "one-user-at-a-time",
since this is a 1-user desktop DB (i'd make it multi-user
intranet, but user dont like it...). I'll change it anyway.
Who knows if and when it goes multi-user...
:)
Kuma
*edited for typo|||Good plan to allow for the possibility of multiple users! I can't count the number of databases I've set up that would "never" be used by more than one person, at least one of which is used by 5000+ people every day!
-PatP|||If structure of permanent tables is thought through then it is definitely an advantage over temp tables (BTW, table variables will not work...should I say why? ;) ).|||Temp tables work nicely though, and have every benefit needed for this problem.
-PatP|||rdjabarov: yes pls u should say why :)
ty|||rdjabarov: yes pls u should say why :)
tyThe source for your INSERT is EXECUTE|||To expand on rdjabarov's answer a bit, you can use INSERT INTO #temp when the source is an EXECUTE, but you can't use INSERT INTO @.temp with an EXECUTE. The temp table works, but the table variable does not because the syntax isn't accepted.
-PatP
trying to do the following:
-create a report in access project
-got 3 stored procedures which return data that shall be shown on report
-need one recordset as datasource (or can i use more than one here?)
Problem:
Data was unrelated before, now needs to be on same report, that's why until now i have 3 different pretty complex stored procedures returning a recordset each.
I could of course copy and paste the whole 3 into 1 new stored proc, but when one changes i had to change the newly created one too (which might get messy when doing a lot of maintenance and changes on the others)
Can create a stored procedure that simply integrates those 3 into one recordset something like this (in pseudo-code):
CREATE PROCEDURE IntegrateSPs AS
INTEGRATE
SP1,SP2,SP3
INTO myRecordset
Anything like this possible?
thx in advance,
KumaHow about this:
create a new "wrapper" procedure. that does the something like:
create #temp (with a buch of fields)
insert into #temp exec proc1
insert into #temp exec proc2
select *
from #temp|||How about this:
create a new "wrapper" procedure. that does the something like:
create #temp (with a buch of fields)
insert into #temp exec proc1
insert into #temp exec proc2
select *
from #temp
Well that would work nicely if the results of each sproc had the same number of columns and the same datatypes, and that each sproc only returns 1 result set...
Otherwise it won't...
Can you show me the result sets of each?|||Example:
SPs look somewhat like this (but huger with more calculation involved)
CREATE PROCEDURE SP1
@.PersonIDStart nvarchar (10),
@.PersonIDEnd nvarchar (10)
AS
SELECT
a + b + c AS fld1,
d + e + f AS fld2
FROM tbl
WHERE PersonID BETWEEN @.PersonIDStart AND @.PersonIDEnd
SP1 would return:
PersonID, Fld1, Fld2, Fld3
SP2:
PersonID, Fld4, Fld5, Fld6
SP3:
PersonID, Fld7, Fld8, Fld9
Of course there are more fields in each recordset. All FldX-fields are smallints.
PersonID is text.
SP1 to SP3 would get the same parameters passed for PersonID and retrieve a number of recordsets accordingly.
Reports will be created for each PersonID with all values from SP1 to SP3 for this PersonID on one sheet.
like this:
Results for PersonID: XYZ123
SP1 SP2 SP3
DimA 4 8 1
DimB 6 8 3
DimC 9 2 7
If i were to put data in a temp table or any go-between permanent table, it had to be one record per PersonID with all the data from the three SPs in it.
SPs are set up to retrieve recordsets only (SELECT). Would i be able to make them dump those into a table without altering them completely (i need the recordset approach elsewhere) and without having to duplicate them into INSERT SPs?
thx
Kuma|||You could use something like:CREATE PROCEDURE p_Wrapper
@.piPersonStart INT
, @.piPersonEnd INT
AS
CREATE TABLE #t1 (personid INT, f1 INT, f2 INT, f3 INT)
INSERT INTO #t1 EXECUTE sp1 @.piPersonStart, @.piPersonEnd
CREATE TABLE #t2 (personid INT, f4 INT, f5 INT, f6 INT)
INSERT INTO #t2 EXECUTE sp2 @.piPersonStart, @.piPersonEnd
CREATE TABLE #t3 (personid INT, f7 INT, f8 INT, f9 INT)
INSERT INTO #t3 EXECUTE sp3 @.piPersonStart, @.piPersonEnd
SELECT *
FROM #t1
FULL JOIN #t2 ON (#t2.personid = #t1.personid)
FULL JOIN #t3 ON (#t3.personid = #t1.personid)
RETURN-PatP|||thx a lot.
hoped for something even simpler, but i can live with that.
Think I'll forego the CREATE TABLE thingy and make permanent tables that I empty after I'm finished. Dont like this temp table stuff.
glad i got the syntax for the INSERT statement!
ty again
Kuma|||Just hope the sproc is executed at the same time then...
I would suggest a table variable if you don't like temp tables...
My question to you then, is how BIG is the result set?
Because if it's not then temp is not a problem...
still I'd use a table variable...|||Just beware if you use permanent tables that you can only allow one user at a time to run the p_Wrapper procedure. Temp tables or table variables dodge that bullet.
-PatP|||thx for the info in the last two posts.
table variable sounds very good. i'll try that.
didnt think about the the issue of "one-user-at-a-time",
since this is a 1-user desktop DB (i'd make it multi-user
intranet, but user dont like it...). I'll change it anyway.
Who knows if and when it goes multi-user...
:)
Kuma
*edited for typo|||Good plan to allow for the possibility of multiple users! I can't count the number of databases I've set up that would "never" be used by more than one person, at least one of which is used by 5000+ people every day!
-PatP|||If structure of permanent tables is thought through then it is definitely an advantage over temp tables (BTW, table variables will not work...should I say why? ;) ).|||Temp tables work nicely though, and have every benefit needed for this problem.
-PatP|||rdjabarov: yes pls u should say why :)
ty|||rdjabarov: yes pls u should say why :)
tyThe source for your INSERT is EXECUTE|||To expand on rdjabarov's answer a bit, you can use INSERT INTO #temp when the source is an EXECUTE, but you can't use INSERT INTO @.temp with an EXECUTE. The temp table works, but the table variable does not because the syntax isn't accepted.
-PatP
Subscribe to:
Posts (Atom)