Re: WHERE clause question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Zach Wells (no_zwells_spam_at_ain1.com)
Date: 04/05/04 

Date: Mon, 05 Apr 2004 14:09:11 -0400

Carl Imthurn wrote:

> I am having a bit of difficulty understanding the
> performance issue I'm facing, as follows:
>
> Here's the WHERE clause of my SQL statement:
>
> WHERE
> (DN.OFFICE_NUM = 3)
> AND
> (DN.ProcedureID < 9000 OR DN.ProcedureID > 9999)
> AND
> (DN.ProcedureID <> 5985)
> AND
> (DN.PostingDate BETWEEN @StartingDate AND @EndingDate)
>
> Fairly straightforward, I thought. However, it just runs and
> runs . . . I cancelled the stored procedure
> after 5 minutes or so and started troubleshooting, and
> here's what I've been able to find out:
>
> If I comment out either one of the above lines having to do
> with DN.ProcedureID, the statement runs to completion within
> 17 seconds,
> returning the correct records (for that condition only, of
> course).
> However, I need *both* conditions to be met ( (not between
> 9000 and 9999) AND <> 5985).
>
> So. I modified the WHERE clause as follows, ran it, and went
> and did something else. Came back about 20 minutes later,
> and lo and behold! it had run to completion, in 14 minutes
> and 5 seconds, returning the exact recordset I need.
>
> WHERE
> (DN.OFFICE_NUM = 3)
> AND
> (DN.ProcedureID <> 5985 AND NOT (DN.ProcedureID BETWEEN
> 9000 AND 9999))
> AND
> (DN.PostingDate BETWEEN @StartingDate AND @EndingDate)
>
> My question --
>
> What in the world am I doing wrong to make it run so slowly?
> The column ProcedureID is indexed.
> I don't know what else to check -- I don't know a lot about
> SQL Server performance tuning,
> so any and all suggestions/help/advice will be gratefully
> accepted.
>
> Thanks --
>
> Carl
>

Couple questions:
How many rows of data in the table?
How unique is the ProcedureID data in relation to the entire table?
What type of index is on the column, clustered or non-clustered?
What other indexes exist on the table?

Zach

Relevant Pages

  • RE: Queries returning with missing info (plus asks query question
    ... Let’s start with the simple query first. ... are aggregate functions, but not reserved words. ... what is at the front of the SQL statement. ... Parameter clause at the top. ...
    (microsoft.public.access.queries)
  • Re: formula help please
    ... primary and foreign keys are essentials in solving such problems. ... each of the tables are related to each other with each SQL statement. ... engine like Oracle), the program adds additional lines to the actual ... WHERE clause that describes how the tables are to be linked together. ...
    (comp.databases.oracle.misc)
  • Re: query based on form
    ... Typically it's only the WHERE clause of the query that changes so you can build that dynamically, patch it into the SQL statement, and assign it to the SQL property of the QueryDef you use for export. ... they can choose one Sales Rep, all cities, all states, all counties, just one type of business and one renewal month. ...
    (microsoft.public.access.queries)
  • Re: Using SQL in Sub to fill box on mouseclick
    ... Your SQL statement is incorrect. ... The WHERE clause comes before the GROUP BY clause. ... > filled with a query telling me which people are scheduled to work a shift. ... > Private Sub Ctl8a_4pb_Click ...
    (microsoft.public.access.formscoding)