The query is the following select statement:

select tbl1."DT_REF_TRANS", tbl1."VL_MTM",
       tbl1."VL_FUTURO", tbl1."DT_VENCIMENTO",
       tbl1."QT_PRAZODIASU", tbl1."ID_ATIVO_PASSIVO",
       tbl1."ID_CARTEIRA", tbl1."VL_MTM_ORIGEM",
       tbl1."ID_LOCAL_REGISTRO", tbl1."CD_NIVEL",
       tbl2."NO_ARQUIVO", tbl3."NO_PRODUTO",
       tbl3."ID_ENTRA_2804", tbl3."ID_ENTRA_VAR",
       tbl4."NO_MOEDA", tbl6."NO_SUBGRUPO",
       tbl5."NO_GRUPO_2804", tbl5."PERDA",
       tbl5."RENOVACAO", tbl5."PRZRENOV",
       tbl5."PRZANTECDU", tbl5."ANTECIPA",
       tbl5."DEPOSMAR", tbl5."RESERVA",
       tbl5."CALCATRASO", tbl5."NORMAL",
       tbl5."CODREGRA", tbl5."CODPRZAJU",
       tbl8."NO_GRUPO_2804" as "GRUPOS_2804_NO_GRUPO_2804",
  from ((((((((("dbo".tbl1 tbl1
  join "dbo".tbl2 tbl2
       on (tbl2."CD_ARQUIVO" = tbl1."CD_ARQ_ORIGEM"))
  join "dbo".tbl3 tbl3
       on (tbl3."CD_PRODUTO" = tbl1."CD_PRODUTO"))
  join "dbo".tbl4 tbl4
       on (tbl4."CD_MOEDA" = tbl1."CD_MOEDA"))
  join "dbo".tbl6 tbl6
       on (tbl6."CD_SUBGRUPO" = tbl3."CD_SUBGRUPO"))
  join "dbo".tbl5 tbl5
       on (tbl5."CD_GRUPO_2804" = tbl3."CD_GRUPO_2804"))
  join "dbo".tbl7 tbl7
       on (tbl7."CD_USUARIO_SIST" = tbl5."CD_USUARIO_SIST"))
  join "dbo".tbl8 tbl8
       on (tbl8."CD_GRUPO_2804" = tbl3."CD_GRUPO_2804"))
  inner join "dbo".tbl9 tbl9
       on (tbl9."CD_GRUPO" = tbl3."CD_GRUPO"))
  inner join "dbo"."USUARIOS" "USUARIOS"
       on ("USUARIOS"."CD_USUARIO" = tbl1."CD_NIVEL"))

You can see the execution plan here.

I've created and dropped indexes in columns inside the joins, have been testing with some hints ( like left loop join for example ( it helped me a lot in the past )). but, is there a way to improve a query that's not using where, or nothing else?

It's running in 30Seconds, returning 1M rows+.

Should I use IN somewhere? Exists? Some hint I don't know?

See, the question is not "hot to update THIS query". I would like to know tips to improve queries using this structure in general.

  • 2
    I don't understand what all the brackets are for. Does it work the same without them? The best index for a table is unique nonclustered on the key column of the join and INCLUDE(the other columns that are output in the query) - at least in a star schema. – Robert Carnegie Jun 23 '17 at 14:21

Many outer joins are not a best option when dealing with big tables. In a nutshell, your plan might not be as optimal as it could be when using outer joins.

Wrong RENDA_FIXA table estimation is the reason why you are getting Parallelism and Hash joins everywhere even though you are not expecting many rows in the first place.

You can try OPTION (MAXDOP 1) hint to see if that any helps. Also you should check statistics on RENDA_FIXA and GRUPOS_USER_2804 tables.

The reason why you are getting that plan is because it expects 120,000 records, instead you're getting 1,800 that's why left loop joins helped you.

My advice is to try to deal with RENDA_FIXA table first, get the correct estimations out first, so the query optimizer could try to create a right plan.

| improve this answer | |

Query hints can help but not always. For example, let's say you have many clients, with different data and DB size, and one with a very big database with lots of data.

The query hint you use to help improve performance on one client's database might decrease performance on another client's database.

You should always analyze the Execution plan. Look maybe for missing indexes. Are all the joins necessary? Can you change some LEFT JOIN for INNER JOIN? Are the statistics up to date?

Many factors can impact your query performance

Your query plan shows a clustered index scan of RENDA_FIXA.RENDA_FIXA_PK, with an estimanted number of rows of 128 662 and Actual number of Rows 1796.
I would check to see if statistics are up to date.
That scan takes 38% of your execution plan by itself.

| improve this answer | |
  • Yes I double checked statistics before. it's all up to date. renda_fixa is the heavier table here a,d I can't think in something to improve performance on it. I think this is the b est the query can do. – Racer SQL Jun 22 '17 at 17:59
  • Well the hash match is taking some cost here. So maybe if you specify query hints you can improve it. Do you have many clients? Or the query will always be run against the same database? If it's against the same database always, you can try to specify hints. What is the server MAXDOP setting? – Danielle Paquette-Harvey Jun 22 '17 at 18:02
  • Yep, those hints I was using was just for test. I don't like to use hints. The maxdop setting is 0 here, but even using 4 ( that is the appropriated here ) the query runs in the same time.I tried to change the order of some joins but it's the same. Some questions here says that INS or INNERS are better but I don't see any results. – Racer SQL Jun 22 '17 at 18:19
  • Inner joins CAN help but not all the time. And they change the result of your query. I've seen queries where you re-write using "WITH" instead of multiple subqueries getting better performances, but your query is fairly simple. Without limiting your query with a "WHERE" clause, I don't see how you can improve performance, except maybe forcing a LOOP JOIN or something like that. – Danielle Paquette-Harvey Jun 22 '17 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.