November 8, 2010 at 10:46 pm
Hi
I have a problem sorting a View in SQLServer 2005. When I join 3 tables the sql is fast but when I add an 'order by' the query goes from 00:00:01 to 00:07:26. It returns 36240 records.
I have changed this in so many ways with no luck. Here's the sql with the order by:
SELECT c.CATEGORY_NAME, YEAR(a.INSTALLATION_DATE) AS [Year of Construction],
a.REPLACEMENT_VALUE AS [Replacement Cost]
FROM dbo.AMS_ASSETREGISTER a INNER JOIN
dbo.AMS_CATEGORY c ON a.CATEGORY_ID = c.CATEGORY_ID INNER JOIN
dbo.AMS_FIN_LINK f ON a.ASSET_SYS = f.asset_sys
order by c.CATEGORY_NAME
Take the order by statement off and it's all good.
Any ideas are appreciated.
Bruce
November 8, 2010 at 10:52 pm
Do you have any indexes on order by c.CATEGORY_NAME ?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
November 8, 2010 at 10:54 pm
Check the Actual Execution Plan to see if the ORDER BY is the real problem. Most probably it might be but its always better to confirm.
If its confirmed that the ORDER BY is the real problem, then try adding a Clustered Index on the order by column. This will increase the speed as the column will already be in a sorted order. Also check if the ORDER BY is really necessary or you can do without it.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 8, 2010 at 11:05 pm
when I check the execution plan it has the sort as 2% and the clustered index scan on the ams_fin_link table as the killer on 68% no matter how I change the links between tables.
If I take off the order by ams_fin_link table is 67% without the sort.
Bruce
November 8, 2010 at 11:08 pm
Test system, I hope? If so, try this:
CREATE INDEX idx_testing_orderby_AMS_CATEGORY ON AMS_Category ( Category_Name, Category_ID)
if that alone doesn't work, alter your JOIN to INNER JOIN dbo.AMS_CATEGORY c WITH (INDEX( idx_testing_orderby_AMS_CATEGORY))
Paraphrase that for any parenthetical errors. See if that helps your response time. The forced index is not pretty but may help.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2010 at 11:08 pm
do you have any indexes on ams_fin_link??
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
November 8, 2010 at 11:10 pm
oh yeah, the tables belong to an application so I can't change any indexes.
November 8, 2010 at 11:12 pm
Get your application Vendor to provide support on their tables.
Vendors usually provide scripts to modify their own tables and structures, they should be able to assist.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
November 8, 2010 at 11:12 pm
Bruce-12445 (11/8/2010)
oh yeah, the tables belong to an application so I can't change any indexes.
Then you're relatively screwed.
You can try dumping it all to a temp table and working it from there, see if that speeds things up.
Basically SELECT ... into #tmp FROM ...
SELECT * FROM #tmp ORDER BY colA.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2010 at 11:29 pm
I didn't try the temp table but that works, thanks for that.
I'll also see if the vendor will allow adding indexes.
If I use a view I obviously can't use a temp table, can I index a view? I was asked this by the guy that wants the data.
Bruce
November 8, 2010 at 11:32 pm
Bruce-12445 (11/8/2010)
I didn't try the temp table but that works, thanks for that.I'll also see if the vendor will allow adding indexes.
If I use a view I obviously can't use a temp table, can I index a view? I was asked this by the guy that wants the data.
Bruce
You *can* index a view. You don't do it for a one off query. If this thing is called every 10 seconds, maybe. Every change to the underlying tables on those columns alters the view. It's a pretty big overhead, make sure it's worth it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2010 at 11:39 pm
Yes, thanks for that.
Would these record counts make any difference to your indexing changes:
select * from dbo.AMS_ASSETREGISTER --66451
select * from dbo.AMS_CATEGORY--115
select * from dbo.AMS_FIN_LINK -- 36240
Bruce
November 9, 2010 at 10:11 am
Bruce-12445 (11/8/2010)
Yes, thanks for that.Would these record counts make any difference to your indexing changes:
select * from dbo.AMS_ASSETREGISTER --66451
select * from dbo.AMS_CATEGORY--115
select * from dbo.AMS_FIN_LINK -- 36240
Bruce
Need to see the full table DDL and existing indexes first.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 9, 2010 at 2:46 pm
I'll skip that, it will be the vendors problem. If it's only to be used in reports I can possibly create the view without the order by and sort it in the report designer. And the temp table could be used.
Thanks for your help, much appreciated.
Bruce
November 9, 2010 at 10:06 pm
select * from
(
SELECT c.CATEGORY_NAME, YEAR(a.INSTALLATION_DATE) AS [Year of Construction],
a.REPLACEMENT_VALUE AS [Replacement Cost]
FROM dbo.AMS_ASSETREGISTER a INNER JOIN
dbo.AMS_CATEGORY c ON a.CATEGORY_ID = c.CATEGORY_ID INNER JOIN
dbo.AMS_FIN_LINK f ON a.ASSET_SYS = f.asset_sys
) as a
order by NAME
this way i believe it won't rescan the original table but order only the records try this.
It should process the order by after it gathers the records
whats inside the parenthesis is done first always....
Eric...
Your way it had to calc every line too many times to get result.
first perform calcs then sort is what i show above.
Hope this helps you
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply