Stored procedure help, makes server jump to 100%

[Luis]

Hello, we have a users table with the following fields:

client_id varchar 20
counter1 int
counter2 int
..
..
..
counter50 int

We need to do some queries that match counters from different users.
For example: user admin1 has a value of 5 in counter1 and zero for the
rest of the counter fields. We would need to find out what users have
zero for counter1 and more than zero for the rest of the fields. So it
is an inverse match.

Because the values change all the time, we could not write a 'fixed'
stored procedure. We use server side (asp) to build the query on the
web server and then send it to the database server.

The query varies, depending on the values, but it is something like:

SELECT * FROM users WHERE counter1=0 AND (counter2>0 OR counter3>0 OR
counter4>0 OR counter5>0 OR counter6>0 OR ..... counter50>0)

The query returns the values we need but the server jumps to 100%
utilization so we need to find out if there's a way of doing the same
query more efficient.

Thanks a lot

This table should be normalized. This would make it much easier to build the query.
user Table
clientID
....

counters Table
clientID
counterNum
counterValue

In this scenario, you would have 50 entries per user in the counters table.

"Luis" <luis@dacorporation.com> a écrit dans le message de news: [email]573cddf1.0306301034.33823423@posting.google.com[/email]...

> Hello, we have a users table with the following fields:
>
> client_id varchar 20
> counter1 int
> counter2 int
> .
> .
> .
> counter50 int
>
> We need to do some queries that match counters from different users.
> For example: user admin1 has a value of 5 in counter1 and zero for the
> rest of the counter fields. We would need to find out what users have
> zero for counter1 and more than zero for the rest of the fields. So it
> is an inverse match.
>
> Because the values change all the time, we could not write a 'fixed'
> stored procedure. We use server side (asp) to build the query on the
> web server and then send it to the database server.
>
> The query varies, depending on the values, but it is something like:
>
> SELECT * FROM users WHERE counter1=0 AND (counter2>0 OR counter3>0 OR
> counter4>0 OR counter5>0 OR counter6>0 OR ..... counter50>0)
>
> The query returns the values we need but the server jumps to 100%
> utilization so we need to find out if there's a way of doing the same
> query more efficient.
>
> Thanks a lot

[Luis Jahn]

Thanks for your answer, I know normalization would help, but we cannot
make any modifications to the database now since this is a production
environment with over 2500 stored procedures.

We need to make the same query more gentle on the server, without
changing the database.

Thanks



*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Steve Kass]

Luis,

If the users table doesn't change often, you could consider
putting a nonclustered index on each of the 50 counter columns.


Steve Kass
Drew University


Luis wrote:

>Hello, we have a users table with the following fields:
>
>client_id varchar 20
>counter1 int
>counter2 int
>.
>.
>.
>counter50 int
>
>We need to do some queries that match counters from different users.
>For example: user admin1 has a value of 5 in counter1 and zero for the
>rest of the counter fields. We would need to find out what users have
>zero for counter1 and more than zero for the rest of the fields. So it
>is an inverse match.
>
>Because the values change all the time, we could not write a 'fixed'
>stored procedure. We use server side (asp) to build the query on the
>web server and then send it to the database server.
>
>The query varies, depending on the values, but it is something like:
>
>SELECT * FROM users WHERE counter1=0 AND (counter2>0 OR counter3>0 OR
>counter4>0 OR counter5>0 OR counter6>0 OR ..... counter50>0)
>
>The query returns the values we need but the server jumps to 100%
>utilization so we need to find out if there's a way of doing the same
>query more efficient.
>
>Thanks a lot
>
>