optimisation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

optimisation

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2006-01-30 : 09:38:42

Do you know how this query can be optimised please?

SELECT
CODOCS01.ParentID, CODOCS01.DocType, CODOCS01.FileType,
CODOCS01.Status, CODOCS01.Description, CODOCS01.FileName,
CODOCS01.ModifiedBy, CODOCS01.ModifiedOn, CODOCS01.ID
FROM
CODOCS01 CROSS JOIN RIMAST01
WHERE
(CODOCS01.ParentID = N'NPI00004') OR
(RIMAST01.QuoteID = N'NPI00004 ') AND
(CODOCS01.ParentID = RIMAST01.ClientID)
GROUP BY
CODOCS01.ParentID, CODOCS01.DocType, CODOCS01.FileType,
CODOCS01.Status, CODOCS01.Description, CODOCS01.FileName,
CODOCS01.ModifiedBy, CODOCS01.ModifiedOn, CODOCS01.ID
HAVING
(NOT (CODOCS01.FileName IS NULL)) OR
(NOT (CODOCS01.FileName IS NULL))

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-30 : 09:43:01

I was gonna say, "Hire a DBA"

Why do you have a GROUP BY with no Scalar functions?

Read the hint link in my sig...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-30 : 11:08:26

quote:
Originally posted by fmardani
FROM
CODOCS01 CROSS JOIN RIMAST01
WHERE
(CODOCS01.ParentID = N'NPI00004') OR
(RIMAST01.QuoteID = N'NPI00004 ') AND
(CODOCS01.ParentID = RIMAST01.ClientID)

Do you really mean this?
I'd be amazed if AND and OR are parenthesized correctly.
It should probably say:


FROM CODOCS01
INNER JOIN RIMAST01 ON CODOCS01.ParentID = RIMAST01.ClientID
WHERE CODOCS01.ParentID = N'NPI00004'
   OR RIMAST01.QuoteID = N'NPI00004'

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-30 : 11:54:09

And since you are not getting anything from RIMAST01, why CROSS JOIN to it...and the group by (which aI really think you mean DISTINCT) will most likely nullify the CROSS JOIN in the first place.

Is this SQL from the kyber belt?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-30 : 12:23:02

I liked this bit too:

quote:
Originally posted by fmardani
HAVING
(NOT (CODOCS01.FileName IS NULL)) OR
(NOT (CODOCS01.FileName IS NULL))

Maybe SQL Server won't spot it the first time, so give it another chance?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-30 : 12:32:28

So, in summary, it should probably be:


SELECT DISTINCT
  C.ParentID, C.DocType, C.FileType, C.Status, C.Description,
  C.FileName, C.ModifiedBy, C.ModifiedOn, C.ID
FROM CODOCS01 AS C
INNER JOIN RIMAST01 AS R ON C.ParentID = R.ClientID
WHERE (C.ParentID = N'NPI00004' OR R.QuoteID = N'NPI00004') AND
  C.FileName IS NOT NULL

If ID is a candidate key of CODOCS01 and ParentID is a foreign key referencing RIMAST01.ClientID then the DISTINCT is unnecessary too.

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-30 : 13:14:09

Why is the join required?

You only want to find parental rows with children?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-30 : 13:34:20

quote:
Originally posted by X002548

Why is the join required?
You only want to find parental rows with children?

because of

... RIMAST01.QuoteID = N'NPI00004' ...

presumably. Why am I having this argument? It's not my code

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-30 : 13:41:11

missed that part...I was going blind

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2006-01-30 : 15:05:48

"missed that part...I was going blind"

A BlindMan will probably offer an opinion in a moment ...

Kristen

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-30 : 15:12:20

Yeah, probably about how much he doesn't like my one character table aliases.

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2006-01-30 : 16:29:45

Running the original query (My first post) returns four records whereas running the following query (someone suggested) returns only one.
SELECT DISTINCT
C.ParentID, C.DocType, C.FileType, C.Status, C.Description,
C.FileName, C.ModifiedBy, C.ModifiedOn, C.ID
FROM CODOCS01 AS C
INNER JOIN RIMAST01 AS R ON C.ParentID = R.ClientID
WHERE (C.ParentID = N'NPI00004' OR R.QuoteID = N'NPI00004') AND
C.FileName IS NOT NULL

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-30 : 16:43:44

> Running the original query (My first post) returns four records whereas running the following query (someone suggested) returns only one.

I'm not surprised. I did say that I didn't think the original query was returning what the person (or program) that wrote it wanted it to. So I made some assumptions and wrote something that, in the absence of any indication of what the query was supposed to do, any DDL for the tables, or the merest hint of example data, looked more plausible. It's possible I was wrong.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-31 : 01:30:51

>>A BlindMan will probably offer an opinion in a moment ...

But actaully he is b lindman

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources