So we've built this web-based application for our Customer Service people to search a specific database table and retreive order information for our customers. The interface consists of an .ASP page that dynamically builds and submits a query and then displays the results. The problem is that it sometimes takes a couple of minutes to return the results and sometimes the thing just times out and throws an error. (Then they tell me about it, that's the real problem.) Anyway I'd like some help trying to optimize the performance of this interface. I know enough to be dangerous, but not quite enough to be completely on top of the DB performance.
The table has 6.7 million records with the following columns:
OrderID (primary key and ID, int)
PartnersOrderID (varchar 255)
OrderDate (varchar 255)
OrderType (char 1)
Status (varchar 255)
FirstName (varchar 255)
LastName (varchar 255)
Address1 (varchar 255)
Address2 (varchar 255)
City (varchar 255)
State (varchar 255)
ZipCode (varchar 255)
The fields on the form which go into the query can be:
When the .ASP builds the query it has to use wildcard characters around a lot of the fields. For instance if someone searches for "Jane" it needs to match "Mary Jane" and "Janessa". Therefore looking for "%jane%" slows the thing down significantly, obviously, but I dont' know how else to make the tool useful because the public-at-large can't be counted on to be helpful enough to know their own name, unfortunately.
The people we've got supporting our servers suggest that it's slow because we're always submitting queries instead of stored procedures, but I don't see how to put all of this into a stored procedure when all of the fields aren't populated.
So I'm now thinking that I could fool around with secondary keys or something.
Anybody got any bright ideas?
The statistics of the SQL server are:
Product Version: "8.00.2039"
Product Level: "SP4"
Edition: "Enterprise Edition"
Engine Edition: "3"
EngineEditionText: "Enterprise . . ."