help getting parameters back from a stored procedure

Hello everyone,

I've been trying to use a stored procedure to return the names of some temporary tables that i put in the tempdb table in SQL Server.

--I've been getting the following error in visual basic 6 when i try to call this:
run-time error '-2147217900 (80040e14)': syntax access violation

--This is the error you get when you try to just run the code in query analyzer:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

--what code i was trying to use (in query analyzer):
{call EXEC CreateTempTables (@.RQSodfil = 'a', @.BulkRan = 'a', @.BulkFor = 'a', @.BulkJit = 'a', @.ID = '0', @.RQSodfilFlag = '1', @.BulkRanFlag = '0', @.BulkForFlag = '0', @.BulkJitFlag = '0', @.DeleteFlag = '0', @.ErrorNum = '0')}

--code that i was trying to use in vb 6:
Public Sub TemporaryTables( _
ByVal bytRQSodfilFlag As Byte, _
ByVal bytBulkRanFlag As Byte, _
ByVal bytBulkForFlag As Byte, _
ByVal bytBulkJitFlag As Byte, _
ByVal bytDeleteFlag As Byte, _
ByVal cnPlant As String)

Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
Dim objRQSodfil As Parameter
Dim objBulkRan As Parameter
Dim objBulkFor As Parameter
Dim objBulkJit As Parameter
Dim objParamID As Parameter
Dim objRQSodfilFlag As Parameter
Dim objBulkRanFlag As Parameter
Dim objBulkForFlag As Parameter
Dim objBulkJitFlag As Parameter
Dim objDeleteFlag As Parameter
Dim objErrorNum As Parameter
Dim intErrorNum As Integer

' setup command variable
Set objCmd = New ADODB.Command
Set objConn = New ADODB.Connection
objConn.Open cnPlant
objCmd.CommandText = "EXEC CreateTempTables"
objCmd.CommandType = adCmdStoredProc
objCmd.ActiveConnection = objConn

' setup parameters
Set objRQSodfil = objCmd.CreateParameter("@.RQSodfil", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objRQSodfil
Set objBulkRan = objCmd.CreateParameter("@.BulkRan", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkRan
Set objBulkFor = objCmd.CreateParameter("@.BulkFor", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkFor
Set objBulkJit = objCmd.CreateParameter("@.BulkJit", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkJit
Set objParamID = objCmd.CreateParameter("@.ID", adChar, adParamInputOutput, 2, 0)
objCmd.Parameters.Append objParamID
Set objRQSodfilFlag = objCmd.CreateParameter("@.RQSodfilFlag", adTinyInt, adParamInput, , bytRQSodfilFlag)
objCmd.Parameters.Append objRQSodfilFlag
Set objBulkRanFlag = objCmd.CreateParameter("@.BulkRanFlag", adTinyInt, adParamInput, , bytBulkRanFlag)
objCmd.Parameters.Append objBulkRanFlag
Set objBulkForFlag = objCmd.CreateParameter("@.BulkForFlag", adTinyInt, adParamInput, , bytBulkForFlag)
objCmd.Parameters.Append objBulkForFlag
Set objBulkJitFlag = objCmd.CreateParameter("@.BulkJitFlag", adTinyInt, adParamInput, , bytBulkJitFlag)
objCmd.Parameters.Append objBulkJitFlag
Set objDeleteFlag = objCmd.CreateParameter("@.DeleteFlag", adTinyInt, adParamInput, , bytDeleteFlag)
objCmd.Parameters.Append objDeleteFlag
Set objErrorNum = objCmd.CreateParameter("@.ErrorNum", adInteger, adParamInputOutput, , 0)
objCmd.Parameters.Append objErrorNum

' execute command
Set rsTableInfo = objCmd.Execute(, , adExecuteRecord)

' find returned parameters
gstrRQSodfilName = rsTableInfo.Fields("@.RQSodfil")
gstrBulkRanName = rsTableInfo.Fields("@.BulkRan")
gstrBulkForName = rsTableInfo.Fields("@.BulkFor")
gstrBulkJitName = rsTableInfo.Fields("@.BulkJit")
gstrID = rsTableInfo.Fields("@.ID")
intErrorNum = rsTableInfo.Fields("@.ErrorNum")

End Sub

any help would be appreciatedactually i just solved my own problem:

at the end of the stored procedure i selected the columns i wanted to return and that did it.

select @.RQSodfil,@.BulkRan,@.BulkFor,@.BulkJit,@.ID,@.ErrorNum

i also just did a regular sql statement where i called the EXEC command to run my stored procedure.

EXEC CreateTempTables @.RQSodfil = 'a', @.BulkRan = 'a', @.BulkFor = 'a', @.BulkJit = 'a', @.ID = '0', @.RQSodfilFlag = '1', @.BulkRanFlag = '0', @.BulkForFlag = '0', @.BulkJitFlag = '0', @.DeleteFlag = '0', @.ErrorNum = '0'

hopefully if someone else has the same problem i've had they can see what i did.

help forms

The below stored procedure works. However, I am trying to use a text box
from a temp form for the where clause.
WHERE Transactions.TransactionID = [forms]![form1]![text0]
I even tried changing first line:
Alter PROCEDURE S3 @.TID varchar (255) = [forms]![form1]![text0]
And changed last line to:
WHERE Transactions.TransactionID = @.TID
So far, I haven't been able to have this parameter work from a temp form
with the parameter typed into the text0 box.
any help here? I get ado error near "!" or something to that affect.

this below is what does work fine:

Alter PROCEDURE S3 @.TID varchar (255)
AS
SELECT Transactions.TransactionID, Transactions.AccountID,
Transactions.TransactionNumber, Transactions.TransactionDate,
Transactions.TransactionDescription, Transactions.WithdrawalAmount,
Transactions.DepositAmount
FROM Transactions
WHERE Transactions.TransactionID = @.TID"JIMMIE WHITAKER" <kpsklab@.worldnet.att.net> wrote in message
news:sHfGc.196809$Gx4.122482@.bgtnsc04-news.ops.worldnet.att.net...
> The below stored procedure works. However, I am trying to use a text box
> from a temp form for the where clause.
> WHERE Transactions.TransactionID = [forms]![form1]![text0]
> I even tried changing first line:
> Alter PROCEDURE S3 @.TID varchar (255) = [forms]![form1]![text0]
> And changed last line to:
> WHERE Transactions.TransactionID = @.TID
> So far, I haven't been able to have this parameter work from a temp form
> with the parameter typed into the text0 box.
> any help here? I get ado error near "!" or something to that affect.
> this below is what does work fine:
> Alter PROCEDURE S3 @.TID varchar (255)
> AS
> SELECT Transactions.TransactionID, Transactions.AccountID,
> Transactions.TransactionNumber, Transactions.TransactionDate,
> Transactions.TransactionDescription, Transactions.WithdrawalAmount,
> Transactions.DepositAmount
> FROM Transactions
> WHERE Transactions.TransactionID = @.TID

SQL Server is a pure server application - it doesn't know anything about
forms or other presentation of data. You need to capture the parameter value
in your client application (Access?), then pass it to the stored procedure
using a client library such as ADO. If you're unsure how to go about this,
you might want to post in a forum for whichever client application you have.

Simon|||I thought sql server was about sql. You're saying there's no way to pass
data from a form to it as a parameter?
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40e99857$1_2@.news.bluewin.ch...
> "JIMMIE WHITAKER" <kpsklab@.worldnet.att.net> wrote in message
> news:sHfGc.196809$Gx4.122482@.bgtnsc04-news.ops.worldnet.att.net...
> > The below stored procedure works. However, I am trying to use a text
box
> > from a temp form for the where clause.
> > WHERE Transactions.TransactionID = [forms]![form1]![text0]
> > I even tried changing first line:
> > Alter PROCEDURE S3 @.TID varchar (255) = [forms]![form1]![text0]
> > And changed last line to:
> > WHERE Transactions.TransactionID = @.TID
> > So far, I haven't been able to have this parameter work from a temp form
> > with the parameter typed into the text0 box.
> > any help here? I get ado error near "!" or something to that affect.
> > this below is what does work fine:
> > Alter PROCEDURE S3 @.TID varchar (255)
> > AS
> > SELECT Transactions.TransactionID, Transactions.AccountID,
> > Transactions.TransactionNumber, Transactions.TransactionDate,
> > Transactions.TransactionDescription, Transactions.WithdrawalAmount,
> > Transactions.DepositAmount
> > FROM Transactions
> > WHERE Transactions.TransactionID = @.TID
> SQL Server is a pure server application - it doesn't know anything about
> forms or other presentation of data. You need to capture the parameter
value
> in your client application (Access?), then pass it to the stored procedure
> using a client library such as ADO. If you're unsure how to go about this,
> you might want to post in a forum for whichever client application you
have.
> Simon|||JIMMIE WHITAKER (kpsklab@.worldnet.att.net) writes:
> I thought sql server was about sql. You're saying there's no way to pass
> data from a form to it as a parameter?

You are right SQL Server is about SQL, but forms are not SQL per se.

The difference between Access and SQL Server is that Access is a one-tier
application. That is, the query language knows about the GUI and can
interact with it directly. This makes it very easy to write application,
but it comes with a price: it does not scale well, and is best suited for
single-user applications. Also, since the application accesses the database
directly, that means a lot of network traffic if the database is on the
other machine.

SQL Server is intended for applications with two or more tiers. That is,
there is a client on one machine, which passes queries or remote procedure
calls to the server which sends data back. Since machine that hosts the
database does not have do care about drawing GUI:s it can concentrate
on managing the data. And since the server program works on the server,
only the data that the user actually requests has to travel the network.

There are of course several ways to pass a parameter from a form to SQL
Server, but the point is that once the parameter reaches SQL Server, SQL
Server has no idea where it came from. It could be from an input form, it
could have been a read from a file, or it could be a constant in the calling
client program.

To talk to SQL Server from a client, there is a whole range of client
libraries to choose from. From Access the most commonly used today is
ADO. But really how you this best in Access, is probably a question
for comp.databases.ms-access.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Help for stored procedure and Null...

Hi,
I have write a stored procedure which makes update in a numeric (int) field.

Depending on data in other table, in some case the result of query get a
Null value instead a zero value...

How can I tell to Update query to NOT update field if the value is Null ?

I hope my word clear...

here the stored procedure:

UPDATE dbo.ANAUTENTI

SET dist1punti = dist1punti +

(SELECT SUM(TEMPIMPORTAZIONEDIST1.qnt * ANAARTICOLI.punti) AS totalepunti

FROM TEMPIMPORTAZIONEDIST1 INNER JOIN

ANAARTICOLI ON TEMPIMPORTAZIONEDIST1.codicearticolo =
ANAARTICOLI.codartdist1

WHERE (TEMPIMPORTAZIONEDIST1.piva = ANAUTENTI.piva))

WHERE (piva IN

(SELECT piva

FROM TEMPIMPORTAZIONEDIST1

GROUP BY piva))

Thanks in advance

Piero

Italypiero (g.pagnoni@.pesaroservice.com) writes:
> Depending on data in other table, in some case the result of query get a
> Null value instead a zero value...
> How can I tell to Update query to NOT update field if the value is Null ?

UPDATE tbl
SET col = col + coalesce((SELECT ...), 0)

The coalesce function takes list of arguments and returns the first non-NULL
value in the list, or NULL if all values are NULL.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> ha scritto nel messaggio
news:Xns944153FCAB58Yazorman@.127.0.0.1...
> UPDATE tbl
> SET col = col + coalesce((SELECT ...), 0)
>
> The coalesce function takes list of arguments and returns the first
non-NULL
> value in the list, or NULL if all values are NULL.

It works fine !
Thank You very much !

Piero
Italysql

Help for Sql Query

I am writing one procedure and in that i am giving three inputs ie account number and from date and to date, and i will give any one inupt ie account number or (from and to dates).

So in the procedure i want to select records from table and in where condition depending on input ie account number or from and to dates, so i am not able to write query,

I will write the query here what i know, please corrct the query bcoz i am getting error

Select
*
From Mf_Tran_Reg
Where
mft_fundcd='ABN' and mft_purred='P'
if @.Folio = '' --if account number is null
begin
and mft_procdate between @.Fdate and @.tdate
end
else
begin
and mft_accno= @.Folio
enddo the conditional branching earlier,

if @.Folio = '' --if account number is null

select * from tbl where param1 and param2 and param3

else

select * from tbl where param2 and param3

Quote:

Originally Posted by majidsql

I am writing one procedure and in that i am giving three inputs ie account number and from date and to date, and i will give any one inupt ie account number or (from and to dates).

So in the procedure i want to select records from table and in where condition depending on input ie account number or from and to dates, so i am not able to write query,

I will write the query here what i know, please corrct the query bcoz i am getting error

Select
*
From Mf_Tran_Reg
Where
mft_fundcd='ABN' and mft_purred='P'
if @.Folio = '' --if account number is null
begin
and mft_procdate between @.Fdate and @.tdate
end
else
begin
and mft_accno= @.Folio
end

Help for sp_updatestats

Hi,
WEhen can I use this procedure? When its recommended and if its safe to use
it while online users are using the database.
Regards,aoxpsql,
This can consume large amounts of server resources, mainly disk IO. I
would prefer to run this at a time when the number of users is at a
minimum, say 3am? Try and run it when no other maintenance or overnight
batch jobs are running.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
aoxpsql wrote:
> Hi,
>
> WEhen can I use this procedure? When its recommended and if its safe to us
e
> it while online users are using the database.
> Regards,
>

Help for sp_updatestats

Hi,
WEhen can I use this procedure? When its recommended and if its safe to use
it while online users are using the database.
Regards,
aoxpsql,
This can consume large amounts of server resources, mainly disk IO. I
would prefer to run this at a time when the number of users is at a
minimum, say 3am? Try and run it when no other maintenance or overnight
batch jobs are running.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
aoxpsql wrote:
> Hi,
>
> WEhen can I use this procedure? When its recommended and if its safe to use
> it while online users are using the database.
> Regards,
>
sql

Help for sp_updatestats

Hi,
WEhen can I use this procedure? When its recommended and if its safe to use
it while online users are using the database.
Regards,> Hi !
> I want to use differnt logins for accessing database .
> User : for select queries
> Programmer : having full access to only to specific tables / and
> create/drop/alter rights for store procedures /functions/triggers
> Admin : having full access for all database objects .
> My main objective is to disable SA user for all the users of database ,and
> keep a audit of these users.
>
> I have already created these users and roles , Can u pls tell me what can
be
> area of attention while doing this ? what would be the impact while doing
> any activities , specially migration from one server to another , or
> transfering the data using DTS.
> Secondly , I want to implement transactional replication on production
> database , will it have any impact of logins and users of database ?
>
> Regards,
> Swati
>|||aoxpsql,
This can consume large amounts of server resources, mainly disk IO. I
would prefer to run this at a time when the number of users is at a
minimum, say 3am? Try and run it when no other maintenance or overnight
batch jobs are running.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
aoxpsql wrote:
> Hi,
>
> WEhen can I use this procedure? When its recommended and if its safe to use
> it while online users are using the database.
> Regards,
>

Help for Fine Tune Store procedure to increase its performance

I want to change following store procedure, it takes around 5 minutes to
execute & I calls for each vessel / voyage combination... , Could any one
help me to fine tune this store procedure.
CREATE PROCEDURE get_ContainerListinExpBL
@.VesselCode varchar(10) ,
@.VoyageCode varchar(10),
@.LinerCode varchar(10) ,
@.BookingList varchar(1000) = null ,
@.BLNumber varchar(20) = null
AS
select distinct t.ContainerNo,
T.ContainerType,
(select BookingNote
from trnActivity TA
inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner =
TA.LinerCode
where TA.ContainerNo = T.ContainerNo
and TA.LinerCode = t.LinerCode
and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
and DO is not Null
and TA.ActivityDate =
(select Max(activityDate ) from
trnActivity TB
where TB.ContainerNo = t.ContainerNo
and TB.LinerCode = t.LinerCode
and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
and TB.DO is not null)) BookingNote ,
t.ImpVesselCode ,
t.ImpVoyageCode ,
t.ReleaseRefNo
from trnExpTracking t
--inner join mstISO O on O.ContainerType = T.ContainerType
where t.ExpVesselCode = @.VesselCode
and t.ExpVoyagecode = @.VoyageCode
and t.LinerCode = @.LinerCode
union
select
c.ContainerNo,
c.ContainerType,
c.BookingNote,
c.ImpVesselCode ,
c.ImpVoyageCode ,
c.ReleaseRefNo
from trnExpContainerDetail c
where C.LinerCode = @.LinerCode
and C.VesselCode = @.VesselCode
and C.VoyageCode = @.VoyageCode
and C.BLnumber = @.BLnumber
GO
-- --
Thanks in advance
Please also post DDL (CREATE TABLE statements, including constraints and
indexes). The underlying schema can affect query performance significantly.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nitu" <Nitu@.discussions.microsoft.com> wrote in message
news:05229BAA-ABA5-4C85-A298-616D82F629D3@.microsoft.com...
>I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner =
> TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate =
> (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
>
> -- --
> Thanks in advance
|||Nitu,
You should post the DDL, including all indexes, that is key. Also, ask
yourself if you really need to handle NULL values in VesselCode and/or
VoyageCode, because the use of ISNULL() will disallow the use of an
index for those columns.
Gert-Jan
Nitu wrote:
> I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner =
> TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate =
> (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
> -- --
> Thanks in advance
|||On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
> I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner =
> TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate =
> (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
> -- --
> Thanks in advance
Hi, try removing the union and use a temp table instead. For example:
select distinct t.ContainerNo etc...
into #my_temp
from trnExpTracking t
--inner join mstISO O on O.ContainerType = T.ContainerType
where t.ExpVesselCode = @.VesselCode
and t.ExpVoyagecode = @.VoyageCode
and t.LinerCode = @.LinerCode
insert #mytemp
select distinct
c.ContainerNo etc...
from trnExpContainerDetail c
where C.LinerCode = @.LinerCode
and C.VesselCode = @.VesselCode
and C.VoyageCode = @.VoyageCode
and C.BLnumber = @.BLnumber
and not exists (select 1 from #my_temp where #my_temp. ContainerNo =
c. ContainerNo
and #my_temp. ContainerType = c. ContainerType and #my_temp.
BookingNote = c. BookingNote
and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
ReleaseRefNo )
That should move the timetable to less than 5 minutes to run. I think
that is acceptable. You can fine tune it further by using more #temp
tables for inner subqueries and joining them with #my_temp using
updates statements.
|||> That should move the timetable to less than 5 minutes to run. I think
> that is acceptable. You can fine tune it further by using more #temp
> tables for inner subqueries and joining them with #my_temp using
> updates statements.
SB, how can you make these recommendations without knowledge of the
underlying schema? I'm not saying that employing a temp tables won't
improve performance, it's just that I've often seen temp tables used as a
kludge workaround when the real issue is lack of useful indexes and
non-saragable query expressions. IMHO, a better approach is to understand
and address the underlying cause for the poor performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"SB" <othellomy@.yahoo.com> wrote in message
news:1185777942.351695.318730@.q75g2000hsh.googlegr oups.com...
> On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
> Hi, try removing the union and use a temp table instead. For example:
> select distinct t.ContainerNo etc...
> into #my_temp
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> insert #mytemp
> select distinct
> c.ContainerNo etc...
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> and not exists (select 1 from #my_temp where #my_temp. ContainerNo =
> c. ContainerNo
> and #my_temp. ContainerType = c. ContainerType and #my_temp.
> BookingNote = c. BookingNote
> and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
> ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
> ReleaseRefNo )
>
> That should move the timetable to less than 5 minutes to run. I think
> that is acceptable. You can fine tune it further by using more #temp
> tables for inner subqueries and joining them with #my_temp using
> updates statements.
>
|||On Jul 30, 6:35 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> SB, how can you make these recommendations without knowledge of the
> underlying schema? I'm not saying that employing a temp tables won't
> improve performance, it's just that I've often seen temp tables used as a
> kludge workaround when the real issue is lack of useful indexes and
> non-saragable query expressions. IMHO, a better approach is to understand
> and address the underlying cause for the poor performance.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SB" <othell...@.yahoo.com> wrote in message
> news:1185777942.351695.318730@.q75g2000hsh.googlegr oups.com...
>
>
>
>
> - Show quoted text -
Dan I don't need schema. If I need one I will ask tl from you. Thanks.
|||With all due respect SB, if you want to provide real help to the OP you DO
need schema, indexes and possibly even data distribution information. Your
stab in the dark could well be WORSE performing due the large I/O overhead
associated with temporary tables (and the sproc recompiles associated with
them too). Even worse is that it completely avoids the possibility that a
single appropriate index addition could improve the sproc performance 1-4
orders of magnitude.
We on this forum should strive to give posters the right tools to help them
in the future as well as simply attempting to solve their current problem.
With just a bit more information from the OP we could have a dialog that
provided good mentoring to all while providing the BEST solution to Nitu.
TheSQLGuru
President
Indicium Resources, Inc.
"SB" <othellomy@.yahoo.com> wrote in message
news:1185852523.248498.24130@.d55g2000hsg.googlegro ups.com...
> On Jul 30, 6:35 pm, "Dan Guzman" <guzma...@.nospam-
> online.sbcglobal.net> wrote:
> Dan I don't need schema. If I need one I will ask tl from you. Thanks.
>

Help for Fine Tune Store procedure to increase its performance

I want to change following store procedure, it takes around 5 minutes to
execute & I calls for each vessel / voyage combination... , Could any one
help me to fine tune this store procedure.
--
CREATE PROCEDURE get_ContainerListinExpBL
@.VesselCode varchar(10) ,
@.VoyageCode varchar(10),
@.LinerCode varchar(10) ,
@.BookingList varchar(1000) = null ,
@.BLNumber varchar(20) = null
AS
select distinct t.ContainerNo,
T.ContainerType,
(select BookingNote
from trnActivity TA
inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner = TA.LinerCode
where TA.ContainerNo = T.ContainerNo
and TA.LinerCode = t.LinerCode
and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
and DO is not Null
and TA.ActivityDate = (select Max(activityDate ) from
trnActivity TB
where TB.ContainerNo = t.ContainerNo
and TB.LinerCode = t.LinerCode
and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
and TB.DO is not null)) BookingNote ,
t.ImpVesselCode ,
t.ImpVoyageCode ,
t.ReleaseRefNo
from trnExpTracking t
--inner join mstISO O on O.ContainerType = T.ContainerType
where t.ExpVesselCode = @.VesselCode
and t.ExpVoyagecode = @.VoyageCode
and t.LinerCode = @.LinerCode
union
select
c.ContainerNo,
c.ContainerType,
c.BookingNote,
c.ImpVesselCode ,
c.ImpVoyageCode ,
c.ReleaseRefNo
from trnExpContainerDetail c
where C.LinerCode = @.LinerCode
and C.VesselCode = @.VesselCode
and C.VoyageCode = @.VoyageCode
and C.BLnumber = @.BLnumber
GO
-- --
Thanks in advancePlease also post DDL (CREATE TABLE statements, including constraints and
indexes). The underlying schema can affect query performance significantly.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Nitu" <Nitu@.discussions.microsoft.com> wrote in message
news:05229BAA-ABA5-4C85-A298-616D82F629D3@.microsoft.com...
>I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner => TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate => (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
>
> -- --
> Thanks in advance|||Nitu,
You should post the DDL, including all indexes, that is key. Also, ask
yourself if you really need to handle NULL values in VesselCode and/or
VoyageCode, because the use of ISNULL() will disallow the use of an
index for those columns.
Gert-Jan
Nitu wrote:
> I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner => TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate => (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
> -- --
> Thanks in advance|||On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
> I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner => TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate => (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
> -- --
> Thanks in advance
Hi, try removing the union and use a temp table instead. For example:
select distinct t.ContainerNo etc...
into #my_temp
from trnExpTracking t
--inner join mstISO O on O.ContainerType = T.ContainerType
where t.ExpVesselCode = @.VesselCode
and t.ExpVoyagecode = @.VoyageCode
and t.LinerCode = @.LinerCode
insert #mytemp
select distinct
c.ContainerNo etc...
from trnExpContainerDetail c
where C.LinerCode = @.LinerCode
and C.VesselCode = @.VesselCode
and C.VoyageCode = @.VoyageCode
and C.BLnumber = @.BLnumber
and not exists (select 1 from #my_temp where #my_temp. ContainerNo =c. ContainerNo
and #my_temp. ContainerType = c. ContainerType and #my_temp.
BookingNote = c. BookingNote
and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
ReleaseRefNo )
That should move the timetable to less than 5 minutes to run. I think
that is acceptable. You can fine tune it further by using more #temp
tables for inner subqueries and joining them with #my_temp using
updates statements.|||> That should move the timetable to less than 5 minutes to run. I think
> that is acceptable. You can fine tune it further by using more #temp
> tables for inner subqueries and joining them with #my_temp using
> updates statements.
SB, how can you make these recommendations without knowledge of the
underlying schema? I'm not saying that employing a temp tables won't
improve performance, it's just that I've often seen temp tables used as a
kludge workaround when the real issue is lack of useful indexes and
non-saragable query expressions. IMHO, a better approach is to understand
and address the underlying cause for the poor performance.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SB" <othellomy@.yahoo.com> wrote in message
news:1185777942.351695.318730@.q75g2000hsh.googlegroups.com...
> On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
>> I want to change following store procedure, it takes around 5 minutes to
>> execute & I calls for each vessel / voyage combination... , Could any
>> one
>> help me to fine tune this store procedure.
>> --
>> CREATE PROCEDURE get_ContainerListinExpBL
>> @.VesselCode varchar(10) ,
>> @.VoyageCode varchar(10),
>> @.LinerCode varchar(10) ,
>> @.BookingList varchar(1000) = null ,
>> @.BLNumber varchar(20) = null
>> AS
>> select distinct t.ContainerNo,
>> T.ContainerType,
>> (select BookingNote
>> from trnActivity TA
>> inner join trnReleasenote RA on
>> RA.ReleaseNoteCode = TA.DO and RA.Liner =>> TA.LinerCode
>> where TA.ContainerNo = T.ContainerNo
>> and TA.LinerCode = t.LinerCode
>> and isnull(TA.VesselCode,'') =>> isnull(t.ImpVesselcode ,'')
>> and isnull(TA.VoyageCode, '') =>> isnull(t.ImpVoyageCode, '')
>> and DO is not Null
>> and TA.ActivityDate =>> (select Max(activityDate ) from
>> trnActivity TB
>> where TB.ContainerNo = t.ContainerNo
>> and TB.LinerCode = t.LinerCode
>> and isnull(TB.VesselCode,'') =>> isnull(t.ImpVesselCode , '')
>> and isnull(TB.VoyageCode, '') =>> isnull(t.ImpVoyageCode, '')
>> and TB.DO is not null)) BookingNote ,
>> t.ImpVesselCode ,
>> t.ImpVoyageCode ,
>> t.ReleaseRefNo
>> from trnExpTracking t
>> --inner join mstISO O on O.ContainerType = T.ContainerType
>> where t.ExpVesselCode = @.VesselCode
>> and t.ExpVoyagecode = @.VoyageCode
>> and t.LinerCode = @.LinerCode
>> union
>> select
>> c.ContainerNo,
>> c.ContainerType,
>> c.BookingNote,
>> c.ImpVesselCode ,
>> c.ImpVoyageCode ,
>> c.ReleaseRefNo
>> from trnExpContainerDetail c
>> where C.LinerCode = @.LinerCode
>> and C.VesselCode = @.VesselCode
>> and C.VoyageCode = @.VoyageCode
>> and C.BLnumber = @.BLnumber
>> GO
>> -- --
>> Thanks in advance
> Hi, try removing the union and use a temp table instead. For example:
> select distinct t.ContainerNo etc...
> into #my_temp
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> insert #mytemp
> select distinct
> c.ContainerNo etc...
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> and not exists (select 1 from #my_temp where #my_temp. ContainerNo => c. ContainerNo
> and #my_temp. ContainerType = c. ContainerType and #my_temp.
> BookingNote = c. BookingNote
> and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
> ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
> ReleaseRefNo )
>
> That should move the timetable to less than 5 minutes to run. I think
> that is acceptable. You can fine tune it further by using more #temp
> tables for inner subqueries and joining them with #my_temp using
> updates statements.
>|||On Jul 30, 6:35 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > That should move the timetable to less than 5 minutes to run. I think
> > that is acceptable. You can fine tune it further by using more #temp
> > tables for inner subqueries and joining them with #my_temp using
> > updates statements.
> SB, how can you make these recommendations without knowledge of the
> underlying schema? I'm not saying that employing a temp tables won't
> improve performance, it's just that I've often seen temp tables used as a
> kludge workaround when the real issue is lack of useful indexes and
> non-saragable query expressions. IMHO, a better approach is to understand
> and address the underlying cause for the poor performance.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SB" <othell...@.yahoo.com> wrote in message
> news:1185777942.351695.318730@.q75g2000hsh.googlegroups.com...
>
> > On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
> >> I want to change following store procedure, it takes around 5 minutes to
> >> execute & I calls for each vessel / voyage combination... , Could any
> >> one
> >> help me to fine tune this store procedure.
> >> --
> >> CREATE PROCEDURE get_ContainerListinExpBL
> >> @.VesselCode varchar(10) ,
> >> @.VoyageCode varchar(10),
> >> @.LinerCode varchar(10) ,
> >> @.BookingList varchar(1000) = null ,
> >> @.BLNumber varchar(20) = null
> >> AS
> >> select distinct t.ContainerNo,
> >> T.ContainerType,
> >> (select BookingNote
> >> from trnActivity TA
> >> inner join trnReleasenote RA on
> >> RA.ReleaseNoteCode = TA.DO and RA.Liner => >> TA.LinerCode
> >> where TA.ContainerNo = T.ContainerNo
> >> and TA.LinerCode = t.LinerCode
> >> and isnull(TA.VesselCode,'') => >> isnull(t.ImpVesselcode ,'')
> >> and isnull(TA.VoyageCode, '') => >> isnull(t.ImpVoyageCode, '')
> >> and DO is not Null
> >> and TA.ActivityDate => >> (select Max(activityDate ) from
> >> trnActivity TB
> >> where TB.ContainerNo = t.ContainerNo
> >> and TB.LinerCode = t.LinerCode
> >> and isnull(TB.VesselCode,'') => >> isnull(t.ImpVesselCode , '')
> >> and isnull(TB.VoyageCode, '') => >> isnull(t.ImpVoyageCode, '')
> >> and TB.DO is not null)) BookingNote ,
> >> t.ImpVesselCode ,
> >> t.ImpVoyageCode ,
> >> t.ReleaseRefNo
> >> from trnExpTracking t
> >> --inner join mstISO O on O.ContainerType = T.ContainerType
> >> where t.ExpVesselCode = @.VesselCode
> >> and t.ExpVoyagecode = @.VoyageCode
> >> and t.LinerCode = @.LinerCode
> >> union
> >> select
> >> c.ContainerNo,
> >> c.ContainerType,
> >> c.BookingNote,
> >> c.ImpVesselCode ,
> >> c.ImpVoyageCode ,
> >> c.ReleaseRefNo
> >> from trnExpContainerDetail c
> >> where C.LinerCode = @.LinerCode
> >> and C.VesselCode = @.VesselCode
> >> and C.VoyageCode = @.VoyageCode
> >> and C.BLnumber = @.BLnumber
> >> GO
> >> -- --
> >> Thanks in advance
> > Hi, try removing the union and use a temp table instead. For example:
> > select distinct t.ContainerNo etc...
> > into #my_temp
> > from trnExpTracking t
> > --inner join mstISO O on O.ContainerType = T.ContainerType
> > where t.ExpVesselCode = @.VesselCode
> > and t.ExpVoyagecode = @.VoyageCode
> > and t.LinerCode = @.LinerCode
> > insert #mytemp
> > select distinct
> > c.ContainerNo etc...
> > from trnExpContainerDetail c
> > where C.LinerCode = @.LinerCode
> > and C.VesselCode = @.VesselCode
> > and C.VoyageCode = @.VoyageCode
> > and C.BLnumber = @.BLnumber
> > and not exists (select 1 from #my_temp where #my_temp. ContainerNo => > c. ContainerNo
> > and #my_temp. ContainerType = c. ContainerType and #my_temp.
> > BookingNote = c. BookingNote
> > and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
> > ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
> > ReleaseRefNo )
> > That should move the timetable to less than 5 minutes to run. I think
> > that is acceptable. You can fine tune it further by using more #temp
> > tables for inner subqueries and joining them with #my_temp using
> > updates statements.- Hide quoted text -
> - Show quoted text -
Dan I don't need schema. If I need one I will ask tl from you. Thanks.|||With all due respect SB, if you want to provide real help to the OP you DO
need schema, indexes and possibly even data distribution information. Your
stab in the dark could well be WORSE performing due the large I/O overhead
associated with temporary tables (and the sproc recompiles associated with
them too). Even worse is that it completely avoids the possibility that a
single appropriate index addition could improve the sproc performance 1-4
orders of magnitude.
We on this forum should strive to give posters the right tools to help them
in the future as well as simply attempting to solve their current problem.
With just a bit more information from the OP we could have a dialog that
provided good mentoring to all while providing the BEST solution to Nitu.
--
TheSQLGuru
President
Indicium Resources, Inc.
"SB" <othellomy@.yahoo.com> wrote in message
news:1185852523.248498.24130@.d55g2000hsg.googlegroups.com...
> On Jul 30, 6:35 pm, "Dan Guzman" <guzma...@.nospam-
> online.sbcglobal.net> wrote:
>> > That should move the timetable to less than 5 minutes to run. I think
>> > that is acceptable. You can fine tune it further by using more #temp
>> > tables for inner subqueries and joining them with #my_temp using
>> > updates statements.
>> SB, how can you make these recommendations without knowledge of the
>> underlying schema? I'm not saying that employing a temp tables won't
>> improve performance, it's just that I've often seen temp tables used as a
>> kludge workaround when the real issue is lack of useful indexes and
>> non-saragable query expressions. IMHO, a better approach is to
>> understand
>> and address the underlying cause for the poor performance.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "SB" <othell...@.yahoo.com> wrote in message
>> news:1185777942.351695.318730@.q75g2000hsh.googlegroups.com...
>>
>> > On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
>> >> I want to change following store procedure, it takes around 5 minutes
>> >> to
>> >> execute & I calls for each vessel / voyage combination... , Could any
>> >> one
>> >> help me to fine tune this store procedure.
>> >> --
>> >> CREATE PROCEDURE get_ContainerListinExpBL
>> >> @.VesselCode varchar(10) ,
>> >> @.VoyageCode varchar(10),
>> >> @.LinerCode varchar(10) ,
>> >> @.BookingList varchar(1000) = null ,
>> >> @.BLNumber varchar(20) = null
>> >> AS
>> >> select distinct t.ContainerNo,
>> >> T.ContainerType,
>> >> (select BookingNote
>> >> from trnActivity TA
>> >> inner join trnReleasenote RA on
>> >> RA.ReleaseNoteCode = TA.DO and RA.Liner =>> >> TA.LinerCode
>> >> where TA.ContainerNo = T.ContainerNo
>> >> and TA.LinerCode = t.LinerCode
>> >> and isnull(TA.VesselCode,'') =>> >> isnull(t.ImpVesselcode ,'')
>> >> and isnull(TA.VoyageCode, '') =>> >> isnull(t.ImpVoyageCode, '')
>> >> and DO is not Null
>> >> and TA.ActivityDate =>> >> (select Max(activityDate ) from
>> >> trnActivity TB
>> >> where TB.ContainerNo = t.ContainerNo
>> >> and TB.LinerCode = t.LinerCode
>> >> and isnull(TB.VesselCode,'') =>> >> isnull(t.ImpVesselCode , '')
>> >> and isnull(TB.VoyageCode, '') =>> >> isnull(t.ImpVoyageCode, '')
>> >> and TB.DO is not null)) BookingNote ,
>> >> t.ImpVesselCode ,
>> >> t.ImpVoyageCode ,
>> >> t.ReleaseRefNo
>> >> from trnExpTracking t
>> >> --inner join mstISO O on O.ContainerType = T.ContainerType
>> >> where t.ExpVesselCode = @.VesselCode
>> >> and t.ExpVoyagecode = @.VoyageCode
>> >> and t.LinerCode = @.LinerCode
>> >> union
>> >> select
>> >> c.ContainerNo,
>> >> c.ContainerType,
>> >> c.BookingNote,
>> >> c.ImpVesselCode ,
>> >> c.ImpVoyageCode ,
>> >> c.ReleaseRefNo
>> >> from trnExpContainerDetail c
>> >> where C.LinerCode = @.LinerCode
>> >> and C.VesselCode = @.VesselCode
>> >> and C.VoyageCode = @.VoyageCode
>> >> and C.BLnumber = @.BLnumber
>> >> GO
>> >> -- --
>> >> Thanks in advance
>> > Hi, try removing the union and use a temp table instead. For example:
>> > select distinct t.ContainerNo etc...
>> > into #my_temp
>> > from trnExpTracking t
>> > --inner join mstISO O on O.ContainerType = T.ContainerType
>> > where t.ExpVesselCode = @.VesselCode
>> > and t.ExpVoyagecode = @.VoyageCode
>> > and t.LinerCode = @.LinerCode
>> > insert #mytemp
>> > select distinct
>> > c.ContainerNo etc...
>> > from trnExpContainerDetail c
>> > where C.LinerCode = @.LinerCode
>> > and C.VesselCode = @.VesselCode
>> > and C.VoyageCode = @.VoyageCode
>> > and C.BLnumber = @.BLnumber
>> > and not exists (select 1 from #my_temp where #my_temp. ContainerNo =>> > c. ContainerNo
>> > and #my_temp. ContainerType = c. ContainerType and #my_temp.
>> > BookingNote = c. BookingNote
>> > and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
>> > ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
>> > ReleaseRefNo )
>> > That should move the timetable to less than 5 minutes to run. I think
>> > that is acceptable. You can fine tune it further by using more #temp
>> > tables for inner subqueries and joining them with #my_temp using
>> > updates statements.- Hide quoted text -
>> - Show quoted text -
> Dan I don't need schema. If I need one I will ask tl from you. Thanks.
>

Help for Fine Tune Store procedure to increase its performance

I want to change following store procedure, it takes around 5 minutes to
execute & I calls for each vessel / voyage combination... , Could any one
help me to fine tune this store procedure.
--
CREATE PROCEDURE get_ContainerListinExpBL
@.VesselCode varchar(10) ,
@.VoyageCode varchar(10),
@.LinerCode varchar(10) ,
@.BookingList varchar(1000) = null ,
@.BLNumber varchar(20) = null
AS
select distinct t.ContainerNo,
T.ContainerType,
(select BookingNote
from trnActivity TA
inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner =
TA.LinerCode
where TA.ContainerNo = T.ContainerNo
and TA.LinerCode = t.LinerCode
and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
and DO is not Null
and TA.ActivityDate =
(select Max(activityDate ) from
trnActivity TB
where TB.ContainerNo = t.ContainerNo
and TB.LinerCode = t.LinerCode
and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
and TB.DO is not null)) BookingNote ,
t.ImpVesselCode ,
t.ImpVoyageCode ,
t.ReleaseRefNo
from trnExpTracking t
--inner join mstISO O on O.ContainerType = T.ContainerType
where t.ExpVesselCode = @.VesselCode
and t.ExpVoyagecode = @.VoyageCode
and t.LinerCode = @.LinerCode
union
select
c.ContainerNo,
c.ContainerType,
c.BookingNote,
c.ImpVesselCode ,
c.ImpVoyageCode ,
c.ReleaseRefNo
from trnExpContainerDetail c
where C.LinerCode = @.LinerCode
and C.VesselCode = @.VesselCode
and C.VoyageCode = @.VoyageCode
and C.BLnumber = @.BLnumber
GO
-- --
Thanks in advancePlease also post DDL (CREATE TABLE statements, including constraints and
indexes). The underlying schema can affect query performance significantly.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nitu" <Nitu@.discussions.microsoft.com> wrote in message
news:05229BAA-ABA5-4C85-A298-616D82F629D3@.microsoft.com...
>I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner =
> TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate =
> (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
>
> -- --
> Thanks in advance|||Nitu,
You should post the DDL, including all indexes, that is key. Also, ask
yourself if you really need to handle NULL values in VesselCode and/or
VoyageCode, because the use of ISNULL() will disallow the use of an
index for those columns.
Gert-Jan
Nitu wrote:
> I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode
= TA.DO and RA.Liner =
> TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesse
lcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoy
ageCode, '')
> and DO is not Null
> and TA.ActivityDate =
> (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselC
ode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVo
yageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
> -- --
> Thanks in advance|||On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
> I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode
= TA.DO and RA.Liner =
> TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesse
lcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoy
ageCode, '')
> and DO is not Null
> and TA.ActivityDate =
> (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselC
ode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVo
yageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
> -- --
> Thanks in advance
Hi, try removing the union and use a temp table instead. For example:
select distinct t.ContainerNo etc...
into #my_temp
from trnExpTracking t
--inner join mstISO O on O.ContainerType = T.ContainerType
where t.ExpVesselCode = @.VesselCode
and t.ExpVoyagecode = @.VoyageCode
and t.LinerCode = @.LinerCode
insert #mytemp
select distinct
c.ContainerNo etc...
from trnExpContainerDetail c
where C.LinerCode = @.LinerCode
and C.VesselCode = @.VesselCode
and C.VoyageCode = @.VoyageCode
and C.BLnumber = @.BLnumber
and not exists (select 1 from #my_temp where #my_temp. ContainerNo =
c. ContainerNo
and #my_temp. ContainerType = c. ContainerType and #my_temp.
BookingNote = c. BookingNote
and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
ReleaseRefNo )
That should move the timetable to less than 5 minutes to run. I think
that is acceptable. You can fine tune it further by using more #temp
tables for inner subqueries and joining them with #my_temp using
updates statements.|||> That should move the timetable to less than 5 minutes to run. I think
> that is acceptable. You can fine tune it further by using more #temp
> tables for inner subqueries and joining them with #my_temp using
> updates statements.
SB, how can you make these recommendations without knowledge of the
underlying schema? I'm not saying that employing a temp tables won't
improve performance, it's just that I've often seen temp tables used as a
kludge workaround when the real issue is lack of useful indexes and
non-saragable query expressions. IMHO, a better approach is to understand
and address the underlying cause for the poor performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"SB" <othellomy@.yahoo.com> wrote in message
news:1185777942.351695.318730@.q75g2000hsh.googlegroups.com...
> On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
> Hi, try removing the union and use a temp table instead. For example:
> select distinct t.ContainerNo etc...
> into #my_temp
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> insert #mytemp
> select distinct
> c.ContainerNo etc...
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> and not exists (select 1 from #my_temp where #my_temp. ContainerNo =
> c. ContainerNo
> and #my_temp. ContainerType = c. ContainerType and #my_temp.
> BookingNote = c. BookingNote
> and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
> ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
> ReleaseRefNo )
>
> That should move the timetable to less than 5 minutes to run. I think
> that is acceptable. You can fine tune it further by using more #temp
> tables for inner subqueries and joining them with #my_temp using
> updates statements.
>|||On Jul 30, 6:35 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> SB, how can you make these recommendations without knowledge of the
> underlying schema? I'm not saying that employing a temp tables won't
> improve performance, it's just that I've often seen temp tables used as a
> kludge workaround when the real issue is lack of useful indexes and
> non-saragable query expressions. IMHO, a better approach is to understand
> and address the underlying cause for the poor performance.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SB" <othell...@.yahoo.com> wrote in message
> news:1185777942.351695.318730@.q75g2000hsh.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -
Dan I don't need schema. If I need one I will ask tl from you. Thanks.|||With all due respect SB, if you want to provide real help to the OP you DO
need schema, indexes and possibly even data distribution information. Your
stab in the dark could well be WORSE performing due the large I/O overhead
associated with temporary tables (and the sproc recompiles associated with
them too). Even worse is that it completely avoids the possibility that a
single appropriate index addition could improve the sproc performance 1-4
orders of magnitude.
We on this forum should strive to give posters the right tools to help them
in the future as well as simply attempting to solve their current problem.
With just a bit more information from the OP we could have a dialog that
provided good mentoring to all while providing the BEST solution to Nitu.
TheSQLGuru
President
Indicium Resources, Inc.
"SB" <othellomy@.yahoo.com> wrote in message
news:1185852523.248498.24130@.d55g2000hsg.googlegroups.com...
> On Jul 30, 6:35 pm, "Dan Guzman" <guzma...@.nospam-
> online.sbcglobal.net> wrote:
> Dan I don't need schema. If I need one I will ask tl from you. Thanks.
>

help for creating stored procedure

ALTER PROCEDURE companyinsert

@.companyid INT IDENTITY(1,1) NOT NULL,
@.companyname varchar(20),
@.address1 varchar(30)

AS

INSERT INTO companymaster
( companyname, address1)
VALUES (@.companyname,@.address1)

i don't want the companyname having the same names are recorded again with the different company id..

Can anyone help me and modify my code according it's giving error...in the @.companyid.

It is being done in sql server 2005 with asp.net C# 2005

You cannot declare a parameter with IDENTITY property in a stored proc. I think what you are looking for is more along these lines. You might want to read up documentation on SCOPE_IDENTITY(). Briefly, it returns the Identity value that was created due to your INSERT.

ALTER PROCEDURE companyinsert@.companyidINT OUTPUT,@.companynamevarchar(20),@.address1varchar(30)ASBEGINSET NOCOUNT ONIFNOT EXISTS(SELECT *FROM companymasterWHERE CompanyName = @.companynameAND Address1 = address1)BEGININSERT INTO companymaster ( companyname, address1)VALUES (@.companyname,@.address1)SELECT @.companyid = SCOPE_IDENTITY()ENDSET NOCOUNT OFFEND

help for a search procedure

hello,
please help me to give me solution
i have a table
CREATE TABLE T_STUDENT_MT (ID INT PRIMARY KEY, NAME VARCHAR2(50) NOT
NULL, FATHERNAME VARCHAR2(50), COUNTRY VARCHAR2(50), STATE VARCHAR2(20),
CITY VARCHAR2(30), STREET VARCHAR2(40))
MY DATA IS
INSERT INTO T_STUDENT_MT VALUES (1,
'ABC1','FABC1','CCC1','SSS1','CTT1','STT
1');
INSERT INTO T_STUDENT_MT VALUES (2,
'ABC2','FABC2','CCC2','SSS2','CTT2','STT
2');
INSERT INTO T_STUDENT_MT VALUES (3,
'ABC3',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO T_STUDENT_MT VALUES (4,
'XXBC3','FCDD','ABDD',NULL,NULL,NULL,NUL
L);
PLEASE SUGGEST TO WRITE A PROCEDURE FOR
'CONDITION 1: If i pass iNAME to null it should NOT check in where
condition
'CONDITION 2:if i pass iNAME Parameter as 'A' IT HAS TO CHECK AS NAME
LIKE iNAME || '%'
CREATE PROCEDURE STUDENTSEARCH(iNAME VARCHAR2, iFATHERNAME VARCHAR2 ,
COUNTRY VARCHAR2, STATE VARCHAR2, CITY VARCHAR2, STREET VARCHAR2)
AS
BEGIN
--SINGE QUERY TO RETURN STUDENTT DATA
END;
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!kamal hussain wrote:
> hello,
> please help me to give me solution
> i have a table
> CREATE TABLE T_STUDENT_MT (ID INT PRIMARY KEY, NAME VARCHAR2(50) NOT
> NULL, FATHERNAME VARCHAR2(50), COUNTRY VARCHAR2(50), STATE VARCHAR2(20),
> CITY VARCHAR2(30), STREET VARCHAR2(40))
>
> MY DATA IS
> INSERT INTO T_STUDENT_MT VALUES (1,
> 'ABC1','FABC1','CCC1','SSS1','CTT1','STT
1');
> INSERT INTO T_STUDENT_MT VALUES (2,
> 'ABC2','FABC2','CCC2','SSS2','CTT2','STT
2');
> INSERT INTO T_STUDENT_MT VALUES (3,
> 'ABC3',NULL,NULL,NULL,NULL,NULL,NULL);
> INSERT INTO T_STUDENT_MT VALUES (4,
> 'XXBC3','FCDD','ABDD',NULL,NULL,NULL,NUL
L);
>
> PLEASE SUGGEST TO WRITE A PROCEDURE FOR
> 'CONDITION 1: If i pass iNAME to null it should NOT check in where
> condition
> 'CONDITION 2:if i pass iNAME Parameter as 'A' IT HAS TO CHECK AS NAME
> LIKE iNAME || '%'
>
> CREATE PROCEDURE STUDENTSEARCH(iNAME VARCHAR2, iFATHERNAME VARCHAR2 ,
> COUNTRY VARCHAR2, STATE VARCHAR2, CITY VARCHAR2, STREET VARCHAR2)
> AS
> BEGIN
> --SINGE QUERY TO RETURN STUDENTT DATA
> END;
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I'm assuming condition 1 means if the variable @.Name is NULL, then you
want all the rows.
Try this:
CREATE PROCEDURE STUDENTSEARCH(
@.NAME VARCHAR(50) = NULL
)
AS
SELECT [ID]
,[NAME]
,FATHERNAME
,COUNTRY
,STATE
,CITY
,STREET
FROM T_STUDENT_MT
WHERE ((@.NAME IS NOT NULL AND [NAME] = @.NAME)
OR (@.NAME IS NULL))
OR (LEN(@.NAME)=1 AND [NAME] LIKE @.NAME + '%')
Note: In SQL Server there isn't a data type of VARCHAR2 and the
concatenation symbol is the plus sign (+).
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQi0xPoechKqOuFEgEQJmOwCg1I3RunB5ASmn
7rtRnj7EZDpoKTkAoMrL
wTkd98HDDTfkao35SpHRNH7S
=WkyP
--END PGP SIGNATURE--

Help Cursor Based Stored Procedure Is Getting Slower and Slower!

I am begginner at best so I hope someone that is better can help.
I have a stored procedure that updates a view that I wrote using 2
cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn't
do it using reqular transact SQL.

The problem is that this procedure is taking longer and longer to run.
Up to 5 hours now! It is anaylizing about 30,000 records. I think
partly because we add new records every month.

The procedure works like this.
The first Cursor stores a unique account and duedate combination from
the view.
It then finds all the accts in the view that have that account duedate
combo and loads them into Cursor 2 this groups them together for data
manipulation. The accounts have to be grouped this way because a
account can have different due dates and multiple records within each
account due date combo and they need to be looked at this way as
little singular groups.

Here is my procedure I hope someone can shead some light on this. My
boss is giving me heck about it. (I think he thinks Girls cant code!)
I got this far I hope someone can help me optimize it further.

CREATE PROCEDURE dbo.sp_PromiseStatus
AS

BEGIN
SET NOCOUNT ON

/* Global variables */
DECLARE @.tot_pay money
DECLARE @.rec_upd VARCHAR(1)
DECLARE @.todays_date varchar(12)
DECLARE @.mActivityDate2_temp datetime
DECLARE @.tot_paydate datetime
/* variables for cursor ACT_CUR1*/
DECLARE @.mAcct_Num1 BIGINT
DECLARE @.mDueDate1 datetime

/* variables for ACT_CUR2 */
DECLARE @.mAcct_Num2 BIGINT
DECLARE @.mActivity_Date2 datetime
DECLARE @.mPromise_Amt_1 money
DECLARE @.mPromise_Status varchar(3)
DECLARE @.mCurrent_Due_Amt money
DECLARE @.mDPD int
DECLARE @.mPromise_Date datetime

SELECT @.todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))
+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))
+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''

DECLARE ACT_CUR1 CURSOR FOR
SELECT DISTINCT
A.ACCT_NUM,
A.DUE_DATE
FROM VWAPPLICABLEPROMISEACTIVITYRECORDS A
OPEN ACT_CUR1
FETCH NEXT FROM ACT_CUR1 INTO @.mAcct_Num1 , @.mDueDate1

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT @.rec_upd = 'N '

DECLARE ACT_CUR2 CURSOR FOR
SELECT
B.ACCT_NUM,
B.ACTIVITY_DATE,
B.PROMISE_AMT_1,
B.PROMISE_STATUS,
B.CURRENT_DUE_AMT,
B.DAYS_DELINQUENT_NUM,
B.PROMISE_DATE_1
FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)
WHERE B.ACCT_NUM = @.mAcct_Num1
ANDB.DUE_DATE = @.mDueDate1
ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASE
B.Time_Obtained
WHEN 0 THEN 0
ELSE 1
END Desc, B.Time_Obtained

OPEN ACT_CUR2
FETCH NEXT FROM ACT_CUR2
INTO @.mAcct_Num2 ,@.mActivity_Date2,@.mPromise_Amt_1,@.mPromise_Status ,@.mCurrent_Due_Amt,@.mDPD,@.mPromise_Date

WHILE (@.@.FETCH_STATUS = 0)
BEGIN

--CHECK----------------------
--DECLARE @.PrintVariable2 VARCHAR (8000)
--SELECT @.PrintVariable2 = CAST(@.MACCT_NUM2 AS VARCHAR)+'
'+CAST(@.MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@.MPROMISE_AMT_1 AS
VARCHAR)+' '+CAST(@.MPROMISE_STATUS AS VARCHAR)+'
'+CAST(@.mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@.mDPD AS VARCHAR)+'
'+CAST(@.mPromise_Date AS VARCHAR)
--PRINT @.PrintVariable2
--END
CHECK------------------

IF @.mDPD >= 30
BEGIN

SELECT @.tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))
FROM vwAplicablePayments C
WHERE C.ACCT_NUM = @.mAcct_Num2
ANDC.ACTIVITY_DATE >= @.mActivity_Date2
ANDC.ACTIVITY_DATE < @.mActivity_Date2 + 15
--CHECK----------------------
--DECLARE @.PrintVariable3 VARCHAR (8000)
--SELECT @.PrintVariable3 ='Greater=30 DOLLARS COLLECTED'
--PRINT @.PrintVariable3
--END
CHECK------------------
END
ELSE IF @.mDPD < 30
BEGIN

SELECT @.tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))
FROM vwAplicablePayments C
WHERE C.ACCT_NUM = @.mAcct_Num2
ANDC.ACTIVITY_DATE >= @.mActivity_Date2
ANDC.ACTIVITY_DATE BETWEEN @.mActivity_Date2 AND
@.mPromise_Date + 5
--CHECK---------------------
--DECLARE @.PrintVariable4 VARCHAR (8000)
--SELECT @.PrintVariable4 ='Less 30 DOLLARS COLLECTED'
--PRINT @.PrintVariable4
--END CHECK------------------
END

------------MY REVISED
LOGIC-----------------
IF @.rec_upd = 'N'
BEGIN

IF @.mDPD >= 30
BEGIN

SELECT @.mActivityDate2_temp = @.mActivity_Date2 + 15

--DECLARE @.PrintVariable5 VARCHAR (8000)
--SELECT @.PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'
--PRINT @.PrintVariable5

END
ELSE IF @.mDPD < 30
BEGIN
SELECT @.mActivityDate2_temp = @.mPromise_Date + 5

--DECLARE @.PrintVariable6 VARCHAR (8000)
--SELECT @.PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'
--PRINT @.PrintVariable6

END

IF @.tot_pay >= 0.9 * @.mCurrent_Due_Amt--used to be promise amt
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET PROMISE_STATUS = 'PK',
TOTAL_DOLLARS_COLL = @.tot_pay
WHERE CURRENT OF ACT_CUR2

--This statement updates the time that the status was placed
into PK.
IF @.mPromise_Status IN ('PTP','OP')
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET Status_Date = @.todays_date
WHERE CURRENT OF ACT_CUR2
END

SELECT @.rec_upd = 'Y '
END
IF ((@.tot_pay < 0.9 * @.mCurrent_Due_Amt) OR @.tot_pay IS NULL)
AND( @.mActivityDate2_temp > @.todays_date )--need to put 1day
of month here for snapshot9/01/2004
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SETPROMISE_STATUS = 'OP'
WHERE CURRENT OF ACT_CUR2

--This statement updates the time that the status was placed
into OP which is the original Activity Date.
--The record will hold this date until it goes into PK,PB,or
IP.
IF @.mPromise_Status IN ('PTP','OP')
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET Status_Date = @.mActivity_Date2
WHERE CURRENT OF ACT_CUR2
END

END
ELSE IF ((@.tot_pay < 0.9 * @.mCurrent_Due_Amt) OR @.tot_pay IS
NULL)
AND( @.mActivityDate2_temp <= @.todays_date )--need to put 1day
of month here for snapshot 9/01/2004
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SETPROMISE_STATUS = 'PB',
TOTAL_DOLLARS_COLL = case when @.tot_pay is null
then 0 else @.tot_pay end
WHERE CURRENT OF ACT_CUR2

--This statement updates the time that the status was placed
into PB.
IF @.mPromise_Status IN ('PTP','OP')
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET Status_Date = @.todays_date
WHERE CURRENT OF ACT_CUR2
END

END
END
ELSE IF @.rec_upd = 'Y'
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SETPROMISE_STATUS = 'IP',
TOTAL_DOLLARS_COLL = 0
WHERE CURRENT OF ACT_CUR2

--This statement updates the time that the status was placed
into IP.
IF @.mPromise_Status NOT IN ('IP')
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET Status_Date = @.todays_date
WHERE CURRENT OF ACT_CUR2
END

END

FETCH NEXT FROM ACT_CUR2 INTO @.mAcct_Num2
,@.mActivity_Date2,@.mPromise_Amt_1,@.mPromise_Status ,@.mCurrent_Due_Amt,@.mDPD,@.mPromise_Date
END
CLOSE ACT_CUR2
DEALLOCATE ACT_CUR2

FETCH NEXT FROM ACT_CUR1 INTO @.mAcct_Num1 , @.mDueDate1
END
CLOSE ACT_CUR1
DEALLOCATE ACT_CUR1

SET NOCOUNT OFF

END
GOWithout getting into code, I think you should consider using faster type
of cursor if possible. FORWARD ONLY for example|||If you are a beginner then avoid cursors. They are rarely a necessary or
good solution to a problem in SQL. You will learn bad practices if you mess
with cursors before you can write good set-based SQL.

It doesn't look like you are doing anything in your code for which a cursor
makes sense so I recommend you start again from scratch. If you need help,
try to pare the problem down to it's essential features and then post again
with more information (DDL, some sample data, required results). This
article explains how best to ask for help:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--|||>> I am beginner at best so I hope someone that is better can help. <<

Virutally everything you have done is wrong.

1) The data element names violate ISO-11179 standards. What does a
VARCHAR(1) mean?

2) You should never write a cursor in an applications. Thye are for
utility programs.

3) You are using FLOAT and MONEY for currency amounts. And you got
the CAST() syntax wrong.

4) Unlike the 3GL languages, SQL has temporal data types. Why did you
avoid them in favor of COBOL-style string processing? You even load
the current timestamp into a string!

>> The problem is that this procedure is taking longer and longer to
run. Up to 5 hours now! It is anaylizing about 30,000 records. I think
partly because we add new records [sic] every month. <<

No. The basic algorithm you have written is a COBOL tape file merge.
30K rows is tiny.

>> The first Cursor [magnetic tape file] stores a unique account and
duedate combination from the view. <<

The view name has a silly "vw_" prefix in violation of ISO-11179. And
a table has no records!!! Rows are not records! Until you understand
that you will never write good SQL. A table is a set, and in SQL we
process data in whole sets, not single records.

>> It then finds all the accts in the view that have that account
duedate combo .. <<

No, you write a query that finds the set of (account_nbr, duedate)

>> .. and loads them into Cursor 2 this groups them together for data
manipulation. The accounts have to be grouped this way because an
account can have different due dates and multiple records within each
account due date combo and they need to be looked at this way as
little singular groups. <<

Think about the phrase "single group" for a minute :) A GROUP BY is
used to build grouped tables.

What you seem to be trying to do is update a table of promises. That
should be one UPDATE statement. The skeleton for the statement would
be something like this:

CREATE PROCEDURE PromiseStatus (..)
AS
UPDATE ApplicablePromises
SET status_date = CURRENT_TIMESTAMP,
promise_status = CASE WHEN ..END,
total_dollars_coll
= (SELECT SUM(P.pay_amt),
FROM ApplicablePromises AS A, Applicablepayments AS P
WHERE P.acct_num = A.acct_num
AND ApplicablePromises.acct_num = A.account_num
AND ApplicablePromises.due_date = A.due_date
AND P.activity_date BETWEEN ...
AND GROUP BY A.acct_num, A.due_date)
WHERE ... ;

You put all the IF_THEN_ELSE logic into a CASE expression. You put
the total into a scalar subquery. You use temporal functions to get
the current timestamp.

Quit thinking about one column at a time. That is how you process a
field in a record. A table is made of rows, and you think of the row
as the unit of work.|||Philip Mette (philipdm@.msn.com) writes:
> I am begginner at best so I hope someone that is better can help.
> I have a stored procedure that updates a view that I wrote using 2
> cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn't
> do it using reqular transact SQL.
> The problem is that this procedure is taking longer and longer to run.
> Up to 5 hours now! It is anaylizing about 30,000 records. I think
> partly because we add new records every month.

While cursor-based solutions sometimes may be defendible and even be
the only solution, the cost for a cursor can be pretty severe. The
corresponding set-based solution is often several magnitudes faster.
Processing 30000 rows with a cursor is not going to be anywhere near
fast.

Just like David I would recommend you to start with a clean paper. I
started to look at your code, but, frankly, all the uppercase and the
poor formatting is quite deterring. And not knowing the tables and not
having sample data is not making things any easier.

In the short run, you may make some improvements by making the cursors
INSENSITIVE by adding this keyword before CURSOR, and by replacing
WHERE CURRENT OF with the actual key values.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ok well I have been yelled at worse;) No I see what you are saying. I
should have used Char variable not Varchar(1).That was a adjustment
later on that frankly wasnt changed properly. I used Cursors based on
a recommendation by a "SQL Guru" Obviously this is not a good
description of him. I am going to try this using the skeleton you gave
me.It is pointing me in a right direction I think.
Again please understand I am a beginner Query writer when it comes to
SQL and I am very good with VB ,.Net and Java(Used to do front end
work and analysis not DB.)I quess I was thinking of this like code
language,ie the use of arrays etc. Thanks for your 'direct' honesty
and help.

jcelko212@.earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0411242243.2c96bbf0@.posting.google.com>...
> >> I am beginner at best so I hope someone that is better can help. <<
> Virutally everything you have done is wrong.
> 1) The data element names violate ISO-11179 standards. What does a
> VARCHAR(1) mean?
> 2) You should never write a cursor in an applications. Thye are for
> utility programs.
> 3) You are using FLOAT and MONEY for currency amounts. And you got
> the CAST() syntax wrong.
> 4) Unlike the 3GL languages, SQL has temporal data types. Why did you
> avoid them in favor of COBOL-style string processing? You even load
> the current timestamp into a string!
> >> The problem is that this procedure is taking longer and longer to
> run. Up to 5 hours now! It is anaylizing about 30,000 records. I think
> partly because we add new records [sic] every month. <<
> No. The basic algorithm you have written is a COBOL tape file merge.
> 30K rows is tiny.
> >> The first Cursor [magnetic tape file] stores a unique account and
> duedate combination from the view. <<
> The view name has a silly "vw_" prefix in violation of ISO-11179. And
> a table has no records!!! Rows are not records! Until you understand
> that you will never write good SQL. A table is a set, and in SQL we
> process data in whole sets, not single records.
> >> It then finds all the accts in the view that have that account
> duedate combo .. <<
> No, you write a query that finds the set of (account_nbr, duedate)
> >> .. and loads them into Cursor 2 this groups them together for data
> manipulation. The accounts have to be grouped this way because an
> account can have different due dates and multiple records within each
> account due date combo and they need to be looked at this way as
> little singular groups. <<
> Think about the phrase "single group" for a minute :) A GROUP BY is
> used to build grouped tables.
> What you seem to be trying to do is update a table of promises. That
> should be one UPDATE statement. The skeleton for the statement would
> be something like this:
> CREATE PROCEDURE PromiseStatus (..)
> AS
> UPDATE ApplicablePromises
> SET status_date = CURRENT_TIMESTAMP,
> promise_status = CASE WHEN ..END,
> total_dollars_coll
> = (SELECT SUM(P.pay_amt),
> FROM ApplicablePromises AS A, Applicablepayments AS P
> WHERE P.acct_num = A.acct_num
> AND ApplicablePromises.acct_num = A.account_num
> AND ApplicablePromises.due_date = A.due_date
> AND P.activity_date BETWEEN ...
> AND GROUP BY A.acct_num, A.due_date)
> WHERE ... ;
> You put all the IF_THEN_ELSE logic into a CASE expression. You put
> the total into a scalar subquery. You use temporal functions to get
> the current timestamp.
> Quit thinking about one column at a time. That is how you process a
> field in a record. A table is made of rows, and you think of the row
> as the unit of work.|||Thanks Erland.
A few Questions:
1.What does insensitive do?
2.Instead of Where Current Of
If I used Activity_Record_Num(The Primary Key)
which is distinctive would that work?
3.I use a statement called (UPDLOCK) in this part of the code:
DECLARE ACT_CUR2 CURSOR FOR
SELECT
B.ACCT_NUM,
B.ACTIVITY_DATE,
B.PROMISE_AMT_1,
B.PROMISE_STATUS,
B.CURRENT_DUE_AMT,
B.DAYS_DELINQUENT_NUM,
B.PROMISE_DATE_1
FROM VWPROMISEACTIVITYRECORDS B (UPDLOCK)

Does that effect performance speed as well? This is the only process
that runs against this table so I am wondering if it is needed.

Just so you know I am working on a rewrite like you suggested. In the
short, I would like to add these suggestions that you made to the
cursor based SP.

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95ACF2C23D6DBYazorman@.127.0.0.1>...
> Philip Mette (philipdm@.msn.com) writes:
> > I am begginner at best so I hope someone that is better can help.
> > I have a stored procedure that updates a view that I wrote using 2
> > cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn't
> > do it using reqular transact SQL.
> > The problem is that this procedure is taking longer and longer to run.
> > Up to 5 hours now! It is anaylizing about 30,000 records. I think
> > partly because we add new records every month.
> While cursor-based solutions sometimes may be defendible and even be
> the only solution, the cost for a cursor can be pretty severe. The
> corresponding set-based solution is often several magnitudes faster.
> Processing 30000 rows with a cursor is not going to be anywhere near
> fast.
> Just like David I would recommend you to start with a clean paper. I
> started to look at your code, but, frankly, all the uppercase and the
> poor formatting is quite deterring. And not knowing the tables and not
> having sample data is not making things any easier.
> In the short run, you may make some improvements by making the cursors
> INSENSITIVE by adding this keyword before CURSOR, and by replacing
> WHERE CURRENT OF with the actual key values.|||Philip Mette (philipdm@.msn.com) writes:
> Thanks Erland.
> A few Questions:
> 1.What does insensitive do?

