Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Slow running query Expand / Collapse
Author
Message
Posted Tuesday, January 14, 2014 7:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 216, Visits: 573
This is the query which takes about 32 seconds to execute. Any help will be appreciated.

Checked the index on **ID which is only 10% fragmented. No blocking issues.


SELECT fct.AccountID, count(*)
FROM fctAssetFulfillment fct
JOIN FulfillmentPartnerInstall fpi ON fpi.FMProBoxID = fct.AMC_BoxID
WHERE fct.Source = 'FMPro' AND isnumeric(fct.AMC_BoxID)<>0
AND fct.SerialNo = fpi.SerialTag
AND fpi.CompletionDate IS NOT NULL
AND fpi.AssetUsage LIKE '%install%'
AND fct.InstallActualDate<>fpi.CompletionDate
--AND (fct.FulfillmentStatus <> 'installed')
GROUP BY fct.AccountID




[i]"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
Post #1530661
Posted Tuesday, January 14, 2014 7:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 6,719, Visits: 13,828
Can you attach the actual execution plan please, saved as a .sqlplan file? Thanks.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1530662
Posted Tuesday, January 14, 2014 7:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 12,897, Visits: 32,101
for this query, i think we'd need to see the actual execution plan on this, as well as a list of all indexes on the fctAssetFulfillment and the FulfillmentPartnerInstall tables.

can you post that here as a .sqlplan attachment?

one problem is this portion of the WHERE statement:
AND fpi.AssetUsage LIKE '%install%'

'that's going to require a scan of the table; is there another way to filter the data to get the same rows? a status which coincides with that, maybe?

New Born DBA (1/14/2014)
This is the query which takes about 32 seconds to execute. Any help will be appreciated.

Checked the index on **ID which is only 10% fragmented. No blocking issues.


SELECT fct.AccountID, count(*)
FROM fctAssetFulfillment fct
JOIN FulfillmentPartnerInstall fpi ON fpi.FMProBoxID = fct.AMC_BoxID
WHERE fct.Source = 'FMPro' AND isnumeric(fct.AMC_BoxID)<>0
AND fct.SerialNo = fpi.SerialTag
AND fpi.CompletionDate IS NOT NULL
AND fpi.AssetUsage LIKE '%install%'
AND fct.InstallActualDate<>fpi.CompletionDate
--AND (fct.FulfillmentStatus <> 'installed')
GROUP BY fct.AccountID


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1530667
Posted Tuesday, January 14, 2014 7:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 216, Visits: 573
Does this help?



[i]"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]


  Post Attachments 
Plan 1.PNG (5 views, 46.06 KB)
Plan 2.PNG (2 views, 39.84 KB)
Post #1530674
Posted Tuesday, January 14, 2014 7:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 6,719, Visits: 13,828
No, they're just pictures. It's like asking for a car and being given a picture. Execution Plans are active - hover over objects to view properties etc.
Right-click on the execution plan (actual, not estimated) and select "save execution plan as..."


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1530676
Posted Tuesday, January 14, 2014 7:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 12,897, Visits: 32,101
not really;
the actual SQL plan allows us to review deep details of the plan, and tell if stats are out of date, which columns were used, which indexes, and so much more;
a picture of it's not enough.



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1530677
Posted Tuesday, January 14, 2014 8:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 216, Visits: 573
I did save the plan but I don't know how to copy it on my local machine.




[i]"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
Post #1530704
Posted Tuesday, January 14, 2014 8:16 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 3,575, Visits: 8,016
This should help you to post the information needed.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1530709
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse