January 27, 2009 at 4:42 am
Hello,
Can anyone take a check on what should i change in the following query to improve the output.
I think there's a problem with so much convert's but there's a count(*).
Can anyone give me one brilliant idea to start improving the execution time of this query? We've got any queries like this one.
What kind of things that i should look? :sick:
Sorry, but i need help on this point.
Thanks and regards,
JMSM :blush: 😉
SELECT DTS_NUM=convert(varchar(5),
batch.NUM_ORDEM),
NUM_DTSS=count(*),
STEP_ACCAO_FALHA=convert(smallint,1),
DTSS_2RUN='|'
FROM PTC_BI..BI10X_BATCH_PARAM batch
WHERE batch.IND_ACTIVO = 1 AND
batch.COD_BATCH = 'CARREGAMENTO_VENDAS_EMP1'
GROUP BY convert(varchar(5),batch.NUM_ORDEM)
ORDER BY convert(varchar(5),batch.NUM_ORDEM)
January 27, 2009 at 4:47 am
Hi there,
Please could you save the execution plan from SSMS and post it here as a file.
Also could you let us know the index definitions on this table and the size of the table.
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 27, 2009 at 5:32 am
Yeah, please post the execution plan.
Why would you do this in the GROUP BY clause?
convert(varchar(5),batch.NUM_ORDEM)
I suspect that's going to force a table scan.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 27, 2009 at 5:39 am
Hello again,
Here goes the execution plan (ExecPlanQ1.zip).
Indexes that are created on this table are the following (i'm sending the output of the script generated)
There's only one PK Cluster Index.
ALTER TABLE [dbo].[BI10X_BATCH_PARAM] ADD CONSTRAINT [PK_BI10X_BATCH_PARAM] PRIMARY KEY CLUSTERED
(
[COD_BATCH] ASC,
[COD_DTS] ASC,
[COD_MODULO] ASC,
[CODS_STEP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Hope u can help me.
Thanks and regards,
JMSM 😉
January 27, 2009 at 6:08 am
That's an estimated execution plan. Can you post the actual execution plan? It's especially important to see the estimated vs. actual rows.
On first blush, you might want to put an index on NUM_ORDEM, but I'd strongly recommend, if you do, that you don't format the code for the ORDER or GROUP BY clauses.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 27, 2009 at 7:19 am
Hello again,
Can you tell me how can i get the actual execution plan?
Sorry for this lazy question..
Thanks and regards
JMSM 😉
January 27, 2009 at 7:20 am
Jorge Mendes (1/27/2009)
There's only one PK Cluster Index.
So no nonclustered indexes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2009 at 7:22 am
Jorge Mendes (1/27/2009)
Can you tell me how can i get the actual execution plan?
Run the query with the execution plan option on (button's a little further along the toolbar from the estimated plan button) Save, zip and attach the same way as you did with the estimated plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2009 at 7:41 am
Hello,
Here goes the actual execution plan (Actual_Execution_Plan.zip) attached to this post.
Once more, thanks and regards.
JMSM 😉
January 27, 2009 at 7:54 am
How about an Index on Cod_Batch and IND_ACTIVO with an includes for NUM_ORDEM?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 27, 2009 at 7:56 am
Also it seems the sort is most costly thing in your query so, I guess I need to ask why you have the current clustered index set up?
Could you clustered index not be on the NUM_ORDEM?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 27, 2009 at 8:02 am
Just one question first. Why do you need to order by the varchar conversion of the NUM_ORDEM? Is there any reason why ordering by the NUM_ORDEM alone wouldn't work?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2009 at 8:08 am
Christopher Stobbs (1/27/2009)
How about an Index on Cod_Batch and IND_ACTIVO with an includes for NUM_ORDEM?
If it's possible to change the group by and order by to be just the num_ordem, instead of the conversion of it to varchar, then a nonclustered index on CodBatch, Ind_Activo and num_ordem in the key (in that order) will satisfy the index seek and the order by all in one (and get a stream aggregate as a bonus)
Try changing the query to this, and then adding an index (nonclustered) on all three columns, and see if that helps
SELECT convert(varchar(5),batch.NUM_ORDEM) AS DTS_NUM,
count(*) AS NUM_DTSS,
convert(smallint,1) AS STEP_ACCAO_FALHA,
'|' AS DTSS_2RUN
FROM PTC_BI..BI10X_BATCH_PARAM batch
WHERE batch.IND_ACTIVO = 1
AND batch.COD_BATCH = 'CARREGAMENTO_VENDAS_EMP1'
GROUP BY batch.NUM_ORDEM
ORDER BY batch.NUM_ORDEM
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2009 at 8:33 am
Thanks Gail,
Would there be a big difference between using the num_ordem in the Key as to having it in an Includes?
normally without a aggregate I think an includes would satisfy this query, but I'm guessing that because there is an aggregate it's better to have the returned/Grouped field in the key?
thanks
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 27, 2009 at 8:48 am
Christopher Stobbs (1/27/2009)
Would there be a big difference between using the num_ordem in the Key as to having it in an Includes?
In this case, yes. With it in the key, it means that the index key is ordered first by COD_BATCH then, within that, by IND_ACTIVO and then within that by NUM_ORDEM.
It means that, because both of the filters are equalities, once the filters have been applied, the resultant rows are ordered by NUM_ORDEM. It means that no sort is required to satisfy the ORDER BY. It's already satisfied by the index key order and the optimiser, knowing that, will ensure that all operations are order-preserving.
With NUM_ORDEM just in the include, the order wouldn't be enforced by the index and hence a sort would be required.
It's what I'd probably call an advanced indexing trick, it can't always be done, but when it can, it's very advantageous.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply