| Author |
 Topic  |
|
|
apantig
Posting Yak Master
Cuba
104 Posts |
 Posted - 03/04/2004 : 02:32:31
|
Our File Server has 1GB of memory with 50 users connected to our MS SQL Server version 7. These 50 users are currently using our Accounting System which was developed in VB6. Whenever I querry in Sql querry analyzer, the users are experiencing very low speed operation as well mine. Our workstations for users are using Windows 98. My computer is Windows 2000. Our File Server is Windows NT 4 with service pack 6.
What is the cause of having very low speed processing in Querry Analyzer?
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 03/04/2004 : 03:26:50
|
My first guess would be a network issue, which is usually speed/duplex related.
Make sure the servers involved are connecting to the network full duplex 100mbit. Don't use autonegotiate, force the connection to 100/full.
-ec
|
Edited by - eyechart on 03/04/2004 03:28:40 |
 |
|
|
apantig
Posting Yak Master
Cuba
104 Posts |
Posted - 03/04/2004 : 03:41:28
|
To EyeChart,
Thanks to your reply.
Our connection is 100/full already. My problem is whenever I execute a querry (10000 records), the CPU utilization increases upto 100 even in a very simple querry. Please help me again.
Thanks to you EyeChart.
|
|
|
|
AjarnMark
SQL Slashing Gunting Master
USA
3246 Posts |
Posted - 03/04/2004 : 15:37:52
|
There are a lot of different possible causes for a slowdown. Just one of which would be blocking. Perhaps it's time to run Profiler to see just what is occurring in the database when these slowdowns are noticed.
--------------------------------------------------------------
Find more words of wisdom at http://weblogs.sqlteam.com/markc |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 03/04/2004 : 15:48:44
|
The initial post says that the users experience slowness when he runs queries in Query Analyzer. Yeah, that'll happen if you don't limit your result sets. Are you returning thousands of rows in Query Analyzer? Use TOP instead:
SELECT TOP 100 *
FROM Table1
WHERE...
Tara |
|
|
|
apantig
Posting Yak Master
Cuba
104 Posts |
Posted - 03/04/2004 : 19:47:31
|
Dear TDUGGAN (Tara),
Good Day and Thanks,
I use "Select Top n * " sometimes, if its necessary. But there are times that I might use "Select * From" to generate all the records in processing and update. Please help.
thanks.
|
|
|
|
AjarnMark
SQL Slashing Gunting Master
USA
3246 Posts |
Posted - 03/04/2004 : 20:35:48
|
Anything you do that is intensive may cause slowdowns for everyone. Sometimes you just have to plan when to run "the big jobs" so as not to interfere with others. Of course that assumes that you've already taken care of the most likely culprits such as applying appropriate indexing, avoiding cursors, etc. Is SQL Server running on a dedicated machine or are other apps running on that same machine (e.g. web service, print services, file sharing, etc.)? You'll get the best performance from a dedicated server.
--------------------------------------------------------------
Find more words of wisdom at http://weblogs.sqlteam.com/markc |
|
|
|
MuadDBA
Aged Yak Warrior
USA
623 Posts |
Posted - 03/05/2004 : 15:13:23
|
Do you actually take the time to page through 10,000 records and look at them all? Or do you need to look at only specific stuff? Limit your query using WHERE clauses and only request the specific columns you want returned, that will go a long way.
Returning 10,000 records shouldn't be a big job even for a meager server (depending on row size, of course), unless you are blocking, cross-joining, using cursors, or something else. If you can post the DDL for the tablecreation, and the actual query you run when things slow down, we can go from there. |
|
|
|
apantig
Posting Yak Master
Cuba
104 Posts |
Posted - 03/05/2004 : 19:27:27
|
Hi Crazyjoe,
Thanks. Well, the 10,000 records I mean are involved in updating records. Each record gets information from the other tables with complex computation and conditions. When I execute the querry analyzer, all of our users are complaining because of the slow-down. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 03/06/2004 : 23:17:43
|
Is your query optimized?
Tara |
|
|
|
mateenmohd
Constraint Violating Yak Guru
United Arab Emirates
297 Posts |
Posted - 03/07/2004 : 07:00:39
|
Speed base on RAM, what is your Server RAM ?
RAM, HD and processor also reflect the speed of server,
good RAM with high HD and processor speed produce quick result
in Query Analyzer.
quote:
Originally posted by apantig
Our File Server has 1GB of memory with 50 users connected to our MS SQL Server version 7. These 50 users are currently using our Accounting System which was developed in VB6. Whenever I querry in Sql querry analyzer, the users are experiencing very low speed operation as well mine. Our workstations for users are using Windows 98. My computer is Windows 2000. Our File Server is Windows NT 4 with service pack 6.
What is the cause of having very low speed processing in Querry Analyzer?
|
|
|
|
mohdowais
Sheikh of Yak Knowledge
United Arab Emirates
1456 Posts |
Posted - 03/07/2004 : 07:52:08
|
apantiq, as a general rule, avoid running heavy queries when most users are connected to your system. You should schedule complex and long-running queries to run during non-peak hours if possible. This way it does not interfere with the queries being run by the hundreds of users slowing them down, and might also make your query run faster as it will have more resources to itself and less locks on the tables.
I suspect your update is causing a table-level lock on the table that is being updated, preventing it from being queried by other users. You should either break up the query to update fewer records at a time (100-500 rows, or enough to generate only a page-level lock) or schedule the query to run during off-peak hours. And as Tara mentioned, perhaps the query is just poorly written, and optimising it might help it run a lot quicker.
OS |
|
|
|
apantig
Posting Yak Master
Cuba
104 Posts |
Posted - 03/07/2004 : 21:21:59
|
| What and how to optimize querry? Sorry, I'm new in SQL. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 03/07/2004 : 21:25:25
|
Well, what is the query then? We can help optimize it.
To optimize it, you check the execution plan. You make sure that you have indexes on the appropriate columns and that scans do not exist.
Do you have indexes to support your UPDATE query?
Tara |
|
|
|
apantig
Posting Yak Master
Cuba
104 Posts |
Posted - 03/07/2004 : 22:02:56
|
-oOo- QUERRY -oOo-
NOTE: 1. THIS STORED PROC IS EXECUTED IN QUERRY ANALYSER.
2. There are some remarks in the code that is written in Filipino dialect (ignore them)
----------------------------------
-- This is how I run the stored proc below in my querry analyser
Delete From AFCM_ScheduleOfAR Where Substring(BuyerCode,1,3)='P02' and CutOff='02/29/2004'
Declare @BuyerCode Char(8)
Declare Buyer Cursor For
Select Code From BuyerAccount Where Substring(Code,1,3)='P02' and isnull(BackOutDate,'')=''
Open Buyer
Fetch Next From Buyer INTO @BuyerCode
While @@Fetch_Status=0
Begin
Execute prAFCM_ScheduleOfAR_Save @BuyerCode,'02/29/2004',55,'System Generated'
Fetch Next From Buyer INTO @BuyerCode
End
Close Buyer
Deallocate Buyer
----------------------------------
-- THIS IS NOW THE STOREC PROC --
CREATE PROCEDURE prAFCM_ScheduleOfAR_Save
@BuyerCode Char(8),
@CutOff DateTime,
@CurrencyRate Numeric(19,2),
@UserName Char(50)
AS
Declare @NextMonth DateTime
Declare @PrevMonth DateTime --new
Declare @ItemCountLastDate int
Set @PrevMonth=(Select PrevCutOff From AFCM_MonthlyInitialization)
--Set @NextMonth=DateAdd(mm,1,@CutOff)
Set @ItemCountLastDate=(Select ItemCount From AFCM_Calendar Where LastDate=@CutOff) + 1
Select @NextMonth=(Select LastDate From AFCM_Calendar Where ItemCount=@ItemCountLastDate)
Declare @PartCode Char(3)
Declare @DueDate DateTime
Declare @Amort Numeric(19,2)
Declare @ItemCount int
Declare @Remarks Char(2000)
Declare @BuyerName Char(50)
Declare @UnitNumber Char(20)
Declare @tmpUnitNumbers Char(20)
Declare @TotalArea Numeric(10,2)
Declare @NetPurchase Numeric(19,2)
Declare @ContractDate DateTime
Declare @Start DateTime
Declare @PaymentsMade Numeric(19,2)
Declare @Balance Numeric(19,2)
Declare @AmountDue Numeric(19,2)
Declare @Agent Char(50)
Declare @tmpUnitArea Numeric(10,2)
Declare @IssuedChecks Numeric(19,2) --new
Declare @PmtScheduleDue Numeric(19,2) --new
Declare @TotalAmountPaid Numeric(19,2)
Declare @pdcIsUsed char(1)
Declare @Excess Numeric(19,2)
set @BuyerName =''
set @UnitNumber =''
set @TotalArea =0
set @NetPurchase =0
set @PaymentsMade =0
set @Balance =0
set @AmountDue =0
Set @Agent =''
Set @Excess =0
Set @Remarks=''
Set @ItemCount=0
Delete From tmpArUnpaidDue Where BuyerCode=@BuyerCode and CutOff=@CutOff
Delete From tmpBuyerSLPayments Where BuyerCode=@BuyerCode
Delete From tmpBuyerSLPayments_ItemCount Where BuyerCode=@BuyerCode
Delete From tmpPayments Where BuyerCode=@BuyerCode
Delete From AFCM_ScheduleOfAR Where BuyerCode=@BuyerCode and CutOff=@CutOff
Delete From AFCRecords Where BuyerCode=@BuyerCode and CutOff=@CutOff
-- To get the basic info of the client
-- Unit Numbers & Total Area
set @tmpUnitNumbers=''
set @UnitNumber=''
Declare UnitNumbers Cursor For
Select Isnull(UnitNumber,''),isnull(UnitArea,0) From ProjectBuildingUnits where ClientCode=@BuyerCode
Open UnitNumbers
Fetch Next From UnitNumbers Into @tmpUnitNumbers,@tmpUnitArea
While @@Fetch_Status=0
Begin
set @UnitNumber = rtrim(@UnitNumber )+' ' + rtrim(@tmpUnitNumbers)
set @TotalArea=@TotalArea + isnull(@tmpUnitArea,0)
Fetch Next From UnitNumbers Into @tmpUnitNumbers,@tmpUnitArea
End
Close UnitNumbers
Deallocate UnitNumbers
if isnull(@UnitNumber,'')=''
Begin
Select @UnitNumber=isnull(Rtrim(UnitNumbers),''),@TotalArea=isnull(TotalUnitArea,0)
From BuyerAccount Where Code=@BuyerCode
End
Set @NetPurchase=(Select isnull(ContractPrice,0) From BuyerAccount Where Code=@BuyerCode)
Set @BuyerName=(Select isnull([Name],'') From CardAccount Where Code=@BuyerCode)
Select @ContractDate=Isnull(ContractDate,''),@Start=isnull(FirstDueDate,'') From BuyerAccount
Where Code=@BuyerCode
Set @PaymentsMade=isnull((Select Sum(isnull(Amount,0)) From BuyerSL Where BuyerCode=@BuyerCode and
(PartCode='MA' or PartCode='RES' or PartCode='LS' or PartCode='BTO' or
PartCode='DP' or PartCode='' or PartCode='ADJ' or PartCode='TRN') and OrDate<=@CutOff),0)
Set @Balance=isnull(@NetPurchase - @PaymentsMade,0)
Declare PmtSched Cursor For
Select Rtrim(PartCode),DueDate,Amount
From PaymentSchedule Where BuyerCode=@BuyerCode and
DueDate<=@NextMonth Order By DueDate,ItemCount
Open PmtSched
Fetch Next From PmtSched INTO @PartCode,@DueDate,@Amort
While @@Fetch_Status=0
Begin
Set @ItemCount=@ItemCount+1
Insert tmpARUnPaidDue
Values
(@BuyerCode,@PartCode,@DueDate,@Amort,0,@Amort,0,'','',@ItemCount,@CutOff,'N',NULL,0,0,'')
Fetch Next From PmtSched INTO @PartCode,@DueDate,@Amort
End
Close PmtSched
Deallocate PmtSched
Execute prAFCM_PostDatedCheck_Save @BuyerCode,@NextMonth,55
-- To get the amount paid from BuyerSL Table --
Declare @slItemCount int
Declare @slPartCode char(3)
Declare @slAmountPaid numeric(19,2)
Set @slItemCount=0
Declare SL Cursor For
Select PartCode,Amount From BuyerSL where BuyerCode=@BuyerCode
and isnull(Amount,0)<>0 Order by DueDate,ItemCount
Open SL
Fetch Next From SL INTO @slPartCode,@slAmountPaid
While @@Fetch_Status=0
Begin
Set @slItemCount=@slItemCount + 1
Insert tmpBuyerSLPayments
Values
(@BuyerCode,@slItemCount,@slPartCode,@slAmountPaid,'N')
Fetch Next From SL INTO @slPartCode,@slAmountPaid
End
Close SL
Deallocate SL
-- To settle the payment by searcing the amort into BuyerSLPayments table that
-- matches the amountpaid. Search only the amountpaid that has mark "N" only.
Declare @aItemCount int
Declare @aPartCode char(3)
Declare @aAmount numeric(19,2)
Declare @aPayment numeric(19,2)
Declare @aBalance numeric(19,2)
Declare @mAdj numeric(19,2)
Declare @mTrn numeric(19,2)
Declare @PartCodeAmt numeric(19,2)
Declare @DummyPartCode Char(3)
Declare @vItemCount int -- para sa tmpBuyerSLPayments
Declare @vAmtPaid numeric(19,2)
Declare @pmtItemCount int
Declare @pmtAmount numeric(19,2)
Declare @pmtTotPaid numeric(19,2)
Declare @ppItemCount int --For PartialPayment cursor
Declare @ppAmount numeric(19,2) --For PartialPayment cursor
Declare @ppTotalAmount numeric(19,2)
Declare @ppBalance numeric(19,2) -- ito yung balance from tmpArUnPaidDue
Declare @TotalRemainingPayment numeric(19,2)
Set @TotalRemainingPayment=0
Set @ppTotalAmount=0
Set @ppBalance=0
Set @vItemCount=0
Set @vAmtPaid=0
Set @pmtTotPaid=0
Set @PartCodeAmt=0
Set @DummyPartCode=''
Set @mAdj=0
Set @mAdj=isnull((Select Sum(isnull(Amount,0)) From tmpBuyerSLPayments Where BuyerCode=@BuyerCode and
PartCode='ADJ'),0)
Set @mTrn=0
Set @mTrn=isnull((Select Sum(isnull(Amount,0)) From tmpBuyerSLPayments Where BuyerCode=@BuyerCode and
PartCode='TRN'),0)
-- Condition 1 and 2 --
Declare AR cursor for
Select ItemCount,PartCode,Amount,Balance From tmpArUnPaidDue
Where BuyerCode=@BuyerCode and CutOff=@CutOff Order By Itemcount
Open AR
Fetch Next From AR INTO @aItemCount,@aPartCode,@aAmount,@aBalance
While @@Fetch_Status=0
Begin
-- Condition 1: kung nakita nya yung parehong partcode and amount at kung hindi pa sya nabasa kanina.
if Exists(Select TOP 1 BuyerCode From tmpBuyerSLPayments
Where BuyerCode=@BuyerCode and PartCode=@aPartCode
and Amount=@aAmount and IsRead='N')
Begin
Set @vItemCount=(Select TOP 1 ItemCount From tmpBuyerSLPayments Where BuyerCode=@BuyerCode and PartCode=@aPartCode and Amount=@aAmount and IsRead='N')
Set @vAmtPaid=(Select Amount From tmpBuyerSLPayments Where BuyerCode=@BuyerCode and ItemCount=@vItemCount)
Update tmpArUnPaidDue
Set Payment=@vAmtPaid,
Balance=0,
PrStatus='C',
PDCPartCode='Condition 1',
FromItemCount=@vItemCount
Where BuyerCode=@BuyerCode and ItemCount=@aItemCount
Update tmpBuyerSLPayments
Set IsRead='Y'
Where BuyerCode=@BuyerCode and ItemCount=@vItemCount
End
else
if Exists(Select TOP 1 BuyerCode From tmpBuyerSLPayments
Where BuyerCode=@BuyerCode and Amount=@aAmount and IsRead='N')
Begin
Set @vItemCount=(Select TOP 1 ItemCount From tmpBuyerSLPayments Where BuyerCode=@BuyerCode and Amount=@aAmount and IsRead='N')
Set @vAmtPaid=(Select Amount From tmpBuyerSLPayments Where BuyerCode=@BuyerCode and ItemCount=@vItemCount)
Update tmpArUnPaidDue
Set Payment=@vAmtPaid,
Balance=0,
PrStatus='C',
PDCPartCode='Condition 2',
FromItemCount=@vItemCount
Where BuyerCode=@BuyerCode and ItemCount=@aItemCount
Update tmpBuyerSLPayments
Set IsRead='Y'
Where BuyerCode=@BuyerCode and ItemCount=@vItemCount
End
else
if Exists(Select Top 1 BuyerCode From tmpBuyerSLPayments
Where BuyerCode=@BuyerCode and
PartCode=@aPartCode and IsRead='N' and isnull(Amount,0)>=@aAmount)
Begin
Set @vItemCount=(Select TOP 1 ItemCount From tmpBuyerSLPayments Where BuyerCode=@BuyerCode and PartCode=@aPartCode and IsRead='N' and isnull(Amount,0)>=@aAmount)
Set @vAmtPaid=(Select Amount From tmpBuyerSLPayments Where BuyerCode=@BuyerCode and ItemCount=@vItemCount)
if @vAmtPaid=@aAmount
Begin
Update tmpArUnPaidDue
Set Payment=@vAmtPaid,
Balance=0,
PrStatus='C',
PDCPartCode='Condition 2a',
FromItemCount=@vItemCount
Where BuyerCode=@BuyerCode and ItemCount=@aItemCount
End
else
if @vAmtPaid>@aAmount
Begin
Update tmpArUnPaidDue
Set Payment=@aAmount,
Balance=0,
PrStatus='C',
PDCPartCode='Condition 2b',
FromItemCount=@vItemCount
Where BuyerCode=@BuyerCode and ItemCount=@aItemCount
Set @Excess=@Excess + (@vAmtPaid - @aAmount)
End
Update tmpBuyerSLPayments
Set IsRead='Y'
Where BuyerCode=@BuyerCode and ItemCount=@vItemCount
End
else
if Exists(Select BuyerCode From tmpBuyerSLPayments Where
BuyerCode=@BuyerCode and PartCode=@aPartCode and IsRead='N'
and isnull(Amount,0)<=@aAmount)
Begin
Set @ppTotalAmount=(Select Sum(isnull(Amount,0)) From tmpBuyerSLPayments
Where
BuyerCode=@BuyerCode and IsRead='N' and
isnull(Amount,0)<=@aAmount
and PartCode=@aPartCode)
Declare PartialPayment Cursor For
Select ItemCount,Amount From tmpBuyerSLPayments Where
BuyerCode=@BuyerCode and IsRead='N' and isnull(Amount,0)<=@aAmount
and PartCode=@aPartCode
Open PartialPayment
Fetch Next From PartialPayment INTO @ppItemCount,@ppAmount
While @@Fetch_Status=0
Begin
--Set @ppTotalAmount=@ppTotalAmount + @ppAmount
Insert tmpBuyerSLPayments_ItemCount
Values (@BuyerCode,@ppItemCount)
Fetch Next From PartialPayment INTO @ppItemCount,@ppAmount
End
Close PartialPayment
Deallocate PartialPayment
Select 'Test 100',@aPartCode,@ppTotalAmount,@aBalance
if @ppTotalAmount=@aBalance
Begin
Update tmpArUnPaidDue
Set Payment=@ppTotalAmount,
Balance=0,
PrStatus='C',
PDCPartCode='Condition 2c'
Where BuyerCode=@BuyerCode and ItemCount=@aItemCount
Declare UpdateItemCount Cursor For
Select ItemCount From tmpBuyerSLPayments a Where
Buyercode=@BuyerCode and a.ItemCount In
(Select ItemCount From tmpBuyerSLPayments_ItemCount Where BuyerCode=@BuyerCode and
ItemCount=a.ItemCount)
Open UpdateItemCount
Fetch Next From UpdateItemCount INTO @ppItemCount
While @@Fetch_Status=0
Begin
Update tmpBuyerSLPayments
Set IsRead='Y' Where Current of UpdateItemCount
Fetch Next From UpdateItemCount INTO @ppItemCount
End
Close UpdateItemCount
Deallocate UpdateItemCount
End
else
if @ppTotalAmount<@aBalance
Begin
Update tmpArUnPaidDue
Set Payment=@ppTotalAmount,
Balance=(Amount - @ppTotalAmount),
PrStatus='',
PDCPartCode='Condition 2d'
Where BuyerCode=@BuyerCode and ItemCount=@aItemCount
Declare UpdateItemCount Cursor For
Select ItemCount From tmpBuyerSLPayments a Where
Buyercode=@BuyerCode and a.ItemCount In
(Select ItemCount From tmpBuyerSLPayments_ItemCount Where BuyerCode=@BuyerCode and
ItemCount=a.ItemCount)
Open UpdateItemCount
Fetch Next From UpdateItemCount INTO @ppItemCount
While @@Fetch_Status=0
Begin
Update tmpBuyerSLPayments
Set IsRead='Y' Where Current of UpdateItemCount
Fetch Next From UpdateItemCount INTO @ppItemCount
End
Close UpdateItemCount
Deallocate UpdateItemCount
End
else
if @ppTotalAmount>@aBalance
Begin
Update tmpArUnPaidDue
Set Payment=@aAmount,
Balance=0,
PrStatus='C',
PDCPartCode='Condition 2e'
Where BuyerCode=@BuyerCode and ItemCount=@aItemCount
Set @Excess=@Excess + (@ppTotalAmount - @aAmount)
Declare UpdateItemCount Cursor For
Select ItemCount From tmpBuyerSLPayments a Where
Buyercode=@BuyerCode and a.ItemCount In
(Select ItemCount From tmpBuyerSLPayments_ItemCount Where BuyerCode=@BuyerCode and
ItemCount=a.ItemCount)
Open UpdateItemCount
Fetch Next From UpdateItemCount INTO @ppItemCount
While @@Fetch_Status=0
Begin
Update tmpBuyerSLPayments
Set IsRead='Y' Where Current of UpdateItemCount
Fetch Next From UpdateItemCount INTO @ppItemCount
End
Close UpdateItemCount
Deallocate UpdateItemCount
End
End
Fetch Next From AR INTO @aItemCount,@aPartCode,@aAmount,@aBalance
End
Close AR
Deallocate AR
-------------------------------------------------------------------------------------------------
--Second Stage
Set @Excess=@Excess+@mAdj+@mTrn
Set @TotalRemainingPayment=isnull((SELECT Sum(isnull(Amount,0))
FROM tmpBuyerSLPayments
WHERE BuyerCode = @BuyerCode AND (ISNULL(PartCode, '') = '' OR PartCode = 'MA' OR PartCode = 'RES' OR
PartCode = 'LS' OR
Partcode = 'DP' OR
PartCode = 'BTO') and isread='N'),0)
Set @TotalRemainingPayment=isnull(@Excess,0) + isnull(@TotalRemainingPayment,0)
Declare AR cursor for
Select ItemCount,PartCode,Amount,Balance,Payment From tmpArUnPaidDue
Where BuyerCode=@BuyerCode and CutOff=@CutOff and Balance<>0 Order By ItemCount
Open AR
Fetch Next From AR INTO @aItemCount,@aPartCode,@aAmount,@aBalance,@aPayment
While @@Fetch_Status=0
Begin
if @TotalRemainingPayment>0
Begin
if @TotalRemainingPayment>=@aBalance
Begin
if @aAmount=@aBalance
Begin
Update tmpArUnPaidDue
Set Payment=@aAmount,
Balance=0,
PrStatus='C',
PDCPartCode='Condition 3a'
Where BuyerCode=@BuyerCode and ItemCount=@aItemCount
Set @TotalRemainingPayment=@TotalRemainingPayment - @aAmount
if @TotalRemainingPayment<=0
Set @TotalRemainingPayment=0
End
else
if @aAmount<>@aBalance
Begin
Update tmpArUnPaidDue
Set Payment=Payment+Balance,
Balance=0,
PrStatus='C',
PDCPartCode='Condition 3b'
Where BuyerCode=@BuyerCode and ItemCount=@aItemCount
Set @TotalRemainingPayment=@TotalRemainingPayment - @aBalance
if @TotalRemainingPayment<=0
Set @TotalRemainingPayment=0
End
End
Else
if @TotalRemainingPayment<@aBalance
Begin
if @aAmount=@aBalance
Begin
Update tmpArUnPaidDue
Set Payment=@TotalRemainingPayment,
Balance=Amount-@TotalRemainingPayment,
PrStatus='',
PDCPartCode='Condition 3c'
Where BuyerCode=@BuyerCode and ItemCount=@aItemCount
Set @TotalRemainingPayment=@TotalRemainingPayment - @aAmount
if @TotalRemainingPayment<=0
Set @TotalRemainingPayment=0
End
else
if @aAmount<>@aBalance
Begin
Select '3d',@aAmount,@aPayment,@aBalance,@TotalRemainingPayment,@aItemCount
Update tmpArUnPaidDue
Set Payment=Payment+Balance,
Balance=Amount-(@aPayment+@aBalance),
PrStatus='',
PDCPartCode='Condition 3d'
Where BuyerCode=@BuyerCode and ItemCount=@aItemCount
Set @TotalRemainingPayment=@TotalRemainingPayment - @aBalance
if @TotalRemainingPayment<=0
Set @TotalRemainingPayment=0
End
End
End
if @TotalRemainingPayment<=0
Begin
Set @TotalRemainingPayment=0
Break
End
Fetch Next From AR INTO @aItemCount,@aPartCode,@aAmount,@aBalance,@aPayment
End
Close AR
Deallocate AR
--- Distribution of PDC Issued/Deposited/Hold ---
Declare @pdcItemCount int
Declare @pdcPartCode Char(3)
Declare @pdcPrDate datetime
Declare @pdcPrStatus char(1)
Declare @pdcCheckDate datetime
Declare @pdcAmount numeric(19,2)
Declare @pdcDateMode char(15)
Declare @pdcDepoDate datetime
Declare @pdcExcess Numeric(19,2)
Declare @fItemCount int
Declare @fPartCode Char(3)
Declare @fDueDate Datetime
Declare @fBalance Numeric(19,2)
Declare @tmpFromItemCount int
Declare @fCondition char(500)
Set @tmpFromItemCount=0
Set @fCondition=''
Set @pdcDepoDate=null
Set @pdcExcess=0
Declare AR Cursor For
Select ItemCount,PartCode,DueDate,Balance From tmpArUnPaidDue
Where BuyerCode=@BuyerCode and CutOff=@CutOff and Balance<>0
OPEN AR
Fetch Next From AR INTO @fItemCount,@fPartCode,@fDueDate,@fBalance
While @@Fetch_Status=0
Begin
--Condition 1
if Exists(Select BuyerCode From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and PartCode=@fPartCode and Amount=@fBalance
and Month(CheckDate)=Month(@fDueDate) and Year(CheckDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='N' and IsUsed='N')
Begin
Select @pdcItemCount=ItemCount,@pdcPartCode=isnull(PartCode,''),
@pdcPrStatus=isnull(PrStatus,''),@pdcCheckDate=isnull(CheckDate,null),
@pdcAmount=isnull(Amount,0)
From tmpPostDatedCheck Where BuyerCode=@BuyerCode and PartCode=@fPartCode
and Amount=@fBalance and Month(CheckDate)=Month(@fDueDate) and
Year(CheckDate)=Year(@fDueDate) and (PrStatus='I' or PrStatus='D' or PrStatus='H')
and IsCash='N' and IsUsed='N'
Set @fCondition='1'
End
else
--Condition 2
if Exists(Select BuyerCode From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and PartCode=@fPartCode and Amount=@fBalance
and Month(PRDate)=Month(@fDueDate) and Year(PrDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='Y' and IsUsed='N')
Begin
Select @pdcItemCount=ItemCount,@pdcPartCode=isnull(PartCode,''),
@pdcPrStatus=isnull(PrStatus,''),@pdcCheckDate=isnull(PrDate,null),
@pdcAmount=isnull(Amount,0)
From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and PartCode=@fPartCode and Amount=@fBalance
and Month(PRDate)=Month(@fDueDate) and Year(PrDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='Y' and IsUsed='N'
Update tmpArUnPaidDue
Set PrStatus=@pdcPrStatus,
CheckDate=@pdcCheckDate,
CheckAmount=@pdcAmount,
FromItemCount=@pdcItemCount,
Condition='2'
Where BuyerCode=@BuyerCode and ItemCount=@fItemCount
Update tmpPostDatedCheck
Set IsUsed='Y'
Where BuyerCode=@BuyerCode and ItemCount=@pdcItemCount
Set @fCondition='2'
End
else
--Condition 3
--Kung parehong PartCode, mas mataas ang amount ng check, Month and Year, hindi sya cash, at hindi pa nagamit
--sa pag-testing, dito sya pupunta.
if Exists(Select BuyerCode From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and PartCode=@fPartCode and Amount>@fBalance
and Month(CheckDate)=Month(@fDueDate) and Year(CheckDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='N' and IsUsed='N')
Begin
Select @pdcItemCount=ItemCount,@pdcPartCode=isnull(PartCode,''),
@pdcPrStatus=isnull(PrStatus,''),@pdcCheckDate=isnull(PrDate,null),
@pdcAmount=isnull(Amount,0) From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and PartCode=@fPartCode and Amount>@fBalance
and Month(CheckDate)=Month(@fDueDate) and Year(CheckDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='N' and IsUsed='N'
if @pdcAmount>@fBalance
Begin
Update tmpArUnPaidDue
Set PrStatus=@pdcPrStatus,
CheckDate=@pdcCheckDate,
CheckAmount=@pdcAmount,
FromItemCount=@pdcItemCount,
Condition='3'
Where BuyerCode=@BuyerCode and ItemCount=@fItemCount
Update tmpPostDatedCheck
Set IsUsed='Y'
Where BuyerCode=@BuyerCode and ItemCount=@pdcItemCount
Set @pdcExcess=@pdcExcess+(@pdcAmount-@fBalance)
Set @fCondition='3'
End
End
else
--Condition 4
--Kung parehong PartCode, mataas ang amount ng Cash, Month and Year, cash payment sya, at hindi pa nagamit
--sa pag-testing, dito sya pupunta.
if Exists(Select BuyerCode From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and PartCode=@fPartCode and Amount>@fBalance
and Month(PRDate)=Month(@fDueDate) and Year(PrDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='Y' and IsUsed='N')
Begin
Select @pdcItemCount=ItemCount,@pdcPartCode=isnull(PartCode,''),
@pdcPrStatus=isnull(PrStatus,''),@pdcCheckDate=isnull(PrDate,null),
@pdcAmount=isnull(Amount,0)from
tmpPostDatedCheck Where BuyerCode=@BuyerCode
and PartCode=@fPartCode and Amount>@fBalance
and Month(PRDate)=Month(@fDueDate) and Year(PrDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='Y' and IsUsed='N'
if @pdcAmount>@fBalance
Begin
Set @pdcExcess=@pdcExcess+(@pdcAmount-@fBalance)
Set @fCondition='4'
End
End
else
--Condition 5
--Kung parehong PartCode, mas mababa ang amount ng check, Month and Year, hindi sya cash, at hindi pa nagamit
--sa pag-testing, dito sya pupunta.
if Exists(Select BuyerCode From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and PartCode=@fPartCode and Amount<@fBalance
and Month(CheckDate)=Month(@fDueDate) and Year(CheckDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='N' and IsUsed='N')
Begin
Select @pdcItemCount=ItemCount,@pdcPartCode=isnull(PartCode,''),
@pdcPrStatus=isnull(PrStatus,''),@pdcCheckDate=isnull(PrDate,null),
@pdcAmount=isnull(Amount,0)From
tmpPostDatedCheck Where BuyerCode=@BuyerCode
and PartCode=@fPartCode and Amount<@fBalance
and Month(CheckDate)=Month(@fDueDate) and Year(CheckDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='N' and IsUsed='N'
Set @fCondition='5'
End
else
--Condition 6
--Kung parehong PartCode, mataas ang amount ng Cash, Month and Year, cash payment sya, at hindi pa nagamit
--sa pag-testing, dito sya pupunta.
if Exists(Select BuyerCode From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and PartCode=@fPartCode and Amount<@fBalance
and Month(PRDate)=Month(@fDueDate) and Year(PrDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='Y' and IsUsed='N')
Begin
Select @pdcItemCount=ItemCount,@pdcPartCode=isnull(PartCode,''),
@pdcPrStatus=isnull(PrStatus,''),@pdcCheckDate=isnull(PrDate,null),
@pdcAmount=isnull(Amount,0) From
tmpPostDatedCheck Where BuyerCode=@BuyerCode
and PartCode=@fPartCode and Amount<@fBalance
and Month(PRDate)=Month(@fDueDate) and Year(PrDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='Y' and IsUsed='N'
Set @fCondition='6'
End
else
--Condition 7
--Kung hindi pareho ang PartCode, pero pareho ng CheckAmount and DueDate=CheckDate, CheckPayment Sya
--Dito sya pupunta
if Exists(Select BuyerCode From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and Amount=@fBalance and Month(CheckDate)=Month(@fDueDate) and Year(CheckDate)=Year(@fDueDate)
and (PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='N' and IsUsed='N')
Begin
Select @pdcItemCount=ItemCount,@pdcPartCode=isnull(PartCode,''),
@pdcPrStatus=isnull(PrStatus,''),@pdcCheckDate=isnull(PrDate,null),
@pdcAmount=isnull(Amount,0)
From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and Amount=@fBalance and Month(CheckDate)=Month(@fDueDate) and Year(CheckDate)=Year(@fDueDate)
and (PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='N' and IsUsed='N'
Set @fCondition='7'
End
else
--Condition 8
--Kung hindi pareho ang PartCode, pero pareho ng CashAmount and DueDate=CheckDate, Cash Payment Sya
--Dito sya pupunta
if Exists(Select BuyerCode From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and Amount=@fBalance and Month(PrDate)=Month(@fDueDate) and Year(PrDate)=Year(@fDueDate)
and (PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='Y' and IsUsed='N')
Begin
Select @pdcItemCount=ItemCount,@pdcPartCode=isnull(PartCode,''),
@pdcPrStatus=isnull(PrStatus,''),@pdcCheckDate=isnull(PrDate,null),
@pdcAmount=isnull(Amount,0)
From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and Amount=@fBalance and Month(PrDate)=Month(@fDueDate) and Year(PrDate)=Year(@fDueDate)
and (PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='Y' and IsUsed='N'
Set @fCondition='8'
End
else
--Condition 9
--Kung HINDI parehong PartCode, mas mataas ang amount ng check, Month and Year, hindi sya cash, at hindi pa nagamit
--sa pag-testing, dito sya pupunta.
if Exists(Select BuyerCode From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and Amount>@fBalance and Month(CheckDate)=Month(@fDueDate) and Year(CheckDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='N' and IsUsed='N')
Begin
Select @pdcItemCount=ItemCount,@pdcPartCode=isnull(PartCode,''),
@pdcPrStatus=isnull(PrStatus,''),@pdcCheckDate=isnull(PrDate,null),
@pdcAmount=isnull(Amount,0)
From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and Amount>@fBalance and Month(CheckDate)=Month(@fDueDate) and Year(CheckDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='N' and IsUsed='N'
Set @fCondition='9'
End
else
--Condition 10
--Kung HINDI PartCode, mataas ang amount ng Cash, Month and Year, cash payment sya, at hindi pa nagamit
--sa pag-testing, dito sya pupunta.
if Exists(Select BuyerCode From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and Amount>@fBalance and Month(PRDate)=Month(@fDueDate) and Year(PrDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='Y' and IsUsed='N')
Begin
Select @pdcItemCount=ItemCount,@pdcPartCode=isnull(PartCode,''),
@pdcPrStatus=isnull(PrStatus,''),@pdcCheckDate=isnull(PrDate,null),
@pdcAmount=isnull(Amount,0)
From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and Amount>@fBalance and Month(PRDate)=Month(@fDueDate) and Year(PrDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='Y' and IsUsed='N'
Set @fCondition='10'
End
else
--Condition 11
--Kung HINDI PartCode, mas mababa ang amount ng check, Month and Year, hindi sya cash, at hindi pa nagamit
--sa pag-testing, dito sya pupunta.
if Exists(Select BuyerCode From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and Amount<@fBalance and Month(CheckDate)=Month(@fDueDate) and Year(CheckDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='N' and IsUsed='N')
Begin
Select @pdcItemCount=ItemCount,@pdcPartCode=isnull(PartCode,''),
@pdcPrStatus=isnull(PrStatus,''),@pdcCheckDate=isnull(PrDate,null),
@pdcAmount=isnull(Amount,0)
From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and Amount<@fBalance and Month(CheckDate)=Month(@fDueDate) and Year(CheckDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='N' and IsUsed='N'
Set @fCondition='11'
End
else
--Condition 12
--Kung HINDI parehong PartCode, mataas ang amount ng Cash, Month and Year, cash payment sya, at hindi pa nagamit
--sa pag-testing, dito sya pupunta.
if Exists(Select BuyerCode From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and Amount<@fBalance and Month(PRDate)=Month(@fDueDate) and Year(PrDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='Y' and IsUsed='N')
Begin
Select @pdcItemCount=ItemCount,@pdcPartCode=isnull(PartCode,''),
@pdcPrStatus=isnull(PrStatus,''),@pdcCheckDate=isnull(PrDate,null),
@pdcAmount=isnull(Amount,0)
From tmpPostDatedCheck Where BuyerCode=@BuyerCode
and Amount<@fBalance and Month(PRDate)=Month(@fDueDate) and Year(PrDate)=Year(@fDueDate) and
(PrStatus='I' or PrStatus='D' or PrStatus='H') and IsCash='Y' and IsUsed='N'
Set @fCondition='12'
End
else
Begin
Set @pdcPrStatus=''
End
if @fCondition='3' or @fCondition='4' or @fCondition='9' or @fCondition='10'
Begin
Update tmpPostDatedCheck
Set IsUsed='N'
Where BuyerCode=@BuyerCode and ItemCount=@pdcItemCount
End
Else
Begin
Update tmpPostDatedCheck
Set IsUsed='Y'
Where BuyerCode=@BuyerCode and ItemCount=@pdcItemCount
End
Update tmpArUnPaidDue
Set PrStatus=@pdcPrStatus,
CheckDate=@pdcCheckDate,
CheckAmount=@pdcAmount,
FromItemCount=@pdcItemCount,
Condition=@fCondition
Where BuyerCode=@BuyerCode and ItemCount=@fItemCount
Set @pdcPrStatus=''
Set @pdcCheckDate=null
Set @pdcAmount=0
Set @pdcItemCount=0
Set @fCondition=''
Fetch Next From AR INTO @fItemCount,@fPartCode,@fDueDate,@fBalance
End
Close AR
Deallocate AR
--- Get Remarks ---
Declare @fPdcAmt Numeric(19,2)
Declare @fAmtDue Numeric(19,2)
Declare @fPdcCount int
Declare @fPdcItemCount int
Declare @fPrStatus Char(1)
Declare @fCheckDate datetime
Declare @fCheckAmount Numeric(19,2)
Declare @fFromItemCount int
Declare @fTmpFromItemCount int
Declare @Trec int
Declare @Cnt int
Set @Trec=0
Set @Cnt=0
Set @fTmpFromItemCount=0
Set @fPdcItemCount=0
Set @fAmtDue=0
Set @fPdcCount=0
Set @fPdcAmt=0
Set @Remarks=''
--Set @Trec=(Select Count(BuyerCode) From tmpArUnPaidDue
--Where BuyerCode=@BuyerCode and CutOff=@CutOff and (PrStatus='I' or PrStatus='D' or PrStatus='H'))
Declare AR Cursor For
Select ItemCount,Rtrim(PartCode),DueDate,Balance,PrStatus,CheckDate,CheckAmount,FromItemCount From tmpArUnPaidDue
Where BuyerCode=@BuyerCode and CutOff=@CutOff and (PrStatus='I' or PrStatus='D' or PrStatus='H' or isnull(PrStatus,'')='')
Open AR
Fetch Next From AR INTO @fItemCount,@fPartCode,@fDueDate,@fBalance,@fPrStatus,@fCheckDate,@fCheckAmount,@fFromItemCount
While @@Fetch_Status=0
Begin
if @fTmpFromItemCount=@fFromItemCount and @fFromItemCount>0 -- baka kc yung Check Amount nya ay compose of 2 payments (ie. MA & LS)
Begin
if @fPrStatus='I' or @fPrStatus='D'
Begin
Set @Remarks=@fPartCode+' '+Convert(Char(10),@fDueDate,101)+' ('+Rtrim(Cast(@fBalance as Char(15)))+') & '+Rtrim(@Remarks)
Set @fAmtDue=@fAmtDue+@fBalance
End
else
if @fPrStatus='H'
Begin
Set @Remarks=Rtrim(@Remarks)+' & '+@fPartCode+' '+Convert(Char(10),@fDueDate,101)+' ('+Rtrim(Cast(@fBalance as Char(15)))+') HOLD'
Set @fAmtDue=@fAmtDue+@fBalance
End
End
Else
Begin
if @fPrStatus='I' or @fPrStatus='D'
Begin
Set @Remarks=Rtrim(@Remarks)+' '+@fPartCode+' '+Convert(Char(10),@fDueDate,101)+' ('+Rtrim(Cast(@fBalance as Char(15)))+') '+
'/w PDC for dep on '+Convert(Char(10),@fCheckDate,101)+' ('+Rtrim(Cast(@fCheckAmount as Char(15)))+')'
Set @fAmtDue=@fAmtDue+@fBalance
End
else
if @fPrStatus='H'
Begin
Set @Remarks=Rtrim(@Remarks)+' '+@fPartCode+' '+Convert(Char(10),@fDueDate,101)+' ('+Rtrim(Cast(@fBalance as Char(15)))+') '+
'/w PDC for dep on '+Convert(Char(10),@fCheckDate,101)+' ('+Rtrim(Cast(@fCheckAmount as Char(15)))+') HOLD'
Set @fAmtDue=@fAmtDue+@fBalance
End
else
if @fPrStatus=''
Begin
Set @Remarks=Rtrim(@Remarks)+' '+@fPartCode+' '+Convert(Char(10),@fDueDate,101)+' ('+Rtrim(Cast(@fBalance as Char(15)))+') '+
'no PDC'
Set @fAmtDue=@fAmtDue+@fBalance
End
End
Set @fTmpFromItemCount=@fFromItemCount
Set @Cnt=@Cnt+1
Fetch Next From AR INTO @fItemCount,@fPartCode,@fDueDate,@fBalance,@fPrStatus,@fCheckDate,@fCheckAmount,@fFromItemCount
End
Close AR
Deallocate AR
Declare @ThisPayment Numeric(19,2)
Set @ThisPayment=(Select Sum(isnull(Payment,0)) From tmpArUnPaidDue Where Buyercode=@BuyerCode and
CutOff=@CutOff)
Insert AFCM_ScheduleOfAR
Values
(@BuyerCode,
@CutOff,
@BuyerName,
@UnitNumber,
@TotalArea,
@NetPurchase,
@PaymentsMade,
@Balance,
@fAmtDue,
rtrim(@Remarks),
'',
@ThisPayment,
@PaymentsMade - @ThisPayment)
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 03/07/2004 : 22:28:04
|
You are using cursors. SQL is meant for set-based meethods and not loops like a cursor. This is where your problem is. The query needs to be rewritten. Could you explain what the goal of it is? There are a few people here who might be able to rewrite it for you.
Tara |
|
|
|
apantig
Posting Yak Master
Cuba
104 Posts |
Posted - 03/08/2004 : 01:25:42
|
The objective of the code is to get the schedule of accounts receivable of each client every end of the month. First, the code must get the values of basic information of the client. Second to spread the amount paid in the amortization schedule depending on what purpose of payment is that. Whatever amortization schedule is unpaid, then the code must check if there are Post Dated Check (PDC)issued, if none, then consider them as Accounts For Collection. If there are PDCs Issued, then consider them Schedule of Accounts Receivable. Amount Due is equivalent to unpaid amortization schedule Below is a sample list of output report.
Code Contract Pmts Balance AmtDue Remarks
B02-5069 3273136.20 149868.45 3123267.75 50131.55 (131.55) 01/28/04 no PDC, (25000) 02/28/04 /w PDC for dep on 02/25/04, (25000) /w PDC for deposit on 03/28/04.
|
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 03/08/2004 : 01:37:43
|
this is starting to feel like work.
where to start?
1. Get famaliar with query analyzer execution plan.
2. Use SET STATISTICS IO ON and SET STATISTICS TIME ON in QA to see where SQL is spending its time. You will also see the tables that are being hit the most. Time is in milliseconds, IO is in 8K pages.
3. Take tara's advice and get rid of the cursors.
4. Make sure you are indexed properly. Start with making sure all tables have a primary key and a clustered index.
5. Put indexes on columns used in your where clause. There is an art to indexing tables, too many and performance sucks, too few and performance sucks.
6. Put indexes on foreign keys used in joins.
7. go to www.sql-server-performance.com and read the entire site.
8. What about modularizing this sproc? Creating several smaller procedures that are called from a driving sproc will be much easier to maintain and troubleshoot.
9. Try and optimize small chunks of your code at a time. FOr example run these pieces of code through QA by themselves and optimize them:
Delete From tmpArUnpaidDue Where BuyerCode=@BuyerCode and CutOff=@CutOff
Delete From tmpBuyerSLPayments Where BuyerCode=@BuyerCode
Delete From tmpBuyerSLPayments_ItemCount Where BuyerCode=@BuyerCode
Delete From tmpPayments Where BuyerCode=@BuyerCode
Delete From AFCM_ScheduleOfAR Where BuyerCode=@BuyerCode and CutOff=@CutOff
Delete From AFCRecords Where BuyerCode=@BuyerCode and CutOff=@CutOff
or this one:
Select PartCode,Amount From BuyerSL where BuyerCode=@BuyerCode
and isnull(Amount,0)<>0 Order by DueDate,ItemCount
Of course, you want to do things like a delete or truncate on a QA copy of your production database. Also, since we have your sproc, you might as well post DDL for the underlying tables/indexes. IF someone here is feeling really generous, that might help them optimize parts of your code.
-ec |
Edited by - eyechart on 03/08/2004 02:04:31 |
|
|
| |
Topic |
|
|
|
Querry Analyzer (Why low speed in querry)
Forum Locked
