Sql Query Optimization - 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

SQL Server Development (2000)

Sql Query Optimization

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2004-06-15 : 18:55:53

Is there a better way to write this sql query? It takes 3.7 seconds to run.

SELECT Top 25 CatNumber as Catalog, SectionID, Publish
FROM Bookstore_TopX x INNER JOIN Stock s ON x.CatNumber = substring(s.Number, 1, 5)
WHERE SectionID = 1 AND (s.Units - s.QtyReserved > 0)
AND Publish = 1
order by Placement ASC

Thanks in advance.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-06-15 : 19:00:54

[code]SELECT Top 25 CatNumber as Catalog, SectionID, Publish
FROM Bookstore_TopX x
INNER JOIN
(
SELECT
substring(s.Number, 1, 5) CatNumber,
(s.Units - s.QtyReserved) Units
FROM
Stock s
) d
ON x.CatNumber = d.CatNumber
WHERE SectionID = 1 AND d.Units > 0
AND Publish = 1
ORDER BY Placement ASC[/code]

RichardSteele
Posting Yak Master

160 Posts

Posted - 2004-06-15 : 19:34:10

Thanks, however this works in sql but doesn't work as a Coldfusion query. Error Executing Database Query is the error message.
There must be some incompatibility in the code that Coldfusion can't parse. Any ideas?

Kristen
Test

22859 Posts

Posted - 2004-06-16 : 04:25:13

Write it as a stored procedure and call that from cold fusion?

Kristen

RichardSteele
Posting Yak Master

160 Posts

Posted - 2004-06-16 : 12:10:55

Ok, I renamed catnumber to catnum and all is well.

However, this revised query is just as slow as the previous query. As the top x increases, the query takes longer and longer to process by a fair degree.

Any other ideas as to how to speed this up? Thanks in advance.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-06-16 : 12:20:20

I suspect the reason it is slow is due to the the lines

FROM Bookstore_TopX x INNER JOIN Stock s ON x.CatNumber = substring(s.Number, 1, 5)

and

(s.Units - s.QtyReserved > 0)

The reason being that your using a function and an expression so indexes will not be used on this columns.

Could you maybe replace this with

SELECT Top 25 CatNumber as Catalog, SectionID, Publish
FROM Bookstore_TopX x INNER JOIN Stock s ON x.CatNumber LIKE s.Number WHERE SectionID = 1 AND s.UnitsOnHand > 0
AND Publish = 1
order by Placement ASC

But for this to work you would probably have to make changes to your database.

Dustin Michaels

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-16 : 13:06:02

This help?


SELECT Top 25 CatNumber as Catalog, SectionID, Publish
FROM Bookstore_TopX x INNER JOIN Stock s ON x.CatNumber = substring(s.Number, 1, 5)
     AND s.Units > s.QtyReserved --moved here to hopefully reduce rows involved in JOIN
WHERE SectionID = 1 
AND Publish = 1
order by Placement ASC

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-16 : 13:30:04

If you can modify the table definition, you can create computed columns and index them:

alter table stocks add catnum as cast (left(number,5) as varchar(5)), unitsonhand as ( Units - QtyReserved )
create index catnum_idx on stocks (catnum)
create index uoh_idx on stocks (unitsonhand)

Then use:

SELECT Top 25 CatNumber as Catalog, SectionID, Publish
FROM Bookstore_TopX x INNER JOIN Stock s ON x.CatNumber = s.catnum
WHERE SectionID = 1 AND unitsonhand > 0
AND Publish = 1
order by Placement ASC

If you don't want to modify the table, you can create an indexed view:

SET ARITHABORT ON -- and all client connections will also have to have this set OR
-- it can be set as the database default with: ALTER DATABASE mydb SET ARITHABORT ON

CREATE VIEW v_sb_Stocks with schemabinding as
SELECT stockid, col2, ..., left(number,5) as catnum, (Units - QtyReserved) as unitsonhand,
FROM dbo.Stocks

CREATE UNIQUE CLUSTERED INDEX v_sb_Stocks_ID_idx on v_sb_Stocks (stockid)
Create INDEX v_sb_Stocks_catnum_idx on v_sb_Stocks (catnum)
Create INDEX v_sb_Stocks_ouh_idx on v_sb_Stocks (unitsonhand)

Then use:

SELECT Top 25 CatNumber as Catalog, SectionID, Publish
FROM Bookstore_TopX x INNER JOIN v_sb_Stocks s ON x.CatNumber = s.catnum
WHERE SectionID = 1 AND s.unitsonhand > 0
AND Publish = 1
order by Placement ASC

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-16 : 13:37:44

What's the query plan show?

What about the DDL and indexes?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources