|
|
Posted by Dan Guzman on 09/26/06 12:36
Without the scripts Hugo requested, we really can't provide specific
recommendations. I suggest you start by identifying the longest running
query in your invoicing process and post the relevant DDL for that query.
To identify problem queries, run a Profiler trace with SQL statement
completed and stored procedure statement completed events and include a
filter on high duration (e.g. > 1000). Examine the execution plans of those
queries and add indexes or tweak the SQL for efficiency.
Some general observations and guidelines:
> I need to run my SP for all invoices older
> than current date (including invoices for 22nd of sept 2006).
I assume there is other criteria besides date for these older invoices. If
you have some indicator like InvoiceProcessed, and index on that column
(perhaps with InvoiceDate too) might be a good candidate.
> The SP runs 2h.
This seems like an extraordinarily long time for even a very involved daily
process. I would expect that a standard invoicing process would take no
more than a minute for thousands of invoices. I suspect sub-optimal
execution plans (e.g. iterative scans) and/or poor query techniques
(cursors). Suboptimal trigger processing (scans) are notorious for causing
concurrency problems. I don't know the purpose of the triggers but you
might instead consider performing the process in stored procedures if
possible.
> 1. What indexes should I set for my invoice table? Currently I have an
> uniqueidentifier as primary key. I suppose I should set another
> non-unique index for the [invoicedate] column.
The best candidates for indexes are columns in JOIN and WHERE clause
predicates. I already mentioned that InvoiceDate and the theoretical
InvoiceProcessed column. In fact, this may be a good candidate for the
clustered index.
In lieu of scripts, you might try running the workload through the Index
Tuning Wizard or Database Tuning Advisor for automated suggestions.
> 2. What types of transactions should I use inside the SP, so that
> inserting new invoices will be accepted by db engine?
The purpose of transactions are to provide data integrity per ACID rules.
It's best to specify the lowest level that satisfies this requirement. READ
COMMITED is most often appropriate. However, keep in mind that performance
and concurrency often go hand-in-hand. Ideally, the daily process should
have minimal effect on inserting new invoices and visa-versa as long as
index are useful.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"nano2k" <adi@ikonsoft.ro> wrote in message
news:1159265897.398795.185840@k70g2000cwa.googlegroups.com...
> Hi Hugo
>
> Thank you for your quick reply.
>
> It's hard for me to provide all data that you need (and I fully
> understand the needing) because all (many) objects involved (SP,
> tables, indexes, triggers) are big (as number of lines and also as
> complexity).
>
> I reformulate my problem to make it more simple to understand and let's
> forget about the complexity of the objects enumerated above.
>
> I have an app that allows me to insert invoices into my invoice table.
> Classic.
> Today is sept 23rd 2006. I need to run my SP for all invoices older
> than current date (including invoices for 22nd of sept 2006). The SP
> ***may*** modify (that is, UPDATE) ***all*** invoices until 22nd
> (including). The SP runs 2h. This means that I have to wait 2h before
> the system allows me to insert new invoices for 23rd of sept 2006, even
> if new invoices do not affect the result of my SP. This is not accepted
> as the SP may be executed 2-3 times a day.
> I also have tens of thousands of records in my invoice table.
>
> My questions:
> 1. What indexes should I set for my invoice table? Currently I have an
> uniqueidentifier as primary key. I suppose I should set another
> non-unique index for the [invoicedate] column.
> 2. What types of transactions should I use inside the SP, so that
> inserting new invoices will be accepted by db engine?
>
> Thanks.
>
Navigation:
[Reply to this message]
|