I have an MSDE 2000 database with several databases and stored procedures that I've duplicated onto a new server.
The new server is Windows 2008 R2 running on VMWare vSphere 4. I've loaded SQL 2008 Standard SP1 onto this VM and copied all of the databases and stored procedures from MSDE 2000 to SQL 2008. I'm running through all the stored procedures to make sure they work before I point everything at the new server. Most stored procedures work just fine, however there are some that I'm having difficulty with.
Here's an example:
When I run this on the server with 2000 using the parameters of '1/1/2007', '5/1/2007' it takes 38 seconds to run. When I run this on the server with 2008 using the same parameters, it takes 39 seconds to run. However, if I run this on the server with 2000 using normal parameters such as '1/1/2007', '12/23/2009' it takes 4 minutes 38 seconds. When I run '1/1/2007', '12/23/2009' on the new 2008 server, it takes well over 45 minutes (I haven't let it run all the way out because 45 minutes is way too long).
It appears that this part of the above stored procedure is what is taking so long:
The above code produces the answers I am looking for. I'm wondering if anyone knows of a better way to go about this, or if there is something unique to SQL 2008 that makes this slower? I'm at a loss. Thanks for any help you can provide.
The new server is Windows 2008 R2 running on VMWare vSphere 4. I've loaded SQL 2008 Standard SP1 onto this VM and copied all of the databases and stored procedures from MSDE 2000 to SQL 2008. I'm running through all the stored procedures to make sure they work before I point everything at the new server. Most stored procedures work just fine, however there are some that I'm having difficulty with.
Here's an example:
Code:
ALTER PROCEDURE [dbo].[CR_AF_OutstandingCylinders]
@BeginDate varchar(11) = '1/1/2007',
@EndDate varchar(11) = '12/31/2007'
AS
DECLARE @InvoiceTable as varchar(255)
DECLARE @InvoiceLineDetailTable as varchar(255)
DECLARE @DROPCDView as varchar(1024), @DROPCMView as varchar(1024)
DECLARE @CREATECDView as varchar(2048), @CREATECMView as varchar(2048)
SET @DROPCDView = 'DROP VIEW CylinderDepositsView'
SET @CREATECDView = 'CREATE VIEW CylinderDepositsView AS
SELECT AF_QB_invoicelinedetail.CustomField6 as CDSerialNum,
CustomerRef_FullName as CDCustName,RefNumber as CDInvoiceNum,TxnDate as CDTxnDate, IsPaid,
AF_QB_invoicelinedetail.TxnLineID as CDTxnLineID, AF_QB_invoice.Franchise as CDFranchise
FROM AF_QB_invoicelinedetail INNER JOIN AF_QB_invoice ON IDKEY = TxnID
WHERE (
(TxnDate >= ''' + @BeginDate + ''' AND TxnDate <= ''' + @EndDate + ''')
AND
(TemplateRef_FullName = ''Cylinder Deposit'')
AND
(CustomerRef_FullName <> ''Miscellaneous'')
AND
(ItemRef_FullName = ''Cylinder Deposit'')
)
'
If object_ID('RRCorp..CylinderDepositsView')IS NOT NULL
BEGIN
PRINT 'CylinderDepositsView Exists!'
exec(@DROPCDView)
PRINT 'CylinderDepositsView Deleted!'
exec(@CREATECDView)
PRINT 'CylinderDepositsView Created!'
END
ELSE
BEGIN
PRINT 'CylinderDepositsView does not exist. Creating CylinderDepositsView...'
exec(@CREATECDView)
PRINT 'CylinderDepositsView Created!'
END
SET @DROPCMView = 'DROP VIEW CylinderReturnsView'
SET @CREATECMView = 'CREATE VIEW CylinderReturnsView AS
SELECT AF_QB_creditmemolinedetail.CustomField6 as CMSerialNum,
CustomerRef_FullName as CMCustName, RefNumber as CMCreditMemoNum,TxnDate as CMTxnDate, CreditRemaining,
AF_QB_creditmemolinedetail.TxnLineID as CMTxnLineID, AF_QB_creditmemo.Franchise as CMFranchise
FROM AF_QB_creditmemolinedetail INNER JOIN AF_QB_creditmemo ON IDKEY = TxnID
WHERE (
(TxnDate >= ''' + @BeginDate + ''' AND TxnDate <= ''' + @EndDate + ''')
AND
(TemplateRef_FullName = ''Cylinder Deposit - Credit Memo'')
AND
(CustomerRef_FullName <> ''Miscellaneous'')
AND
(ItemRef_FullName = ''Cylinder Deposit'')
)
'
If object_ID('RRCorp..CylinderReturnsView')IS NOT NULL
BEGIN
PRINT 'CylinderReturnsView Exists!'
exec(@DROPCMView)
PRINT 'CylinderReturnsView Deleted!'
exec(@CREATECMView)
PRINT 'CylinderReturnsView Created!'
END
ELSE
BEGIN
PRINT 'Creating CylinderReturns View...'
exec(@CREATECMView)
PRINT 'CylinderReturnsView Created!'
END
DECLARE @Reconcile TABLE (InvoiceNum varchar(255), CreditMemoNum varchar(255), SerialNum varchar(50), InvoiceLineTxnLineID varchar(255),
CreditMemoTxnLineID varchar(255), IsPaid bit, Franchise varchar(50))
INSERT @Reconcile (InvoiceNum,CreditMemoNum,SerialNum,InvoiceLineTxnLineID,CreditMemoTxnLineID,IsPaid,Franchise)
SELECT CDInvoiceNum as InvoiceNum, CMCreditMemoNum as CreditMemoNum, CDSerialNum as SerialNum,
CDTxnLineID as InvoiceTxnLineID, CMTxnLineID as CreditMemoTxnLineID, IsPaid, CDFranchise As Franchise FROM CylinderDepositsView CD
INNER JOIN CylinderReturnsView CM
ON CD.CDSerialNum = CM.CMSerialNum
WHERE CD.CDCustName = CM.CMCustName AND CMTxnDate >= CDTxnDate AND CD.CDFranchise = CM.CMFranchise
-- This Query shows the serial #'s that haven't been picked up
select DISTINCT CDSerialNum,
CDCustName,
CDInvoiceNum,
CDTxnDate,
CDTxnLineID,
CD.IsPaid,
CD.CDFranchise
from CylinderDepositsView CD, @Reconcile RC
where (RC.Franchise = CD.CDfranchise AND CDSerialNum IN
(select CMSerialNum
from CylinderReturnsView
WHERE CD.CDTxnDate > CMTxnDate AND CDTxnLineID NOT IN (Select InvoiceLineTxnLineID from @Reconcile)))
OR
(CDSerialNum NOT IN (select CMSerialNum from CylinderReturnsView) OR (CDTxnLineID NOT IN (Select InvoiceLineTxnLineID from @Reconcile)))When I run this on the server with 2000 using the parameters of '1/1/2007', '5/1/2007' it takes 38 seconds to run. When I run this on the server with 2008 using the same parameters, it takes 39 seconds to run. However, if I run this on the server with 2000 using normal parameters such as '1/1/2007', '12/23/2009' it takes 4 minutes 38 seconds. When I run '1/1/2007', '12/23/2009' on the new 2008 server, it takes well over 45 minutes (I haven't let it run all the way out because 45 minutes is way too long).
It appears that this part of the above stored procedure is what is taking so long:
Code:
select DISTINCT CDSerialNum,
CDCustName,
CDInvoiceNum,
CDTxnDate,
CDTxnLineID,
CD.IsPaid,
CD.CDFranchise
from CylinderDepositsView CD, @Reconcile RC
where (RC.Franchise = CD.CDfranchise AND CDSerialNum IN
(select CMSerialNum
from CylinderReturnsView
WHERE CD.CDTxnDate > CMTxnDate AND CDTxnLineID NOT IN (Select InvoiceLineTxnLineID from @Reconcile)))
OR
(CDSerialNum NOT IN (select CMSerialNum from CylinderReturnsView) OR (CDTxnLineID NOT IN (Select InvoiceLineTxnLineID from @Reconcile)))The above code produces the answers I am looking for. I'm wondering if anyone knows of a better way to go about this, or if there is something unique to SQL 2008 that makes this slower? I'm at a loss. Thanks for any help you can provide.
