Showing posts with label filter. Show all posts
Showing posts with label filter. Show all posts

Wednesday, March 28, 2012

query problem

hi,


I need this as a filter criteria in my where clause for my select statement,

I need to set

Year1 = current year and

Year2 = currentyear-1

when current date's [month-day] > july30th

current year is in the form of 07 or 06 or 05

i.e

Year1 = 07

Year2 = 06

when current date's[ month-day] < july01

Year1 = current year - 1 and

Year2 = currentyear-2

Year1 = 06

Year2 = 05

I know I can use datepart, but current date from getdate() function gives

2007-10-07-hh.min.sec

and I need to compare this with

july 10th or june30th at any point of a year

also

set Year1

Year2 in the form 07 or 06 or 05

thnx

I suggest that this, and many date related queries you now have or will have in the future will be so much easier if you were to explore using a Calendar table. See this source.

Seems like you need to compare a date against Jul 01, so DATEDIFF() 'could' help.

Here is one simple use of DATEDIFF():


DECLARE
@.DateToVerify datetime,
@.FYear datetime


SELECT
@.DateToVerify = getdate(),
@.FYear = dateadd( day, 181, dateadd( year, datediff( year, 0, getdate() ), 0 ))


SELECT CASE
WHEN datediff( day, @.FYear, @.DateToVerify ) >= 0 THEN '07'
WHEN datediff( day, @.FYear, @.DateToVerify ) >= (-365) THEN '06'
WHEN datediff( day, @.FYear, @.DateToVerify ) >= (-730) THEN '05'
ELSE '04'
END

Most likely there are many ways to 'skin this cat', as the saying goes. But a Calendar table is often the most robust method of handling date span/range issues.

Wednesday, March 21, 2012

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...