Make T-Sql query with parameters FASTER

Question

First of all I want to say that I am really begginer with databases, don't judge me.

I am receiving a lot of parameters from a webservice and I have to do a sql query with those parameters. The thing is that my query consumes a lot of cpu because of my bad optimization of the query.

  SELECT 
  SUM(t.total_amount) as SumaAmount,
  COUNT(t.id)  as TotalTransaccions

    FROM RealTimeVending.dbo.rtv_turnover_transaction as t, 
    RealTimeVending.dbo.rtv_trans_articles as ta,
    RealTimeVending.dbo.articles as art, RealTimeVending.dbo.groups,
    RealTimeVending.dbo.Clients as s,
    RealTimeVending.dbo.rtv_transactions as tr, 
    RealTimeVending.dbo.tills as till, RealTimeVending.dbo.Ubicacion as u, 
    RealTimeVending.dbo.Operadores as o 

    where t.operador_id=o.ID and t.transaction_id=ta.transaction_id and 
    art.id=ta.article_id and s.id=t.cliente_id and tr.id=t.transaction_id 
    and groups.id=art.group_a_id and t.ubicacio_id=u.id 

    and convert(date,t.trans_date) >='"+globalMap.get("datainici")+"'and 
    convert(date,t.trans_date) <= '"+globalMap.get("datafinal")+"'and 
    and (s.codigo IS NULL or s.codigo like '%"+globalMap.get("client")+"%') 
    and (t.total_amount IS NULL or t.total_amount like'"+globalMap.get("amount")+"%')

Imagine that I have all the parameters from the webservice empty. The database would spend a lot of time with those parameters. What I would like to do is a query where I could just search for parameters which are not null. For example, if "t.total_amount" was empty, I don't want to insert the search on the query.

I hope you understand my problem. Thank you so much.

No need to apologise for being a beginner - in addition to the query, the schema of the tables, the indexes on the tables, and ideally also the query plan of the query would be needed for anyone to provide specific advice. Even without those, s.codigo like '%"+globalMap.get("client")+"%' is going to cause a problem ,full wildcard searches like that will always scan data — Andrew
– Andrew, Commented Aug 10, 2018 at 9:50
Tip of today: Switch to modern, explicit JOIN syntax! Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed. — jarlh
– jarlh, Commented Aug 10, 2018 at 9:56
This query uses variables with string concatenation instead of parameters. I can't comment on how to correctly parameterized it with talend but the query would contain parameter markers with the actual run-time values added to the command separately (e.g. convert(date,t.trans_date) >= ?). It would be best to refactor the query in order to avoid applying functions like convert to the column so that indexes can be used efficiently. — Dan Guzman
– Dan Guzman, Commented Aug 10, 2018 at 9:58

Richard Hansell · Accepted Answer · 2018-08-10 10:03:40Z

Here's a quick rewrite of your query to use modern JOIN syntax:

SELECT 
  SUM(t.total_amount) AS SumaAmount,
  COUNT(t.id) AS TotalTransaccions
FROM 
    RealTimeVending.dbo.rtv_turnover_transaction t
    INNER JOIN RealTimeVending.dbo.rtv_trans_articles ta ON ta.transaction_id = t.transaction_id
    INNER JOIN RealTimeVending.dbo.articles art ON art.id = ta.article_id
    INNER JOIN RealTimeVending.dbo.groups g ON g.id = art.group_id
    INNER JOIN RealTimeVending.dbo.Clients s ON s.id = t.cliente_id
    INNER JOIN RealTimeVending].dbo.rtv_transactions tr ON tr.id = t.transaction_id
    --INNER JOIN RealTimeVending.dbo.tills till (not used)
    INNER JOIN RealTimeVending.dbo.Ubicacion u ON u.id = t.ubicacio_id 
    INNER JOIN RealTimeVending.dbo.Operadores o ON o.id = t.operador_id
WHERE
    CONVERT(DATE, t.trans_date) >= '"+globalMap.get("datainici")+"' --is this really not already a date?
    AND CONVERT(DATE, t.trans_date) <= '"+globalMap.get("datafinal")+"'
    AND (s.codigo IS NULL or s.codigo LIKE '%"+globalMap.get("client")+"%') 
    AND (t.total_amount IS NULL or t.total_amount LIKE '"+globalMap.get("amount")+"%');

The first thing that sticks out to me is that your trans_date is being converted to a DATE for comparison, and this is probably inefficient. What would be better is if you could change this criteria to something like t.trans_date BETWEEN <dateinici> AND <datefinal> as this would then allow indexes to be used.

