Rossamino
asked on
Please help optimize this web interface
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)
PartnersID (int)
PartnersOrderID (varchar 255)
OrderDate (varchar 255)
ProductCode (int)
OrderType (char 1)
Status (varchar 255)
CreateDate (datetime)
ProcessDate (datetime)
CancelDate (smalldatetime)
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:
FirstName
LastName
ZipCode
PartnersOrderID
OrderID
ProductCode
PartnerID
OrderType
StartDate
EndDate
Status
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 . . ."
The table has 6.7 million records with the following columns:
OrderID (primary key and ID, int)
PartnersID (int)
PartnersOrderID (varchar 255)
OrderDate (varchar 255)
ProductCode (int)
OrderType (char 1)
Status (varchar 255)
CreateDate (datetime)
ProcessDate (datetime)
CancelDate (smalldatetime)
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:
FirstName
LastName
ZipCode
PartnersOrderID
OrderID
ProductCode
PartnerID
OrderType
StartDate
EndDate
Status
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 . . ."
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>So I should add indexes for the integer and date fields and implement full-text searching for the character data? <<
No, I did not say that. I said you should read up on Full-Text search. After you have done that then you may decide that it is the approach you want to take. But the decision should not be taken lightly as there is more then just a code change. It is not trivial.
No, I did not say that. I said you should read up on Full-Text search. After you have done that then you may decide that it is the approach you want to take. But the decision should not be taken lightly as there is more then just a code change. It is not trivial.
ASKER
Oh, well I've created the Full Text Catalog and set the one table to Populate tonight at 1:00am. I don't really see any downside. I hope there's nothing I'm missing.
I'm looking at the MSDN overview of how to write a full text query and the syntax is a bit puzzling. If I wanted to rewrite:
select * from OrderData where firstname like '%jane%'
it should look like:
select * from OrderData where contains(firstname, 'jane') ?
The overview page has some characters that aren't rendering properly on the page so I can't quite make it out.
I'm looking at the MSDN overview of how to write a full text query and the syntax is a bit puzzling. If I wanted to rewrite:
select * from OrderData where firstname like '%jane%'
it should look like:
select * from OrderData where contains(firstname, 'jane') ?
The overview page has some characters that aren't rendering properly on the page so I can't quite make it out.
>>I hope there's nothing I'm missing.<<
That new or modified data has to be managed in one of three ways:
1. Added Manually.
2. Added on schedule.
3. Added in the background
In all three cases it is not updated immediately.
>>I'm looking at the MSDN overview of how to write a full text query and the syntax is a bit puzzling. <<
Provided the name you are looking for is jane then yes the syntax is:
select * from OrderData where firstname like '%jane%'
SELECT *
FROM OrderData
WHERE CONTAINS(firstname, 'jane' )
Or to look in all Full-Text columns
SELECT *
FROM OrderData
WHERE CONTAINS(*, 'jane' )
This will find instancees of "Jane" or "Mary Jane" or "Jane Elizabeth"
You can also do the following to search for all names starting with "jan":
SELECT *
FROM OrderData
WHERE CONTAINS(*, '"jan*"' )
What you cannot do with Full-Text Search is search for "jane" to find "maryjane" (one word). In other words you cannot do this:
SELECT *
FROM OrderData
WHERE CONTAINS(*, '"*jan"' )
That new or modified data has to be managed in one of three ways:
1. Added Manually.
2. Added on schedule.
3. Added in the background
In all three cases it is not updated immediately.
>>I'm looking at the MSDN overview of how to write a full text query and the syntax is a bit puzzling. <<
Provided the name you are looking for is jane then yes the syntax is:
select * from OrderData where firstname like '%jane%'
SELECT *
FROM OrderData
WHERE CONTAINS(firstname, 'jane' )
Or to look in all Full-Text columns
SELECT *
FROM OrderData
WHERE CONTAINS(*, 'jane' )
This will find instancees of "Jane" or "Mary Jane" or "Jane Elizabeth"
You can also do the following to search for all names starting with "jan":
SELECT *
FROM OrderData
WHERE CONTAINS(*, '"jan*"' )
What you cannot do with Full-Text Search is search for "jane" to find "maryjane" (one word). In other words you cannot do this:
SELECT *
FROM OrderData
WHERE CONTAINS(*, '"*jan"' )
ASKER
Thanks a lot and I'm sure the CSR's will thank you as well.
ASKER