t-sql help - 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 2005 Forums

Transact-SQL (2005)

t-sql help

Author

Topic

harpreetkaur
Starting Member

14 Posts

Posted - 2007-02-08 : 20:13:24

My sql below returns like 10,000 records and takes like 22 seconds. Is this
normal or can I tune it to run faster.

SELECT s.story_id, spec.title as title, s.title AS story_name,
CONVERT(char(10), u.air_date, 101) AS rundown_date,
'' AS video, '' AS cg_text,i.text AS item_text, i.type,
i.content_status, k.keyword, i.editorial_description AS description,
d.description AS notes, s.editor AS creator, i.original_material_id AS
clipname,
i.ar_material_id AS material_id FROM
na_rundown_state_view AS u
INNER JOIN na_rundown_tbl AS spec ON u.rundown_id = spec.rundown_id
INNER JOIN na_story_tbl AS s ON s.rundown_id = u.rundown_id
LEFT OUTER JOIN na_item_tbl AS i ON s.story_id = i.story_id
LEFT OUTER JOIN na_itemkeyword_tbl AS k ON i.item_id = k.item_id
LEFT OUTER JOIN na_itemdesc_tbl AS d ON i.item_id = d.item_id
where Convert(datetime,u.air_date) >= '02/08/2006' and Convert(datetime,u.air_date) <= '02/08/2007'

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-02-08 : 20:47:32

make sure your index's are setup properly on all of the following fields along with the tables that make up your view.

na_rundown_tbl.Rundown_ID
na_Story_tbl.Rundown_ID
na_Story_tbl.Story_ID
na_item_tbl.story_ID
na_item_tbl..Item_ID
na_ItemKeyword_tbl.Item_ID
ma_ItemDesc_tbl.Item_ID

If they are propely configured, then 22 second may be accurrate taking into consideration the processer speed, amount of records, memory, free space, and so on, but my assumption is you'll be able to speed it up quite a bit.

harpreetkaur
Starting Member

14 Posts

Posted - 2007-02-08 : 21:35:12

Actually if i use top 400 in my select the main query takes like couple seconds but if i use a where claue it takes more then 25 seconds.

Query is below
My query below takes a couple seconds when run without the where clause. When run with the where clause it takes like 25 seconds. How can I improve it.

SELECT top 400 s.story_id, spec.title as title, s.title AS story_name, CONVERT(char(10), u.air_date, 101) AS rundown_date,'' AS video, '' AS cg_text,i.text AS item_text, i.type, i.content_status, k.keyword, i.editorial_description AS description, d.description AS notes, s.editor AS creator, i.original_material_id AS clipname, i.ar_material_id AS material_id FROM

na_rundown_state_view AS u

INNER JOIN na_rundown_tbl AS spec ON u.rundown_id = spec.rundown_id

INNER JOIN na_story_tbl AS s ON s.rundown_id = u.rundown_id

LEFT OUTER JOIN na_item_tbl AS i ON s.story_id = i.story_id

LEFT OUTER JOIN na_itemkeyword_tbl AS k ON i.item_id = k.item_id

LEFT OUTER JOIN na_itemdesc_tbl AS d ON i.item_id = d.item_id

where ((contains(s.title, 'midlothian')) or (contains(spec.title, 'midlothian'))

or (contains(s.text, 'midlothian')) or ( contains(i.text, 'midlothian')) or (contains(k.keyword, 'midlothian'))

or (contains(d.description, 'midlothian')) or (contains(i.editorial_description, 'midlothian'))

or (contains(i.original_material_id, 'midlothian'))or (contains(s.editor, 'midlothian') ))

and i.type=1 and i.content_status=1

harpreetkaur
Starting Member

14 Posts

Posted - 2007-02-11 : 02:42:56

I am at a point where my sql takes like 3 seconds when run without the otder by clause. But when I add an order by clause it takes like 18 seconds. How can i order my results by a field and still have my query return in like 3 seconds or less.

SQL as follows
select top 400 s.story_id, spec.title as title, s.title AS story_name, u.state,
CONVERT(char(10), u.air_date, 101) AS rundown_date,'' AS video, '' AS cg_text,
SUBSTRING(s.text,1,100) AS script,i.text AS item_text, i.type, i.content_status,
k.keyword, i.editorial_description AS description, d.description AS notes,
s.editor AS creator, i.original_material_id AS clipname, i.ar_material_id AS material_id
FROM na_rundown_state_view AS u
INNER JOIN na_rundown_tbl AS spec ON u.rundown_id = spec.rundown_id
INNER JOIN na_story_tbl AS s ON spec.rundown_id = s.rundown_id
LEFT OUTER JOIN na_item_tbl AS i ON s.story_id = i.story_id
LEFT OUTER JOIN na_itemkeyword_tbl AS k ON i.item_id = k.item_id
LEFT OUTER JOIN na_itemdesc_tbl AS d ON i.item_id = d.item_id
where i.type=1 and i.content_status=1
and ((contains(s.*, 'midlothian')) or (contains(spec.title, 'midlothian')) or
(contains(i.*, 'midlothian')) or (contains(k.keyword, 'midlothian')) or
(contains(d.description, 'midlothian') )) order by k. keyword

Kristen
Test

22859 Posts

Posted - 2007-02-11 : 05:27:14

When you do

SELECT TOP 400

SQL Server selects the first 400 rows it finds.

When you do:

SELECT TOP 400
...
ORDER BY k.keyword

SQL Server has to find ALL the rows, and then order them, and then throw away all-but the first 400 - so that's a lot more work if you have significantly more than 400 rows which match the query criteria

This is probably compounded because you are sorting on a column in an OUTER JOIN

Kristen

harpreetkaur
Starting Member

14 Posts

Posted - 2007-02-12 : 08:20:50

Can I use something like
SELECT *

FROM (SELECT TOP 400 … )

ORDER BY date

or any other ideas to sort the results returned in the previous query.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 08:28:45

Yes, you can.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-02-12 : 08:42:30

Note that unless the inner SELECT TOP 400 has an ORDER BY then SQL Server will "randomly" select 400 rows, and then ORDER BY date. (Note also that SQL Server will often appear to select the same 400 rows each time, but you should not rely on this because it won't be the case always)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources