Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
apantig
Posting Yak Master
104 Posts |
Posted - 2004-03-04 : 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
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-03-04 : 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 |
|
|
apantig
Posting Yak Master
104 Posts |
Posted - 2004-03-04 : 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
3246 Posts |
Posted - 2004-03-04 : 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 [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-04 : 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 Table1WHERE...Tara |
|
|
apantig
Posting Yak Master
104 Posts |
Posted - 2004-03-04 : 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
3246 Posts |
Posted - 2004-03-04 : 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 [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
MuadDBA
628 Posts |
Posted - 2004-03-05 : 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
104 Posts |
Posted - 2004-03-05 : 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
38200 Posts |
Posted - 2004-03-06 : 23:17:43
|
Is your query optimized?Tara |
|
|
mateenmohd
Constraint Violating Yak Guru
297 Posts |
Posted - 2004-03-07 : 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 resultin 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
1456 Posts |
Posted - 2004-03-07 : 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
104 Posts |
Posted - 2004-03-07 : 21:21:59
|
What and how to optimize querry? Sorry, I'm new in SQL. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-07 : 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
104 Posts |
Posted - 2004-03-07 : 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 analyserDelete From AFCM_ScheduleOfAR Where Substring(BuyerCode,1,3)='P02' and CutOff='02/29/2004'Declare @BuyerCode Char(8)Declare Buyer Cursor ForSelect Code From BuyerAccount Where Substring(Code,1,3)='P02' and isnull(BackOutDate,'')=''Open BuyerFetch Next From Buyer INTO @BuyerCodeWhile @@Fetch_Status=0 Begin Execute prAFCM_ScheduleOfAR_Save @BuyerCode,'02/29/2004',55,'System Generated' Fetch Next From Buyer INTO @BuyerCode EndClose BuyerDeallocate Buyer------------------------------------ THIS IS NOW THE STOREC PROC --CREATE PROCEDURE prAFCM_ScheduleOfAR_Save@BuyerCode Char(8),@CutOff DateTime,@CurrencyRate Numeric(19,2),@UserName Char(50)ASDeclare @NextMonth DateTimeDeclare @PrevMonth DateTime --newDeclare @ItemCountLastDate intSet @PrevMonth=(Select PrevCutOff From AFCM_MonthlyInitialization)--Set @NextMonth=DateAdd(mm,1,@CutOff)Set @ItemCountLastDate=(Select ItemCount From AFCM_Calendar Where LastDate=@CutOff) + 1Select @NextMonth=(Select LastDate From AFCM_Calendar Where ItemCount=@ItemCountLastDate)Declare @PartCode Char(3)Declare @DueDate DateTimeDeclare @Amort Numeric(19,2)Declare @ItemCount intDeclare @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 DateTimeDeclare @Start DateTimeDeclare @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) --newDeclare @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 =0set @NetPurchase =0set @PaymentsMade =0set @Balance =0 set @AmountDue =0Set @Agent =''Set @Excess =0Set @Remarks=''Set @ItemCount=0Delete From tmpArUnpaidDue Where BuyerCode=@BuyerCode and CutOff=@CutOffDelete From tmpBuyerSLPayments Where BuyerCode=@BuyerCode Delete From tmpBuyerSLPayments_ItemCount Where BuyerCode=@BuyerCode Delete From tmpPayments Where BuyerCode=@BuyerCodeDelete From AFCM_ScheduleOfAR Where BuyerCode=@BuyerCode and CutOff=@CutOffDelete From AFCRecords Where BuyerCode=@BuyerCode and CutOff=@CutOff-- To get the basic info of the client-- Unit Numbers & Total Areaset @tmpUnitNumbers=''set @UnitNumber=''Declare UnitNumbers Cursor ForSelect Isnull(UnitNumber,''),isnull(UnitArea,0) From ProjectBuildingUnits where ClientCode=@BuyerCodeOpen UnitNumbersFetch Next From UnitNumbers Into @tmpUnitNumbers,@tmpUnitAreaWhile @@Fetch_Status=0 Begin set @UnitNumber = rtrim(@UnitNumber )+' ' + rtrim(@tmpUnitNumbers) set @TotalArea=@TotalArea + isnull(@tmpUnitArea,0) Fetch Next From UnitNumbers Into @tmpUnitNumbers,@tmpUnitArea EndClose UnitNumbersDeallocate UnitNumbersif isnull(@UnitNumber,'')='' Begin Select @UnitNumber=isnull(Rtrim(UnitNumbers),''),@TotalArea=isnull(TotalUnitArea,0) From BuyerAccount Where Code=@BuyerCode EndSet @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 BuyerAccountWhere Code=@BuyerCodeSet @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 ForSelect Rtrim(PartCode),DueDate,Amount From PaymentSchedule Where BuyerCode=@BuyerCode and DueDate<=@NextMonth Order By DueDate,ItemCountOpen PmtSchedFetch Next From PmtSched INTO @PartCode,@DueDate,@AmortWhile @@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 EndClose PmtSched Deallocate PmtSchedExecute prAFCM_PostDatedCheck_Save @BuyerCode,@NextMonth,55-- To get the amount paid from BuyerSL Table --Declare @slItemCount intDeclare @slPartCode char(3)Declare @slAmountPaid numeric(19,2)Set @slItemCount=0Declare SL Cursor ForSelect PartCode,Amount From BuyerSL where BuyerCode=@BuyerCodeand isnull(Amount,0)<>0 Order by DueDate,ItemCountOpen SLFetch Next From SL INTO @slPartCode,@slAmountPaidWhile @@Fetch_Status=0 Begin Set @slItemCount=@slItemCount + 1 Insert tmpBuyerSLPayments Values (@BuyerCode,@slItemCount,@slPartCode,@slAmountPaid,'N') Fetch Next From SL INTO @slPartCode,@slAmountPaid EndClose SLDeallocate 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 intDeclare @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 tmpBuyerSLPaymentsDeclare @vAmtPaid numeric(19,2)Declare @pmtItemCount int Declare @pmtAmount numeric(19,2)Declare @pmtTotPaid numeric(19,2)Declare @ppItemCount int --For PartialPayment cursorDeclare @ppAmount numeric(19,2) --For PartialPayment cursorDeclare @ppTotalAmount numeric(19,2)Declare @ppBalance numeric(19,2) -- ito yung balance from tmpArUnPaidDueDeclare @TotalRemainingPayment numeric(19,2)Set @TotalRemainingPayment=0Set @ppTotalAmount=0Set @ppBalance=0Set @vItemCount=0Set @vAmtPaid=0Set @pmtTotPaid=0Set @PartCodeAmt=0Set @DummyPartCode=''Set @mAdj=0Set @mAdj=isnull((Select Sum(isnull(Amount,0)) From tmpBuyerSLPayments Where BuyerCode=@BuyerCode and PartCode='ADJ'),0)Set @mTrn=0Set @mTrn=isnull((Select Sum(isnull(Amount,0)) From tmpBuyerSLPayments Where BuyerCode=@BuyerCode and PartCode='TRN'),0)-- Condition 1 and 2 --Declare AR cursor forSelect ItemCount,PartCode,Amount,Balance From tmpArUnPaidDueWhere BuyerCode=@BuyerCode and CutOff=@CutOff Order By ItemcountOpen ARFetch Next From AR INTO @aItemCount,@aPartCode,@aAmount,@aBalanceWhile @@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 EndClose ARDeallocate AR---------------------------------------------------------------------------------------------------Second StageSet @Excess=@Excess+@mAdj+@mTrnSet @TotalRemainingPayment=isnull((SELECT Sum(isnull(Amount,0))FROM tmpBuyerSLPaymentsWHERE 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 forSelect ItemCount,PartCode,Amount,Balance,Payment From tmpArUnPaidDueWhere BuyerCode=@BuyerCode and CutOff=@CutOff and Balance<>0 Order By ItemCountOpen ARFetch Next From AR INTO @aItemCount,@aPartCode,@aAmount,@aBalance,@aPaymentWhile @@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 EndClose ARDeallocate AR--- Distribution of PDC Issued/Deposited/Hold ---Declare @pdcItemCount intDeclare @pdcPartCode Char(3)Declare @pdcPrDate datetimeDeclare @pdcPrStatus char(1)Declare @pdcCheckDate datetimeDeclare @pdcAmount numeric(19,2)Declare @pdcDateMode char(15)Declare @pdcDepoDate datetimeDeclare @pdcExcess Numeric(19,2)Declare @fItemCount intDeclare @fPartCode Char(3)Declare @fDueDate DatetimeDeclare @fBalance Numeric(19,2)Declare @tmpFromItemCount int Declare @fCondition char(500)Set @tmpFromItemCount=0Set @fCondition=''Set @pdcDepoDate=nullSet @pdcExcess=0Declare AR Cursor ForSelect ItemCount,PartCode,DueDate,Balance From tmpArUnPaidDueWhere BuyerCode=@BuyerCode and CutOff=@CutOff and Balance<>0OPEN ARFetch Next From AR INTO @fItemCount,@fPartCode,@fDueDate,@fBalanceWhile @@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 EndClose ARDeallocate AR--- Get Remarks ---Declare @fPdcAmt Numeric(19,2)Declare @fAmtDue Numeric(19,2)Declare @fPdcCount intDeclare @fPdcItemCount intDeclare @fPrStatus Char(1)Declare @fCheckDate datetimeDeclare @fCheckAmount Numeric(19,2)Declare @fFromItemCount intDeclare @fTmpFromItemCount intDeclare @Trec intDeclare @Cnt intSet @Trec=0Set @Cnt=0Set @fTmpFromItemCount=0Set @fPdcItemCount=0Set @fAmtDue=0Set @fPdcCount=0Set @fPdcAmt=0Set @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 ForSelect ItemCount,Rtrim(PartCode),DueDate,Balance,PrStatus,CheckDate,CheckAmount,FromItemCount From tmpArUnPaidDueWhere 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,@fFromItemCountWhile @@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 EndClose ARDeallocate ARDeclare @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
38200 Posts |
Posted - 2004-03-07 : 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
104 Posts |
Posted - 2004-03-08 : 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 RemarksB02-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
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-03-08 : 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=@CutOffDelete From tmpBuyerSLPayments Where BuyerCode=@BuyerCode Delete From tmpBuyerSLPayments_ItemCount Where BuyerCode=@BuyerCode Delete From tmpPayments Where BuyerCode=@BuyerCodeDelete From AFCM_ScheduleOfAR Where BuyerCode=@BuyerCode and CutOff=@CutOffDelete From AFCRecords Where BuyerCode=@BuyerCode and CutOff=@CutOff or this one:Select PartCode,Amount From BuyerSL where BuyerCode=@BuyerCodeand 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 |
|
|
|
|
|
|
|