[AccessD] Optimizing Stored Procedures and Views

If you want to make sure that both are running identically... one way
would be to run the utility from red-gate.

http://www.red-gate.com/products/SQL_Compare/index.htm

It's trial ware which is great for 14 days.  I use it from time to
time on dev systems.  Typically I run a backup of the live system to
the dev box, but this is another quick fix you can use

On 4/2/06, John Ruff <papparuff at comcast.net> wrote:
> Check your indexes in the tables that make up the views on the db that is
> running slow and compare them with the same tables in the db that is running
> fast. I'd venture to say you don't have one or more columns indexed in the
> slow db that should be indexed.
>
> Also, I would suggest using aliases in your stored procedures or any long
> query. It makes reading them much easier. Such as this:
>
> SELECT
>         a.CustomerID
>         ,a.StatementID
>         ,a.AccountNo
>         ,a.CustName
>         ,a.StatementDate
>         ,a.StatementNumber
>         ,a.AccFreq
>         ,a.CurrentMth
>         ,a.OneMonth
>         ,a.TwoMonths
>         ,a.ThreeMonths
>         ,a.NoOverdueNote
>         ,c.NetReceipts
>         ,c.GJCredit
>         ,d.NetReceiptsInv
>         ,d.GJCreditInv
>         ,a.MGrpIDNo
>         ,a.DDRegistered
>         ,a.DDFrequency
>         ,a.PayArrange
>         ,e.ReceiptNotPost AS ReceiptsNotPosted
>         ,a.VIPCust
>         ,a.AccStatus
> FROM dbo.vwAgedDebtorGet AS a
> INNER JOIN dbo.vwAgedDebtorGetLastStatDate AS b
>         ON a.CustomerID = b.CustIDNo
>         AND a.StatementDate = b.StatementDate
> LEFT OUTER JOIN dbo.vwAgedDebtorReceipt AS c
>         ON a.CustomerID = c.CustIDNo
>         AND a.StatementNumber = c.StatNum
> LEFT OUTER JOIN dbo.vwAgedDebtorReceiptInv AS d
>         ON a.CustomerID = d.CustIDNo
>         AND a.StatementNumber = d.InvNum
> LEFT OUTER JOIN dbo.vwAgedDebtorReceiptNotPost AS e
>         ON a.CustomerID = e.CustIDNo
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David Emerson
> Sent: Sunday, April 02, 2006 6:03 PM
> To: accessd at databaseadvisors.com; dba-SQLServer at databaseadvisors.com
> Subject: [AccessD] Optimizing Stored Procedures and Views
>
> Cross posted AccessD SQL
>
> SQL2000
>
> I have a stored procedure that is based on a number of views.  I have
> two different databases that have the same tables, views and stored
> procedures.  The only difference is that the data is for separate
> companies.  Both databases are on the same server.
>
> I developed one set of queries and stored procedure in one of the
> databases.  The main part of the stored procedure is as follows:
>
> SELECT dbo.vwAgedDebtorGet.CustomerID,
> dbo.vwAgedDebtorGet.StatementID,
> dbo.vwAgedDebtorGet.AccountNo,  dbo.vwAgedDebtorGet.CustName,
> dbo.vwAgedDebtorGet.StatementDate, dbo.vwAgedDebtorGet.StatementNumber,
>          dbo.vwAgedDebtorGet.AccFreq, dbo.vwAgedDebtorGet.CurrentMth,
> dbo.vwAgedDebtorGet.OneMonth,
>          dbo.vwAgedDebtorGet.TwoMonths,
> dbo.vwAgedDebtorGet.ThreeMonths, dbo.vwAgedDebtorGet.NoOverdueNote,
>          dbo.vwAgedDebtorReceipt.NetReceipts,
> dbo.vwAgedDebtorReceipt.GJCredit, dbo.vwAgedDebtorReceiptInv.NetReceiptsInv,
>          dbo.vwAgedDebtorReceiptInv.GJCreditInv,
> dbo.vwAgedDebtorGet.MGrpIDNo,
>          dbo.vwAgedDebtorGet.DDRegistered,
> dbo.vwAgedDebtorGet.DDFrequency, dbo.vwAgedDebtorGet.PayArrange,
>          dbo.vwAgedDebtorReceiptNotPost.ReceiptNotPost AS
> ReceiptsNotPosted,
> dbo.vwAgedDebtorGet.VIPCust,        dbo.vwAgedDebtorGet.AccStatus
> FROM dbo.vwAgedDebtorGet INNER JOIN dbo.vwAgedDebtorGetLastStatDate
> ON dbo.vwAgedDebtorGet.CustomerID =
>          dbo.vwAgedDebtorGetLastStatDate.CustIDNo AND
> dbo.vwAgedDebtorGet.StatementDate =
>          dbo.vwAgedDebtorGetLastStatDate.StatementDate LEFT OUTER
> JOIN dbo.vwAgedDebtorReceipt ON
>          dbo.vwAgedDebtorGet.CustomerID = dbo.vwAgedDebtorReceipt.CustIDNo
> AND
>          dbo.vwAgedDebtorGet.StatementNumber =
> dbo.vwAgedDebtorReceipt.StatNum LEFT OUTER JOIN dbo.vwAgedDebtorReceiptInv
> ON
>          dbo.vwAgedDebtorGet.CustomerID =
> dbo.vwAgedDebtorReceiptInv.CustIDNo AND
>          dbo.vwAgedDebtorGet.StatementNumber =
> dbo.vwAgedDebtorReceiptInv.InvNum
>          LEFT OUTER JOIN dbo.vwAgedDebtorReceiptNotPost ON
>          dbo.vwAgedDebtorGet.CustomerID =
> dbo.vwAgedDebtorReceiptNotPost.CustIDNo
>
> In the SQL database that this was developed in it runs fine (in about
> 30 seconds).  However, when I script the objects and restore them
> into the second database, the same stored procedure runs for over 10
> minutes with about a fifth of the data.
>
> Is there some optimizing that I need to do to get the second database
> to work as fast as the first one?
>
> Regards
>
> David Emerson
> Dalyn Software Ltd
> New Zealand
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


--
-Francisco
http://pcthis.blogspot.com |PC news with out the jargon!
http://sqlthis.blogspot.com | Tsql and More...