Optimising SQL Server Requests from VB / ASP .NET

[ben1234zz]

Hi

We are trying to optimise a web page by reducing the number of sql requests.

We have 4 repeaters on the page displaying:

Search Results
(SQL: SELECT TOP 25 * FROM (SELECT ROW_NUMBER() OVER (ORDER BY Product ASC) as RK FROM View_Products WHERE Code > 200) as a WHERE rk >= 2)

Manufacturer
(SQL: SELECT Distinct(Manuf) FROM View_Products WHERE Code > 200)

Head Category
(SQL: SELECT Distinct(HeadCat) FROM View_Products WHERE Code > 200)

Sub Category
(SQL: SELECT Distinct(HeadCat) FROM View_Products WHERE Code > 200)

These are then laid out in separate repeaters in the below format:

Manuf: xxx (add to search), yyyy (add to search)

Head Cat: xxx (add to search), yyyy (add to search)

Sub Cat: xxx (add to search), yyyy (add to search)

Search results
Record 1
Record 2
Record 3
Record 4

The Search SQL can get quite complex using full text searches and there are many records, therefore each of the manufacturer & category searches can again get complex. Essentially the search is run 3/4 times against the database which doesn’t seem efficient.

I am out of ideas to make this more effective.

Any suggestions would be much appreciated.

Thanks
B

PS: there may be Typos in the SQL but its only to give an impression

 

[jmeckley]

does the current user experience expose latency? the reason I ask is that if this isn't a perceived issue yet, then I wouldn't worry too much about it. after that...

are you using any type of data access/orm framework? if so the framework may have a "best practices" or preferred usage methodologies.

if you are using the raw ADO.Net objects then i would look into connection management and command management. take a look at my FAQ in my signature below for more details.

with MS SQL you can return multiple record sets with one command. this may be another optimization worth looking into.

if you are still looking for preformance boosts after that research Lucene.Net. this is an indexing engine (in place of full text search) that basically would translate the fuzz text search into a list of primary keys. you can then select specific records by there primary key from the MS SQL database.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[jbenson001]

Are you running the 4 commands separately in the page? As Jason states, one statement would work. What you have to do is write a stored procedure that runs your code and returns each of he 4 datasets. This would help with speed. But you should look into optimizing the SQL in the stored procedure if you go that way.