You should really be using parameters properly, as this helps to avoid SQL Injection.

Depending on the optimiser it should realise that there's no point in comparing a value if it's NULL and you have a constraint of x IS NULL OR x LIKE <something>.

It's probably worth taking a sample query and seeing what execution plan it uses?

Using modern JOIN syntax, it also becomes obvious that your tills table wasn't being used, so was essentially a CROSS JOIN, which would slow everything down a lot if it's a big table?

Cetin Basoz · Accepted Answer · 2018-08-10 10:25:32Z

Your SQL needs lots of correction.

Let's first dissect your SQL a bit:

FROM RealTimeVending.dbo.rtv_turnover_transaction as t, 
RealTimeVending.dbo.rtv_trans_articles as ta,
RealTimeVending.dbo.articles as art, RealTimeVending.dbo.groups,
RealTimeVending.dbo.Clients as s,
RealTimeVending].dbo.rtv_transactions as tr, 
RealTimeVending.dbo.tills as till, RealTimeVending.dbo.Ubicacion as u, 
RealTimeVending.dbo.Operadores as o 
where t.operador_id=o.ID and t.transaction_id=ta.transaction_id and 
art.id=ta.article_id and s.id=t.cliente_id and tr.id=t.transaction_id 
and groups.id=art.group_a_id and t.ubicacio_id=u.id 
and convert(date,t.trans_date) >='"+globalMap.get("datainici")+"'and 
convert(date,t.trans_date) <= '"+globalMap.get("datafinal")+"'and 
and (s.codigo IS NULL or s.codigo like '%"+globalMap.get("client")+"%') 
and (t.total_amount IS NULL or t.total_amount like'"+globalMap.get("amount")+"%')

You are using old style joins which makes it harder to read. You could rewrite it as (let's assume RealTimeVending was the current database):

FROM rtv_turnover_transaction as t
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  
cross join tills as till 
inner join Ubicacion as u on t.ubicacio_id=u.id
inner join Operadores as o on t.operador_id=o.ID

where
--convert(date,t.trans_date) >='"+globalMap.get("datainici")+"'and 
--convert(date,t.trans_date) <= '"+globalMap.get("datafinal")+"'and 
--and (s.codigo IS NULL or s.codigo like '%"+globalMap.get("client")+"%') 
--and (t.total_amount IS NULL or t.total_amount like'"+globalMap.get("amount")+"%')

Here you have inner joins with many tables and one CROSS JOIN that is completely (not only) useless. That cross join by itself is a cause for slowness. With a cross join, say:

t1 cross join t2

If t1 has 1000 rows and t2 has 10000 rows, you get 1000 * 10000 = 10,000,000 rows where each row on t1 is repeated 10,000 times (and t2 rows 1,000 times).

Next question is when you need fields from only rtv_turnover_transaction why do you join other tables? It may be on purpose no way to know without knowing schema and necessities. Likely they were used only for the purpose of (if EXISTS) check. If so you could add them as EXISTS queries in WHERE.

Then comes your WHERE clauses:

where
 convert(date,t.trans_date) >='"+globalMap.get("datainici")+"'and 
 convert(date,t.trans_date) <= '"+globalMap.get("datafinal")+"'and 
 and (s.codigo IS NULL or s.codigo like '%"+globalMap.get("client")+"%') 
 and (t.total_amount IS NULL or t.total_amount like'"+globalMap.get("amount")+"%')

Here why the:

convert(date,t.trans_date)

That would nullify the use of an existing index on trans_date and make it slow. Instead:

 t.trans_date >='"+globalMap.get("datainici")+"'and

is just fine if the globalMap.get("datainici") is returning a date or a datetime where time part is 00:00:00.

Then comes the most important one:

where
 convert(date,t.trans_date) >='"+globalMap.get("datainici")+"'and 
 convert(date,t.trans_date) <= '"+globalMap.get("datafinal")+"'and 
 and (s.codigo IS NULL or s.codigo like '%"+globalMap.get("client")+"%') 
 and (t.total_amount IS NULL or t.total_amount like'"+globalMap.get("amount")+"%')

You should NEVER build SQL queries like this by concatenation of strings. This is not only a well known SQL injection attack cause also it might cause you to define parameters wrong. Simple cure is to use PARAMETERS.

Then would come other optimizations.

Collectives™ on Stack Overflow

Make T-Sql query with parameters FASTER

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related