query performance for filtering and lookup in a web-app

Hi,

I am developing a web-app with ASP.NET 2.0.

I have main query for a grid which accepts a lot of filter parameter. If the filter are set to "all" I would like to retrive all records for this condition... it has also like 6 Left joins for looking up values (long description of a key)

I tryed this query with like 50 Records and it was very quick... then I saw the production this one got like 20.000 Records and of course bigger lookup tables, too. I used my query in Enterprise Manager it took 2 minuetes (which is way to long)... Now I need to come up with a solution which faster...

The left joins should be not the issues... without the filtering I come with the 20.000 records to 5 sek. That should be valueable...

For filtering I use this trick:

...

AND

(Table1.Name = @.Value1 OR @.Value1 is NULL OR @.Value1='')

AND

(Table1.LastName = @.Value2 OR @.Value2 is NULL OR @.Value2='')

AND

(Table1.ZIPCODE = @.Value3 OR @.Value3 is NULL OR @.Value3='')

...

I don't know wheather this is the right place to post but I hope someone has some performance tips for me...

Is results in the domain of 20k rows what you expect?

If it is, then you need also find out where the most part of the time is spent - is it on the server, or is it in the asp app?
(I could imagine it would take a while to deal with 20.000 rows in the grid?)

Also, look at the query plans.
Is there proper indicies in place to support the query?
Depending on how the parameters to the query are supplied, different combinations may get suboptimal plans, depending on what other combos have been used.

When developing, one should always try to test against 'production-like' data, in quality as well as volume.
It helps in order to find 'issues' as early as possible.

...I don't think I can be more specific at this time.

/Kenneth

|||

Thanks for your answer. I think my question was not to specific, too.

I don't think that I will always have the 20k records inside the db. But what I whould like to know is how performand a query is when you have a construct like this for filtering in the where clause.

AND

(Table1.Name = @.Value1 OR @.Value1 is NULL OR @.Value1='')

AND

(Table1.LastName = @.Value2 OR @.Value2 is NULL OR @.Value2='')

AND

(Table1.ZIPCODE = @.Value3 OR @.Value3 is NULL OR @.Value3='')

It is more a basic question how whould one have to set up query which needs like 7 filter options. I would like to tackle this with one query and the TableAdapters. Otherwise I have to check in Code which values are set and build a dynamic query... But asp.net security guides says... avoid dynamic queries... so I wanna basically know a bestpractise way which is also fast =) ...

Sorry for beeing not so precise...

Regards,

P.S.: I will look at indeces, too. This might be a point to optimize...