The meaning of INSENSITIVE is that the dataset does not change
once the cursor has been created. SQL Server copies the data to a
worktable. This may sound like some overhead, and it probably is, but
it's a small overhead in comparison with the iterative processing.

The default cursor type is keyset. I think it means that the keys are
stable, but updates in non-key columns will be reflected. But I have
never fully grasped keyset cursors so I could be wrong. What I have
seen is some really horrible query plans to set up a keyset-driven
cursor. Sure, it was in 6.5, but I see no reason to try it again.

> 2.Instead of Where Current Of
> If I used Activity_Record_Num(The Primary Key)
> which is distinctive would that work?

I didn't look to close on your code, but you should pick data from
the cursor which identifies the current row(s) being processed. (Note
that you could run a cursor over an aggregate, and thus update several
rows at same time. There are situations when this is a possible solution.

> 3.I use a statement called (UPDLOCK) in this part of the code:
>...
> Does that effect performance speed as well?

Not very likely. Since you don't seem to have an active transaction, the
effect of the UPDLOCK is none anyway.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

I got the process run time to 2:00 minutes! The solution was to create
a composite index on Account and Due_date in the tables, which is what
is in my where clauses in my select statements. That and changing the
cursors to Insensitive, made this query fast. This amount of time is
more than acceptable. Thanks for pointing me in the right direction. I
am going to continue to look into a transact SQL solution in the
future.
Thanks Again,

Jill
(Using Phil's Login Name. Dont have a google acct.)

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95ADDFEC37F0BYazorman@.127.0.0.1>...
> Philip Mette (philipdm@.msn.com) writes:
> > Thanks Erland.
> > A few Questions:
> > 1.What does insensitive do?
> The meaning of INSENSITIVE is that the dataset does not change
> once the cursor has been created. SQL Server copies the data to a
> worktable. This may sound like some overhead, and it probably is, but
> it's a small overhead in comparison with the iterative processing.
> The default cursor type is keyset. I think it means that the keys are
> stable, but updates in non-key columns will be reflected. But I have
> never fully grasped keyset cursors so I could be wrong. What I have
> seen is some really horrible query plans to set up a keyset-driven
> cursor. Sure, it was in 6.5, but I see no reason to try it again.
> > 2.Instead of Where Current Of
> > If I used Activity_Record_Num(The Primary Key)
> > which is distinctive would that work?
> I didn't look to close on your code, but you should pick data from
> the cursor which identifies the current row(s) being processed. (Note
> that you could run a cursor over an aggregate, and thus update several
> rows at same time. There are situations when this is a possible solution.
> > 3.I use a statement called (UPDLOCK) in this part of the code:
> >...
> > Does that effect performance speed as well?
> Not very likely. Since you don't seem to have an active transaction, the
> effect of the UPDLOCK is none anyway.|||Hello guys,

I have may be similar problem.
I need to recalculate a progressive balance based on a table with the
following structure:

EcrID varchar(15)
AccountNumber varchar(15)
EcrDate DateTime
EcrAmount float
ProgBalance float

The program has to calculate the progbalance which is for each row :
progbalance=progbalance + EcrAmount
Rows are sorted by AccountNumber and EcrDate.

Is it possible to that by a single update statement or do I have to
use cursors?

(Sorry for my english)

Many thanks for your help.|||On 29 Nov 2004 10:11:58 -0800, badiane wrote:

>Hello guys,
>I have may be similar problem.
>I need to recalculate a progressive balance based on a table with the
>following structure:
>EcrID varchar(15)
>AccountNumber varchar(15)
>EcrDate DateTime
>EcrAmount float
>ProgBalance float
>The program has to calculate the progbalance which is for each row :
>progbalance=progbalance + EcrAmount
>Rows are sorted by AccountNumber and EcrDate.
>Is it possible to that by a single update statement or do I have to
>use cursors?
>(Sorry for my english)
>Many thanks for your help.

Hi badiane,

The best option is to remove ProgBalance from the table and calculate it
in a view instead. If it's a column in the table, you'll have to keep
updating large chunks of data in the table whenever rows are inserted (or
even changed) in a non-chronological order.

CREATE VIEW MyView
AS
SELECT a.EcrID, a.AccountNumber, a.EcrDate, a.EcrAmount,
(SELECT SUM(b.EcrAmount)
FROM MyTable AS b
WHERE b.AccountNumber = a.AccountNumber
AND b.EcrDate <= a.EcrDate) AS ProgBalance
FROM MyTable AS a
go
(untested)

If you really must store it in a table, you can use the same technique for
the update:

UPDATE MyTable
SET ProgBalance = (SELECT SUM(b.EcrAmount)
FROM MyTable AS b
WHERE b.AccountNumber = MyTable.AccountNumber
AND b.EcrDate <= MyTable.EcrDate)
WHERE ......
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> The best option is to remove ProgBalance from the table and calculate it
> in a view instead. If it's a column in the table, you'll have to keep
> updating large chunks of data in the table whenever rows are inserted (or
> even changed) in a non-chronological order.

On the other hand, this may never happen in a table like this. (That
would be a busines rule that the balance shown is always in booked order.)
Computing the balance on each invocation may be very expensive, for an
account with many transactions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||[posted and mailed, please reply in news]

badiane (alioune_badiane@.yahoo.com) writes:
> I have may be similar problem.
> I need to recalculate a progressive balance based on a table with the
> following structure:
> EcrID varchar(15)
> AccountNumber varchar(15)
> EcrDate DateTime
> EcrAmount float
> ProgBalance float
> The program has to calculate the progbalance which is for each row :
> progbalance=progbalance + EcrAmount
> Rows are sorted by AccountNumber and EcrDate.
> Is it possible to that by a single update statement or do I have to
> use cursors?

Standard remark:

If you in your posting include the following:

o CREATE TABLE statement for your table(s).
o INSERT statements with sample data.
o The desired result given that sample.

You have good chances to get a tested solution.

This is an untested solution:

UPDATE tbl
SET ProgBalance = (SELECT SUM(EcrAmount)
FROM tbl b
WHERE b.AccountNumber = a.AccountNumber
AND b.EcrDate <= a.EcrDate)
FROM tbl b

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 29 Nov 2004 22:30:51 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> The best option is to remove ProgBalance from the table and calculate it
>> in a view instead. If it's a column in the table, you'll have to keep
>> updating large chunks of data in the table whenever rows are inserted (or
>> even changed) in a non-chronological order.
>On the other hand, this may never happen in a table like this. (That
>would be a busines rule that the balance shown is always in booked order.)
>Computing the balance on each invocation may be very expensive, for an
>account with many transactions.

Hi Erland,

Indeed. That is why I did include the UPDATE statement as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I think that the view is a good solution, I tested with the following
data and think that it is OK. Using a view is it a good solution for
performance point of vue?

The table:
---

CREATE TABLE [dbo].[test2_h_ecr] (
[numero_operation] [nvarchar] (14) NOT NULL ,
[numero_ecriture] [smallint] NOT NULL ,
[numero_compte] [nvarchar] (13) NULL ,
[date_ecriture] [datetime] NULL ,
[montant] [float] NULL ,
[solde_progressif] [float] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test2_h_ecr] WITH NOCHECK ADD
CONSTRAINT [PK_test2_h_ecr] PRIMARY KEY CLUSTERED
(
[numero_operation],
[numero_ecriture]
) ON [PRIMARY]
GO

The sample Data :
------

10003350136522000811012003-12-16 12:41:10.000-425690.0-425690.0
10004012025622000811012004-01-12 00:00:00.00042569.0-383121.0
10004041103822000811012004-02-10 00:00:00.00012795.0-370326.0
10004041103922000811012004-02-10 00:00:00.00029774.0-340552.0
10004070030822000811012004-03-10 00:00:00.00042569.0-297983.0
10004106011322000811012004-04-15 00:00:00.00042569.0-255414.0
10004138009222000811012004-05-17 00:00:00.00042569.0-212845.0
10004167017422000811012004-06-15 00:00:00.00042569.0-170276.0
10004197033722000811012004-07-15 00:00:00.00021840.0-148436.0
10004197033822000811012004-07-15 00:00:00.00020729.0-127707.0
10004229009222000811012004-08-16 00:00:00.00042569.0-85138.0

The view (modified a bit):
--------
CREATE VIEW MyView
AS
SELECT a.numero_operation, a.numero_ecriture, a.numero_compte,
a.date_ecriture,
a.montant,
(SELECT SUM(b.montant)
FROM test2_h_ecr AS b
WHERE b.numero_compte = a.numero_compte
AND b.date_ecriture <= a.date_ecriture
AND b.numero_operation <= a.numero_operation
AND b.numero_ecriture <= a.numero_ecriture) AS ProgBalance
FROM test2_h_ecr AS a
go

The compared result sql :
--------

select * from MyView order by
date_ecriture,numero_operation,numero_ecriture

select * from test2_h_ecr order by
date_ecriture,numero_operation,numero_ecriture

I obtained the same result is it. Is it correct?

Many thanks guys.|||On 1 Dec 2004 11:22:14 -0800, badiane wrote:

>I think that the view is a good solution, I tested with the following
>data and think that it is OK.

Hi badiane,

I would test it with different values and different combinations of values
for numero_compte, date_ecriture, numero_operation and numero_ecriture;
your current test set is way too limited and I'm not sure if the view will
always return the results you expect.

> Using a view is it a good solution for
>performance point of vue?

I *think* it is, but it does depend. If you often change data without
following the chronological order, the view would definitely win (as such
a change would require updates to many rows in the table). On the other
hand, if most changes are for "recent" dates, the total change frequency
is low and the ProgBalance is used extensively, you might find prefer to
store the ProgBalance and use triggers to recalculate ProgBalance for
changed rows (and rows that come "later").

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)