Preston M. Price


I have a ledger table that will have many entries for a given invoice, I need a good way to identify the most recent entry for the invoice.
I've thought about putting an IsMostRecent flag in the column, but it would still require a table scan to identify the most recent records for invoices.
I thought I could build a view over that table that had the invoiceID and max(tableID) and build an index on it, but you can't use the max() function in this instance.
The only other thing I can think of us to build another lookup table, with the invoiceID and the tableID, but I don't think that is the best solution.

Any tips would be greatly appreciated.



Re: Need advice on how to optimize table.

mvdholst


Could you post your table definition and any indexes you currently have defined on your table. Based on this information, we might be able to give you tips about how to optimize.

Thanks,

Marcel van der Holst
[MSFT]






Re: Need advice on how to optimize table.

Preston M. Price

The table looks like:
ID int, -- Identity NOT NULL Primary Key
DayID int, -- NOT NULL
InvoiceID int, -- NOT NULL
UserID int, -- NOT NULL
DollarValue float, -- NOT NULL
PointValue int, -- NOT NULL
DollarDelta float, -- NOT NULL
PointDelta int -- NOT NULL

I do not currently have any indexes (other than the PK index) on the table.






Re: Need advice on how to optimize table.

Chris Howarth

Assuming that your Primary Key is CLUSTERED then personally I would create a NONCLUSTERED index on the InvoiceID column then use the following query:

SELECT TOP 1 ID

FROM <Table>

WHERE InvoiceID = @InvoiceID

ORDER BY ID DESC

If your Primary Key is NONCLUSTERED, and assuming you have used the CLUSTERED index elsewhere, then create a NONCLUSTERED composite index on InvoiceID (ASC), ID (DESC) before running the same query.

Hope this helps,

Chris






Re: Need advice on how to optimize table.

GlennAlanBerry

You might try creating a unique, non-clustered index with InvoiceID as the first column, and ID as the second column (sorted DESC). This will create a "covering" index that completely covers the query below.

SELECT TOP (1) ID
FROM <Table>
WHERE InvoiceID = @InvoiceID
ORDER BY ID DESC

If you have SQL 2005 Enterprise, you can use the online index creation functionality to create the index without causing blocking/locking and without taking an outage. Then you can run your query in SSMS and see whether it is using your index.