Database Performance - Big searches
Posted on 2005-05-10
I have a few issues regarding performance with SQL server.
Basically its abit of an ongoing process, Customers send us spreadsheets with data which we put through a data cleansing process, this data cleansing process spits out a cleaned spreadsheet and inserts all of the records into a table called 'tblCustomerData', clients are then given the option to search this cleaned data online through search pages.
The search page itself contains about 6 or 7 list boxes (html) populated from their data with Distinct/Group by queries so that the data in the lists are relevant to their data
When data is pulled from this search page it is inserted into another table called "tblTempData", this table only has 3 colums which are foreign keys to the tblCustomerdata tables and has a unique identifier
the next page displays results on a page with a menu at the top which allows them to break down the records into different categories (this is just dont by submitting a number to a stored procedure and a recordset is sent back grouped by accordingly - this page used tblTempData as its source and this table joins on to tblcustomerdata, here is an ERD because this is getting messy:
tblCustomerData ----------------- tblTempData (One to One relationship)
Ok, both of these pages take a good 30seconds to 1minute to load - which is quite a while
the reason for this TempTable is so they can remove unwanted search results
I have indexed the tblCustomerData properly, i have created the Clustered index on the CustomerID because this is what the customer data will be grouped by in the tblCustomerdata table - this is because more than one customer may have access to this facility. And i have indexed all of the other fields that will be searched on, thing is i dont want to index more than i have to as this will slow the insertion process that happens with the data cleansing process
the search the users carry out can bring back anything from 5 records to 50,000 records
does anyone know of any techniques for speeding up querying?
If sorry for the length of this, any help is appreciated thanks