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

Query performace problem

We have a query, which accepts a number of parameters.
When the parameter @.activeAffiliateCode is set, the following statement
executes, and the query runs very slowly on our LIVE server.
update t
set t.activeAffiliateID = @.activeAffiliateCode,
t.Weight = t.Weight + power(2,6)
from @.results t, BMG_VFE_WORLD.dbo.tblSongTerritory st with (nolock)
where t.SongID = st.SongID
and st.SysTerritoryCode = @.activeAffiliateCode
In the Development environment, the query runs in just a couple of
seconds when this parameter is set.
The two queryplans are mostly identical, except in the DEV version,
when it does it's index s on tblSongTerritory the rowcount is 313,
where as in the LIVE version it does the index s against
tblSongTerritory and the rowcount is 717,248,519!
Although both queryplans are using the same index on tblSongTerritory
(which is an Index on columns SysTerritoryCode and SongID), the LIVE
version doesn't seem to be able to use the SongID part of the index
1) DEV (runs in a couple of seconds)
OBJECT:([bmg_vfe_world].[dbo].[tblSongTerritory].[IX_tblSongTerritory_STCSongID]
as [st], SEEK:([st]. [SysTerritoryCode]=[@.activeAffiliateCode
] AND
[st].[SongID]=[t].[SongID]) ORDERED FORWARD
2) LIVE (takes about 5 minutes)
OBJECT:([bmg_vfe_world].[dbo].[tblSongTerritory].[IX_tblSongTerritory_STCSongID]
as [st], SEEK:([st]. [SysTerritoryCode]=[@.activeAffiliateCode
] ORDERED
FORWARD
So, you would have thought a statistics or index problem then?
Well, the statistics get updated nightly, and the indexes are rebuilt
nightly, and the query plan still stays the same on live.
Any suggestions from the gurus out there?Hi
Compare both execution plans to see what is going on. Run DBCC UPDATEUSAGE
on LIVE server as well as check out your SQL Server's settings on DEV and
LIVE enviroments
<jamiesurman@.gmail.com> wrote in message
news:1150358041.460713.27250@.i40g2000cwc.googlegroups.com...
> We have a query, which accepts a number of parameters.
> When the parameter @.activeAffiliateCode is set, the following statement
> executes, and the query runs very slowly on our LIVE server.
>
> update t
> set t.activeAffiliateID = @.activeAffiliateCode,
> t.Weight = t.Weight + power(2,6)
> from @.results t, BMG_VFE_WORLD.dbo.tblSongTerritory st with (nolock)
> where t.SongID = st.SongID
> and st.SysTerritoryCode = @.activeAffiliateCode
>
> In the Development environment, the query runs in just a couple of
> seconds when this parameter is set.
> The two queryplans are mostly identical, except in the DEV version,
> when it does it's index s on tblSongTerritory the rowcount is 313,
> where as in the LIVE version it does the index s against
> tblSongTerritory and the rowcount is 717,248,519!
> Although both queryplans are using the same index on tblSongTerritory
> (which is an Index on columns SysTerritoryCode and SongID), the LIVE
> version doesn't seem to be able to use the SongID part of the index
> 1) DEV (runs in a couple of seconds)
> OBJECT:([bmg_vfe_world].[dbo].[tblSongTerritory].[IX_tblSongTerritory_STCSongID]
> as [st], SEEK:([st]. [SysTerritoryCode]=[@.activeAffiliateCode
] AND
> [st].[SongID]=[t].[SongID]) ORDERED FORWARD
>
> 2) LIVE (takes about 5 minutes)
> OBJECT:([bmg_vfe_world].[dbo].[tblSongTerritory].[IX_tblSongTerritory_STCSongID]
> as [st], SEEK:([st]. [SysTerritoryCode]=[@.activeAffiliateCode
] ORDERED
> FORWARD
>
> So, you would have thought a statistics or index problem then?
> Well, the statistics get updated nightly, and the indexes are rebuilt
> nightly, and the query plan still stays the same on live.
> Any suggestions from the gurus out there?
>|||Uri Dimant wrote:
> Hi
> Compare both execution plans to see what is going on. Run DBCC UPDATEUSAGE
> on LIVE server as well as check out your SQL Server's settings on DEV and
> LIVE enviroments
Hi Uri, it runs a DBCC DBREINDEX against all the tables in the database
every night. Surely this should mean the index is fine? (I can't really
run anything against LIVE at the moment, because there are millions of
rows, and I don't want to affect the users)|||jamiesurman@.gmail.com wrote:

> Uri Dimant wrote:
>
> Hi Uri, it runs a DBCC DBREINDEX against all the tables in the database
> every night. Surely this should mean the index is fine? (I can't really
> run anything against LIVE at the moment, because there are millions of
> rows, and I don't want to affect the users)
Are you using SQL Server 2005.
You can force query plan using USE PLAN hint.
Look in BOL for more details
Regards
Amish Shah|||jamiesurman@.gmail.com wrote:

> Uri Dimant wrote:
>
> Hi Uri, it runs a DBCC DBREINDEX against all the tables in the database
> every night. Surely this should mean the index is fine? (I can't really
> run anything against LIVE at the moment, because there are millions of
> rows, and I don't want to affect the users)
Are you using SQL Server 2005.
You can force query plan using USE PLAN hint.
Look in BOL for more details
Regards
Amish Shah|||
> Are you using SQL Server 2005.
> You can force query plan using USE PLAN hint.
> Look in BOL for more details
> Regards
> Amish Shah
Hi Amish, nope it's SQL 2000. I'm not sure if forcing the plan would
help though, because both plans use an Index s against the same
index...however one of them seems to try to only match on
SysTerritoryCode (there are only 30-odd out of millions of records),
and the other one matches on SysTerritoryCode AND SongID (which has a
much better selectivity)|||How much data is on the test side? Is it really equivalent to the
live side?
I would start with basic checks. Are the table definitions on both
sides EXACTLY the same? Are the index definitions? The @.variables?
Something as simple as slight data type difference between
ysTerritoryCode and @.activeAffiliateCode can cause the optimizer to
skip the index s you need.
Roy Harvey
Beacon Falls, CT
On 15 Jun 2006 00:54:01 -0700, jamiesurman@.gmail.com wrote:

>We have a query, which accepts a number of parameters.
>When the parameter @.activeAffiliateCode is set, the following statement
>executes, and the query runs very slowly on our LIVE server.
>
>update t
>set t.activeAffiliateID = @.activeAffiliateCode,
> t.Weight = t.Weight + power(2,6)
>from @.results t, BMG_VFE_WORLD.dbo.tblSongTerritory st with (nolock)
>where t.SongID = st.SongID
>and st.SysTerritoryCode = @.activeAffiliateCode
>
>In the Development environment, the query runs in just a couple of
>seconds when this parameter is set.
>The two queryplans are mostly identical, except in the DEV version,
>when it does it's index s on tblSongTerritory the rowcount is 313,
>where as in the LIVE version it does the index s against
>tblSongTerritory and the rowcount is 717,248,519!
>Although both queryplans are using the same index on tblSongTerritory
>(which is an Index on columns SysTerritoryCode and SongID), the LIVE
>version doesn't seem to be able to use the SongID part of the index
>1) DEV (runs in a couple of seconds)
>OBJECT:([bmg_vfe_world].[dbo].[tblSongTerritory].[IX_tblSongTerritory_STCSongID]
>as [st], SEEK:([st]. [SysTerritoryCode]=[@.activeAffiliateCode
] AND
>[st].[SongID]=[t].[SongID]) ORDERED FORWARD
>
>2) LIVE (takes about 5 minutes)
>OBJECT:([bmg_vfe_world].[dbo].[tblSongTerritory].[IX_tblSongTerritory_STCSongID]
>as [st], SEEK:([st]. [SysTerritoryCode]=[@.activeAffiliateCode
] ORDERED
>FORWARD
>
>So, you would have thought a statistics or index problem then?
>Well, the statistics get updated nightly, and the indexes are rebuilt
>nightly, and the query plan still stays the same on live.
>Any suggestions from the gurus out there?|||Roy Harvey wrote:
> How much data is on the test side? Is it really equivalent to the
> live side?
Yes, it's a copy of the live DB from 2 ws ago,

> I would start with basic checks. Are the table definitions on both
> sides EXACTLY the same? Are the index definitions? The @.variables?
> Something as simple as slight data type difference between
> ysTerritoryCode and @.activeAffiliateCode can cause the optimizer to
> skip the index s you need.
I'll investigate further along these lines...the index is definitely
exactly the same in both environments though.|||Are your Development and Production SQL Servers both at the same SQL Server
Service Pack level?
Chris
"jamiesurman@.gmail.com" wrote:

> Roy Harvey wrote:
> Yes, it's a copy of the live DB from 2 ws ago,
>
> I'll investigate further along these lines...the index is definitely
> exactly the same in both environments though.
>|||Chris Howarth wrote:
> Are your Development and Production SQL Servers both at the same SQL Serve
r
> Service Pack level?
Both servers are SP4 Chris