Professional Web Applications Themes

Stored procedure help, makes server jump to 100% - Microsoft SQL / MS SQL Server

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

  1. #1

    Default Stored procedure help, makes server jump to 100%

    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 Guest

  2. #2

    Default Re: Stored procedure help, makes server jump to 100%

    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" <luisdacorporation.com> a écrit dans le message de news: [email]573cddf1.0306301034.33823423posting.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
    Guest

  3. #3

    Default Re: Stored procedure help, makes server jump to 100%

    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!
    Luis Jahn Guest

  4. #4

    Default Re: Stored procedure help, makes server jump to 100%

    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
    >
    >
    Steve Kass Guest

Similar Threads

  1. SQL Server Stored Procedure Authentication
    By airnewhouse in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 9th, 09:19 PM
  2. RecordCount with Stored Procedure in SQL Server
    By Ken VdB in forum ASP Database
    Replies: 8
    Last Post: October 21st, 03:01 PM
  3. Legacy ASP, SQL Server, Paging Stored Procedure - resend
    By Bob Barrows in forum ASP Database
    Replies: 1
    Last Post: September 30th, 05:36 PM
  4. Replies: 7
    Last Post: September 11th, 07:03 PM
  5. Replies: 3
    Last Post: July 21st, 07:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139