1

I don't know what else can I do to improve the speed of this query. I have created non-clustered index but the result is the same. I have a lot of data and Azure is giving me some DTU problems.

What Index would you recommend? 

SELECT 
    SUM(t.total_amount) as SumaAmount,
        COUNT(t.id) as TotalTransaccions
FROM 
    rtv_turnover_transaction as t 
    INNER JOIN tills as till ON till.ID=t.till_id
    INNER JOIN rtv_trans_articles as ta ON t.transaction_id=ta.transaction_id
    INNER JOIN articles as art ON art.id=ta.article_id
    INNER JOIN groups ON groups.id=art.group_a_id
    INNER JOIN Clients as s ON s.id=t.cliente_id
    INNER JOIN rtv_transactions as tr ON tr.id=t.transaction_id
    INNER JOIN Ubicacion as u ON t.ubicacio_id=u.id 
    INNER JOIN Operadores as o ON t.operador_id=o.ID
where 
    tr.card_num IS NULL 
    and t.trans_date >= '2018-08-01' and t.trans_date >= '2018-09-01'

Hope you can help me. I am newbie with databases.

Thom A
  • 88,727
  • 11
  • 45
  • 75
talendguy
  • 81
  • 1
  • 9
  • What index(es) do you have (both `CLUSTERED` and `NONCLUSTERED`)? Please include their DDL too. – Thom A Oct 16 '18 at 11:15
  • 2
    Could you upload an actual execution plan to this site? https://www.brentozar.com/pastetheplan/ – MJH Oct 16 '18 at 11:17
  • It would help to give us the output of your query plan: https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan – cbp Oct 16 '18 at 11:17
  • 5
    You may want to check your WHERE clause: `and t.trans_date >= '2018-08-01' and t.trans_date >= '2018-09-01'` – MJH Oct 16 '18 at 11:18
  • I doubt between is quicker in execution. It evaluates to two inequalities. It's more handy, though. – George Menoutis Oct 16 '18 at 11:24
  • 1
    Why do you need all these joins? Get rid of the ones that serve no useful purpose. – SMor Oct 16 '18 at 12:25

1 Answers1

1

@talendguy,first tell us your requirement.Wht is the need to join so many table only to get count and sum from one table.

Revise carefully are those each table require.

From your query it appear that, most of the tables are related to rtv_turnover_transaction.

Create non clustered index on all those columns of rtv_turnover_transaction which are use in join condition.

You can leave those columns which belong to small table.

If still your query is slow then Define PK-FK relationship and Trusted Check constraint.

This way no need of creating so many index.Trusted relationship help optimiser

When primary and foreign keys are defined as constraints in the database schema, the server can use that information to create optimal execution plans.

Further Reading

create separate Non Clustered Index on trans_date

In table rtv_transactions create non clustered index on card_num

Also if READ UNCOMMITTED Data is not a problem or there is no chance of READ UNCOMMITTED Data then you can safely use WITH (NOLOCK) hint in all table.

rtv_turnover_transaction WITH (NOLOCK)

Note : I am not telling you to blindly use WITH (NOLOCK) hint, but if circumstances allow you then it is very performant.

You can read Benefit and Limitation of Nolock.

KumarHarsh
  • 5,046
  • 1
  • 18
  • 22
  • I think where clause is typo mistake – KumarHarsh Oct 16 '18 at 11:57
  • How does a FK negate the need for indexing? That is wrong. And why are you suggesting they use NOLOCK? That is simply bad advice, especially for a system that appears to be dealing with money of some sort. That hint is NOT performance tool that doesn't have cost. It can and will randomly return missing and/or duplicate rows along with a host of other "features". https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/ – Sean Lange Oct 16 '18 at 13:35
  • I will reply about FK negate the need of indexing later ,because explanation need time.I never said to use WITH (NOLOCK) straight away.Read my statement carefully .Nolock is not always bad, in some scenario it can be very safely use. – KumarHarsh Oct 17 '18 at 03:17
  • NOLOCK can be useful **very** occasionally. And never when you care about the accuracy of the results. You suggested using that and didn't offer any warnings about how bad it can be. And the indexing thing as posted is simply wrong. Maybe a language barrier but the way it reads is incorrect. – Sean Lange Oct 17 '18 at 13:07