I'm in the process of converting a non SQL DB application into MS SQL.
I've got the data in and it is slow.
The original application only worked on 1 row at a time and had no SQL support.
My question relates, I suppose, to indexes.
The main tables currently have 250,000 header style rows with 1,500,000 transaction style rows.
The tables are linked via the record number (which was imported and is unique).
The new app is to investigate very small ranges of data and normally provide counts.
The "headers" are tickets and the "transactions" are casings. There are a maximum of 20 casings per ticket.
A casing will have a casing code, an acceptance, a rejection, a comment, a price and other things. Lookups to appropriate tables (an acceptance code will link to the acceptancecodes table, etc) also exist.
The sort of thing being asked for is the number of casings where a rejection code of ER1 exists for all tickets processed for a particular customer in a particular date range.
The customer column and date are on the ticket.
With another SQL DB I have used in the past, I can say "EXPLAIN SELECT ...". This reports the indexes being used for the statement and other things.
Is there a way of seeing HOW a query will be executed?
I don't think I want to add indexes to every column, though every column could be used as part of a query specifically to filter the results of the query.
Now here is another thing to consider.
The data, currently and for the foreseeable future, is being exported from one system and imported into MS SQL.
Once the data is in MSSQL, it is not being edited.
Most of the time, the data being requested is counts of casings for a particular condition for a particular customer.
I was considering using a data cube (now just because I used the word's "data cube", doesn't mean I understand how to use them or even if it is appropriate).
I think this can be used to deal with this sort of aggregation.
Ideas, suggestions, etc on the OLAP/Data Cube idea. How do you know when it is appropriate.
And anything else on my query analysis issue.